Oracle Data Pump (expdp, impdp) in Oracle Database

The Data Pump feature had been introduced from Oracle 10g. Until Oracle 9i, There were a tradition export and import (exp, imp) tools for logical backup. Nowadays Data Pump (expdp & impdp) utilities are mostly use by oracle DBA.

Data Pump is very useful tools to migrate database in difference version of Oracle databases or partial refresh (full database backup / restore also is the option) from prod to non-prod. In addition to basic import and export functionality data pump provides a PL/SQL API and support for external tables.

Data Pump Supports following way to loading or unloading data:

  • Direct path
  • External tables
  • Data file copy
  • Network link

Create a Directory or Use Existing Directory for Dump and Log Files:

Data Pump is a server-based technology, so it typically deals with directory objects pointing to physical directories on the database server. Dump and log file will create on Database Server on the path which pointed by Directory.

List of Directories:

SQL> col OWNER for a15
col DIRECTORY_NAME for a30
col DIRECTORY_PATH for a70
SQL> set linesize 200
SQL> Select * from ALL_DIRECTORIES;
OWNER                DIRECTORY_NAME                 DIRECTORY_PATH                                                         ORIGIN_CON_ID
-------------------- ------------------------------ ---------------------------------------------------------------------- -------------
SYS                  ORACLE_OCM_CONFIG_DIR2         /u01/app/oracle/product/12.1.0/dbhome_1/ccr/state                                  1
SYS                  DATA_PUMP_DIR                  /ade/b/1281484529/oracle/admin/seeddata/dpdump/                                    1
SYS                  OPATCH_LOG_DIR                 /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch                                    1

3 rows selected.

You can use any existing directory if it meets you requirement like Directory Path. Let say, you want to take a backup for list of tables which size is more than 100GB. So you have to make sure your dump file will save on the filesystem where has available space.

For Example, I want to take backup on /backup filesystem. So I need to create a Directory.

Create a Directory on Non-CDB:

SQL> CREATE OR REPLACE DIRECTORY  ORCL_DIR AS '/backup/orcl/logical_backup';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY ORCL_DIR TO SYSTEM;

Grant succeeded.

SQL> Select * from ALL_DIRECTORIES WHERE DIRECTORY_NAME='ORCL_DIR';

OWNER           DIRECTORY_NAME                 DIRECTORY_PATH                           ORIGIN_CON_ID
--------------- ------------------------------ ---------------------------------------- -------------
SYSTEM             ORCL_DIR                       /backup/orcl/logical_backup                          1

Create a Directory on PDB:

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         4 SALESPDB                       MOUNTED
         5 PDBTEST                        READ WRITE NO
         7 PDBNODATA                      MOUNTED

SQL> ALTER SESSION SET CONTAINER=PDBTEST;

Session altered.

# Connect to PDB using TNS. 
SQL> conn USER_PDB/oracle@pdbtest
Connected.

SQL> CREATE OR REPLACE DIRECTORY PDBTEST_DIR AS '/backup/orcl/logical_backup';

Directory created.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDBTEST

Export/Import Tables:

To export and import on PDB, have to connect using TNS. But for Non-Container database you can directly connect without tns but also can use the TNS.

PDB Connection:
$ expdp user_pdb/oracle@pdbtest 

Non-Container DB:
$ expdp system/oracle 
$ expdp system/oracle@orcl 

Export Tables:

  • List of Tables with schema name need to mention on TABLES=Table_List with comma.
  • Tables from different Schemas can be exported in one dump/backup file
  • CONTENT : [ALL], DATA_ONLY and METADATA_ONLY.

$ expdp user_pdb/oracle@pdbtest TABLES=USER_PDB.LOCATIONS,USER_PDB.REGIONS, HR.EMP, HR.DEPT DIRECTORY=PDBTEST_DIR dumpfile=EXP_PDB_LOC_REG.dmp logfile=EXP_LOC_REG.log

Import Tables:

Once backup is done, copy the backup to target database server on the location for directory.

  • If tables and schema are same on source and target then it is not mandatory to use TABLES and Schema options. By default it will import on same schema & table name on target.
  • If want to import less number of tables from from export list, then have to mentioned list of tables on TABLES option. i.e. TABLES=SCHEMA.TABLE_NAME1,SCHEMA.TABLE_NAME2
  • TABLE_EXISTS_ACTION: APPEND, REPLACE, [SKIP] and TRUNCATE
