The Problem:

I've been trying to restore this dump file, but unfortunately, whoever did the exporting did not make it clear on what users, privileges, and roles are required for the database.

So I thought I'd just create a random user, import it there, look at the errors, and see what I can do.

During the import process, Oracle asked for system's username and password. I, thinking that it merely needed that for authentication, obliged.

What happened instead was that the dump file's database tables were restored to the user system.

Now, I may not know much about Oracle, but I can guess that this is probably bad. Really bad. I'm looking at Oracle SQL Developer, and I can see a LOT of tables that probably aren't supposed to be there - I'm pretty sure ACCRUALRULE is not an Oracle system table.

How do I fix this? Is there a way to reset the user system back to its default setting? I'd hate to have to manually get rid of every table, especially considering I don't know which ones are supposed to stay there.


My System:

  • Windows 7
  • Oracle 11g XE

Delete the created objects by hand ( after verifying that they were indeed created from the dump ) or restore from backup ...

PS.: Make a backup and test that backup on another machine or in an VM before you do anything to SYS!!!

If you have flashback and archivelog enabled you could flashback the database to a point in time before the import using RMAN.

Oracle Flashback at "Rewinding a Database with Flashback Database"


If I don't have a backup and Flashback is disabled, I would do something like this:

select 'drop table ' || table_name || ' cascade constraints purge;'
from dba_tables
where table_name in (
  select object_name
  from dba_objects
  where owner = 'SYS'
  and object_type = 'TABLE'
  and created > 'DATE-OF-IMPORT-HERE');

Of course you need to check twice before running this DDLs (look for system tables, you should not have a lot of them created after import). And repeat this for other objects.

And of course, test and backup!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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