Manage schema data

Go back to OCM 12c Preparation Project

Hands On Lab – Index

Manage schema data
[1] DataPump
[2] SqlLoader

[1] DataPump
-> New Features in 12.1.0.2

Export parameters

1.1 tables

-bash-4.1$ sqlplus system/oracle_4U@racnode2/pdb2

SQL> create table tbl_dp_tab_export as select * from dba_objects;

Table created.

SQL> create directory DATA_PUMP_PDIR AS '/u03/app/oracle/admin/pdb2/dpdump';

Directory created.

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_table.dmp logfile=log_table.log tables=tbl_dp_tab_export

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 10:55:41 2019

#Create an partition table and export only single partition.

SQL> create table tbl_dp_tab_export1
2 partition by range(object_id)
3 ( partition one values less than (100),
4 partition two values less than (maxvalue))
5 as
6 select * from dba_objects;

Table created.

SQL> select count(*) from tbl_dp_tab_export1 partition (one);

COUNT(*)
----------
98

SQL> select count(*) from tbl_dp_tab_export1 partition (two)

COUNT(*)
----------
91637

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_tab_par2.dmp logfile=log_tab_part2.log tables=tbl_dp_tab_export1:one,tbl_dp_tab_export

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:02:10 2019

1.2 schemas

-bash-4.1$ sqlplus system/oracle_4U@racnode2/pdb2

SQL> create user usr_exp1 identified by usr_exp1;

User created.

SQL> r
1* create user usr_exp2 identified by usr_exp2

User created.

SQL> grant connect,resource to usr_exp1,usr_exp2;

Grant succeeded.

SQL> alter user usr_exp1 default tablespace users quota unlimited on users;

User altered.

SQL> r
1* alter user usr_exp2 default tablespace users quota unlimited on users

User altered.

SQL> create table usr_exp1.tbl_one as select * from dba_objects;

Table created.

SQL> c/one/two
1* create table usr_exp1.tbl_two as select * from dba_objects
SQL> r
1* create table usr_exp1.tbl_two as select * from dba_objects

Table created.

SQL> c/exp1/exp2
1* create table usr_exp2.tbl_two as select * from dba_objects
SQL> r
1* create table usr_exp2.tbl_two as select * from dba_objects

Table created.

SQL> c/two/one
1* create table usr_exp2.tbl_one as select * from dba_objects
SQL> r
1* create table usr_exp2.tbl_one as select * from dba_objects

Table created.

SQL> quit

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=schema_exp.dmp logfile=schema_log.log schemas=usr_exp1,usr_exp2

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:12:31 2019

1.3 full
#Export entire database [NO].
$expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_full.dmp logfile=log_full.log full=y

#Import everything from source [YES].
$impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_full.dmp logfile=log_full.log full=y

1.4 Filtering export of DB objects
(INCLUDE/EXCLUDE/parfile Parameter)

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

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

Use the following views to get the list of objects that may be passed
as a value for both parameters.

col object_path format a30
col comments format a60
set linesize 150
set pagesize 32676
select * from database_export_objects where named='Y';
select * from schema_export_objects where named='Y';
select * from table_export_objects where named='Y';

SQL> grant connect,resource,unlimited tablespace to usr_deptest1
identified by oracle;

Grant succeeded.

SQL> create table usr_dptest1.tbl_one as select * from dba_objects where rownum < 100;
create table usr_dptest1.tbl_one as select * from dba_objects where rownum < 100 * ERROR at line 1: ORA-01918: user 'USR_DPTEST1' does not exist SQL> create table usr_deptest1.tbl_one as select * from dba_objects where rownum < 100; Table created. SQL> c/one/two
1* create table usr_deptest1.tbl_two as select * from dba_objects where rownum < 100 SQL> r
1* create table usr_deptest1.tbl_two as select * from dba_objects where rownum < 100 Table created. SQL> c/two/three
1* create table usr_deptest1.tbl_three as select * from dba_objects where rownum < 100 SQL> r
1* create table usr_deptest1.tbl_three as select * from dba_objects where rownum < 100 Table created. SQL> create procedure usr_deptest1.my_proc
2 as
3 begin
4 NULL;
5 END;
6 /

Procedure created.

SQL> create view usr_deptest1.my_view as select * from tbl_one;

View created.

SQL> quit

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_include_test1.dmp schemas=usr_deptest1 include=table

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:26:10 2019

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_include_test2.dmp schemas=usr_deptest1 include=view

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:26:35 2019

#parfile

$ cat test_parfile.dat
schemas=usr_deptest1
directory=DATA_PUMP_PDIR
dumpfile=exp_parfile_test.dmp
logfile=log_parfile_test.log
include=procedure
include=table:"in ('TBL_ONE','TBL_TWO')"
include=view:"LIKE 'MY%'"

$ expdp system/oracle_4U@racnode2/pdb2 parfile=test_parfile.dat

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:39:06 2019

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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 parfile=test_parfile.dat
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USR_DEPTEST1"."TBL_ONE" 21.50 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 21.50 KB 99 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_parfile_test.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 11:39:20 2019 elapsed 0 00:00:12

1.5 Perform compressed export

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.

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

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 dumpfile=exp_compress.dmp logfile=log_compress.log
compression=all compression_algorithm=HIGH

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:44:06 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR schemas=usr_deptest1 dumpfile=exp_compress.dmp logfile=log_compress.log compression=all compression_algorithm=HIGH
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_DEPTEST1"."TBL_ONE" 6.148 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_THREE" 6.148 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 6.148 KB 99 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_compress.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 11:44:27 2019 elapsed 0 00:00:19

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 dumpfile=exp_noncompress.dmp
logfile=log_noncompress.log compression=NONE

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:45:08 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR schemas=usr_deptest1 dumpfile=exp_noncompress.dmp logfile=log_noncompress.log compression=NONE
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_DEPTEST1"."TBL_ONE" 21.50 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_THREE" 21.50 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 21.50 KB 99 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_noncompress.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 11:45:29 2019 elapsed 0 00:00:20

$ ls -ltr *compress*
-rw-r----- 1 oracle oinstall 102400 Sep 15 11:44 exp_compress.dmp
-rw-r--r-- 1 oracle oinstall 1884 Sep 15 11:44 log_compress.log
-rw-r----- 1 oracle oinstall 446464 Sep 15 11:45 exp_noncompress.dmp
-rw-r--r-- 1 oracle oinstall 1867 Sep 15 11:45 log_noncompress.log

1.6 Specify type of content to export
CONTENT Parameter

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

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

CONTENT = DATA_ONLY | METADATA_ONLY | ALL(default option)

-bash-4.1$ sqlplus system/oracle_4U@racnode2/pdb2

SQL> grant connect,resource,unlimited tablespace to usr_content_test identified by oracle;

Grant succeeded.

SQL> create table usr_content_test.tbl_one as select * from dba_objects;

Table created.

SQL>
SQL> select count(*) from usr_content_test.tbl_one;

COUNT(*)
----------
91745

-bash-4.1$
$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=usr_content_test.dmp content=metadata_only schemas=usr_content_test

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:51:45 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

-bash-4.1$ sqlplus system/oracle_4U@racnode2/pdb2

SQL> drop table usr_content_test.tbl_one;

Table dropped.

-bash-4.1$ impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=usr_content_test.dmp

Import: Release 12.1.0.2.0 - Production on Sun Sep 15 11:52:43 2019
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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=usr_content_test.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"USR_CONTENT_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/TABLE/TABLE
ORA-39151: Table "USR_CONTENT_TEST"."TBL_ONE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Sun Sep 15 11:52:46 2019 elapsed 0 00:00:02

-bash-4.1$ sqlplus system/oracle_4U@racnode2/pdb2
SQL> select count(*) from usr_content_test.tbl_one;

COUNT(*)
----------
0

SQL> quit

1.6 Perform encrypted export

(ENCRYPTION(ALL | DATA_ONLY | METADATA_ONLY | ENCRYPTED_COLUMNS_ONLY | NONE(default)),
ENCRYPTION_ALGORITHM(AES128,AES192,AES256 Options),
ENCRYPTION_MODE(PASSWORD(default),TRANSPERENT,DUAL Options),
ENCRYPTION_PASSWORD)

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

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_encrypt_test1.dmp logfile=log_encrypt_test1.log
schemas=usr_deptest1 encryption=all encryption_algorithm=AES256
encryption_mode=password encryption_password=oracle

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:58:06 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_encrypt_test1.dmp logfile=log_encrypt_test1.log schemas=usr_deptest1 encryption=all encryption_algorithm=AES256 encryption_mode=password encryption_password=********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_DEPTEST1"."TBL_ONE" 21.51 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_THREE" 21.51 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 21.51 KB 99 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_encrypt_test1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 11:58:32 2019 elapsed 0 00:00:25

