export DDL of tablespaces – useful when importing specific oracle schemas into a different oracle database




if you want to export and import specific schemas or a full database into another environment , one of the way would be to export and import the database or specific schemas . however in the target environment you might not have the same table spaces. so the usual approach is to export the DDL of the table spaces in the source environment. below is an example .

 

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

 

BEGIN

DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘SQLTERMINATOR’, true);

DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘PRETTY’, true);

END;

/

 

SELECT DBMS_METADATA.get_ddl (‘TABLESPACE’, tablespace_name)

FROM   dba_tablespaces

WHERE  tablespace_name = DECODE(UPPER(‘&1’), ‘ALL’, tablespace_name, UPPER(‘&1’));

 

SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON

 

 

 

 

 

 

 

 

2 parts

 

 

  1. Export  tablespaces/datafiles. Correct the statements by adding semicolon , removing  default tablespaces like system, sysaux etc. (script  tablespace_ddl.sql attached).

clean up tablespace creation file

a.there might not be semi colons after the alter database statements. So add them.

b.The default tablespaces like system,sysaux,users,temp have to be deleted in case they exist on the target system.

c.change any path names to reflect the target pathnames.

  1. Exporting the DD_PROD schema

a.make sure that an  oracle directory called DATAPUMP_EXPORTS is present. This should point to the correct path where the export will be taken.

Select * from dba_directories;

Create directory datapump_exports as ‘path name’ ;

Grant read,write on directory datapump_exports to system;

  1. As oradb user , from command like run the export command(make sure you know the system password)

expdp  system  parfile=exp_ddprod.par (the exp_ddprod.par file has been attached)

 

that’s it!!

 

 

set echo off

set heading off wrap ON trimspool ON

set feedback off

 

set long 10000000

set longchunksize 6000

set line 600

set pages 0

spool tablespace_ddl_create.sql

select dbms_metadata.get_ddl(‘TABLESPACE’,tablespace_name)||’;’ from dba_tablespaces;

spool off

exit

 

 

 

dumpfile=exp_ddprod_%U.dmp

logfile=exp_ddprod.log

directory=datapump_exports

filesize=30G

parallel=4

schemas=DD_PROD

 

 

Author: admin