Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a script which lists all tables belonging to the user and executes DROP for all of them.

By mistake, I logged in oracle with 'sys as sysdba' and ran above script. Due to which all sysdba tables are dropped.

Now i can not startup database instance. In alert log, it gives following error:

Sat Jul 20 15:28:21 2013

Errors in file orcl_ora_4276.trc:

ORA-00942: table or view does not exist

Error 942 happened during db open, shutting down database

USER: terminating instance due to error 942

I tried to flashback one droppd table, but it is giving error:

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> FLASHBACK TABLE MAP_OBJECT TO BEFORE DROP;

ERROR:

ORA-03114: not connected to ORACLE

Please suggest if there is any way to restore all these tables. Or if creating new database is the only way?

share|improve this question

migrated from stackoverflow.com Jul 23 '13 at 7:23

This question came from our site for professional and enthusiast programmers.

    
i would suggest looking for old exp/expdp or backups on a storage/data level. I might also suggest something like trasnportable tablespaces but i don`t believe you can export them without dictionary data. –  haki Jul 22 '13 at 9:06
3  
You might be able to flashback database to before the drop script ran, but I suspect there might be be too much damage. You have a backup though, right? –  Alex Poole Jul 22 '13 at 9:06
    
Hi @haki and Alex, thanks for your reply. 1. I too suspect that damage is too much to use flashback, as from the description above, i first need to connect to oracle to be able to use flashback command. 2. Regarding backup, we always take backup of tables of a specific user. But that does not include the tables under 'SYS' user. This is why i do not understant how i can get those tables back. –  Black Dranzer Jul 22 '13 at 11:56
2  
It looks like your instance is being terminated when you open it; you perform flashback database when it's only mounted (see the previous doc link). That might save you, or still might not work. Sounds like you have exports of specific schemas, which is not the same as a backup. If that is all you have, and flashback doesn't work, then I think your only option may be to recreate the database and all the schemas. –  Alex Poole Jul 22 '13 at 12:12
    
If anyone needs an[other] explanation why one should always include object schema (owner) name explicitly in SQL statements, this is it. –  mustaccio Nov 20 '13 at 23:13

1 Answer 1

This is probably as complete a way of killing an Oracle database as you could wish for. The sys tables contain all the metadata about every object in the database -- objects, segments, extents ... so the database now contains no information on what user tables it stores, including the tables that store the data about that.

New database, I think.

And no more sys connection accidents.

share|improve this answer
    
Thanks for the reply. We created new database. Will take care of such mistakes now. –  Black Dranzer Dec 1 '13 at 8:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.