SQL> drop user usr_deptest1 cascade;

User dropped.

SQL> grant connect,resource,unlimited tablespace to usr_deptest1 identified by oracle;

Grant succeeded.

$ impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_encrypt_test1.dmp schemas=usr_deptest1 encryption_password=oracle

Import: Release 12.1.0.2.0 - Production on Sun Sep 15 12:03:16 2019
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 "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_encrypt_test1.dmp schemas=usr_deptest1 encryption_password=********
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"USR_DEPTEST1" 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USR_DEPTEST1"."TBL_ONE" 21.51 KB 99 rows
. . imported "USR_DEPTEST1"."TBL_THREE" 21.51 KB 99 rows
. . imported "USR_DEPTEST1"."TBL_TWO" 21.51 KB 99 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed with 1 error(s) at Sun Sep 15 12:03:32 2019 elapsed 0 00:00:15

#Test with ENCRYPTION_PWD_PROMPT

-> In previous versions, data pump encryption required the ENCRYPTION_PASSWORD parameter to be entered on the command line, making password snooping relatively easy.
In Oracle 12c, the ENCRYPTION_PWD_PROMPT parameter enables encryption without requiring the password to be entered as a command line parameter. Instead, the user
is prompted for the password at runtime, with their response not echoed to the screen.
ENCRYPTION_PWD_PROMPT=[YES | NO]

SQL> drop user usr_deptest1 cascade;

User dropped.

SQL> grant connect,resource,unlimited tablespace to usr_deptest1 identified by oracle;

Grant succeeded.

SQL> quit

-bash-4.1$ impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_encrypt_test1.dmp schemas=usr_deptest1 encryption_pwd_prompt=Y

Import: Release 12.1.0.2.0 - Production on Sun Sep 15 12:06:08 2019

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

Encryption Password:
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_encrypt_test1.dmp schemas=usr_deptest1 encryption_pwd_prompt=Y
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"USR_DEPTEST1" 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USR_DEPTEST1"."TBL_ONE" 21.51 KB 99 rows
. . imported "USR_DEPTEST1"."TBL_THREE" 21.51 KB 99 rows
. . imported "USR_DEPTEST1"."TBL_TWO" 21.51 KB 99 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed with 1 error(s) at Sun Sep 15 12:06:29 2019 elapsed 0 00:00:20

#Test with ENCRYPTION_MODE=DUAL

-bash-4.1$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_encrypt_test2.dmp logfile=log_encrypt_test2.log schemas=usr_deptest1 encryption=all encryption_algorithm=AES192 encryption_mode=dual encryption_password=oracle
Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:00:03 2019
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-39002: invalid operation
ORA-39188: unable to encrypt dump file set
ORA-28365: wallet is not open

-bash-4.1$ sql
SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
CLOSED /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle_4U CONTAINER=ALL;

keystore altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/

SQL> ALTER SESSION SET CONTAINER=PDB2;

Session altered.

SQL> select status,wrl_parameter from v$encryption_wallet;

STATUS WRL_PARAMETER
--------------------------------------------------------------------------------
OPEN /u01/app/oracle/product/12.1.0.2/db_1/wallets/cdb2/

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_encrypt_test2.dmp logfile=log_encrypt_test2.log
schemas=usr_deptest1 encryption=all encryption_algorithm=AES192
encryption_mode=dual encryption_pwd_prompt=yes

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:15:06 2019

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

Encryption Password:
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_encrypt_test2.dmp logfile=log_encrypt_test2.log schemas=usr_deptest1 encryption=all encryption_algorithm=AES192 encryption_mode=dual encryption_pwd_prompt=yes
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_DEPTEST1"."TBL_ONE" 21.44 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_THREE" 21.44 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 21.44 KB 99 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_encrypt_test2.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 12:15:30 2019 elapsed 0 00:00:23

SQL> drop user usr_deptest1 cascade;

User dropped.

SQL> grant connect,resource,unlimited tablespace to usr_deptest1 identified by oracle;

Grant succeeded.

$ impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_encrypt_test2.dmp schemas=usr_deptest1 encryption_pwd_prompt=yes
Import: Release 12.1.0.2.0 - Production on Sun Sep 15 12:17:08 2019

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

Encryption Password:
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_encrypt_test2.dmp schemas=usr_deptest1 encryption_pwd_prompt=yes
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"USR_DEPTEST1" 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USR_DEPTEST1"."TBL_ONE" 21.44 KB 99 rows
. . imported "USR_DEPTEST1"."TBL_THREE" 21.44 KB 99 rows
. . imported "USR_DEPTEST1"."TBL_TWO" 21.44 KB 99 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed with 1 error(s) at Sun Sep 15 12:17:30 2019 elapsed 0 00:00:20

1.7 Estimate the disk space of the export process
ESTIMATE(blocks(default) | statistics),ESTIMATE_ONLY=Y Parameters

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_estimate_test1.dmp logfile=log_estimate_test1.log
estimate=blocks schemas=usr_deptest1

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:22:05 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_estimate_test1.dmp logfile=log_estimate_test1.log estimate=blocks schemas=usr_deptest1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "USR_DEPTEST1"."TBL_ONE" 64 KB
. estimated "USR_DEPTEST1"."TBL_THREE" 64 KB
. estimated "USR_DEPTEST1"."TBL_TWO" 64 KB
Total estimation using BLOCKS method: 192 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_DEPTEST1"."TBL_ONE" 21.43 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_THREE" 21.43 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 21.43 KB 99 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_estimate_test1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 12:22:27 2019 elapsed 0 00:00:21

$expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_estimate_test2.dmp logfile=log_estimate_test2.log
estimate=statistics schemas=usr_deptest1

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:22:52 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_estimate_test2.dmp logfile=log_estimate_test2.log estimate=statistics schemas=usr_deptest1
Estimate in progress using STATISTICS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "USR_DEPTEST1"."TBL_ONE" 19.84 KB
. estimated "USR_DEPTEST1"."TBL_THREE" 19.84 KB
. estimated "USR_DEPTEST1"."TBL_TWO" 19.84 KB
Total estimation using STATISTICS method: 59.53 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_DEPTEST1"."TBL_ONE" 21.43 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_THREE" 21.43 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 21.43 KB 99 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_estimate_test2.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 12:23:13 2019 elapsed 0 00:00:20

#->Test with ESTIMATE_ONLY
It will prevent creation of dump file and just estimate the size of the dump file.

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
estimate=blocks schemas=usr_deptest1 estimate_only=yes

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:30:58 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

$ sqlplus system/oracle_4U@racnode2/pdb2
SQL> select segment_name obj_name,(blocks*8192)/1024 size_kb from dba_extents where owner='USR_DEPTEST1';

OBJ_NAME SIZE_KB
----------------------
TBL_THREE 64
TBL_TWO 64
TBL_ONE 64

-bash-4.1$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
estimate=statistics schemas=usr_deptest1 estimate_only=yes
Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:31:17 2019

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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR estimate=statistics schemas=usr_deptest1 estimate_only=yes
Estimate in progress using STATISTICS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "USR_DEPTEST1"."TBL_ONE" 19.84 KB
. estimated "USR_DEPTEST1"."TBL_THREE" 19.84 KB
. estimated "USR_DEPTEST1"."TBL_TWO" 19.84 KB
Total estimation using STATISTICS method: 59.53 KB
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 12:31:21 2019 elapsed 0 00:00:03

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

ORA-31618: FILESIZE is outside valid range of 40KB to 16TB
31618, 00000, "FILESIZE is outside valid range of %sKB to %sTB"
// *Cause: The value specified for the FILESIZE parameter was either too small
// to hold the header information for the export file, plus any data,
// or exceeded the allowed maximum size for a dump file.
// *Action: Retry the operation using a value for FILESIZE that falls within
// the valid range.

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_filesize_test%U.dmp logfile=log_filesize_test.log
filesize=40K schemas=usr_deptest1

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:39:07 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_filesize_test%U.dmp logfile=log_filesize_test.log filesize=40K schemas=usr_deptest1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_DEPTEST1"."TBL_ONE" 21.43 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_THREE" 21.43 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 21.43 KB 99 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test01.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test02.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test03.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test04.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test05.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test06.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test07.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test08.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test09.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test10.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test11.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_filesize_test12.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 12:39:30 2019 elapsed 0 00:00:21

1.9 FLASHBACK_TIME,FLASHBACK_SCN:Parameter

By default the expdp utility exports are only consistent on a per table basis.
If you want all tables in the export to be
consistent to the same point in time, you need to use the FLASHBACK_SCN or
FLASHBACK_TIME parameter.

FLASHBACK_TIME
Time used to find the closest corresponding SCN value.

expdp ..... flashback_time=systimestamp