Import all exported Tables: To use TABLES parameter not mandatory

$ impdp user_pdb/oracle@pdbnodata TABLES=USER_PDB.LOCATIONS, HR.EMP TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_PDB_LOC_REG.dmp logfile=IMPDP_PDBNODATA_LOC_REG.log

Import less number of tables than exported list: TABLES Parameter is mandatory

$ impdp user_pdb/oracle@pdbnodata TABLES=USER_PDB.LOCATIONS, HR.EMP TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_PDB_LOC_REG.dmp logfile=IMPDP_PDBNODATA_LOC_REG.log

Export / Import Schema:

Export Schema:

SCHEMAS=LIST of Schema with comma. i.e. schemas=user_pdb, HR, Payrol

Single Schema export:
$expdp system/oracle schemas=hr directory=ORCL_DIR dumpfile=EXP_SCHEMA.dmp logfile=EXP_SCHEMA.log

Multiple Schemas export:
$expdp system/oracle schemas=hr, payrol directory=ORCL_DIR dumpfile=EXP_SCHEMA.dmp logfile=EXP_SCHEMA.log

Import Schema:

Single Schema import:
$impdp system/oracle schemas=hr directory=ORCL_DIR dumpfile=EXP_SCHEMA.dmp logfile=IMP_SCHEMA.log

Multiple Schemas import:
$impdp system/oracle schemas=hr, payrol directory=ORCL_DIR dumpfile=EXP_SCHEMA.dmp logfile=IMP_SCHEMA.log

Specific Tables from Schema Backup's dump file:

$impdp system/oracle tables=SCHEMA_NAME.TABLE_NAME1, SCHEMA_NAME.TABLE_NAME2 directory=ORCL_DIR dumpfile=EXP_SCHEMA.dmp logfile=IMP_SCHEMA.log

Export / Import Database:

We can export and import full database using FULL=y parameter. We can use following command to export and import entire database using Data Pump.

#Export of full database
expdp system/password@orcl full=Y directory=ORCL_DIR dumpfile=FULL_DB.dmp logfile=exp_FULL_DB_BACKUP.log

#Import of full database
impdp system/password@orcl full=Y directory=ORCL_DIR dumpfile=FULL_DB.dmp logfile=impdp_FULL_DB.log

Export / Import using per file:

Using per file for export and import is very useful. It is easy to manger for lots of tables including where clause for filtering records.

Per File for Export:

[oracle@ocm12c logical_backup]$ cat exp_user_pdb.par
userid=user_pdb/oracle@pdbtest
tables=USER_PDB.LOCATIONS,
USER_PDB.REGIONS,
USER_PDB.DEPARTMENTS,
USER_PDB.EMPLOYEES,
USER_PDB.JOBS,
USER_PDB.JOB_HISTORY
directory=PDBTEST_DIR
dumpfile=EXP_PDB_LOC_REG.dmp
logfile=EXP_LOC_REG.log

$ expdp perfile=exp_user_pdb.par 
Or, using nohup 
$ nohup expdp perfile=exp_user_pdb.par &

Per File for Import:

[oracle@ocm12c logical_backup]$ cat imp_user_pdb.par
impdp user_pdb/oracle@pdbnodata
TABLE_EXISTS_ACTION=TRUNCATE
DIRECTORY=PDBNODATA_DIR
dumpfile=EXP_SCHEMA_PDB.dmp
logfile=IMPDP_PDBNODATA_COUNTRY.log

$ impdp perfile=imp_user_pdb.par
Or, using nohup 
$ nohup impdp perfile=imp_user_pdb.par &

Export / Import using CONTENT and QUERY:

How to use CONTENT Parameter:

To export only meta data only then use CONTENT=METADATA_ONLY.

#CONTENT=METADATA_ONLY
$ expdp user_pdb/oracle@pdbtest schemas=HR  CONTENT=METADATA_ONLY directory=PDBTEST_DIR dumpfile=EXP_HR.dmp logfile=EXP_HR.log
$

To export the data without the metadata use the DATA_ONLY parameter value.

