Recovery of Oracle data table after drop

The drop tables and indexes are stored in the Recycle bin ( so the Recycle Bin function must be set for the production database )

Because this generation environment after dropping the existing table, re-create a lot of tables, all the direct restore will prompt the original object exists, the table name is duplicated. The workaround is to rename the table name that already exists, let the deleted table be restored, and then merge the data .

1 renaming a table that already exists

Spool Bak.txt

Select ' ALTER TABLE '||T.original_name||' Rename to '||T.original_name||' _bak; ',' SELECT COUNT (*) from '||T.original_name||' _bak; '  from User_recyclebin T;

Spool off

Then the statement that modifies the table name is stored in bak.txt , and we can tweak it to run the script directly or copy the sqlplus .

Because considering the merging of data, it is necessary to merge data for the data of Count(*) greater than 0 .

2 data restore using the command to restore the database

Spool Bak2.txt

Select ' Flashback table '| | T.original_name ||  ' to before drop ' from User_recyclebin t;

Spool off

Execute the saved script in this bak2.txt to restore the data

3 merging the table data in the database ()

Note that some of the data is directly insert to can be, but some is the function of the timer, only need to accumulate on the number of can, and some data because there is no reconstruction of a copy of the same data, for such data do not need to tube him

4 Rebuilding the index

Recovery of Oracle data table after drop