Databases Reference
In-Depth Information
IN_ACC_02 BIN$xmtCqONVcZjgQ4CohAoD7Q==$0 INDEX NO
TR_ACC_01 BIN$xmtCqONWcZjgQ4CohAoD7Q==$0 TRIGGER NO
ACCOUNTS BIN$xmtCqONXcZjgQ4CohAoD7Q==$0 TABLE YES
The most important column is the column CAN_UNDROP. If this column is YES,
then you can undrop an object cleanly without any additional efforts. Objects with
CAN_UNDROP = NO can still be reinstated, but you have to change their names to the
original names manually.
■
the original_name column shows the original names. once a table is undropped, the recycle bin information is
removed, and you will never be able to see the original names of the dependent objects, such as triggers and indexes of
that table. so, save the output of this query before you go to the next step.
Tip
2.
Now undrop the table ACCOUNTS by executing the following SQL statement:
SQL> flashback table accounts to before drop;
Flashback complete.
The table is now available in the database.
3.
Display the constraints of the table:
SQL> select constraint_type, constraint_name
2 from user_constraints
3 where table_name = 'ACCOUNTS';
C CONSTRAINT_NAME
- ------------------------------
P BIN$ncFOiaduRZeURXatWq8lyA==$0
C BIN$782qhcPvQbajusPeAEiR3Q==$0
The flashback (or the undrop) brought back the primary key and check constraints but not
the foreign keys, if there were any. The foreign keys are lost forever.
4.
Change the names of the reinstated objects to their original names, if you know
what they were. For example, if you know the original name of the constraint
BIN$ncFOiaduRZeURXatWq8lyA==$0
was pk_accounts, you can issue the following query to
restore the original name:
SQL> alter table accounts rename constraint "BIN$ncFOiaduRZeURXatWq8lyA==$0" to
pk_accounts;
Table altered.
SQL> alter table accounts rename constraint "BIN$782qhcPvQbajusPeAEiR3Q==$0" to
ck_acc_01;
Table altered.
If you don't have the names, use any human-readable name you consider appropriate.