# In parameter file.
flashback_time="to_timestamp('09-05-2011 09:00:00', 'DD-MM-YYYY HH24:MI:SS')"

# Escaped on command line.
expdp ..... flashback_time=\"to_timestamp$\'09-05-2011 09:00:00\', \'DD-MM-YYYY HH24:MI:SS\'$\"

FLASHBACK_SCN
SCN used to reset session snapshot.

Demo

SQL> grant connect,resource to usr_flashback_dp identified by oracle;

Grant succeeded.

SQL> alter user usr_flashback_dp default tablespace users quota unlimited on users;

User altered.

SQL> create table usr_flashback_dp.tbl_test tablespace users as select * from dba_objects;

Table created.

SQL> select owner,table_name,tablespace_name from dba_tables where owner='USR_FLASHBACK_DP';

OWNER TABLE_NAME TABLESPACE_NAME
-----------------------------------------------------------------
USR_FLASHBACK_DP TBL_TEST USERS

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
4161987

SQL> delete from usr_flashback_dp.tbl_test;

91755 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from usr_flashback_dp.tbl_test;

no rows selected

SQL> quit

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_flashback_test01.dmp logfile=log_flashback_test01.log
schemas=usr_flashback_dp flashback_scn=4161987

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:48:29 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_flashback_test01.dmp logfile=log_flashback_test01.log schemas=usr_flashback_dp flashback_scn=4161987
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_FLASHBACK_DP"."TBL_TEST" 10.45 MB 91755 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_flashback_test01.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 12:48:51 2019 elapsed 0 00:00:20

-bash-4.1$ sql
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter session set container=pdb2;

Session altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> quit

#Test with CONSISTENT=Y

The exp utility used the CONSISTENT=Y parameter to indicate the export
should be consistent to a point in time.
In 11.2, the introduction of legacy mode means that you can use the
CONSISTENT=Y parameter with the expdp utility if you wish.

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_flashback_test01.dmp logfile=log_flashback_test01.log
schemas=usr_flashback_dp CONSISTENT=y

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 12:54:57 2019
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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "consistent=TRUE" Location: Command Line, Replaced with: "flashback_time=TO_TIMESTAMP('2019-09-15 12:54:57', 'YYYY-MM-DD HH24:MI:SS')"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_flashback_test01.dmp logfile=log_flashback_test01.log schemas=usr_flashback_dp flashback_time=TO_TIMESTAMP('2019-09-15 12:54:57', 'YYYY-MM-DD HH24:MI:SS') reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 13 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "USR_FLASHBACK_DP"."TBL_TEST" 12.39 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_flashback_test01.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 12:55:15 2019 elapsed 0 00:00:17

1.10 PARALLEL Parameter
Change the number of active workers for current job.

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_parallel_test%U.dmp logfile=log_parallel_test.log
schemas=usr_deptest1 parallel=2

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 13:01:33 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR dumpfile=exp_parallel_test%U.dmp logfile=log_parallel_test.log schemas=usr_deptest1 parallel=2
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
. . exported "USR_DEPTEST1"."TBL_ONE" 21.43 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_THREE" 21.43 KB 99 rows
. . exported "USR_DEPTEST1"."TBL_TWO" 21.43 KB 99 rows
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/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_parallel_test01.dmp
/u03/app/oracle/admin/pdb2/dpdump/exp_parallel_test02.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 13:01:55 2019 elapsed 0 00:00:20

$ ll exp_parallel_test*
-rw-r----- 1 oracle oinstall 380928 Sep 15 13:01 exp_parallel_test01.dmp
-rw-r----- 1 oracle oinstall 65536 Sep 15 13:01 exp_parallel_test02.dmp

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

-bash-4.1$ sqlplus system/oracle_4U@racnode2/pdb2

SQL> create table usr_deptest1.new_table tablespace users as select * from dba_objects;

Table created.

SQL> r
1* create table usr_deptest1.new_table2 tablespace users as select * from dba_objects

Table created.

SQL> select count(*) from new_table;

COUNT(*)
----------
91756

SQL> select count(*) from new_table2;

COUNT(*)
----------
91757

SQL> select count(*) from usr_deptest1.NEW_TABLE WHERE object_id<100;
COUNT(*)
----------
98

SQL> select count(*) from usr_deptest1.NEW_TABLE2 WHERE object_id<400
and object_type='TABLE';

COUNT(*)
----------
170

$ cat test2_parfile.dat
directory=data_pump_pdir
dumpfile=exp_query_03.dmp
logfile=log_query_03.log
schemas=usr_deptest1
include=table:"in ('NEW_TABLE','NEW_TABLE2')"
query=usr_deptest1.NEW_TABLE:"WHERE object_id<100"
query=usr_deptest1.NEW_TABLE2:"WHERE object_id<400 and object_type='TABLE'"

$ expdp system/oracle_4U@racnode2/pdb2 parfile=test2_parfile.dat

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 13:53:16 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 parfile=test2_parfile.dat
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 26 MB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USR_DEPTEST1"."NEW_TABLE" 21.41 KB 98 rows
. . exported "USR_DEPTEST1"."NEW_TABLE2" 29.5 KB 170 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_query_03.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 13:53:27 2019 elapsed 0 00:00:10

1.12 REMAP_DATA Parameter
Apply specific function to each row that is being exported

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

create or replace package usr_deptest1.pkg_remap
as
function make_upper(p_val varchar2)
return varchar2;
end;
/

create or replace package body usr_deptest1.pkg_remap
as
function make_upper(p_val varchar2)
return varchar2
is
begin
return upper(p_val);
end;
end;
/

create table usr_deptest1.all_lowers
as
select lower(object_name) obj_name,object_id,object_type from dba_objects
where rownum <= 5;

SET LINESIZE 150
COL obj_name FORMAT a15
SELECT * FROM usr_deptest1.all_lowers;
OBJ_NAME OBJECT_ID OBJECT_TYPE
--------------- ---------- -----------------------
icol$ 20 TABLE tab$ 4 TABLE
i_user2 47 INDEX
i_ccol2 58 INDEX
i_obj1 36 INDEX
$ cat test3_parfile.dat directory=data_pump_pdir
dumpfile=exp_remap_data_test1.dmp logfile=log_remap_data_test1.log
schemas=usr_deptest1 include=table:"in('ALL_LOWERS')"
remap_data=usr_deptest1.all_lowers.obj_name:usr_deptest1.pkg_remap.make_upper