#CONTENT=DATA_ONLY
$ expdp user_pdb/oracle@pdbtest schemas=HR  CONTENT=DATA_ONLY directory=PDBTEST_DIR dumpfile=EXP_HR.dmp logfile=EXP_HR.log
$

How to use QUERY Parameter:

The QUERY parameter allows to filter the rows of table for exported from one or more tables. 

#Use Query parameter to filter data
$ expdp user_pdb/oracle@pdbtest directory=PDBTEST_DIR dumpfile=EXP_HR.dmp logfile=EXP_HR.log query=HR.EMP:"WHERE STARTDATE >= TO_DATE('01-JAN-2017','dd-mon-yyyy')", HR.DEPT:"WHERE DEPTNO IN (50,70)"
$

Export Partition Table:

Export a single or list of partition from a partition table: TABLES=(T1:P1,T1:P2), here T1 is partitioned table. For all partition, TABLES=SCHEMA_NAME.TABLE_NAME

#Export two partitions
$ expdp user_pdb/oracle@pdbtest TABLES=(INVENTORY.TBL_SALES:DATE2001,INVENTORY.TBL_SALES:DATE2002) directory=PDBTEST_DIR dumpfile=EXP_SALES.dmp logfile=EXP_SALES.log
$

Example of Export/Import Tables:

Export Tables:

[oracle@ocm12c order_entry]$ expdp user_pdb/oracle tables=USER_PDB.LOCATIONS,USER_PDB.REGIONS  dire                                                                       ctory=PDBTEST_DIR dumpfile=EXP_PDB_LOC_REG.dmp logfile=EXP_LOC_REG.log

