extract tables spaces from exp dump file

How to extract the tablespaces names from dump file which is an full exp dump file done using exp utility in 10g oracle.

So that i can create the tablespaces first before importing.

Dba_Oracledb2Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
On imp use show=Y log=mylog

This says to 'show' me what you would do but don't actually do it.
0
 
johnsoneSenior Oracle DBACommented:
Use

imp show=y indexfile=ddl.txt ...

That should go through the dump file and extract the DDL into the ddl.txt file.  You can look through that file and get the tablespace creates from it.
0
 
Mark GeerlingsDatabase AdministratorCommented:
If you have the ability to repeat the export and specify "full=Y" and "rows=N" this time (and with a different *.dmp file name) that would give you a *MUCH* smaller (and faster) *.dmp file to work with!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dba_Oracledb2Author Commented:
imp file=exp_file.dmp indexfile=ddl_script.sql userid=system/xxxxxxxx full=y , it did extract lot of things in sql file, and unable to fine the tablespaces alone properly....is their any other way to just extract the tablespaces ??
0
 
Mark GeerlingsDatabase AdministratorCommented:
Add "rows=N".  That should give you a *MUCH* smaller file to work with.  You can use that "rows=N" parameter on either exp or imp.  Of course, if you do this with exp, that gives you a *MUCH* smaller *.dmp file for imp to process!
0
 
slightwv (䄆 Netminder) Commented:
Going from memory but I don't think indexfile shows tablespaces outright.  In other words, you have to look for them.

The log file should show the create tablespace commands.
0
 
johnsoneSenior Oracle DBACommented:
Also, you are missing SHOW=Y.
0
 
Dba_Oracledb2Author Commented:
imp \"/ as sysdba\"  file=exp_orap_full.dmp indexfile=ddl_script.sql  full=y rows=N SHOW=Y

I didnot CREATE TABLESPACE word at all.....
0
 
Dba_Oracledb2Author Commented:
i only see things like..... ALTER TABLE "SYSTEM"."REPCAT$_REPOBJECT" ADD CONSTRAINT
REM  "REPCAT$_REPOBJECT_VERSION" CHECK (version# >= 0 AND version# <
REM  65536) ENABLE NOVALIDATE ;
REM  ALTER TABLE "SYSTEM"."REPCAT$_REPOBJECT" ADD CONSTRAINT
REM  "REPCAT$_REPOBJECT_STATUS" CHECK (status IN (0, 1, 2, 3, 4, 5, 6))
REM  ENABLE NOVALIDATE ;
REM  ALTER TABLE "SYSTEM"."REPCAT$_REPOBJECT" ADD CONSTRAINT
REM  "REPCAT$_REPOBJECT_GENPACKAGE" CHECK (genpackage IN (0, 1, 2)) ENABLE
REM  NOVALIDATE ;
REM  ALTER TABLE "SYSTEM"."REPCAT$_REPOBJECT" ADD CONSTRAINT
REM  "REPCAT$_REPOBJECT_GENTRIGGER" CHECK (gentrigger IN (0, 1, 2)) ENABLE
REM  NOVALIDATE ;
REM  ALTER TABLE "SYSTEM"."REPCAT$_REPOBJECT" ADD CONSTRAINT
REM  "REPCAT$_REPOBJECT_PRIMARY" PRIMARY KEY ("SNAME", "ONAME", "TYPE")
REM  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536
REM  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  "SYSTEM" LOGGING ENABLE ;
REM  CREATE TABLE "SYSTEM"."REPCAT$_REPPROP" ("SNAME" VARCHAR2(30),
REM  "ONAME" VARCHAR2(30), "TYPE" NUMBER(*,0), "DBLINK" VARCHAR2(128),
REM  "HOW" NUMBER(*,0), "PROPAGATE_COMMENT" VARCHAR2(80), "DELIVERY_ORDER"
REM  NUMBER, "RECIPIENT_KEY" NUMBER, "EXTENSION_ID" RAW(16)) PCTFREE 10
REM  PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1
REM  FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" LOGGING
REM  NOCOMPRESS ;
REM  ALTER TABLE "SYSTEM"."REPCAT$_REPPROP" MODIFY ("EXTENS


i can find manually tablespace names from the logfile , by searching word tablespace which is more than 1000 pages and hidden randomly......
0
 
Dba_Oracledb2Author Commented:
Finally i request from the export find senders to send tablespace names...problem solved by this way....thanks...

Now i will create table spaces in this new db , can i just start full import , ?? do i need to create scemas and give unlimited space on respective tablespaces?
0
 
Mark GeerlingsDatabase AdministratorCommented:
No, you don't need to create the schemas manually, a full import will do that for you.  Also, if you have the tablespaces created with the same tablespace names (the file and/or directory names may be different) and with as much or more space as they had in the source database (or with autoextend on) import will give those shemas the same privileges they had in the source database.
0
 
Dba_Oracledb2Author Commented:
so even the source and target datafile locations are different , the import will be done with out problems ???
0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes.  An Oracle import operation is a logical operation that uses the tablespace names (if you have them created manually in advance) to control where the tables and indexes get created.  It doesn't know or care if the physical file names or locations for these are different in the new database compared to the old one.  A physical restore from a backup is different.  That will expect the same physical disks and directories.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.