EVERYTHING YOU NEED TO KNOW ABOUT EXPDP IMPDP DATAPUMP METHODS ORACLE

EVERYTHING YOU NEED TO KNOW ABOUT EXPDP IMPDP DATAPUMP METHODS ORACLE

Things to precheck before a refresh activity using datapump. Points to consider very carefully!!

  • Size of the objects(table or schema or metadata)
  • Space availability in filesystem where you export or import the data and store the dump
  • Check if logical directory is created on database
  • Check if permissions and ownership available on the directory of export
  • Check FRA or log_archive_dest space availability on filesystem or ASM due to archive generation. Its better to estimate the archive generation based on the hourly archive generation and set FRA space accordingly
  • Check tablespace freespace on the destination (dev). If less space free, then add few datafiles to tablespace before import to avoid exhaust errors ora-1653
  • Take a backup of the grants of source table or schema(production database)
  • Check for the database version of source(prod) and destination(dev or QA) are same. If source is 12c and destination is 11g then you might end up with errors with datapump client compatibility and waste your valuable time if the data is huge. Because once you export the data without specifying the ‘version’ parameter on source 12c and try to import the data in destination, then 11g database datapump cannot read the dump file of 12c
  • Check if the tablespace holding the tables and indexes of source and destination are same.
  • Check if destination(dev) is on different server and you dont have space to copy the dumpfiles remotely. In this case , you need to find out a way to copy dumps to NFS mount with sufficient space or use network link to copy data
  • Check if you can speed up the export or import using parallel by checking the CPU resource
  • Check if you can strategize the import to reduce downtime if you need to copy very large data in TB size
  • Check for lockings by application user or module on the tables you export or import because this cause locking errors
  • Check the requirement carefully related to query(rows),table,schema,tablespace,database or metadata
  • There are three options basically to be considered during import for table_exists_action 1)append(add on top of existing) 2)truncate(overwrite the data inside table without removing table structure and metadata structure 3)replace(Completely drop the table and recreate table with new grants)
  • Verify if you need to change the schema or table during import. Two options are remap_table and remap_schema
  • Last but not least, try to automate the datapump if the objects you refresh donot change

Prechecks before export 

--check table size
SQL> col SEGMENT_NAME format a20
SQL> select segment_name,sum(bytes)/1048576 MB from dba_segments where segment_name='LOCATIONS' group by segment_name;

SEGMENT_NAME                 MB
-------------------- ----------
LOCATIONS                 .1875

--check logical directory
SQL> select * from dba_directories where directory_name='EXP';

OWNER                DIRECTORY_NAME       DIRECTORY_PATH       ORIGIN_CON_ID
-------------------- -------------------- -------------------- -------------
SYS                  EXP                  /home/oracle                     0

--check filesystem space
SQL> !df -h /home/oracle
Filesystem      Size  Used Avail Use% Mounted on
/dev/sda2        20G   13G  7.2G  64% /


--check FRA space for archive generation
SQL> show parameter db_recovery_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data01
db_recovery_file_dest_size           big integer 5G

--check permissions on the export directory
SQL> !ls -lrt /home/oracle
total 709116
drwxrwxr-x. 4 oracle oinstall        74 Dec 13  2020 oraInventory

--check tablespace size on destination(dev)
SQL> col TOTAL_SPACE format 999999
col TABLESPACE_NAME format a20
col TOTAL_FREE_SPACE format 999999
col UTIL_PCT format 999999
select x.TABLESPACE_NAME,round((x.bytes/1048576),2) as TOTAL_SPACE_MB,
round(x.bytes/1048576,2) - round(sum(y.bytes/1048576),2) as TOTAL_FREE_SPACE_MB,
to_number(round((round(sum(y.bytes/1048576),2)/round(x.bytes/1048576,2))*100,2)) as UTIL_PCT
from dba_data_files x
inner join dba_free_space y on x.TABLESPACE_NAME = y.TABLESPACE_NAME
group by x.TABLESPACE_NAME,x.bytes/1048576;SQL> SQL> SQL> SQL>   2    3    4    5    6