Export: Release 12.1.0.2.0 - Production on Thu Sep 3 23:59:02 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "USER_PDB"."SYS_EXPORT_TABLE_01":  user_pdb/********@pdbtest tables=USER_PDB.LOCATIONS,USER_PDB.RE                                                                       GIONS directory=PDBTEST_DIR dumpfile=EXP_PDB_LOC_REG.dmp logfile=EXP_LOC_REG.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "USER_PDB"."LOCATIONS"                      8.445 KB      23 rows
. . exported "USER_PDB"."REGIONS"                        5.554 KB       4 rows
Master table "USER_PDB"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER_PDB.SYS_EXPORT_TABLE_01 is:
  /backup/orcl/logical_backup/EXP_PDB_LOC_REG.dmp
Job "USER_PDB"."SYS_EXPORT_TABLE_01" successfully completed at Thu Sep 3 23:59:14 2020 elapsed 0 00:00:11

Import Tables:

[oracle@ocm12c order_entry]$ impdp user_pdb/oracle@pdbnodata TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_PDB_LOC_REG.dmp logfile=IMPDP_PDBNODATA_LOC_REG.log

Import: Release 12.1.0.2.0 - Production on Fri Sep 4 00:14:40 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "USER_PDB"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER_PDB"."SYS_IMPORT_FULL_01":  user_pdb/********@pdbnodata TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_PDB_LOC_REG.dmp logfile=IMPDP_PDBNODATA_LOC_REG.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER_PDB"."LOCATIONS"                      8.445 KB      23 rows
. . imported "USER_PDB"."REGIONS"                        5.554 KB       4 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "USER_PDB"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Sep 4 00:14:55 2020 elapsed 0 00:00:11

Schema Export:

Example of Schema export.

[oracle@ocm12c order_entry]$ expdp user_pdb/oracle@pdbtest schemas=user_pdb directory=PDBTEST_DIR dumpfile=EXP_SCHEMA_PDB.dmp logfile=EXP_SCHEMA_LOC_REG.log

Export: Release 12.1.0.2.0 - Production on Fri Sep 4 00:33:22 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "USER_PDB"."SYS_EXPORT_SCHEMA_01":  user_pdb/********@pdbtest schemas=user_pdb directory=PDBTEST_DIR dumpfile=EXP_SCHEMA_PDB.dmp logfile=EXP_SCHEMA_LOC_REG.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 576 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USER_PDB"."COUNTRIES"                      6.468 KB      25 rows
. . exported "USER_PDB"."DEPARTMENTS"                    7.132 KB      27 rows
. . exported "USER_PDB"."EMPLOYEES"                      17.09 KB     107 rows
. . exported "USER_PDB"."JOBS"                           7.109 KB      19 rows
. . exported "USER_PDB"."JOB_HISTORY"                    7.203 KB      10 rows
. . exported "USER_PDB"."LOCATIONS"                      8.445 KB      23 rows
. . exported "USER_PDB"."REGIONS"                        5.554 KB       4 rows
Master table "USER_PDB"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for USER_PDB.SYS_EXPORT_SCHEMA_01 is:
  /backup/orcl/logical_backup/EXP_SCHEMA_PDB.dmp
Job "USER_PDB"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Sep 4 00:33:55 2020 elapsed 0 00:00:29

Import Schema:

[oracle@ocm12c order_entry]$ impdp user_pdb/oracle@pdbnodata schemas=user_pdb TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_SCHEMA_PDB.dmp logfile=IMPDP_PDBNODATA_SCHEMA.log

Import: Release 12.1.0.2.0 - Production on Fri Sep 4 00:43:14 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "USER_PDB"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "USER_PDB"."SYS_IMPORT_SCHEMA_01":  user_pdb/********@pdbnodata schemas=user_pdb TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_SCHEMA_PDB.dmp logfile=IMPDP_PDBNODATA_SCHEMA.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"USER_PDB" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "USER_PDB"."REGIONS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "USER_PDB"."LOCATIONS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USER_PDB"."COUNTRIES"                      6.468 KB      25 rows
. . imported "USER_PDB"."DEPARTMENTS"                    7.132 KB      27 rows
. . imported "USER_PDB"."EMPLOYEES"                      17.09 KB     107 rows
. . imported "USER_PDB"."JOBS"                           7.109 KB      19 rows
. . imported "USER_PDB"."JOB_HISTORY"                    7.203 KB      10 rows
. . imported "USER_PDB"."LOCATIONS"                      8.445 KB      23 rows
. . imported "USER_PDB"."REGIONS"                        5.554 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "USER_PDB"."SYS_IMPORT_SCHEMA_01" completed with 0 error(s) at Fri Sep 4 00:43:30 2020 elapsed 0 00:00:13

Import to different Schema using REMAP_SCHEMA parameter:

[oracle@ocm12c order_entry]$ impdp user_pdb/oracle@pdbnodata REMAP_SCHEMA=user_pdb:user_pdb2 TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_SCHEMA_PDB.dmp logfile=IMPDP_PDBNODATA_SCHEMA1.log

Import: Release 12.1.0.2.0 - Production on Fri Sep 4 01:22:20 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "USER_PDB"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "USER_PDB"."SYS_IMPORT_FULL_01":  user_pdb/********@pdbnodata REMAP_SCHEMA=user_pdb:user_pdb2 TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_SCHEMA_PDB.dmp logfile=IMPDP_PDBNODATA_SCHEMA1.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/INC_TYPE
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USER_PDB2"."COUNTRIES"                     6.468 KB      25 rows
. . imported "USER_PDB2"."DEPARTMENTS"                   7.132 KB      27 rows
. . imported "USER_PDB2"."EMPLOYEES"                     17.09 KB     107 rows
. . imported "USER_PDB2"."JOBS"                          7.109 KB      19 rows
. . imported "USER_PDB2"."JOB_HISTORY"                   7.203 KB      10 rows
. . imported "USER_PDB2"."LOCATIONS"                     8.445 KB      23 rows
. . imported "USER_PDB2"."REGIONS"                       5.554 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "USER_PDB"."SYS_IMPORT_FULL_01" completed with 0 error(s) at Fri Sep 4 01:22:32 2020 elapsed 0 00:00:11

Import a single or multiple tables from dump file which was exported for schemas:

[oracle@ocm12c logical_backup]$ impdp user_pdb/oracle@pdbnodata Tables=USER_PDB.COUNTRIES TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_SCHEMA_PDB.dmp logfile=IMPDP_PDBNODATA_COUNTRY.log

Import: Release 12.1.0.2.0 - Production on Fri Sep 4 18:40:56 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "USER_PDB"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "USER_PDB"."SYS_IMPORT_TABLE_01":  user_pdb/********@pdbnodata Tables=USER_PDB.COUNTRIES TABLE_EXISTS_ACTION=TRUNCATE DIRECTORY=PDBNODATA_DIR dumpfile=EXP_SCHEMA_PDB.dmp logfile=IMPDP_PDBNODATA_COUNTRY.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "USER_PDB"."COUNTRIES" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USER_PDB"."COUNTRIES"                      6.468 KB      25 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "USER_PDB"."SYS_IMPORT_TABLE_01" successfully completed at Fri Sep 4 18:41:11 2020 elapsed 0 00:00:11

Most useful parameters for export and import:

Common Parameters on both expdp and impdp:

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DIRECTORY
Directory object to be used for dump and log files.

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FILESIZE
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Export entire database [NO].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

SCHEMAS
List of schemas to export [login schema].

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

Most frequently use parameters for expdp: (+ list from common)

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

ESTIMATE_ONLY
Calculate job estimates without performing the export [NO].

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

Most frequently use parameters for impdp: (+ list from common)

PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE
Redefine data file references in all DDL statements.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.

REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.

REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.

REUSE_DATAFILES
Tablespace will be initialized if it already exists [NO].

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

EXPDP Parameters List:

[oracle@ocm12c ~]$ expdp -help

Export: Release 12.1.0.2.0 - Production on Thu Sep 3 13:50:39 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.

ACCESS_METHOD
Instructs Export to use a particular method to unload data.
Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

COMPRESSION
Reduce the size of a dump file.
Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

COMPRESSION_ALGORITHM
Specify the compression algorithm that should be used.
Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH.

CONTENT
Specifies data to unload.
Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keyword values are: XML_CLOBS.

DIRECTORY
Directory object to be used for dump and log files.

DUMPFILE
Specify list of destination dump file names [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION
Encrypt part or all of a dump file.
Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM
Specify how encryption should be done.
Valid keyword values are: [AES128], AES192 and AES256.

ENCRYPTION_MODE
Method of generating encryption key.
Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT].

ENCRYPTION_PASSWORD
Password key for creating encrypted data within a dump file.

ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.

ESTIMATE
Calculate job estimates.
Valid keyword values are: [BLOCKS] and STATISTICS.

ESTIMATE_ONLY
Calculate job estimates without performing the export [NO].

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FILESIZE
Specify the size of each dump file in units of bytes.

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Export entire database [NO].

HELP
Display Help messages [NO].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of export job to create.

KEEP_MASTER
Retain the master table after an export job that completes successfully [NO].

LOGFILE
Specify log file name [export.log].

LOGTIME
Specifies that messages displayed during export operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

METRICS
Report additional job information to the export log file [NO].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [NO].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file name.

QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

SAMPLE
Percentage of data to be exported.

SCHEMAS
List of schemas to export [login schema].

SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.

SOURCE_EDITION
Edition to be used for extracting metadata.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

TABLES
Identifies a list of tables to export.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to export.

TRANSPORTABLE
Specify whether transportable method can be used.
Valid keyword values are: ALWAYS and [NEVER].

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be unloaded.

VERSION
Version of objects to export.
Valid keyword values are: [COMPATIBLE], LATEST or any valid database version.

VIEWS_AS_TABLES
Identifies one or more views to be exported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

ADD_FILE
Add dumpfile to dumpfile set.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

FILESIZE
Default filesize (bytes) for subsequent ADD_FILE commands.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

REUSE_DUMPFILES
Overwrite destination dump file if it exists [NO].

START_JOB
Start or resume current job.
Valid keyword values are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keyword values are: IMMEDIATE.

IMPDP Parameters:

[oracle@ocm12c ~]$ impdp -help

Import: Release 12.1.0.2.0 - Production on Thu Sep 3 13:54:03 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


The Data Pump Import utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Import runs by entering the 'impdp' command followed
by various parameters. To specify parameters, you use keywords:

     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

USERID must be the first parameter on the command line.

------------------------------------------------------------------------------

The available keywords and their descriptions follow. Default values are listed within square brackets.

ABORT_STEP
Stop the job after it is initialized or at the indicated object.
Valid values are -1 or N where N is zero or greater.
N corresponds to the object's process order number in the master table.

ACCESS_METHOD
Instructs Import to use a particular method to load data.
Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATH
and EXTERNAL_TABLE.

ATTACH
Attach to an existing job.
For example, ATTACH=job_name.

CLUSTER
Utilize cluster resources and distribute workers across the Oracle RAC [YES].

CONTENT
Specifies data to load.
Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY.

DATA_OPTIONS
Data layer option flags.
Valid keywords are: DISABLE_APPEND_HINT and SKIP_CONSTRAINT_ERRORS.

DIRECTORY
Directory object to be used for dump, log and SQL files.

DUMPFILE
List of dump files to import from [expdat.dmp].
For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.

ENCRYPTION_PASSWORD
Password key for accessing encrypted data within a dump file.
Not valid for network import jobs.

ENCRYPTION_PWD_PROMPT
Specifies whether to prompt for the encryption password [NO].
Terminal echo will be suppressed while standard input is read.

ESTIMATE
Calculate network job estimates.
Valid keywords are: [BLOCKS] and STATISTICS.

EXCLUDE
Exclude specific object types.
For example, EXCLUDE=SCHEMA:"='HR'".

FLASHBACK_SCN
SCN used to reset session snapshot.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

FULL
Import everything from source [YES].

HELP
Display help messages [NO].

INCLUDE
Include specific object types.
For example, INCLUDE=TABLE_DATA.

JOB_NAME
Name of import job to create.

KEEP_MASTER
Retain the master table after an import job that completes successfully [NO].

LOGFILE
Log file name [import.log].

LOGTIME
Specifies that messages displayed during import operations be timestamped.
Valid keyword values are: ALL, [NONE], LOGFILE and STATUS.

MASTER_ONLY
Import just the master table and then stop the job [NO].

METRICS
Report additional job information to the import log file [NO].

NETWORK_LINK
Name of remote database link to the source system.

NOLOGFILE
Do not write log file [NO].

PARALLEL
Change the number of active workers for current job.

PARFILE
Specify parameter file.

PARTITION_OPTIONS
Specify how partitions should be transformed.
Valid keywords are: DEPARTITION, MERGE and [NONE].

QUERY
Predicate clause used to import a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".

REMAP_DATA
Specify a data conversion function.
For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO.

REMAP_DATAFILE
Redefine data file references in all DDL statements.

REMAP_SCHEMA
Objects from one schema are loaded into another schema.

REMAP_TABLE
Table names are remapped to another table.
For example, REMAP_TABLE=HR.EMPLOYEES:EMPS.

REMAP_TABLESPACE
Tablespace objects are remapped to another tablespace.

REUSE_DATAFILES
Tablespace will be initialized if it already exists [NO].

SCHEMAS
List of schemas to import.

SERVICE_NAME
Name of an active Service and associated resource group to constrain Oracle RAC resources.

SKIP_UNUSABLE_INDEXES
Skip indexes that were set to the Index Unusable state.

SOURCE_EDITION
Edition to be used for extracting metadata.

SQLFILE
Write all the SQL DDL to a specified file.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STREAMS_CONFIGURATION
Enable the loading of Streams metadata [YES].

TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

TABLES
Identifies a list of tables to import.
For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995.

TABLESPACES
Identifies a list of tablespaces to import.

TARGET_EDITION
Edition to be used for loading metadata.

TRANSFORM
Metadata transform to apply to applicable objects.
Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE,
LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, STORAGE, and
TABLE_COMPRESSION_CLAUSE.

TRANSPORTABLE
Options for choosing transportable data movement.
Valid keywords are: ALWAYS and [NEVER].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_DATAFILES
List of data files to be imported by transportable mode.

TRANSPORT_FULL_CHECK
Verify storage segments of all tables [NO].
Only valid in NETWORK_LINK mode import operations.

TRANSPORT_TABLESPACES
List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.

VERSION
Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

VIEWS_AS_TABLES
Identifies one or more views to be imported as tables.
For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW.
Note that in network import mode, a table name may be appended
to the view name.

------------------------------------------------------------------------------

The following commands are valid while in interactive mode.
Note: abbreviations are allowed.

CONTINUE_CLIENT
Return to logging mode. Job will be restarted if idle.

EXIT_CLIENT
Quit client session and leave job running.

HELP
Summarize interactive commands.

KILL_JOB
Detach and delete job.

PARALLEL
Change the number of active workers for current job.

START_JOB
Start or resume current job.
Valid keywords are: SKIP_CURRENT.

STATUS
Frequency (secs) job status is to be monitored where
the default [0] will show new status when available.

STOP_JOB
Orderly shutdown of job execution and exits the client.
Valid keywords are: IMMEDIATE.

Leave a Reply