asked on

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.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

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!

Dba_Oracledb2

ASKER

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 ??

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

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.

johnsone

Also, you are missing SHOW=Y.

Dba_Oracledb2

ASKER

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.....

Dba_Oracledb2

ASKER

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......

Dba_Oracledb2

ASKER

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?

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dba_Oracledb2

ASKER

so even the source and target datafile locations are different , the import will be done with out problems ???

Mark Geerlings

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.