TABLESPACE_NAME      TOTAL_SPACE_MB TOTAL_FREE_SPACE_MB UTIL_PCT
-------------------- -------------- ------------------- --------
SYSAUX                          530              502.62        5
UNDOTBS1                        100                  97        3
USERS                             5                1.31       74
SYSTEM                          740              737.94        0

--take grants backup on source(prod)
SQL> select 'grant '||PRIVILEGE||' on '||owner||'.'||table_name|| ' to '||grantee|| ';' from dba_tab_privs where owner='TEST' and table_name='SALES_TAB';

'GRANT'||PRIVILEGE||'ON'||OWNER||'.'||TABLE_NAME||'TO'||GRANTEE||';'
--------------------------------------------------------------------------------
grant DELETE on TEST.SALES_TAB to TEST1;
grant INSERT on TEST.SALES_TAB to TEST1;
grant SELECT on TEST.SALES_TAB to TEST1;
grant UPDATE on TEST.SALES_TAB to TEST1;

Different Scenarios)

1)Export a table test.customers and import the table to different schema test1.customers

Use “remap_schema”

[oracle@exdbadm01 ~]$ expdp \'/ as sysdba\' directory=EDP dumpfile=cust.dmp logfile=cust.log tables=test.customers

Export: Release 11.2.0.4.0 - Production on Sat Aug 7 00:04:35 2021

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_07":  "/******** AS SYSDBA" directory=EDP dumpfile=cust.dmp logfile=cust.log tables=test.customers
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 96 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
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/STATISTICS/TABLE_STATISTICS
. . exported "TEST"."CUSTOMERS"                          111.1 KB    3000 rows
Master table "SYS"."SYS_EXPORT_TABLE_07" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_07 is:
  /home/oracle/expdp/cust.dmp
Job "SYS"."SYS_EXPORT_TABLE_07" successfully completed at Sat Aug 7 00:08:02 2021 elapsed 0 00:03:14

Import the table to different schema from test to test1

[oracle@exdbadm01 ~]$ impdp \'/ as sysdba\' directory=EDP dumpfile=cust.dmp logfile=impcust.log remap_schema=TEST:TEST1

Import: Release 11.2.0.4.0 - Production on Sat Aug 7 00:11:58 2021

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=EDP dumpfile=cust.dmp logfile=impcust.log remap_schema=TEST:TEST1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST1"."CUSTOMERS"                         111.1 KB    3000 rows
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/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Sat Aug 7 00:12:08 2021 elapsed 0 00:00:08

2)Export multiple tables from production test schema and import those tables to development under test schema. 

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
COUNTRIES
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS
SALES_TAB
SALES_TAB_COPY
TABX
TEST

11 rows selected.

Here i have exported four tables ‘ test.COUNTRIES,test.DEPARTMENTS,test.EMPLOYEES,test.JOBS ‘ with parallel 4 to increase the speed

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=multitable.dmp                  logfile=multitable.log tables=test.COUNTRIES,test.DEPARTMENTS,test.EMPLOYEES,test.JOBS parallel=4

Export: Release 12.1.0.2.0 - Production on Tue Aug 10 13:30:05 2021

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 opt                         ions
Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp dumpf                         ile=multitable.dmp logfile=multitable.log tables=test.COUNTRIES,test.DEPARTMENTS                         ,test.EMPLOYEES,test.JOBS parallel=4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
. . exported "TEST"."COUNTRIES"                          6.468 KB      25 rows
. . exported "TEST"."DEPARTMENTS"                        7.132 KB      27 rows
. . exported "TEST"."EMPLOYEES"                          18.40 KB     107 rows
. . exported "TEST"."JOBS"                               7.101 KB      19 rows
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/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDE                         X_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/multitable.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Aug 10 13:30:58 20                         21 elapsed 0 00:00:48

Now import the tables to dev database under same test schema. Here if the goal is to replace the exisiting tables and overwrite the dev data with production data, then give table_exists_action=replace . Note that this option overwrite the dependent metadata as well like index,constraints and other grants. 

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp dumpfile=multitable.dmp logfile=multitable.log parallel=4 table_exists_action=replace                                                                     
Import: Release 12.1.0.2.0 - Production on Tue Aug 10 13:36:36 2021

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 "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=exp dumpfile=multitable.dmp logfile=multitable.log parallel=4 table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."COUNTRIES"                          6.468 KB      25 rows
. . imported "TEST"."DEPARTMENTS"                        7.132 KB      27 rows
. . imported "TEST"."EMPLOYEES"                          18.40 KB     107 rows
. . imported "TEST"."JOBS"                               7.101 KB      19 rows
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Tue Aug 10 13:37:00 2021 elapsed 0 00:00:23

3)Export a schema ‘TEST’ from production and import them by overwriting the test schema to development or QA 

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=schema.dmp logfile=schema.log schemas=TEST parallel=8

Export: Release 12.1.0.2.0 - Production on Tue Aug 10 13:47:07 2021

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 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=exp dumpfile=schema.dmp logfile=schema.log schemas=TEST parallel=8
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320.5 MB
. . exported "TEST"."COUNTRIES"                          6.468 KB      25 rows
. . exported "TEST"."DEPARTMENTS"                        7.132 KB      27 rows
. . exported "TEST"."EMPLOYEES"                          18.40 KB     107 rows
. . exported "TEST"."JOBS"                               7.101 KB      19 rows
. . exported "TEST"."JOB_HISTORY"                        7.203 KB      10 rows
Processing object type SCHEMA_EXPORT/USER
. . exported "TEST"."LOCATIONS"                          8.445 KB      23 rows
. . exported "TEST"."REGIONS"                            5.554 KB       4 rows
. . exported "TEST"."TEST"                               5.648 KB      13 rows
. . exported "TEST"."SALES_TAB_COPY"                     89.14 MB 1398102 rows
. . exported "TEST"."SALES_TAB"                          89.14 MB 1398102 rows
. . exported "TEST"."TABX"                               79.96 MB 1048576 rows
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/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/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/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
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/schema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Aug 10 13:48:20 2021 elapsed 0 00:01:12

Import the prod test schema by overwrite the tables of dev test schema. Note that you cannot overwrite the schema but the tables under the schema. You might get some errors with ORA-31684 and ORA-39111 which can be ignored because the dependents object already exist on the dev. Table_exists_action=replace would only drop the existing table and recreate the whole table structurally

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp dumpfile=schema.dmp logfile=schema.log table_exists_action=replace parallel=8

Import: Release 12.1.0.2.0 - Production on Tue Aug 10 13:51:40 2021

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 "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=exp dumpfile=schema.dmp logfile=schema.log table_exists_action=replace parallel=8
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" 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/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"TEST"."LOCATIONS_SEQ" already exists
ORA-31684: Object type SEQUENCE:"TEST"."EMPLOYEES_SEQ" already exists
ORA-31684: Object type SEQUENCE:"TEST"."DEPARTMENTS_SEQ" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."COUNTRIES"                          6.468 KB      25 rows
. . imported "TEST"."DEPARTMENTS"                        7.132 KB      27 rows
. . imported "TEST"."EMPLOYEES"                          18.40 KB     107 rows
. . imported "TEST"."JOBS"                               7.101 KB      19 rows
. . imported "TEST"."JOB_HISTORY"                        7.203 KB      10 rows
. . imported "TEST"."LOCATIONS"                          8.445 KB      23 rows
. . imported "TEST"."REGIONS"                            5.554 KB       4 rows
. . imported "TEST"."TEST"                               5.648 KB      13 rows
. . imported "TEST"."SALES_TAB"                          89.14 MB 1398102 rows
. . imported "TEST"."TABX"                               79.96 MB 1048576 rows
. . imported "TEST"."SALES_TAB_COPY"                     89.14 MB 1398102 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"TEST"."ADD_JOB_HISTORY" already exists
ORA-31684: Object type PROCEDURE:"TEST"."SECURE_DML" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39111: Dependent object type ALTER_PROCEDURE:"TEST"."SECURE_DML" skipped, base object type PROCEDURE:"TEST"."SECURE_DML" already exists
ORA-39111: Dependent object type ALTER_PROCEDURE:"TEST"."ADD_JOB_HISTORY" skipped, base object type PROCEDURE:"TEST"."ADD_JOB_HISTORY" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/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 "SYS"."SYS_IMPORT_FULL_01" completed with 8 error(s) at Tue Aug 10 13:53:12 2021 elapsed 0 00:01:31

4. Export a schema ‘TEST’ from production and import only specific tables out of whole schema by overwriting the test schema to development or QA . This scenario is similar to 3rd point while export of data.

Export schema TEST from prod 

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=schema.dmp logfile=schema.log schemas=TEST parallel=8

Export: Release 12.1.0.2.0 - Production on Tue Aug 10 13:47:07 2021

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 "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=exp dumpfile=schema.dmp logfile=schema.log schemas=TEST parallel=8
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320.5 MB
. . exported "TEST"."COUNTRIES"                          6.468 KB      25 rows
. . exported "TEST"."DEPARTMENTS"                        7.132 KB      27 rows
. . exported "TEST"."EMPLOYEES"                          18.40 KB     107 rows
. . exported "TEST"."JOBS"                               7.101 KB      19 rows
. . exported "TEST"."JOB_HISTORY"                        7.203 KB      10 rows
Processing object type SCHEMA_EXPORT/USER
. . exported "TEST"."LOCATIONS"                          8.445 KB      23 rows
. . exported "TEST"."REGIONS"                            5.554 KB       4 rows
. . exported "TEST"."TEST"                               5.648 KB      13 rows
. . exported "TEST"."SALES_TAB_COPY"                     89.14 MB 1398102 rows
. . exported "TEST"."SALES_TAB"                          89.14 MB 1398102 rows
. . exported "TEST"."TABX"                               79.96 MB 1048576 rows
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/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/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/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
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/schema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Aug 10 13:48:20 2021 elapsed 0 00:01:12

Import only specific 4 tables from TEST schema dump from prod to dev schema TEST by replacing them . This scenario is similar to 3rd point while export of data but for import you have to specify an extra parameter ‘tables’

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp dumpfile=schema.dmp logfile=schema.log tables=test.COUNTRIES,test.DEPARTMENTS,test.EMPLOYEES,test.JOBS table_exists_action=replace parallel=8

Import: Release 12.1.0.2.0 - Production on Tue Aug 10 14:41:50 2021

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 "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp dumpfile=schema.dmp logfile=schema.log tables=test.COUNTRIES,test.DEPARTMENTS,test.EMPLOYEES,test.JOBS table_exists_action=replace parallel=8
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."COUNTRIES"                          6.468 KB      25 rows
. . imported "TEST"."DEPARTMENTS"                        7.132 KB      27 rows
. . imported "TEST"."EMPLOYEES"                          18.40 KB     107 rows
. . imported "TEST"."JOBS"                               7.101 KB      19 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_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/INDEX/STATISTICS/FUNCTIONAL_INDEX/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 "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Aug 10 14:41:54 2021 elapsed 0 00:00:04

5. Export a schema ‘TEST’ from production and import only data from the specific tables out of whole schema dump without overwriting the tables to development or QA by maintaining the original structure of table . This scenario is similar to 4th point while export of data but here you have to specify table_exist_action=truncate . All the dependent metadata like constraints if already exist will be skipped as part of truncate option

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp dumpfile=schema.dmp logfile=schema.log tables=test.COUNTRIES,test.DEPARTMENTS,test.EMPLOYEES,test.JOBS table_exists_action=truncate parallel=8

Import: Release 12.1.0.2.0 - Production on Tue Aug 10 14:49:25 2021

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 "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp dumpfile=schema.dmp logfile=schema.log tables=test.COUNTRIES,test.DEPARTMENTS,test.EMPLOYEES,test.JOBS table_exists_action=truncate parallel=8
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39120: Table "TEST"."JOBS" can't be truncated, data will be skipped. Failing error is:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
ORA-00955: name is already used by an existing object
ORA-39120: Table "TEST"."EMPLOYEES" can't be truncated, data will be skipped. Failing error is:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
ORA-00955: name is already used by an existing object
ORA-39120: Table "TEST"."DEPARTMENTS" can't be truncated, data will be skipped. Failing error is:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
ORA-00955: name is already used by an existing object
Table "TEST"."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 "TEST"."COUNTRIES"                          6.468 KB      25 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."JOBS" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"TEST"."DEPT_LOCATION_IX" skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type INDEX:"TEST"."DEPT_ID_PK" skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type INDEX:"TEST"."JOB_ID_PK" skipped, base object type TABLE:"TEST"."JOBS" creation failed
ORA-39112: Dependent object type INDEX:"TEST"."EMP_NAME_IX" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"TEST"."EMP_MANAGER_IX" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"TEST"."EMP_JOB_IX" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"TEST"."EMP_DEPARTMENT_IX" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"TEST"."EMP_EMP_ID_PK" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type INDEX:"TEST"."EMP_EMAIL_UK" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
ORA-39112: Dependent object type INDEX:"TEST"."FNC_IDX" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"TEST"."DEPT_ID_PK" skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TEST"."JOB_ID_PK" skipped, base object type TABLE:"TEST"."JOBS" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TEST"."EMP_EMP_ID_PK" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TEST"."EMP_EMAIL_UK" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type CONSTRAINT:"TEST"."EMP_SALARY_MIN" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39112: Dependent object type REF_CONSTRAINT:"TEST"."DEPT_LOC_FK" skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"TEST"."DEPT_MGR_FK" skipped, base object type TABLE:"TEST"."DEPARTMENTS" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"TEST"."EMP_DEPT_FK" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"TEST"."EMP_JOB_FK" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"TEST"."EMP_MANAGER_FK" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39112: Dependent object type TRIGGER:"TEST"."SECURE_EMPLOYEES" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
ORA-39112: Dependent object type TRIGGER:"TEST"."UPDATE_JOB_HISTORY" skipped, base object type TABLE:"TEST"."EMPLOYEES" creation failed
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" completed with 47 error(s) at Tue Aug 10 14:49:44 2021 elapsed 0 00:00:19

6. Append the data to an existing table from prod to dev

Example: regions_bkp contains new row called 120,north and we need to append this row to regions table which missed the row. ‘Append‘ is also similar to insert of row into an existing table

SQL> select * from test.regions_bkp;

 REGION_ID REGION_NAME
---------- -------------------------
       120 north


SQL> select * from regions;

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa

I exported the regions_bkp table which contains extra data 

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=append.dmp logfile=append.log tables=test.regions_bkp

Export: Release 12.1.0.2.0 - Production on Tue Aug 10 22:35:00 2021

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 "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp dumpfile=append.dmp logfile=append.log tables=test.regions_bkp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."REGIONS_BKP"                        5.570 KB       5 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/append.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Aug 10 22:35:09 2021 elapsed 0 00:00:08

Other tips:

7)If you use same dumpfile which was previously used before , then use this parameter reuse_dumpfiles=Y

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=append.dmp logfile=append.log tables=test.regions_bkp

Export: Release 12.1.0.2.0 - Production on Fri Aug 13 14:17:46 2021

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
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/home/oracle/append.dmp"
ORA-27038: created file already exists
Additional information: 1

After use of this parameter, existing dumps can be overwritten 

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=append.dmp logfile=append.log tables=test.regions_bkp reuse_dumpfiles=Y

Export: Release 12.1.0.2.0 - Production on Fri Aug 13 14:59:31 2021

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 "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp dumpfile=append.dmp logfile=append.log tables=test.regions_bkp reuse_dumpfiles=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."REGIONS_BKP"                          5.5 KB       1 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/append.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 13 14:59:55 2021 elapsed 0 00:00:23

8) If you want only metadata backup to be pulled out from the objects like the table structure , then use content=metadata_only

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=metadatabkp.dmp logfile=metadatabkp.log tables=test.tabx content=metadata_only

Export: Release 12.1.0.2.0 - Production on Fri Aug 13 17:47:23 2021

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 "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp dumpfile=metadatabkp.dmp logfile=metadatabkp.log tables=test.tabx content=metadata_only
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/metadatabkp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 13 17:47:33 2021 elapsed 0 00:00:10

The metadata option would only export the definitions of table which looks like this for tabx table

-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "TEST"."TABX"
   (    "COUNTRY" VARCHAR2(380 BYTE),
        "ITEM_TYPE" VARCHAR2(38 BYTE),
        "SALES_CHANNEL" VARCHAR2(38 BYTE),
        "ORDER_PRIORITY" VARCHAR2(38 BYTE),
        "ORDER_DATE" DATE,
        "ORDER_ID" NUMBER(20,0),
        "SHIP_DATE" DATE,
        "UNITS_SOLD" NUMBER(38,0),
        "UNIT_PRICE" NUMBER(38,0),
        "UNIT_COST" NUMBER(38,0),
        "TOTAL_REVENUE" NUMBER(38,0),
        "TOTAL_COST" NUMBER(38,0),
        "TOTAL_PROFIT" NUMBER(38,0),
        "NULL_COLUMN" VARCHAR2(10 BYTE),
        "ORDER_COST" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/MARKER

9) If you want to export the definitions of table or index from to an sql file, try the following with impdp using sqlfile=filename.sql

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp dumpfile=metadatabkp.dmp sqlfile=databkp.sql

Import: Release 12.1.0.2.0 - Production on Fri Aug 13 17:55:50 2021

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
ORA-31655: no data or metadata objects selected for job
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_SQL_FILE_FULL_01":  "/******** AS SYSDBA" directory=exp dumpfile=metadatabkp.dmp sqlfile=databkp.sql
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at Fri Aug 13 17:55:51 2021 elapsed 0 00:00:01

10) Exporting specific predicate result set from where clause using expdp 

SQL> select count(*) from test.tabx where order_id > 100000;

  COUNT(*)
----------
    474288

11)Use query parameter to export only specific predicate data from a table

[oracle@orcl19x ~]$ expdp \'/ as sysdba\' directory=exp dumpfile=querybkp.dmp logfile=querybkp.log tables=test.tabx query="'where order_id > 100000'" reuse_dumpfiles=Y

Export: Release 12.1.0.2.0 - Production on Fri Aug 13 18:35:12 2021

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 "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp dumpfile=querybkp.dmp logfile=querybkp.log tables=test.tabx query='where order_id > 100000' reuse_dumpfiles=Y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 96 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "TEST"."TABX"                               36.41 MB  474288 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/querybkp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 13 18:35:19 2021 elapsed 0 00:00:07

12)Use network link if you have less space to store the export dump and have a strong network connection like infiniband switch with rds on source and destination

In order to use network link, you have to create database link on the destination with source service name used in TNS entry

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX 

Import: Release 12.1.0.2.0 - Production on Mon Aug 16 16:55:10 2021

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 "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX metrics=YES exclude=statistics
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
     Estimated 1 TABLE_DATA objects in 0 seconds
Total estimation using BLOCKS method: 95.75 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 2 seconds
. . imported "TEST"."TABX"                              1048576 rows in 20 seconds
     Completed 1 TABLE_EXPORT/TABLE/TABLE_DATA objects in 20 seconds
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Mon Aug 16 16:55:37 2021 elapsed 0 00:00:25

13)Use metrics parameter to show the time statistics on the export or import log

;;;
Import: Release 12.1.0.2.0 - Production on Mon Aug 16 16:55:10 2021

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 "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX metrics=YES 
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
     Estimated 1 TABLE_DATA objects in 0 seconds
Total estimation using BLOCKS method: 95.75 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 2 seconds
. . imported "TEST"."TABX"                              1048576 rows in 20 seconds
     Completed 1 TABLE_EXPORT/TABLE/TABLE_DATA objects in 20 seconds
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Mon Aug 16 16:55:37 2021 elapsed 0 00:00:25

14)To exclude the metadata like index,constraint and statistics during import, use exclude parameter 

[oracle@orcl19x ~]$ impdp \'/ as sysdba\' directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX metrics=YES exclude=statistics,indexes,constraints

Import: Release 12.1.0.2.0 - Production on Mon Aug 16 20:02:14 2021

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 "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=exp logfile=NL_TEST.log tables=TEST.TABX table_exists_action=replace network_link=EXDBX metrics=YES exclude=statistics,indexes,constraints
Startup took 1 seconds
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
     Estimated 1 TABLE_DATA objects in 1 seconds
Total estimation using BLOCKS method: 95.75 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
     Completed 1 TABLE objects in 3 seconds
. . imported "TEST"."TABX"                              1048576 rows in 17 seconds
     Completed 1 TABLE_EXPORT/TABLE/TABLE_DATA objects in 17 seconds
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Mon Aug 16 20:02:39 2021 elapsed 0 00:00:24

Use parameter file (PAR) which is a standardized way to perform the datapump operations 

[oracle@exdbadm01 expdp]$ cat expdp_TEST.par
directory=EDP
dumpfile=exp_SALES_TAB_%U.dmp
logfile=exp_SALES_TAB.log
tables=TEST.SALES_TAB
parallel=2
filesize=50M
cluster=Y

Use nohup when you export or import LOB objects to run in background avoiding any session timeout or kickout 


[oracle@exdbadm01 ~]$ nohup expdp \'/ as sysdba\' parfile=expdp_TEST.par &
[1] 27308
[oracle@exdbadm01 ~]$ nohup: appending output to `nohup.out'

To monitor the datapump from OS level use ps command

[oracle@exdbadm01 ~]$ ps -ef|grep 'expdp'
oracle   27308  6583  0 03:23 pts/1    00:00:00 expdp    as sysdba' parfile=expdp_TEST.par
oracle   27330  6583  0 03:23 pts/1    00:00:00 grep expdp

There is hidden parameter trace for tracing the datapump jobs for both master and worker process.Below are the trace number with specifications

Sample trace file generated for the error 

[oracle@exdbadm01 expdp]$ cat expdp_TEST.par
directory=EDP
dumpfile=exp_SALES_TAB_%U.dmp
logfile=exp_SALES_TAB.log
tables=TEST.SALES_TAB
parallel=2
filesize=50M
cluster=Y
reuse_dumpfiles=Y
trace=480300

ORA-19505: failed to identify file "/home/oracle/expdp/exp_SALES_TAB_02.dmp"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.KUPW$WORKER", line 9650
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xc2ca32e0     21979  package body SYS.KUPW$WORKER
0xc2ca32e0      9742  package body SYS.KUPW$WORKER
0xc2ca32e0      3366  package body SYS.KUPW$WORKER
0xc2ca32e0     10436  package body SYS.KUPW$WORKER
0xc2ca32e0      1824  package body SYS.KUPW$WORKER
0xc2c23ef8         2  anonymous block

Monitoring and Controlling the datapump jobs

To monitor the datapump jobs, just tail the logfile

[oracle@exdbadm01 expdp]$ tail -f exp_SALES_TAB.log
;;;
Export: Release 11.2.0.4.0 - Production on Wed Sep 29 03:57:32 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_13":  "/******** AS SYSDBA" parfile=expdp_TEST.par

To monitor the job with additional statistics , then grab the job name from the logfile for the master table. Use the below command to fetch the job name from the logfile

[oracle@exdbadm01 expdp]$ grep 'Starting' exp_SALES_TAB.log|awk -F '.' '{print $2}'|awk '{print $1}'
"SYS_EXPORT_TABLE_13":

Use attach option to get into the prompt 

[oracle@exdbadm01 ~]$ expdp \'/ as sysdba \' attach=SYS_EXPORT_TABLE_13

If you wish to see the status of job, then do this

Export> status

To influence the parallelism for the job or change the number of parallel workers, then

Export> parallel=4

To stop the job 

Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

If you want to restart the job after pause, specify CONTINUE_CLIENT 

Export> CONTINUE_CLIENT
Job SYS_EXPORT_TABLE_13 has been reopened at Wed Sep 29 04:59:23 2021
Restarting "SYS"."SYS_EXPORT_TABLE_13":  "/******** AS SYSDBA" parfile=expdp_TEST.par
Job "SYS"."SYS_EXPORT_TABLE_13" completed with 2 error(s) at Wed Sep 29 04:59:34 2021 elapsed 0 00:00:12

To start the job

Export> START_JOB=SKIP_CURRENT

To kill job

Export> KILL_JOB
Comments 0

Leave a Reply

Discover more from XscalibaL

Subscribe now to keep reading and get access to the full archive.

Continue reading