Databases Reference
In-Depth Information
If you make a mistake in identifying the correct table, Oracle Database returns an
ORA-38305
error, as shown in
the following example where you are trying to revive a table named ACCOUNTS that does not exist in the recycle bin:
SQL> flashback table accounts to before drop;
flashback table accounts to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN
The error says it all.
■
if you want to delete a table permanently, without sending it to the recycle bin, then use the
purge
clause in the
drop
statement. for example:
Tip
SQL> drop table test purge;
Table dropped.
the table is now completely dropped; similar to the pre-10g behavior, it does not go to the recycle bin.
Undropping a Table When Another Exists with the Same Name
Problem
You had a table called ACCOUNTS that was dropped, and since then you created another table also called
ACCOUNTS. Now you want to reinstate the first table ACCOUNTS from the recycle bin.
Solution
There are two potential solutions:
•
Drop the existing table so there will be no conflict for the name of the table undropped.
•
Undrop the table but reinstate it to a different name.
Here are the solutions in detail.
Solution 1: Dropping the Existing Table
The easiest approach is, of course, to drop the existing table. The flashed-back table then comes on the database
without any problems.