$ expdp system/oracle_4U@racnode2/pdb2 parfile=test3_parfile.dat
Export: Release 12.1.0.2.0 - Production on Sun Sep 15 14:18:48 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01":
system/********@racnode2/pdb2 parfile=test3_parfile.dat
Estimate in progress using BLOCKS method... Processing object type
SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method:
64 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object
type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . .
exported "USR_DEPTEST1"."ALL_LOWERS" 6.023 KB 5 rows Master table
"SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_remap_data_test1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at
Sun Sep 15 14:19:09 2019 elapsed 0 00:00:18

SQL> drop table usr_deptest1.all_lowers;

Table dropped.

$ impdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir dumpfile=exp_remap_data_test1.dmp

Import: Release 12.1.0.2.0 - Production on Sun Sep 15 14:21:15 2019
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 "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@racnode2/pdb2 directory=data_pump_pdir dumpfile=exp_remap_data_test1.dmp
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "USR_DEPTEST1"."ALL_LOWERS" 6.023 KB 5 rows
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Sep 15 14:21:19 2019 elapsed 0 00:00:02

-bash-4.1$ sqlplus system/oracle_4U@racnode2/pdb2

SET LINESIZE 150
COL obj_name FORMAT a15
SELECT * FROM usr_deptest1.all_lowers;

OBJ_NAME OBJECT_ID OBJECT_TYPE
--------------- ---------- -----------------------
ICOL$ 20 TABLE
TAB$ 4 TABLE
I_USER2 47 INDEX
I_CCOL2 58 INDEX
I_OBJ1 36 INDEX

1.13 REUSE_DUMPFILES Parameters

Specify if the dump file should be reused if the same command is run again
Overwrite destination dump file if it exists [NO].

$ expdp system/oracle_4U@racnode2/pdb2 parfile=test3_parfile.dat

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 14:26:16 2019

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 "/u03/app/oracle/admin/pdb2/dpdump/exp_remap_data_test1.dmp"
ORA-27038: created file already exists
Additional information: 1

$ expdp system/oracle_4U@racnode2/pdb2 parfile=test3_parfile.dat reuse_dumpfiles=yes

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 14:26:34 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 parfile=test3_parfile.dat reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "USR_DEPTEST1"."ALL_LOWERS" 6.015 KB 5 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_remap_data_test1.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 14:26:52 2019 elapsed 0 00:00:17

1.14 SAMPLE Parameter
Percentage of data to be exported.

-> Sometimes using Query parameter might not help to limit amount of rows being exported.
Especially, if you export the schemas with lot of different tables it would be hard to specify query parameter for each table.
-> Using SAMPLE parameter you can export specific percentage of data.

create table usr_deptest1.tbl_sample_test tablespace users
as
select * from dba_objects where rownum <= 1000;

Table created.

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 sample=10

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 14:34:38 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 sample=10 Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using
BLOCKS method: 2.643 MB 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/TABLESPACE_QUOTA Processing object type
SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type
SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE
/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/PACKAGE/
PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing
object type SCHEMA_EXPORT/STATISTICS/MARKER . .
exported "USR_DEPTEST1"."NEW_TABLE" 1.053 MB 9140 rows . .
exported "USR_DEPTEST1"."NEW_TABLE2" 1.056 MB 9165 rows . .
exported "USR_DEPTEST1"."TBL_SAMPLE_TEST" 22.68 KB 104 rows . .
exported "USR_DEPTEST1"."ALL_LOWERS" 5.937 KB 1 rows . .
exported "USR_DEPTEST1"."TBL_ONE" 13.33 KB 11 rows . .
exported "USR_DEPTEST1"."TBL_THREE" 12.96 KB 7 rows . .
exported "USR_DEPTEST1"."TBL_TWO" 12.60 KB 3 rows Master table "SYSTEM".
"SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded *********************
*********************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u03/app/oracle/admin/pdb2/dpdump/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 14:35:27 2019 elapsed
0 00:00:45

Note: The usage of CONTENT,INCLUDE,EXCLUDE,PARALLEL,QUERY,REMAP_DATA,SCHEMAS,TABLES
parameters are same in both EXPDP and IMPDP. Import parameters

1.15 REMAP_SCHEMA Parameter Objects
from one schema are loaded into another schema.

$ impdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
dumpfile=expdat.dmp remap_schema=usr_deptest1:hitesh

Import: Release 12.1.0.2.0 - Production on Sun Sep 15 14:47:48 2019
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 "SYSTEM"."SYS_IMPORT_FULL_01"
successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01":
system/********@racnode2/pdb2 directory=data_pump_pdir dumpfile=expdat.dmp
remap_schema=usr_deptest1:hitesh 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/TABLESPACE_QUOTA Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HITESH"."NEW_TABLE" 1.053 MB 9140 rows . .
imported "HITESH"."NEW_TABLE2" 1.056 MB 9165 rows . .
imported "HITESH"."TBL_SAMPLE_TEST" 22.68 KB 104 rows . .
imported "HITESH"."ALL_LOWERS" 5.937 KB 1 rows . .
imported "HITESH"."TBL_ONE" 13.33 KB 11 rows . .
imported "HITESH"."TBL_THREE" 12.96 KB 7 rows . .
imported "HITESH"."TBL_TWO" 12.60 KB 3 rows
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sun Sep 15 14:48:23 2019 elapsed 0 00:00:34

sqlplus HITESH/oracle@racnode2/pdb2

SQL> select tname from tab;

TNAME
--------------------------------------
ALL_LOWERS
MY_VIEW
NEW_TABLE
NEW_TABLE2
TBL_ONE
TBL_SAMPLE_TEST
TBL_THREE
TBL_TWO

8 rows selected.

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

$ impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=expdat.dmp remap_table=NEW_TABLE:NEW_TABLE_RENAMED

1.17 REMAP_TABLESPACE Parameter
Tablespace objects are remapped to another tablespace.

$ impdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
dumpfile=expdat.dmp remap_schema=usr_deptest1:usr_remap_test2 remap_tablespace=users:tbs_remap_test

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

Purpose of this parameter is to skip the import of a table or a table partition with
index in UNUSABLE state. Which means the table should be there in the database with
an index in unusable state. This will reduce the unnecessary time for index creation
which is in unusable state during the import. By default value for this parameter is ‘Y’.
It will come into action when its value is N.

grant connect,resource to test_unused_idx identified by oracle;

alter user test_unused_idx default tablespace users quota unlimited on users;

create table test_unused_idx.tbl_one tablespace users as select * from dba_objects;
create table test_unused_idx.tbl_two tablespace users as select * from dba_objects;

create index test_unused_idx.idx1 on test_unused_idx.tbl_one(object_id) tablespace users;
create index test_unused_idx.idx2 on test_unused_idx.tbl_two(object_id) tablespace users;

alter index test_unused_idx.idx2 unusable;

SQL> select index_name,table_name,STATUS from dba_indexes where owner='TEST_UNUSED_IDX';

INDEX_NAME TABLE_NAME STATUS
------------------------------------------------
IDX1 TBL_ONE VALID
IDX2 TBL_TWO UNUSABLE

$ expdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
dumpfile=exp_unused_idx01.dmp schemas=test_unused_idx

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 15:03:15 2019
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 "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@racnode2/pdb2 directory=data_pump_pdir dumpfile=exp_unused_idx01.dmp schemas=test_unused_idx
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 26 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
. . exported "TEST_UNUSED_IDX"."TBL_ONE" 10.45 MB 91774 rows
. . exported "TEST_UNUSED_IDX"."TBL_TWO" 10.45 MB 91775 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u03/app/oracle/admin/pdb2/dpdump/exp_unused_idx01.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 15 15:03:59 2019 elapsed 0 00:00:42

$ impdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
dumpfile=exp_unused_idx01.dmp skip_unusable_indexes=n schemas=test_unused_idx
table_exists_action=append

Import: Release 12.1.0.2.0 - Production on Sun Sep 15 15:08:11 2019
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 "SYSTEM"."SYS_IMPORT_SCHEMA_02" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_02": system/********@racnode2/pdb2 directory=data_pump_pdir dumpfile=exp_unused_idx01.dmp skip_unusable_indexes=n schemas=test_unused_idx table_exists_action=append
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST_UNUSED_IDX" already exists
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "TEST_UNUSED_IDX"."TBL_ONE" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "TEST_UNUSED_IDX"."TBL_TWO" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST_UNUSED_IDX"."TBL_ONE" 10.45 MB 91774 rows
ORA-31693: Table data object "TEST_UNUSED_IDX"."TBL_TWO" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-26028: index TEST_UNUSED_IDX.IDX2 initially in unusable state
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_02" completed with 2 error(s) at Sun Sep 15 15:08:27 2019 elapsed 0 00:00:13

1.19 SQLFILE Parameter
Write all the SQL DDL to a specified file.

#Export DDL of tablespaces

$ impdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
dumpfile=exp_full.dmp include=tablespace full=y
sqlfile=data_pump_pdir:tablespace.sql content=metadata_only

#Export DDL of View
$ impdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
dumpfile=exp_full.dmp include=view full=y
sqlfile=data_pump_pdir:view.sql content=metadata_only

#Export procedure,package DDL of usr_dptest1 schema
$ impdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
schemas=usr_deptest1 dumpfile=expdat.dmp include=procedure,package
sqlfile=data_pump_pdir:procedure.sql content=metadata_only

#Export Indexes DDL of test_unused_idx schema
$ impdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
schemas=test_unused_idx dumpfile=exp_unused_idx01.dmp include=index
sqlfile=data_pump_pdir:index.sql content=metadata_only

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

expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 sample=10 dumpfile=exp_sample_test01.dmp

impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 dumpfile=exp_sample_test01.dmp table_exists_action=skip
impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 dumpfile=exp_sample_test01.dmp table_exists_action=truncate
impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 dumpfile=exp_sample_test01.dmp table_exists_action=append
impdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
schemas=usr_deptest1 dumpfile=exp_sample_test01.dmp table_exists_action=replace

1.21 DATAPUMP Interactive-command mode

CONTINUE_CLIENT: is used to exit the interactive mode.
KILL_JOB: terminates the current job and detach the client session
STOP_JOB: is used to stop the job and might be restart again(using attach=job_name parameter of export command)
START_JOB: is used to restart a stopped job.
STATUS: command is used to get the status of the current job
PARALLEL: is used to change parallelism degree of the running job

$ expdp system/oracle_4U@racnode2/pdb2 directory=data_pump_pdir
full=y dumpfile=full_15092019.dmp logfile=log_full_15092019.log

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 15:36:57 2019

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 "SYSTEM"."SYS_EXPORT_FULL_01": system/********@racnode2/pdb2 directory=data_pump_pdir full=y dumpfile=full_15092019.dmp logfile=log_full_15092019.log
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 367.9 MB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SYS_USER/USER
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/ROLE
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
^C
Export> help
------------------------------------------------------------------------------

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.

Export> status

Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /u03/app/oracle/admin/pdb2/dpdump/full_15092019.dmp
bytes written: 4,096

Worker 1 Status:
Instance ID: 1
Instance name: cdb2
Host name: racnode2
Process Name: DW00
State: EXECUTING
Object Type: DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Completed Objects: 2
Total Objects: 2
Worker Parallelism: 1

Export> parallel 2
LRM-00108: invalid positional parameter value '2'

Export> parallel=2

Export> status

Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 2
Job Error Count: 0
Dump File: /u03/app/oracle/admin/pdb2/dpdump/full_15092019.dmp
bytes written: 4,096

Worker 1 Status:
Instance ID: 1
Instance name: cdb2
Host name: racnode2
Process Name: DW00
State: EXECUTING
Object Type: DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Completed Objects: 1
Total Objects: 1
Worker Parallelism: 1

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

-bash-4.1$ expdp system/oracle_4U@racnode2/pdb2 ATTACH=SYS_EXPORT_FULL_01

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 15:39:54 2019

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

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 9295A5C54AEE3BDBE0536638A8C099B6
Start Time: Sunday, 15 September, 2019 15:39:57
Mode: FULL
Instance: cdb2
Max Parallelism: 2
Timezone: -07:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/********@racnode2/pdb2 directory=data_pump_pdir full=y dumpfile=full_15092019.dmp logfile=log_full_15092019.log
State: IDLING
Bytes Processed: 0
Current Parallelism: 2
Job Error Count: 0
Dump File: /u03/app/oracle/admin/pdb2/dpdump/full_15092019.dmp
bytes written: 4,096

Worker 1 Status:
Instance ID: 1
Instance name: cdb2
Host name: racnode2
Process Name: DW00
State: UNDEFINED

Export> START_JOB

Export> STATUS

Job: SYS_EXPORT_FULL_01
Operation: EXPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 423,480
Percent Done: 1
Current Parallelism: 2
Job Error Count: 0
Dump File: /u03/app/oracle/admin/pdb2/dpdump/full_15092019.dmp
bytes written: 634,880

Worker 1 Status:
Instance ID: 1
Instance name: cdb2
Host name: racnode2
Process Name: DW00
State: EXECUTING

Worker 2 Status:
Instance ID: 1
Instance name: cdb2
Host name: racnode2
Process Name: DW01
State: EXECUTING
Object Schema: SYSTEM
Object Name: SCHEDULER_PROGRAM_ARGS
Object Type: DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Completed Objects: 67
Total Objects: 10
Worker Parallelism: 1

Export> CONTINUE_CLIENT
Job SYS_EXPORT_FULL_01 has been reopened at Sun Sep 15 15:39:57 2019
Restarting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@racnode2/pdb2 directory=data_pump_pdir full=y dumpfile=full_15092019.dmp logfile=log_full_15092019.log
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.359 KB 54 rows
. . exported "ORDDATA"."ORDDCM_DOCS" 252.9 KB 9 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "SYS"."AUD$" 26.62 KB 35 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows
. . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.539 KB 14 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "WMSYS"."WM$ENV_VARS$" 6.015 KB 3 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 5.812 KB 12 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 9.453 KB 75 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
^C
Export> EXIT_CLIENT

-bash-4.1$
-bash-4.1$ expdp system/oracle_4U@racnode2/pdb2 ATTACH=SYS_EXPORT_FULL_01

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 15:42:44 2019

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

Job: SYS_EXPORT_FULL_01
Owner: SYSTEM
Operation: EXPORT
Creator Privs: TRUE
GUID: 9295A5C54AEE3BDBE0536638A8C099B6
Start Time: Sunday, 15 September, 2019 15:39:57
Mode: FULL
Instance: cdb2
Max Parallelism: 2
Timezone: -07:00
Timezone version: 18
Endianness: LITTLE
NLS character set: AL32UTF8
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND system/********@racnode2/pdb2 directory=data_pump_pdir full=y dumpfile=full_15092019.dmp logfile=log_full_15092019.log
State: EXECUTING
Bytes Processed: 203,863,160
Percent Done: 99
Current Parallelism: 2
Job Error Count: 0
Dump File: /u03/app/oracle/admin/pdb2/dpdump/full_15092019.dmp
bytes written: 204,263,424

Worker 1 Status:
Instance ID: 1
Instance name: cdb2
Host name: racnode2
Process Name: DW00
State: EXECUTING
Object Type: DATABASE_EXPORT/SCHEMA/TABLE/FGA_POLICY
Completed Objects: 1
Total Objects: 1
Worker Parallelism: 1

Worker 2 Status:
Instance ID: 1
Instance name: cdb2
Host name: racnode2
Process Name: DW01
State: WORK WAITING

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

1.22 Monitoring

SET LINESIZE 150
COL EVENT FORMAT a60
SELECT W.SID,W.EVENT,W.SECONDS_IN_WAIT
FROM V$SESSION S,DBA_DATAPUMP_SESSIONS d,V$SESSION_WAIT w
WHERE S.saddr = d.saddr and S.sid = w.sid;

SID EVENT SECONDS_IN_WAIT
---------- ------------------------------------------------------------ ---------------
278 db file sequential read 7
26 latch: row cache objects 0
237 wait for unread message on broadcast channel 4
27 SQL*Net message from client 17

COL owner_name format a10
COL job_name format a20
COL operation format a10
COL state format a20
select owner_name,
job_name,
operation,
state,
degree,
attached_sessions
from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION STATE DEGREE ATTACHED_SESSIONS
---------- -------------------- ---------- -------------------- ---------- -----------------
SYSTEM SYS_EXPORT_FULL_01 EXPORT EXECUTING 2 1

1.23 NETWORK_LINK Parameter
Name of remote database link to the source system.

Using the NETWORK_LINK option you can import the schema from source database
to target database. One advantage of this option you don’t need export and
import as it does the export and import in single shot from the source to
destination. Also, the file system space is not needed to accommodate the
huge dump files as we can directly import to target using network_link.

It is very amazing option with data pump. You can take the backup of source
database schema from another database and you can store in dump files in target
location as well.

-bash-4.1$ sqlplus system/oracle_4U@pdb2

grant connect,resource to usr_net_link identified by oracle;

alter user usr_net_link default tablespace users quota unlimited on users;

create table usr_net_link.tbl_one tablespace users as select * from dba_objects;

select count(*) from usr_net_link.tbl_one;

COUNT(*)
----------
91833

-bash-4.1$ sqlplus system/oracle_4U@pdb1

SQL> create public database link cdb2.example.com connect to system identified
by oracle_4U using 'pdb2';

Database link created.

SQL> select count(*) from tab@cdb2.example.com;

COUNT(*)
----------
202

create directory data_pump_pdir as '/u03/app/oracle/admin/pdb1/dpump';

$ expdp system/oracle_4U@pdb1 directory=data_pump_pdir
dumpfile=exp_net_link_pdb3.dmp network_link=cdb2.example.om tables=usr_net_link.tbl_one
Export: Release 12.1.0.2.0 - Production on Sun Sep 15 21:50:10 2019

$ impdp system/oracle_4U@pdb1 directory=data_pump_pdir network_link=cdb2.example.com \
> tables=usr_net_link.tbl_one remap_table=tbl_one:tbl_two table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Sun Sep 15 21:51:56 2019

$ sqlplus system/oracle_4U@pdb1

grant connect,resource to usr_net_link identified by oracle;

alter user usr_net_link default tablespace users quota unlimited on users;

$ impdp system/oracle_4U@pdb1 directory=data_pump_pdir network_link=cdb2.example.com \
> tables=usr_net_link.tbl_one remap_table=tbl_one:tbl_two table_exists_action=replace

Import: Release 12.1.0.2.0 - Production on Sun Sep 15 21:56:11 2019

sqlplus usr_net_link/oracle@pdb1

select count(*) from tbl_two;

COUNT(*)
----------
91833

sqlplus usr_net_link/oracle@pdb2

select count(*) from tbl_one;

COUNT(*)
----------
91833

1.24 TRANSFORM

#For Impdp 12c New Feature

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.

-> DISABLE_ARCHIVE_LOGGING

The TRANSFORM parameter of impdp has been extended to include a
DISABLE_ARCHIVE_LOGGING option.
The default setting of "N" has no affect on logging behaviour. Using a
value "Y" reduces the logging associated with tables and indexes during
the import by setting their logging attribute to NOLOGGING
before the data is imported and resetting it to LOGGING once the operation
is complete.

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

An example of its use is shown below.

$ impdp system/Password1@pdb1 directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log \
remap_schema=scott:test transform=disable_archive_logging:y

Note: The DISABLE_ARCHIVE_LOGGING option has no effect if the database is
running in FORCE LOGGING mode.

-> Change Table Compression at Import

The TABLE_COMPRESSION_CLAUSE clause of the TRANSFORM parameter allows the
table compression characteristics of the tables
in an import to be altered on the fly.

TRANSFORM=TABLE_COMPRESSION_CLAUSE:[NONE | compression_clause]

The allowable values for the TABLE_COMPRESSION_CLAUSE include the following.

NONE : The table compression clause is omitted, so the table takes on the
compression characteristics of the tablespace.
NOCOMPRESS : Disables table compression.
COMPRESS : Enables basic table compression.
ROW STORE COMPRESS BASIC : Same as COMPRESS.
ROW STORE COMPRESS BASIC : Same as COMPRESS.
ROW STORE COMPRESS ADVANCED : Enables advanced compression, also known as
OLTP compression.
COLUMN STORE COMPRESS FOR QUERY : Hybrid Columnar Compression (HCC) available
in Exadata and ZFS storage appliances.
COLUMN STORE COMPRESS FOR ARCHIVE : Hybrid Columnar Compression (HCC) available
in Exadata and ZFS storage appliances.

Note: Compression clauses that contain whitespace must be enclosed by
single or double quotes.

Example:

impdp system/Password1@pdb1 directory=test_dir dumpfile=emp.dmp logfile=impdp_emp.log \
remap_schema=scott:test transform=table_compression_clause:compress

-> Change Table LOB Storage at Import

The LOB_STORAGE clause of the TRANSFORM parameter allows the LOB storage
characteristics of table columns in a
non-transportable import to be altered on the fly.

TRANSFORM=LOB_STORAGE:[SECUREFILE | BASICFILE | DEFAULT | NO_CHANGE]

The allowable values for the LOB_STORAGE clause include the following.

SECUREFILE : The LOBS are stored as SecureFiles.
BASICFILE : The LOBS are stored as BasicFiles.
DEFAULT : The LOB storage is determined by the database default.
NO_CHANGE : The LOB storage matches that of the source object.

An example of its use is shown below.

Example:
TRANSFORM=LOB_STORAGE:SECUREFILE

$impdp system/Password1@pdb1 directory=test_dir dumpfile=lob_table.dmp logfile=impdp_lob_table.log \
transform=lob_storage:securefile

-> Change Table INMEMORY Storage at Import

Example: TRANSFORM=INMEMORY:Y|N

TRANSFORM=INMEMORY_CLAUSE:string option

Example:
INMEMORY_CLAUSE=\"NO INMEMORY\"
INMEMORY_CLAUSE=\"INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY LOW\"
INMEMORY_CLAUSE:\"INMEMORY MEMCOMPRESS FOR DML\"

1.25 VIEWS_AS_TABLES

The VIEWS_AS_TABLES parameter allows Data Pump to export the specified
views as if they were tables.
The table structure matches the view columns, with the data being the rows
returned by the query supporting the views.

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

$ expdp system/oracle_4U@racnode2/pdb2 directory=DATA_PUMP_PDIR
dumpfile=exp_view_as_table.dmp VIEWS_AS_TABLES=USR_DEPTEST1.MY_VIEW

Export: Release 12.1.0.2.0 - Production on Sun Sep 15 11:30:22 2019

#For Impdp
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.

1.26 Data Transportable Option

# For Expdp
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.

# For Impdp

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.

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

Version of objects to import.
Valid keywords are: [COMPATIBLE], LATEST, or any valid database version.

12c
The TRANSPORTABLE option can now be combined with the FULL option to transport a whole database.

expdp system/Password1 full=Y transportable=always version=12 directory=TEMP_DIR \
dumpfile=orcl.dmp logfile=expdporcl.log

1.28 ACCESS_METHOD

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

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

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

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

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

The LOGTIME parameter determines if timestamps should be included in the
output messages from the expdp and impdp utilities.

NONE : The default value, which indicates that no timestamps should be
included in the output, making the output look similar to that of
previous versions.
STATUS : Timestamps are included in output to the console, but not in the
associated log file.
LOGFILE : Timestamps are included in output to the log file, but not in
the associated console messages.
ALL : Timestamps are included in output to the log file and console.

expdp scott/tiger@pdb1 tables=emp directory=test_dir
dumpfile=emp.dmp logfile=expdp_emp.log logtime=all

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

JOB_NAME
Name of export/import job to create.

NOLOGFILE
Do not write log file [NO].

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

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

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

#####Reference#####

===========
Data Export
===========

-bash-4.1$ expdp help=y

Export: Release 12.1.0.2.0 - Production on Sat Sep 14 19:34:10 2019

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

===========
Data Import
===========
-bash-4.1$ impdp help=y

Import: Release 12.1.0.2.0 - Production on Sat Sep 14 19:39:20 2019

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.

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.

[2] SqlLoader
-> New Features in 12.1.0.2

$ sqlldr

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE)
readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
no_index_errors -- abort load on any index errors (Default FALSE)
partition_memory -- direct path partition memory limit to start spilling (kb) (Default 0)
table -- Table for express mode load
date_format -- Date format for express mode load
timestamp_format -- Timestamp format for express mode load
terminated_by -- terminated by character for express mode load
enclosed_by -- enclosed by character for express mode load
optionally_enclosed_by -- optionally enclosed by character for express mode load
characterset -- characterset for express mode load
degree_of_parallelism -- degree of parallelism for express mode load and external table load
trim -- trim type for express mode load and external table load
csv -- csv format data files for express mode load
nullif -- table level nullif clause for express mode load
field_names -- field names setting for first record of data files for express mode load
dnfs_enable -- option for enabling or disabling Direct NFS (dNFS) for input data files (Default FALSE)
dnfs_readbuffers -- the number of Direct NFS (dNFS) read buffers (Default 4)

PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.

Database Utilities
7 SQL*Loader Concepts
SQL*Loader Case Studies

The following SQL*Loader features are new in this release:

[1] A new SQL*Loader express mode provides a streamlined way to quickly and
easily load tables that have simple column data types and data files that
contain only delimited character data.
See SQL*Loader Express .

To activate SQL*Loader express mode, you can simply specify your username
and a table name. SQL*Loader prompts you for a password, for example:

sqlldr username TABLE=employees

If you activate SQL*Loader express mode by specifying only the TABLE parameter,
then SQL*Loader uses default settings for a number of other parameters. You can
override most of the default values by specifying additional parameters on the command line.

SQL*Loader express mode generates a log file that includes a SQL*Loader
control file. The log file also contains SQL scripts for creating the external
table and performing the load using a SQL INSERT AS SELECT statement. Neither
the control file nor the SQL scripts are used by SQL*Loader express mode.
They are made available to you in case you want to use them as a starting
point to perform operations using regular SQL*Loader or standalone external
tables; the control file is for use with SQL*Loader, whereas the
SQL scripts are for use with standalone external tables operations.

[2] The maximum size of the VARCHAR2, NVARCHAR2, and RAW data types has
been increased to 32 KB when the COMPATIBLE initialization parameter is
set to 12.0 or later and the MAX_STRING_SIZE initialization parameter is
set to EXTENDED. SQL*Loader supports this new maximum size.
See "SQL*Loader Data Types" for information about SQL*Loader data types.

[3] You can now enable auditing of SQL*Loader operations that use direct
path mode. See "Auditing SQL*Loader Operations That Use Direct Path Mode".

[4] The SQL*Loader control file has several new clauses available that
simplify its use. Additionally some existing parameters have been modified
and new parameters have been added to streamline load execution.

4.1 You can specify wildcard characters on the INFILE clause.
See "Specifying Data Files".
An asterisk (*) represents multiple characters and a question mark
(?) represents a single character.
For example:
INFILE 'emp*.dat'
INFILE 'm?emp.dat'

*
If your data is in the control file itself, then use an asterisk instead
of the file name.
If you have data in the control file and in data files, then you must
specify the asterisk first in
order for the data to be read.

4.2 You can direct SQL*Loader to access the data files as comma-separated-values
(CSV) format files

FIELDS CSV [WITH EMBEDDED|WITHOUT EMBEDDED] [FIELDS TERMINATED BY ',']
[OPTIONALLY ENCLOSED BY '"']

When the CSV clause is used, only delimitable data types are allowed as
control file fields.
Delimitable data types include CHAR, datetime, interval, and numeric EXTERNAL.

The TERMINATED BY and ENCLOSED BY clauses cannot be used at the field level
when the CSV clause is specified.

When the CSV clause is specified, normal SQL*Loader blank trimming is done
by default. You can specify PRESERVE BLANKS to avoid trimming of spaces.
Or, you can use the SQL functions LTRIM and RTRIM in the field
specification to remove left and/or right spaces.

When the CSV clause is specified, the INFILE * clause in not allowed.
This means that there cannot be any data
included in the SQL*Loader control file.

The following sample SQL*Loader control file uses the FIELDS CSV clause
with the default delimiters:

LOAD DATA
INFILE "mydata.dat"
TRUNCATE
INTO TABLE mytable
FIELDS CSV WITH EMBEDDED
TRAILING NULLCOLS
(
c0 char,
c1 char,
c2 char,
)

4.3 At the table level, you can specify a datetime format to apply to all
datetime fields.

You can specify certain datetime formats at the table level in a
SQL*Loader control file.
The syntax for each is as follows:

DATE FORMAT mask
TIMESTAMP FORMAT mask
TIMESTAMP WITH TIME ZONE mask
TIMESTAMP WITH LOCAL TIME ZONE mask

The following is an example of using the DATE FORMAT clause in a
SQL*Loader control file. The DATE FORMAT clause is overridden by DATE at
the field level for the hiredate and entrydate fields:

LOAD DATA
INFILE myfile.dat
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ","
DATE FORMAT "DD-Month-YYYY"
(empno,
ename,
job,
mgr,
hiredate DATE,
sal,
comm,
deptno,
entrydate DATE)

4.4 At the table level, you can specify NULLIF to apply to all character
fields. See "Specifying a NULLIF Clause At the Table Level".

NULLIF {=|!=}{"char_string"|x'hex_string'|BLANKS}

SQL*Loader checks the specified value against the value of the field in
the record. If there is a match using the equal or not equal specification,
then the field is set to NULL for that row. Any field that has a length of
0 after blank trimming is also set to NULL.

If you do not want the default NULLIF or any other NULLIF clause applied
to a field, you can specify NO NULLIF at the field level.

4.5 You can specify that SQL*Loader should determine the field order from
the order of the field names in the first record of the data file.
See "Specifying Field Order".

FIELD NAMES {FIRST FILE|FIRST FILE IGNORE|ALL FILES|ALL FILES IGNORE|NONE}

4.6 The SQL*Loader command line has new and modified parameters that help
to streamline load execution:

The new TRIM command-line parameter allows you to override the TRIM=LDRTRIM
default when you are using the external tables option. See "TRIM".

The new DEGREE_OF_PARALLELISM command-line parameter allows you to specify a
degree of parallelism to use for the load when the external tables option is used.

When specifying the bad, discard, and log files on the SQL*Loader command line,
you now have the option of specifying only a directory name.

#Test with traditional SQL*Loader

SQL> create table tbl_sqlldr_test01
2 (id number,name varchar2(10),surname varchar2(10),team varchar2(10),
salary number,username varchar2(10),year number);

Table created.

-bash-4.1$ cat empxt1.dat
1.john.watson.mg_team.3000.jwtson.1970
1;ohn;watson;mg_team;3000;jwtson;1970
1;hn;watson;mg_team;3000;jwtson;1970
1;n;watson;mg_team;3000;jwtson;1970
1;joh;watson;mg_team;3000;jwtson;1970

-bash-4.1$ cat control01.ctl
load data
infile '/home/oracle/sqlldr/empxt1.dat'
into table tbl_sqlldr_test01
fields terminated by ';' optionally enclosed by '"'
(id,name,surname,team,salary,username,year)

-bash-4.1$ sqlldr HR/HR@pdb2 control=control01.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 16 10:44:47 2019

Path used: Conventional
Commit point reached - logical record count 6

Table TBL_SQLLDR_TEST01:
4 Rows successfully loaded.

Check the log file:
control01.log
for more information about the load.

$ sqlplus HR/HR@pdb2

SQL> select * from tbl_sqlldr_test01;

ID NAME SURNAME TEAM SALARY USERNAME YEAR
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 ohn watson mg_team 3000 jwtson 1970
1 hn watson mg_team 3000 jwtson 1970
1 n watson mg_team 3000 jwtson 1970
1 joh watson mg_team 3000 jwtson 1970

#Test with SQL*Loader Express Mode

-> Records must be in comma separated.
-> data file name must be same with table name with .dat

SQL> truncate table tbl_sqlldr_test01;

-bash-4.1$ cat tbl_sqlldr_test01.dat
1.john.watson.mg_team.3000.jwtson.1970
1,ohn,watson,mg_team,3000,jwtson,1970
1,hn,watson,mg_team,3000,jwtson,1970
1,n,watson,mg_team,3000,jwtson,1970
1,joh,watson,mg_team,3000,jwtson,1970

$ sqlldr HR/HR@pdb2 table=tbl_sqlldr_test01

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 16 11:00:53 2019

Express Mode Load, Table: TBL_SQLLDR_TEST01
Path used: External Table, DEGREE_OF_PARALLELISM=AUTO

Table TBL_SQLLDR_TEST01:
4 Rows successfully loaded.

Check the log files:
tbl_sqlldr_test01.log
tbl_sqlldr_test01_%p.log_xt
for more information about the load.

-bash-4.1$ sqlplus HR/HR@pdb2

SQL> select * from tbl_sqlldr_test01;

$ cat tbl_sqlldr_test01_6990.bad
1.john.watson.mg_team.3000.jwtson.1970

Express Mode Load, Table: TBL_SQLLDR_TEST01
Data File: tbl_sqlldr_test01.dat
Bad File: tbl_sqlldr_test01_%p.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table

Table TBL_SQLLDR_TEST01, loaded from every logical record.
Insert option in effect for this table: APPEND

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID FIRST * , CHARACTER
NAME NEXT * , CHARACTER
SURNAME NEXT * , CHARACTER
TEAM NEXT * , CHARACTER
SALARY NEXT * , CHARACTER
USERNAME NEXT * , CHARACTER
YEAR NEXT * , CHARACTER

Generated control file for possible reuse:
OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)
LOAD DATA
INFILE 'tbl_sqlldr_test01'
APPEND
INTO TABLE TBL_SQLLDR_TEST01
FIELDS TERMINATED BY ","
(
ID,
NAME,
SURNAME,
TEAM,
SALARY,
USERNAME,
YEAR
)
End of generated control file for possible reuse.

enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_TBL_SQLLDR_TE"

CREATE TABLE "SYS_SQLLDR_X_EXT_TBL_SQLLDR_TE"
(
"ID" NUMBER,
"NAME" VARCHAR2(10),
"SURNAME" VARCHAR2(10),
"TEAM" VARCHAR2(10),
"SALARY" NUMBER,
"USERNAME" VARCHAR2(10),
"YEAR" NUMBER
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET AL32UTF8
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'tbl_sqlldr_test01_%p.bad'
LOGFILE 'tbl_sqlldr_test01_%p.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255),
"NAME" CHAR(255),
"SURNAME" CHAR(255),
"TEAM" CHAR(255),
"SALARY" CHAR(255),
"USERNAME" CHAR(255),
"YEAR" CHAR(255)
)
)
location
(
'tbl_sqlldr_test01.dat'
)
)REJECT LIMIT UNLIMITED

executing INSERT statement to load database table TBL_SQLLDR_TEST01

INSERT /*+ append parallel(auto) */ INTO TBL_SQLLDR_TEST01
(
ID,
NAME,
SURNAME,
TEAM,
SALARY,
USERNAME,
YEAR
)
SELECT
"ID",
"NAME",
"SURNAME",
"TEAM",
"SALARY",
"USERNAME",
"YEAR"
FROM "SYS_SQLLDR_X_EXT_TBL_SQLLDR_TE"

