Delete mistakenly imported schema objects from 'SYS' user in ORACLE 10.2g?

Question

I am working on Oracle10.2g database for a web project. I had exported full Schema objects of a database from a remote system in a file(some-file.dmp). Then I wanted to import the contents of file into another database on the local system. The process worked perfectly.

However, I accidently imported the file contents(including tables,views etc.) into SYS user. So, the SYS user is now overcrowded with around 1500 unwanted objects.

I know I can drop the objects individually, but, that's a tiresome effort. Now, I was wondering if there is any way that I can kind of undo the process and remove the unwanted objects(remove the tables,views etc. from SYS user that were mistakenly imported)?

EDIT :

The imported objects include in particular

1. Tables(Obviously including FK Constraints)
2. Views
3. Indexes
4. Packages
5. Procedures
6. Functions
7. Sequences
8. Triggers
9. Java Code

So, they are inter-related to one another. Any ideas or Advice greatly appreciated!

Answer 1

You can try querying DBA_OBJECTS and looking for any that are owned by SYS and recently created. For example, the following lists all objects that were created in the SYS schema today:

SELECT object_name, object_type
  FROM dba_objects
 WHERE owner = 'SYS'
   AND created >= TRUNC(SYSDATE)

You can then use this to generate some dynamic SQL to drop the objects. That should save you dropping them manually.

Note however that there may be some objects that have been recently created and should be owned by SYS, so double-check what it is you're dropping before you drop it. (On my Oracle 11g XE database, the newest objects in the SYS schema were index and table partitions created about a week and a half ago.)

I don't know what types of objects you have, but there will be some dependencies between object types. In particular, you can't drop a table if another table has foreign-key constraints pointing to it. This answer provides some PL/SQL code to disable all constraints on a table, which you can adapt to drop all constraints, or just drop all foreign-key constraints, if you need to.

Also, if a table column uses a type, that table will need to be dropped before dropping the type. Similarly, you may have to take care if types have dependencies on other types.

Other things to be aware of:

• You don't need to drop package bodies, just drop the packages and the bodies will go with them.
• You don't need to drop triggers on tables and views: the triggers go when the table or view is dropped. Similarly, dropping a table drops all of the indexes on that table.
• Views, procedures, functions and packages may depend on other objects but they shouldn't stop those other objects from being dropped. The views/procedures/functions/packages should become invalid, but if they're going to be dropped anyway that doesn't matter.

You don't specify what other types of object you have, so there may well be other issues you encounter.

EDIT: in response to your updated question:

• You can drop the objects in the order you specify, once you've dropped the FK constraints. The tables will be the hardest part: once they're all gone everything else should be straightforward.
• You don't need to drop indexes as they get dropped automatically when you drop the tables.
• You don't need to drop triggers on tables or views as these get dropped automatically when you drop the view or table. (I don't know whether you have any other triggers such as AFTER LOGON ON DATABASE, but such triggers might not be included in exports anyway.)
• I only have Oracle XE, which doesn't support Java, so I can't be sure of the exact incantation necessary to drop Java classes. The Oracle documentation for DROP JAVA may be of some help to you.