In this tutorial we will see how we can export and import the content of one Oracle Schema using Oracle tools called EXPDP and IMPDP.
The syntax for this task is :
expdp user/passwd
directory=DATA_PUMP_DIR
schemas=schema_name
dumpfile=dump_file.dmp
logfile=log_file.log
CLUSTER {Y | N }
COMPRESSION={METADATA_ONLY | NONE}
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
DATA_OPTIONS XML_CLOBS
DIRECTORY=directory_object
DUMPFILE=[directory_object:]file_name [,[directory_object:]file_name...]
ESTIMATE={ BLOCKS | STATISTICS}
ESTIMATE_ONLY={Y|N}
EXCLUDE={object_type [:name_clause]} [,EXCLUDE=...]
ENCRYPTION { ALL | DATA_ONLY | METADATA_ONLY }
ENCRYPTION_PASSWORD=password
ENCRYPTION_ALGORITHM { AES128 | AES192 | AES256 }
ENCRYPTION_MODE { DUAL | PASSWORD | TRANSPARENT }
FILESIZE=number_of_bytes
FLASHBACK_SCN=scn_value
FLASHBACK_TIME=timestamp
INCLUDE={object_type [:name_clause]} [,INCLUDE=...]
JOB_NAME=jobname_string
LOGFILE=[directory_object:]file_name
NOLOGFILE={Y|N}
PARFILE=[directory_path]file_name
NETWORK_LINK=database_link
PARALLEL=int
REMAP_DATA
QUERY=[schema_name.][table_name:]query_clause
REUSE_DUMPFILES Overwrite destination dump file if it exists (N)
SAMPLE=[schema_name.][table_name:]sample_percent
SERVICE_NAME
SOURCE_EDITION
STATUS [=int]
TRANSPORTABLE { ALWAYS | NEVER }
VERSION={COMPATIBLE | LATEST | version_string}
select directory_name,directory_path from dba_directories;
DIRECTORY_NAME DIRECTORY_PATH
------------------------------ --------------------------------------------------------------------------------
SQLT$BDUMP /oracle/app/admin/PCORP1/bdump
DATA_PUMP_DIR /oracle/oradata/PCORP1/flash_recovery_area/PCORP1/datapump
ORACLE_OCM_CONFIG_DIR /oracle/app/product/10.2.0/db_1/ccr/state
SQLT$UDUMP /oracle/app/admin/PCORP1/udump
SQLT$STAGE /oracle/app/admin/PCORP1/udump
TRCA$INPUT1 /oracle/app/admin/PCORP1/udump
TRCA$STAGE /oracle/app/admin/PCORP1/udump
TRCA$INPUT2 /oracle/app/admin/PCORP1/bdump
EXPDPFURH /l00/oradata/DB/flash_recovery_area/DB/datapump
EXPDPFURD /l00/oradata/DCORP1/flash_recovery_area/DCORP1/datapump
ADUMP /oracle/app/admin/PCORP1/adump
EXPDP /export/oradata/PCORP1/datapump
12 rows selected
CREATE OR REPLACE DIRECTORY PUMP_DIR AS '/oracle/app/admin/utl';
. oraenv
ORACLE_SID = [PCORP1] ? PCORP1
oracle@dc8004:/opt/oracle/otk/home[PCORP1]
> expdp system/xxxxxxxx directory=EXPDP schemas=SEG dumpfile=GVC.dmp logfile=GVC.log
Export: Release 10.2.0.5.0 - 64bit Production on Quarta-Feira, 16 Outubro, 2013 11:46:12
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Conectado a: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
scp oracle@HOST:/DIRECTORY
select
'drop table '||owner||'.'||table_name ||' cascade constraints PURGE;'
from all_tables where owner=schema;
select
'drop '||object_type ||' '||owner||'.'||object_name ||';'
from all_objects where owner=schema and object_type not in('INDEX','TABLE');
impdp system/ schemas=xxxx directory=DATA_PUMP_DIR dumpfile=xxx.dmp logfile=impdp.log
remap_schema=old:new
remap_tablespace=old:new
exclude=grant
. oraenv
ORACLE_SID = [DCORP1] ? DB
The /oracle/app/product/10.2.0/db_1/bin/orabase binary does not exist
You can set ORACLE_BASE manually if it is required.
oracle:/oracle/app/admin/utl[DB]
> impdp system/xxxxx schemas=SEG directory=PUMP_DIR dumpfile=SEG.dmp logfile=SEG.log remap_schema=SEG:SEG remap_tablespace=TSSEG:TSSEG exclude=grant
Import: Release 10.2.0.5.0 - 64bit Production on Quarta-Feira, 16 Outubro, 2013 12:55:21
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
locate utlrp.sql
/oracle/app/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql
oracle:/u02[DB]
> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Qua Out 16 13:38:00 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
@/oracle/app/product/11.2.0/dbhome_2/rdbms/admin/utlrp.sql
...
...
...
...
oracle@:/u02[DB]
> locate alert_db.log
/oracle/app/admin/DB/bdump/alert_DB.log
oracle@:/u02[DB]
>
oracle@:/u02[DB]
> tail -f /oracle/app/admin/DB/bdump/alert_DB.log
Current log# 2 seq# 2206 mem# 0: /l00/oradata/DB/redo2.rdo
Wed Oct 16 13:42:37 BRT 2013
Deleted Oracle managed file /u01/oradata/DB/flash_recovery_area/DB/archivelog/2013_09_30/o1_mf_1_1849_94kxvw08_.arc
Wed Oct 16 13:42:41 BRT 2013
Thread 1 cannot allocate new log, sequence 2207
Checkpoint not complete
Current log# 2 seq# 2206 mem# 0: /l00/oradata/DB/redo2.rdo
Wed Oct 16 13:42:44 BRT 2013
....
...
...
..
..
..
.