dropping external table "SYS_SQLLDR_X_EXT_TBL_SQLLDR_TE"

Table TBL_SQLLDR_TEST01:
4 Rows successfully loaded.

Run began on Mon Sep 16 11:00:53 2019
Run ended on Mon Sep 16 11:00:53 2019

Elapsed time was: 00:00:00.60
CPU time was: 00:00:00.03

------------------------------------------
#Load a data without providing a flat file
------------------------------------------
$ cat control02.ctl
load data
infile *
into table tbl_sqlldr_test02
fields terminated by ';' optionally enclosed by '"'
(id,name,surname,team,salary,username,year)
begindata
1.john.watson.mg_team.3000.jwtson.1970
2;ohn;watson;mg_team;3000;jwtson;1970
3;hn;watson;mg_team;3000;jwtson;1970
4;n;watson;mg_team;3000;jwtson;1970
5;joh;watson;mg_team;3000;jwtson;1970

$ sqlldr HR/HR@pdb2 control=control02.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 16 11:08:33 2019

Path used: Conventional
Commit point reached - logical record count 5

Table TBL_SQLLDR_TEST02:
4 Rows successfully loaded.

Check the log file:
control02.log
for more information about the load.

$ cat control02.bad
1.john.watson.mg_team.3000.jwtson.1970

