To: Kader Ben 

>     I accidently deleted a big table. Can someone tell
> me how (step by step) recover this table. I am running
> on archive mode on solaris 2.6.
> Thanks'
> Ben


If you have an export of the table, then you are in luck. Drop the table
and then import:

imp userid=username/password fromuser=OWNER touser=OWNER tables=TABLE_NAME

Since you did not mention that you have an export in your email, then I
assume you do not have one. This means that you have to do a full database
recovery (up to a point-in-time).

If you have only one server, then you will be replacing your current
database. All data past the time that you are recovering to will be lost.

If you have another server, then recover your datafiles and archived redo
logs (and redo logs and controlfiles) to the other database. You will 
want to recover to a point-in-time before the table was dropped. Bring up
the database, take an export, ftp the export .dmp file to your original
server, and import the table.

I wish I could give step-by-step instructions more detailed, but it would
take a while to write it all out. I strongly recommend getting Oracle
Press's "Oracle8 Backup and Recovery" which has step-by-step instructions
on this particular case, as well as other cases (missing datafiles,
missing redo logs, etc.).

Also be sure to check that not only the table is recovered, but all
dependent objects (indexes, referential constraints, grants,
triggers, etc.) are there as well. Also double-check to see if procedures
and packages have not become invalid due to the dropping of the table.

NOTE: Make a full backup of your database before you attempt any of this!

Best of luck,

-Ari Kaplan
"": Oracle Tips + Discussion Page

Back to Ari Kaplan's Home Page

Ari Kaplan's Home Page