This is an excerpt from Chapter 29 of Oracle Database 10g OCP Certification All-In-One Exam Guide by Damir Bersinic and John Watson, copyright 2006 from Oracle Press, a division of McGraw-Hill. Click here to read the full chapter.
In this chapter you will learn how to:
- Recover a dropped table using Flashback technology
- Manage the recycle bin
- Perform a Flashback Table operation
- Recover from user errors using Flashback Versions Query
- Perform transaction-level recovery using Flashback Transaction Query
The previous chapter introduced Flashback Database, a powerful but drastic feature
that is functionally equivalent to an incomplete recovery. This chapter covers the other
flashback technologies available in an Oracle 10g database. These are not as extreme
as Flashback Database in that they do not entail either downtime or loss of data. They
are still, however, very powerful techniques for recovering from errors by backing out
changes that you would prefer not to have been committed.
The flashback technologies discussed here are, first, Flashback Drop, enabled by
the way the DROP TABLE command is used and implemented, and second, various
ways of exploiting the UNDO capability: Flashback Versions Query, Flashback Table
Query, and Flashback Transaction Query.
Flashback and the ACID test
Remember the ACID test, described in Chapter 9. This is part of the rules to which a
relational database must conform, and is critical to an understanding of the flashback
technologies: both their capabilities and their limitations.
All DML transactions are terminated with either a COMMIT or a ROLLBACK
statement. Until then, while the transaction is in progress, the principle of transaction
isolation (the I of the ACID test) as implemented by Oracle guarantees that no one,
other than the session carrying out the transaction, can see the changes it has made.
Furthermore, the principle of atomicity (the A of the ACID test) guarantees that
the session can terminate the transaction with a ROLLBACK, which will reverse the
changes completely; no other session will have any knowledge that the changes were
ever made. If the transaction is terminated with a COMMIT, then the changes will be
immediately visible to all other sessions. The only exception to this is any sessions
that for reasons of read consistency (the C of the ACID test) need to be protected
from the changes. Furthermore, once a transaction is committed, it must be
absolutely impossible for the database to lose the changes; this is the D, for durable,
of the ACID test.
In many ways, DDL commands are transactions like any other. The rules of a
relational database require that the effect of committed DDL can never be reversed,
and all DDL is committed, automatically. You have no control over this; the COMMIT
is an integral part of all DDL commands.
Flashback Drop provides a means whereby you can reverse the effect of a DROP
TABLE command, but there is no guarantee that it will succeed. This will depend on
other activity in the database since the DROP was executed. You can use the various
Flashback Query commands to reverse DML commands, but again, whether this will
succeed will depend on what other activity has occurred in the intervening time. It is
impossible to roll back a committed transaction, whether DML or DDL. The ACID
test does not permit this. The flashback technologies rely on constructing another
transaction that will reverse the impact of the original transaction, but it may be that
this new transaction will fail, because of other, incompatible committed changes.
Flashback Drop
Accidentally dropping a table is terrifyingly easy to do. It is not just that you can drop
the wrong table because of a typing error; it could be the right table, but you are
connected to the wrong schema, or logged onto the wrong instance. You can reduce
the likelihood of this by setting your SQL*Plus prompt, for example,
SQL> set sqlprompt "_user'@'_connect_identifier> "
SYSTEM@ocp10g>
TIP To set your sqlprompt automatically for all SQL*Plus sessions, put the
preceding command into the glogin.sql file, in the ORACLE_HOME/sqlplus
/admin directory.
Flashback Drop lets you reinstate a previously dropped table (but not a truncated
table!) exactly as it was before the drop. All the indexes will also be recovered, and
also any triggers and grants. Unique, primary key, and not-null constraints will also be
recovered, but not foreign key constraints.
EXAM TIPThe Flashback Drop command applies only to tables, but all
associated objects will also be recovered, except for foreign key constraints.
The implementation of Flashback Drop
In earlier releases of the Oracle Database, when a table was dropped all references to
it were removed from the data dictionary. If it were possible to see the source code for
the old DROP TABLE command, you would see that it was actually a series of DELETE
commands against the various tables in the SYS schema that define a table and its
space usage, followed by a COMMIT. There was no actual clearing of data from disk,
but the space used by a dropped table was flagged as being unused and thus available
for reuse. Even though the blocks of the table were still there, there was no possible
way of getting to them because the data dictionary would have no record of which
blocks were part of the dropped table. The only way to recover a dropped table was to
do a point-in-time recovery, restoring a version of the database from before the drop
when the data dictionary still knew about the table.
In release 10g of the Oracle database, the implementation of the DROP TABLE
command has been completely changed. Tables are no longer dropped at all; they are
renamed.
In Figure 29-1, you can see that a table, OLD_NAME, occupies one extent of 64KB,
which starts in the seventeenth block of file six. After the rename to NEW_NAME,
the storage is exactly the same; therefore, the table is the same. Querying the view
DBA_OBJECTS would show that the table's object number had not changed either.
The release 10g implementation of the DROP TABLE command has been mapped
internally onto a RENAME command, which affects the table and all its associated
indexes, triggers, and constraints, with the exception of foreign key constraints, which
are dropped. Foreign key constraints have to be dropped for real. If they were maintained,
even with a different name, then DML on the non-dropped parent table would be
constrained by the contents of a dropped table, which would be absurd.
Grants on tables do not have names, so they can't be renamed. But even though
when you grant a privilege you specify the object by name, the underlying storage of
the grant references the object by number. As the object numbers don't get changed
by a RENAME operation, the grants are still valid.
As far as normal SELECT and DML statements are concerned, a dropped table is
definitely dropped. There is no change to any other commands, and all your software
will assume that a dropped table really is gone. But now that DROP is in fact a
RENAME, it becomes possible to un-drop, by renaming the table back to its original
name. However, this is not guaranteed to succeed. It may be that the space occupied
by the dropped table has been reused. There are also complications if in the interim
period another table has been created, reusing the same name as the dropped table.
The dropped objects can be queried, by looking at the "recycle bin" to obtain their
new names. This is a listing of all objects that have been dropped, mapping the original
table and index names onto the system-generated names of the dropped objects. There
is a recycle bin for each user, visible in the USER_RECYCLEBIN data dictionary view,
or for a global picture, you can query DBA_RECYCLEBIN. The space occupied by the
recycle bin objects will be reused automatically when a tablespace comes under space
pressure (after which time the objects cannot be recovered), or you can manually force
Oracle to really drop the objects with the PURGE command.
TIP There are no guarantees of success with Flashback Drop, but it may well
work. The sooner you execute it, the greater the likelihood of success.
Sample questions
After dropping a table, how can you access the rows within it? (Choose the
best answer.)
- Query the table using the AS OF syntax.
- Query the table using the BEFORE DROP syntax.
- Query the table using its recycle bin name.
- You can't query it until it has been recovered.
If a table has been dropped and then another table created with the same
name, which of the following statements is correct? (Choose the best answer.)
- You must rename the new table before you can flash back the dropped one.
- You can flash back the dropped table if you specify a new name for it.
- You can flash back the dropped table into a different schema.
- You must drop the new table before flashing back the old one.
Under which of the following circumstances will Flashback Drop work?
(Choose the best answer.)
- When a table has been truncated
- When a table has been purged
- When a user has been dropped
- When an index has been dropped
- None of the above
Click here to read the answers and the rest of this chapter.
About the authors
Damir Bersinic, OCP, MCSE, MCSDBA, MCT, is an infrastructure consultant with Trecata Corporation, a system integration consultancy in Toronto, Canada. He has more than 20 years of industry experience and has authored a number of titles on Oracle, SQL Server, Active Directory and Windows. His credits include multiple exam preparation titles for Oracle and Microsoft certification programs.
John Watson, OCP DBA 8.0, 8.i, and 9i, Internet Application Server Administrator, and certified on Managing Oracle on Unix, is a senior consultant with BLP Management Consultants in South Africa. He has taught for Oracle University in South Africa for four years. John taught the DB10g New Features course, the DB10g Manageability and Performance seminars, validated hundreds of OCP 10g New Features exam questions and contributed to the course material for all the 10g courses. John is the author of numerous articles on technology.