SQL> select count(*) from tbl_sqlldr_test02;

COUNT(*)
----------
4

$ cat control02.log

----------------------------------
Load a data to the non-empty table
----------------------------------
$ sqlldr HR/HR@pdb2 control=control02.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 16 11:11:53 2019

Path used: Conventional
SQL*Loader-601: For INSERT option, table must be empty. Error on table TBL_SQLLDR_TEST02

cp control02.ctl control03.ctl

$ cat control03.ctl
load data
infile *
append
into table tbl_sqlldr_test02
fields terminated by ';' optionally enclosed by '"'
(id,name,surname,team,salary,username,year)
begindata
1.john.watson.mg_team.3000.jwtson.1970
2;ohn;watson;mg_team;3000;jwtson;1970
3;hn;watson;mg_team;3000;jwtson;1970
4;n;watson;mg_team;3000;jwtson;1970
5;joh;watson;mg_team;3000;jwtson;1970

$ sqlldr HR/HR@pdb2 control=control03.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 16 11:12:48 2019

Path used: Conventional
Commit point reached - logical record count 5

Table TBL_SQLLDR_TEST02:
4 Rows successfully loaded.

Check the log file:
control03.log
for more information about the load.

$ cat control03.bad
1.john.watson.mg_team.3000.jwtson.1970

