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