Databases Reference
In-Depth Information
Undropping a Specific Table from Two Dropped Tables
with the Same Name
Problem
You had a table called ACCOUNTS, which you dropped. Later you created a table, again called ACCOUNTS, and
dropped that, too. Now you want to revive the table ACCOUNTS, the one that was dropped first.
Solution
To reinstate a specific dropped table, follow the steps:
1.
First find out the presence of these objects in the recycle bin:
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
Note there are two different tables with the same name—ACCOUNTS.
2.
Decide which of the two accounts tables to revive. The column DROP_TIME helps in your
decision; it shows when each table was dropped. In your case, you want to recover the
one that was dropped earlier. If you issue the statement
flashback table accounts to
before drop
, the more recently dropped table will be revived—not what you want in this
scenario.
To revive the earlier table, the one that was dropped first, issue the
flashback table
command, giving the recycle bin name as the table name:
3.
SQL> flashback table "BIN$bQ8QU1bWSD2Rc9uHevUkTw==$0" to before drop;
Flashback complete.
Be sure to put the recycle bin name—
BIN$xmtCqONKcZjgQ4CohAoD7Q==$0
—in double
quotes. The double quotes are necessary because of the presence of special characters in
the name.
4.
Check the recycle bin. You will see only one table now:
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
------------- ------------------------------ ------------ -------------------
ACCOUNTS BIN$xmtCqONLcZjgQ4CohAoD7Q==$0 TABLE 2012-08-04:02:09:42
There is just one table in the recycle bin. You have successfully restored the earlier version
of the table.