$ sqlplus HR/HR@pdb2

SQL> select count(*) from tbl_sqlldr_test02;

COUNT(*)
----------
8

--------------------------------
Truncate table before the load
--------------------------------
$ cat control04.ctl
load data
infile *
truncate
into table tbl_sqlldr_test02
fields terminated by ';' optionally enclosed by '"'
(id,name,surname,team,salary,username,year)
begindata
1.john.watson.mg_team.3000.jwtson.1970
2;ohn;watson;mg_team;3000;jwtson;1970
3;hn;watson;mg_team;3000;jwtson;1970
4;n;watson;mg_team;3000;jwtson;1970
5;joh;watson;mg_team;3000;jwtson;1970

$ sqlldr HR/HR@pdb2 control=control04.ctl

$ sqlplus HR/HR@pdb2

SQL> select count(*) from tbl_sqlldr_test02;

COUNT(*)
----------
4
--------------------------------
Load the data with fixed format
--------------------------------
$vi empxt2.dat
1 john watson mg_team 3000 jwtson 1970
1 ohn watson mg_team 3000 jwtson 1970
1 hn watson mg_team 3000 jwtson 1970
1 n watson mg_team 3000 jwtson 1970
1 joh watson mg_team 3000 jwtson 1970

sqlldr HR/HR@pdb2 control=control05.ctl

