using "imp" 1

[jestrada101]

when using imp to import a schema, is there an option that will have it overwrite the entire schema and replace with what is in teh *.dmp or do I have to recreate teh schema.

Thanks

 

[SantaMufasa]

J,

If you are importing a schema, there are two stipulations that Oracle expects:

1) The target schema exists
2) The schema is empty.



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing low-cost remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jestrada101]

Thanks!

 

[SantaMufasa]

I should clarify: The schema does not need to be empty, but existing objects should not conflict with incoming objects. If an incoming object conflicts, and:

1) "IGNORE=N" (default), then the importer throws an error,
2) "IGNORE=Y", then the import will append rows to existing tables provided that the append does not cause violation of UNIQUE constraints.


Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing low-cost remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jestrada101]

Is there a command to extract teh DDL of a schema to recreate the schema with no data?

Thanks
JE

 

[SantaMufasa]

Yes, but it's a little tricky. Here are the steps:

1)

imp file=<incoming dump file name> indexfile=<resulting SQL DDL file> userid=<username>/<password> FULL=Y

2) using your favourite text editor, alter the <resulting SQL DDL file> to remove all of the "REM " entries that precede lines of table DDL. ("imp" generates DDL for both indexes and tables, but comments out the table DDL.) You will also probably wish to move (or remove) the "CONNECT <user>" statement following the initial REM'd table DDL.

Let us know how you succeed with this.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com:

Providing low-cost remote Database Administration and support services

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[jestrada101]

that got me the DDL that I wanted! Thanks!

 

[bookouri]

im doing some importing and exporting right now and doing something similar. Why would you want to recreate all those schema objects? why wouldnt you just drop the schema, recreate the schema/owner ID and import the schema at that point? Im in the process of moving a database from UNIX to Windows so im having to use export and import. I imported the full database as a test. My plan was to now drop the schema(s) in the new database, get a fresh export and import just the schema(s) when Im ready to go live with the new server. Am i missing something? is there a better way to do it? Does getting the DDL and creating the empty objects just a performance measure.. a good way to speed up the import?

Ive never used export/import to move a database before, I may be way off base.

 

[jestrada101]

bookouri

I was wanting an empty schema, but with the same objects created. No special reason, only cleaningup the environment for further testing of an application.

 

[bookouri]

i was so intent on what i was trying to do, all i was thinking of was importing data. <G>

 

[haste]

Am having a prob importing a dump keep getting this error

IMP-00017: following statement failed with ORACLE error 604:
 "CREATE INDEX "EUL5_DHN_DBH_I" ON "EUL5_DBH_NODES" ("DHN_HI_ID" )  
PCTFREE 1"
 "0 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 573440 NEXT 516096 
MINEXTENTS 1 M"
 "AXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL 
DEFAU"
 "LT) TABLESPACE "ORION93" LOGGING"
IMP-00003: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-00607: Internal error occurred while making a change to a data 
block
ORA-00600: internal error code, arguments: [4193], [0], [78], [], [], 
[], [], []

 . importing table               "EUL5_DOCUMENTS"          0 rows 
imported
IMP-00017: following statement failed with ORACLE error 604:

Am not sure how the block size is interfering cause the machine I made the dump from uses block size 8192 and I specified that while importing

 

[haste]

My apologise for my earlier post used the wrong facility to post a message.

 

[eagertotry]

Just for completeness sake, if you really wanted the DDLs, SantaMufasa method is the way to go. However, if the objective is just to create all the objects in the schema with no data, you could do that directly in import with parameter ROWS=N.