Databases Reference
In-Depth Information
Solution 2: Renaming the Reinstated Table
The alternative approach is safer because you do not need to drop anything. When you flash back a table to undrop
it, you can optionally rename it. In this case, when you flash back the table ACCOUNTS, you want to reinstate it as
NEW_ACCOUNTS.
SQL> flashback table accounts to before drop rename to new_accounts;
Flashback complete.
The existing table still remains as ACCOUNTS, but the reinstated table is renamed to NEW_ACCOUNTS.
How It Works
When you flash back a table from the recycle bin, a table with that name must not already exist in the database.
Suppose you are trying to revive a table called ACCOUNTS but it already exists. In that case, the flashback statement
returns with an error:
ORA-38312
:
SQL> flashback table accounts to before drop;
Flashback complete.
SQL> flashback table accounts to before drop;
flashback table accounts to before drop
*
ERROR at line 1:
ORA-38312: original name is used by an existing object
■
Note
suppose there are two tables in the recycle bin with the same name—aCCoUnts, as shown here:
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------- ------------------------------ ------------ -------------------
ACCOUNTS BIN$xmtCqONLcZjgQ4CohAoD7Q==$0 TABLE 2012-08-04:02:09:42
ACCOUNTS BIN$xmtCqONKcZjgQ4CohAoD7Q==$0 TABLE 2012-08-04:02:09:24
TEST1 BIN$xmtCqONDcZjgQ4CohAoD7Q==$0 TABLE 2012-08-04:02:05:45
now you issue this:
SQL> flashback table accounts to before drop;
Which table aCCoUnts will be reinstated?
the table that was dropped last will be reinstated; that is, the table that shows up first will be reinstated. pay attention to
this behavior while reinstating a table from the recycle bin.