-----------------------------
Load data from multiple files
-----------------------------
$ cp empxt1.dat empxt3.dat
$ vi control06.ctl
$ cat control06.ctl
load data
infile '/home/oracle/sqlldr/empxt1.dat'
infile '/home/oracle/sqlldr/empxt3.dat'
truncate
into table tbl_sqlldr_test01
fields terminated by ';' optionally enclosed by '"'
(id,name,surname,team,salary,username,year)

$ sqlldr HR/HR@pdb2 control=control06.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 16 11:33:31 2019

Path used: Conventional
Commit point reached - logical record count 5
Commit point reached - logical record count 10

Table TBL_SQLLDR_TEST01:
8 Rows successfully loaded.

Check the log file:
control06.log
for more information about the load.

-bash-4.1$ sqlplus HR/HR@pdb2
SQL> select count(*) from tbl_sqlldr_test01;

COUNT(*)
----------
8

----------------------------
Load data to multiple tables
----------------------------
$ cat control07.ctl
load data
infile '/home/oracle/sqlldr/empxt2.dat'
truncate
into table tbl_sqlldr_test01
(id position(1:1),name position(3:6),surname position(8:13),team position(15:21),salary position(23:26),username position(28:33),year position(35:38))
into table tbl_sqlldr_test02
(id position(1:1),name position(3:6),surname position(8:13),team position(15:21),salary position(23:26),username position(28:33),year position(35:38))
-bash-4.1$

$ sqlldr HR/HR@pdb2 control=control07.ctl

SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 16 11:36:22 2019

Path used: Conventional
Commit point reached - logical record count 5

Table TBL_SQLLDR_TEST01:
5 Rows successfully loaded.

Table TBL_SQLLDR_TEST02:
5 Rows successfully loaded.

Check the log file:
control07.log
for more information about the load.

$ sqlplus HR/HR@pdb2

SQL> select count(*) from tbl_sqlldr_test01;

COUNT(*)
----------
5

SQL> r
1* select count(*) from tbl_sqlldr_test02

COUNT(*)
----------
5

---------------------------------
Using WHEN clause with SQL*Loader
---------------------------------
$ cat empxt8.dat
1 john watson mg_team 3000 jwtson 1970
1 ohn watson mg_team 3000 jwtson 1970
1 hn watson mgteam 3000 jwtson 1970
1 n watson mgteam 3000 jwtson 1970
1 joh watson mg_team 3000 jwtson 1970

-bash-4.1$ vi control08.ctl
-bash-4.1$ cat control08.ctl
load data
infile '/home/oracle/sqlldr/empxt8.dat'
truncate
into table tbl_sqlldr_test01
WHEN team = 'mg_team'
(id position(1:1),name position(3:6),surname position(8:13),team position(15:21),salary position(23:26),username position(28:33),year position(35:38))

$ sqlldr HR/HR@pdb2 control=control08.ctl

$ sqlplus HR/HR@pdb2

SQL> select count(*) from tbl_sqlldr_test01;

COUNT(*)
----------
3

SQL> select * from tbl_Sqlldr_test01;

ID NAME SURNAME TEAM SALARY USERNAME YEAR
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 john watson mg_team 3000 jwtson 1970
1 ohn watson mg_team 3000 jwtson 1970
1 joh watson mg_team 3000 jwtson 1970

$ vi empxt8.dat
-bash-4.1$ cat empxt8.dat
1 john watson mg_team 3000 jwtson 1970
2 ohn watson mg_team 3000 jwtson 1970
3 hn watson mgteam 3000 jwtson 1970
4 n watson mgteam 3000 jwtson 1970
5 joh watson mg_team 3000 jwtson 1970
-bash-4.1$
-bash-4.1$ vi control08.ctl
-bash-4.1$ cat control08.ctl
load data
infile '/home/oracle/sqlldr/empxt8.dat'
truncate
into table tbl_sqlldr_test01
WHEN ID='2'
(id position(1:1),name position(3:6),surname position(8:13),team position(15:21),salary position(23:26),username position(28:33),year position(35:38))

$ sqlldr HR/HR@pdb2 control=control08.ctl

$ sqlplus HR/HR@pdb2

SQL> select * from tbl_sqlldr_test01;

ID NAME SURNAME TEAM SALARY USERNAME YEAR
---------- ---------- ---------- ---------- ---------- ---------- ----------
2 ohn watson mg_team 3000 jwtson 1970

-----------------------
Method of data loading
-----------------------
Two methods
1. Conventional path(default): INSERT Command are used to load the data to
the table
2. Direct path(direct=true parameter): The insertion process goes directly
to the data files
by skiping the database buffer cache.
This method doesn't validate check constraint and doesn't fire triggers

$ sqlldr HR/HR@pdb2 control=control08.ctl log=control08.log bad=control08.bad
discard=control08.dsc direct=true
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Sep 16 11:47:07 2019
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

Path used: Direct

Load completed - logical record count 5.
Table TBL_SQLLDR_TEST01:
1 Row successfully loaded.

Check the log file:
control08.log
for more information about the load.
-bash-4.1$
-bash-4.1$ cat control08.dsc
1 john watson mg_team 3000 jwtson 1970
3 hn watson mgteam 3000 jwtson 1970
4 n watson mgteam 3000 jwtson 1970
5 joh watson mg_team 3000 jwtson 1970
-bash-4.1$
-bash-4.1$ cat control08.bad
cat: control08.bad: No such file or directory

----------------
Loading LOB data
----------------

SQL> create table tbl_loader_lob(id number,product varchar2(10),text CLOB);

Table created.

$ cat control09.ctl
load data
infile *
truncate
into table tbl_loader_lob
FIELDS TERMINATED BY ','
(id char(1),product char(10),clob_fname FILLER char(500),text lobfile(clob_fname)
TERMINATED BY EOF)
begindata
1,Exadata,/home/oracle/sqlldr/exadata.dat

sqlplus HR/HR@pdb2
SQL> SET LINESIZE 150
SQL> COL TEXT FORMAT A50
SQL> SELECT * FROM TBL_LOADER_LOB;
ID PRODUCT TEXT
---------- ---------- --------------------------------------------------
1 Exadata This is advanced database machine in the earth
This is advanced database machine

======================
Patch to Documentation:
======================
Database Utilities
Part I Oracle Data Pump
Part II SQL*Loader

Database Utilities
11 Loading Objects, LOBs, and Collections
Examples of Loading LOB Data from LOBFILEs

Thank you for visiting this blog 🙂

ORACLE-LEARN

Welcome to our oracle learning world !

Manage schema data

Go back to OCM 12c Preparation Project

Go back to OCM 12c Preparation Project

Like this: