Question: I have accidentally dropped a schema
owner and all of the table data has disappeared! How can I recover
an Oracle user ID and its tables? I will get fired if they found
out that I dropped a production user ID and lost the table data.
Answer:
In ordinary cases from dropped tables, the Oracle flashback utility
makes easy to restore individual dropped tables:
flashback table
persons to
before drop
flashback table
"BIN$ksisyyg0TxKnt18rqukpQA==$0"
to
before drop
rename to
new_persons;
If you use the command drop user … cascade, any objects
in the recycle bin that belong to that user are automatically
purged. The problem is getting all of the table names, plus the
indexes constraints and other related schema objects.
Restoring a dropped user requires copying the production
database into a sandbox, where you use flashback to recover the
whole database to a point immediately before the schema owner user
was dropped. Then, you simply export the user, FTP the dmp file
into production, re-create the user ID and re-import the objects.
However, fear not, because the drop flashback feature can get
them back!
Follow these steps:
- Clone the whole production database into your test instance
area.
- Once you have fully cloned production you need to
start Oracle in mount mode
- Next, you use flashback database to rollback the
database to a point in time immediately before the user ID was
dropped.
SQL> startup mount SQL>
flashback database to timestamp to_date('23-DEC-2010
11.38.00','DD-MM-YYYY HH24:MI:SS');
- Now that you have flashed back,open the test database in
read-only mode:
SQL > alter
database open read only;
- Use the export (expdp) utility to export the user and
its objects.
- FTP the export dmp file onto your production server
- Re-create the user ID that was dropped
SQL> create user fred identified
by flintstone . . . ;
- Run the data pump import utility (impdp) to restore
all of the users tables, indexes, constraints, and table rows.
- Select counts from the tables to ensure that the
import has been successful.
