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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.