4

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?

Improve this question
5
  • 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
    Commented Jul 22, 2013 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
    Commented Jul 22, 2013 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
    Commented Jul 22, 2013 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
    Commented Jul 22, 2013 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
    Commented Nov 20, 2013 at 23:13

1 Answer 1

Reset to default
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.

Improve this answer
1
  • Thanks for the reply. We created new database. Will take care of such mistakes now.
    – Black Dranzer
    Commented Dec 1, 2013 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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