As Published In
Oracle Magazine
March/April 2005

TECHNOLOGY: Inside OCP


Flashback for OCPs

By Aradhana Puri

More concepts and sample questions for OCP certification

Oracle Database 10g: Admini-stration I and Oracle Database 10g: Administration II are the two exams a candidate must take to achieve the Oracle Database 10g Administrator Certified Professional level of certification. This column focuses on topics pertaining to Flashback technology and presents sample questions of the type you may encounter when taking the Oracle Database 10g: Administration II exam. Note that the exam sample question format has been adjusted for presentation in this column.

Oracle9i Database introduced Flashback Query to provide a simple mechanism for recovering from user errors. It enables users to perform queries on the database as of a certain time or based on a user-specified system change number (SCN). Oracle Database 10g provides significant advances in the area of Flashback technology. Users can now perform easy and fast recovery from errors such as dropping a wrong table, running a batch job twice, or performing incorrect updates.

Flashback Drop

Before Oracle Database 10g, if you dropped a table by mistake, you had to recover the database to a prior time to retrieve the dropped table. The Oracle Database 10g Flashback Drop feature enables you to undo the effects of the DROP TABLE statement, without requiring you to perform a point-in-time recovery.

When you drop a table in Oracle Database 10g, the dropped table is temporarily placed in the recycle bin, a "virtual container" where the dropped objects reside.

You dropped the DEPT table, which belongs to the USER_DATA tablespace. The dropped DEPT table would occupy space in the ______.

A. SYSTEM tablespace
B. SYSAUX tablespace
C. Default permanent tablespace
D. USER_DATA tablespace

The correct answer is D. The dropped table and any associated objects, such as indexes and constraints, are not moved to a different tablespace; they are simply renamed with a system-generated name.

You can use the following command to recover a dropped table: 

FLASHBACK TABLE <table_name> TO BEFORE DROP;


Alternatively, you can use Oracle Enterprise Manager 10g to recover a dropped table. You can query the USER_RECYCLEBIN or DBA_RECYCLEBIN view to identify the objects that have been dropped by you or all users.

You can use the SHOW RECYCLEBIN command to view the objects that can be undropped.

Which statements are correct regarding the Flashback Drop feature? (Choose two.)

A. You can flash back a dropped table, provided the associated tablespace is offline.
B. When you issue the DROP TABLE <table_name> PURGE command, the table, as well as its associated objects, is stored in the recycle bin.
C. You can flash back a dropped table, provided the table is stored in a locally managed non- SYSTEM tablespace.
D. If another table with the same original name exists in your schema, you must use the RENAME TO clause with the FLASHBACK TABLE command while restoring a dropped table.

The correct answers are C and D. The recycle bin exists for the tables in locally managed non- SYSTEM tablespaces only. Oracle Database attempts to preserve the original table name when you undrop a table. If a new table of the same name has been created in the same schema since the original table was dropped, the database returns an error, unless you also specify the RENAME TO clause. For example: If you accidentally drop the HR.EMP table and want to retrieve it, issue the following statement: 

FLASHBACK TABLE hr.emp TO BEFORE DROP;


If another EMP table has been created in the HR schema, use the RENAME TO clause to rename the retrieved table: 

FLASHBACK TABLE hr.emp TO BEFORE DROP RENAME TO emp_old;


Answer A is incorrect, because you need not bring the associated tablespace offline to undrop a table. Answer B is incorrect, because when you use the DROP TABLE <table_name> PURGE command, the table is permanently dropped and is not moved to the recycle bin.

You created and dropped the dept_test table multiple times, as shown below: 

CREATE TABLE dept_test (dept_id number);
INSERT INTO dept_test values (1);
COMMIT;
DROP TABLE dept_test;

CREATE TABLE dept_test (dept_id number);

INSERT INTO dept_test values (2);
COMMIT;
DROP TABLE dept_test;
CREATE TABLE dept_test (dept_id number);
INSERT INTO dept_test values (3);
COMMIT;
DROP TABLE dept_test;


Then you executed the following command: 

FLASHBACK TABLE dept_test TO BEFORE DROP;


What would be the outcome of the FLASHBACK TABLE command above?

A. The table version with dept_id=3 would be recovered, because this was the most recently dropped version.
B. The table version with dept_id=1 would be recovered, because this was the first version dropped.
C. None of the table versions would be recovered, because there are multiple entries in the recycle bin with the same original name.
D. All the table versions would be recovered, each with a unique system-generated name.

The correct answer is A. If you specify the original table name and the recycle bin contains more than one object of that name, the object that is moved to the recycle bin most recently is recovered first. If you want to retrieve an older version of the table, you can specify the system-generated name of the table you want to retrieve. The recycle bin provides information about the original name and the system-generated name.

Flashback Versions Query

The Flashback Versions Query feature enables you to use the VERSIONS clause to retrieve all the versions of rows that exist between two points in time or between two SCNs. You can also perform a Flashback Versions Query by using Oracle Enterprise Manager 10g. Figure 1 shows a Flashback Versions Query result, including a list of SCNs, timestamps, transaction IDs, and operations.

 

figure 1
Figure 1: Oracle Enterprise Manager 10g Flashback Versions Query result


Why would you use the Flashback Versions Query feature?

A. To identify the versions of a table before the table was dropped
B. To flash back the table to a specified point in time in the past
C. To identify the inserts, deletes, and updates performed on a row and not the Data Definition Language (DDL) operations performed on the table
D. To identify the inserts, deletes, and updates performed on external tables, fixed tables, and temporary tables.

Next Steps


 ENROLL in classes at Oracle Education

GET certified
Oracle Database 10g administrator certification

The correct answer is C. You can use the Flashback Versions Query feature to identify the inserts, deletes, and updates performed on a row. The VERSIONS clause in a SELECT statement cannot produce versions across the DDL statements that change the structure of the table. That is, the Flashback Versions Query stops reporting rows once it hits a time when the table structure was changed.

Answer A is incorrect, because you cannot issue a Flashback Versions Query on a dropped table. Answer B is incorrect, because Flashback Versions Query does not flash back a table to a previous point in time. Answer D is incorrect, because Flashback Versions Query is not supported on external tables, fixed tables, and temporary tables.

You performed the following operations on the dept table on Monday: 

INSERT INTO dept (dept_id) values (4);
ROLLBACK;

INSERT INTO dept (dept_id) values (5);
INSERT INTO dept (dept_id) values (6);
COMMIT;

INSERT INTO dept (dept_id) values (7);
COMMIT;


You performed a Flashback Versions Query to identify the changes made to the dept table on Monday. Which dept_id inserts would be listed?

A. Inserts for values 4, 5, 6, and 7 (all the committed as well as the noncommitted inserts) would be listed.
B. Inserts for values 5, 6, and 7 only (all the committed inserts) would be listed.
C. Inserts for values 6 and 7 only (all the explicitly committed inserts) would be listed.
D. The insert for value 7 only (the most recently committed insert) would be listed.

The correct answer is B. The Flashback Versions Query lists all the committed changes. Noncommitted changes are not listed. These changes need not be explicitly committed.

Conclusion

In this column, I discussed two topics associated with Flashback technology: Flashback Drop and Flashback Versions Query. In my next column, I'll continue my discussion of Flashback technology and cover more concepts that may be covered in the Oracle Database 10g: Administration II exam.


Aradhana Puri (aradhana.puri@oracle.com) is a principal Oracle certification exam developer. She has been with Oracle for mote than four years.


Send us your comments