	AllExperts > Oracle
	Search

Volunteer
Answers to thousands of questions

Home · More Oracle Questions · Answer Library

· Encyclopedia ·

Oracle - imp in system.....

Expert: Peter Choi - 5/21/2009

Question
Hi Peter,

I have a question and I really hope you have an answer :-)

I received an Oracle dump without user-info, which I needed to inspect in a hurry (I use Oracle Spatial 10g). B/c the user-info was missing, I couldn't use the fromuser/touser params of the imp statement, so I used .... system to import to. Not handy.

Now I want to clean my db (system user). Is there a way to easily clean the system account of all the imported objects (e.g. by date?). The dump contains many tables, views, triggers, sequences etc..... so I hope there is an intelligent solution.

Regards,

Beginner

Answer
Hi Arjen,

Ideally, you would have taken a database backup before you imported objects into your database. Hindsight is 20-20 :-).

If you did not create your database and immediately imported your objects, then there is a solution.

Option 1.
Examine your source .DMP file. Use the IMP command to generate a list of all the objects that is in this file and that would be imported into your database.

The syntax/command that you would use:

imp <user>/<password>@<target_db> SHOW=Y FILE=<src_DMP_file> > <Your_Output_File>

The content of <Your_Output_File> will contain the DDL that Oracle uses to create and import the exported objects into your database.

You can review this list of objects and determine which one you can safely drop (should also include indexes, tablespaces, tables, views, etc.)

Option 2.
Similar to Option 1, this option uses the DBA_OBJECTS view to identify all the objects that were created.

SQL> desc dba_objects
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------

OWNER                                              VARCHAR2(30)
OBJECT_NAME                                        VARCHAR2(128)
SUBOBJECT_NAME                                     VARCHAR2(30)
OBJECT_ID                                          NUMBER
DATA_OBJECT_ID                                     NUMBER
OBJECT_TYPE                                        VARCHAR2(19)
CREATED                                            DATE
LAST_DDL_TIME                                      DATE
TIMESTAMP                                          VARCHAR2(19)
STATUS                                             VARCHAR2(7)
TEMPORARY                                          VARCHAR2(1)
GENERATED                                          VARCHAR2(1)
SECONDARY                                          VARCHAR2(1)

You can use this query (modify it to meet your criteria):

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, CREATED
FROM DBA_OBJECTS
WHERE CREATED='<DATE_TIME_OF_IMPORT"
AND OWNER='SYSTEM'
ORDER BY CREATED
/

Again you can use this list to validate against the list generated in option 1 to determine which objects you can safely remove.

Hope this helps.

Peter

Add to this Answer Ask a Question