Oracle Data Pump
PURPOSE: All
documents are provided on this Blog just for educational purposes
only. Please make sure that you run it in your test environment
before to move on to production environment.
ORA-31633: unable to create master table.
SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
You can see the master table.
SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j WHERE o.owner=j.owner_name
AND o.object_name=j.job_name AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
You can check the running Job:
drop table SYS.EXPDP_JOB;
Create Directory for DATAPUMP:
Create or change a directory for the expdp
CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/fast_recovery_area/${ORACLE_SID}/dpump';
GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO system;
Directory location.
col DIRECTORY_PATH format a43;
col OWNER format a10;
col DIRECTORY_NAME format a20;
set linesize 200;
select * from dba_directories where directory_name='DATA_PUMP_DIR';
Export full DB:
. /users/oracle/set_home_env KARDBTES
expdp "'"/ as sysdba"'" directory=DATA_PUMP_DIR \
logfile=dexp_ISMDB.log JOB_NAME= dexp_KARDBTES DUMPFILE=KARDBTES_%U.dmp FULL=YES
Import full DB:
impdp "'"/ as sysdba"'" DUMPFILE=kardbtes_%U.dmp logfile=imp_FULLDB_KARDBTES.log \
directory=DATA_PUMP_DIR PARALLEL=4 full=y
Export Table:
#!/bin/bash
. /users/oracle/set_home_env TSTDB
cd /fast_recovery_area/TSTDB/dpdump
expdp \'/ as sysdba\' tables=HR.JOBS directory=data_pump_dir \
LOGFILE=JOBS.$(date +%d%m%y-%H%M).log JOB_NAME=exp_HR_JOBS_job \
DUMPFILE=HR_JOBS_.$(date +%d%m%y-%H%M).dmp
# If you want to just export data_only:
expdp system/xxxxx directory=DATA_PUMP_DIR dumpfile=table_data_%U.dmp \tables=KARDBTES.MIGTAB01 content=data_only logfile=export_to_fix_.log
Import Table
# You can import multiple tables as follow
cd /oracledb/datapump
impdp system/xxxx directory=DATA_PUMP_DIR dumpfile=table_data_%u.dmp \
tables=KARDBTES.MIGTAB01 , KARDBTES.MIGTAB02 logfile=import_to_fix.log parallel=4
Import table as a different name. REMAP Command.
#!/bin/bash
. /users/oracle/set_home_env TSTDB
cd /fast_recovery_area/TSTDB/dpdump
impdp \'/ as sysdba\' \
DIRECTORY=data_pump_dir \
DUMPFILE=HR_EMPLOYEES_01_.dmp \
LOGFILE=EMPLOYEES.$(date +%d%m%y-%H%M).log \
TABLES=HR.EMPLOYEES \
EXCLUDE=CONSTRAINT # Notewendig wenn die Tabelle constraint enthaelt
Export Scheam.
#!/bin/bash
. /users/oracle/set_home_env KARDBTES
expdp "'"/ as sysdba"'" schemas=KARDBTES01 directory=DATA_PUMP_DIR \
logfile=exp_KARDBTES01_$(date +%d%m%y-%H%M).log JOB_NAME=expdp_KARDBTES01 \
DUMPFILE=KARDBTES01_%U_$(date +%d%m%y-%H%M).dmp
Or:
#Create a script to export multiple schemas.
vi datapump_exp_schema.sh
. /users/oracle/set_home_env KARDBTES
expdp "'"/ as sysdba"'" schemas=KARDBTES01, KARDBTES02 directory=DATA_PUMP_DIR parallel=5 \
logfile=dexp_KARDBTES .log JOB_NAME= dexp_KARDBTES DUMPFILE=kardbtes_%U.dmp
#Execute the script as follow.
nohup /oracledb/datapump/datapump_exp_schema.sh >/oracledb/datapump/datapump_exp_schema.log &
#Import data into another DB with same Schemaname.
vi datapump_imp_schema.sh
. /users/oracle/setdb_home KARDBTES
impdp "'"/ as sysdba"'" schemas= KARDBTES01, KARDBTES02 directory=DATA_PUMP_DIR parallel=5 \
logfile=dimp_ KARDBTES.log JOB_NAME= dimp_KARDBTES DUMPFILE= KARDBTES _%U.dmp TRANSFORM=oid:n
#Execute JOB es follow:
nohup /oracledb/datapump/datapump_imp_schema.sh >/oracledb/datapump / datapump_imp_schema.log &
Import as different Schemaname REMAP Schema.
#!/bin/bash
. /users/oracle/setdb_home KARDBTES
impdp \'/ as sysdba\' schemas=KARDB_DEV REMAP_SCHEMA=KARDB_DEV:KARDB_TST exclude=statistics \ directory=data_pump_dir LOGFILE=KARDB_DEV_imp.$(date +%d%m%y-%H%M).log JOB_NAME=imp_kardb_job \
DUMPFILE=KARDB_DEV01.210624-1109.dmp
At the end you may need to execute following command
sqlplus /
(ownname => 'SCHEMA_NAME', tabname => 'TABLE_NAME' , estimate_percent => 5,
degree => 8, cascade => true);
SQL> alter system flush shared_pool;
export import NO DATA just Schema Structure.
#!/bin/bash
. set_home_env KARDBTES
echo $ORACLE_SID
date
YYYYMMDD=`date +%Y%m%d`
schemas=schema1,schema2,schema3 \
directory=DATA_PUMP_DIR reuse_dumpfiles=y \
dumpfile=kardbtes_description_$YYYYMMDD.dmp \ logfile=kardbtes_description_$YYYYMMDD.log
date
Import Schema as follow.
You may need to create table and index before you import
Example:
Tablespace:
CREATE TABLESPACE DATA
DATAFILE '+DATA01' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
LOGGING
DEFAULT
NO INMEMORY
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
Index
CREATE TABLESPACE INDX
DATAFILE '+DATA01' SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
LOGGING
DEFAULT
NO INMEMORY
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 16K
SEGMENT SPACE MANAGEMENT AUTO
#!/bin/bash
. /users/oracle/set_home_env KARDBTES
echo $ORACLE_SID
date
YYYYMMDD=`date +%Y%m%d`
impdp "'"/ as sysdba"'" content=metadata_only \
schemas=schema1,schema2,schema3 \
exclude=statistics \
logfile=KARDBTES_nodata_impdp_$YYYYMMDD.log \ JOB_NAME=imp_nodataKARDBTES \
DUMPFILE=KARDBTES_description_schema_20210319.dmp
date
Exclude Some Tables
Export complete Schema except some Tables - Example.
#!/bin/bash
. /users/oracle/set_home_env KARDBTESß
expdp "'"/ as sysdba"'" schemas=schemaname directory=DATA_PUMP_DIR \
logfile=exp_schema.log PARALLEL=7 JOB_NAME=expdp_exclude \
DUMPFILE=schema_name_%U.dmp \
EXCLUDE=TABLE:\"IN\(\'table1\',\'table2\',\'table3\',\'table4\'\)\"
Exclude Some Tables
Import complete Schema except some Tables - Example.
#!/bin/bash
. /users/oracle/set_home_env KARDBTES
impdp "'"/ as sysdba"'" schemas=schemaname directory=DATA_PUMP_DIR \
logfile=imp_schema.log PARALLEL=7 JOB_NAME=impdp_exclude \ DUMPFILE=schema_name_%U.dmp \
EXCLUDE=TABLE:\"IN\(\'table1\',\'table2\',\'table3\',\'table4\'\)\" \
TABLE_EXISTS_ACTION=REPLACE
