8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
SQLcl : Data Pump
In this article we demonstrate the Data Pump functionality in SQLcl. From SQLcl version 21.4 onward, we can issue some data pump commands. This is a wrapper over the DBMS_DATAPUMP package. Prior to version 22.1 the functionality is limited to schema exports and imports. From version 22.1 onward table exports are possible.
Related articles.
- SQLcl : Data Pump
- SQLcl : Data Pump (Part 2)
- SQLcl : All Articles
- Data Pump API for PL/SQL (DBMS_DATAPUMP)
Setup
The examples in this article require the following tables.
-- drop table emp purge;
-- drop table dept purge;
create table dept (
deptno number(2) constraint pk_dept primary key,
dname varchar2(14),
loc varchar2(13)
) ;
create table emp (
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2) constraint fk_deptno references dept
);
insert into dept values (10,'ACCOUNTING','NEW YORK');
insert into dept values (20,'RESEARCH','DALLAS');
insert into dept values (30,'SALES','CHICAGO');
insert into dept values (40,'OPERATIONS','BOSTON');
insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,null,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,null,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,null,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,null,20);
insert into emp values (7839,'KING','PRESIDENT',null,to_date('17-11-1981','dd-mm-yyyy'),5000,null,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,null,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,null,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,null,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,null,10);
commit;
We also need a directory object to work with. The following code connects to a privileged user, creates a directory object and grants access to our test user. We then connect as the test user.
-- Connect to a privileged user. conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba -- Create a directory object. create or replace directory tmp_dir as '/tmp/'; grant read, write on directory tmp_dir to testuser1; -- Connect to the test user. conn testuser1/testuser1@//localhost:1521/pdb1
Export
The USER_OBJECTS view shows us the contents of the test schema. We have two tables, with their respective primary key indexes.
column object_name format a30
column object_type format a30
select object_name,
object_type
from user_objects
order by 1, 2;
OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------------
DEPT TABLE
EMP TABLE
PK_DEPT INDEX
PK_EMP INDEX
4 rows selected.
SQL>
We use the DATAPUMP EXPORT command, passing the current user in the schemas argument. This is the default action if the schemas argument is not used. We also supply the directory, dumpfile and logfile names.
datapump export -
-schemas testuser1 -
-directory tmp_dir -
-dumpfile testuser1.dmp -
-logfile testuser1.log
** Datapump Command Start ** at 2022.03.18-13.37.18
Initiating DATA PUMP
DATABASE TIME ZONE: VERSION:35 CON_ID:0
Log Location: TMP_DIR:testuser1.log
Starting "TESTUSER1"."ESQL_642":
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TESTUSER1"."DEPT" 6.031 KB 4 rows
. . exported "TESTUSER1"."EMP" 8.781 KB 14 rows
Master table "TESTUSER1"."ESQL_642" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.ESQL_642 is:
/tmp/testuser1.dmp
Job "TESTUSER1"."ESQL_642" successfully completed at Fri Mar 18 13:38:16 2022 elapsed 0 00:00:55
DataPump Operation Status 'COMPLETED'
Jobname = ESQL_642
** Datapump Command End ** at 2022.03.18-13.38.19
SQL>
Once the export is complete we see the two tables have been exported, and a master table for the export operation has been created.
We can perform the same operation with a short-form of the command, and the arguments.
dp export -
-s testuser1 -
-d tmp_dir -
-f testuser1.dmp -
-lf testuser1.log
** Datapump Command Start ** at 2022.03.18-13.39.17
Initiating DATA PUMP
DATABASE TIME ZONE: VERSION:35 CON_ID:0
Log Location: TMP_DIR:testuser1.log
Starting "TESTUSER1"."ESQL_682":
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TESTUSER1"."ESQL_642" 332.9 KB 1598 rows
. . exported "TESTUSER1"."DEPT" 6.031 KB 4 rows
. . exported "TESTUSER1"."EMP" 8.781 KB 14 rows
Master table "TESTUSER1"."ESQL_682" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.ESQL_682 is:
/tmp/testuser1.dmp
Job "TESTUSER1"."ESQL_682" successfully completed at Fri Mar 18 13:40:13 2022 elapsed 0 00:00:53
DataPump Operation Status 'COMPLETED'
Jobname = ESQL_682
** Datapump Command End ** at 2022.03.18-13.40.16
SQL>
The result is similar, but this time we've also exported the master table from first export operation. We have a new master table created for the second export operation.
From version 22.1 onward we can include and exclude objects, and use name lists to target specific objects. You can get the full list of arguments here, or from the Help text below.
In the following example we use the -namelist flag, to perform a table-level export.
datapump export -
-schemas testuser1 -
-namelist TABLE=emp -
-directory tmp_dir -
-dumpfile emp.dmp -
-logfile emp.log
** Datapump Command Start ** at 2022.04.10-14.26.26
Initiating DATA PUMP
DATABASE TIME ZONE: VERSION:35 CON_ID:0
Log Location: TMP_DIR:emp.log
Starting "TESTUSER1"."ESQL_84":
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "TESTUSER1"."EMP" 8.781 KB 14 rows
Master table "TESTUSER1"."ESQL_84" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.ESQL_84 is:
/tmp/emp.dmp
Job "TESTUSER1"."ESQL_84" successfully completed at Sun Apr 10 13:27:09 2022 elapsed 0 00:00:40
DataPump Operation Status 'COMPLETED'
Jobname = ESQL_84
** Datapump Command End ** at 2022.04.10-14.27.12
SQL>
Import
We drop the EMP and DEPT tables so we can import them.
drop table emp purge; drop table dept purge;
This time we use the DATAPUMP IMPORT command, with the same arguments as before. Once the operation completes we can see we imported the two tables.
datapump import -
-schemas testuser1 -
-directory tmp_dir -
-dumpfile testuser1.dmp -
-logfile testuser1.log
** Datapump Command Start ** at 2022.03.18-13.44.26
Initiating DATA PUMP
DATABASE TIME ZONE: VERSION:35 CON_ID:0
Log Location: TMP_DIR:testuser1.log
Master table "TESTUSER1"."ISQL_722" successfully loaded/unloaded
Starting "TESTUSER1"."ISQL_722":
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "TESTUSER1"."ESQL_642" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."DEPT" 6.031 KB 4 rows
. . imported "TESTUSER1"."EMP" 8.781 KB 14 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "TESTUSER1"."ISQL_722" completed with 1 error(s) at Fri Mar 18 13:44:55 2022 elapsed 0 00:00:27
DataPump Operation Status 'COMPLETED'
Jobname = ISQL_722
** Datapump Command End ** at 2022.03.18-13.44.58
SQL>
We check for the master tables using the ESQL and ISQL prefixes. We see the two master tables for the export operations, and one for the import operation.
column table_name format a30 select table_name from user_tables where table_name like 'ESQL%' or table_name like 'ISQL%' order by 1; TABLE_NAME ------------------------------ ESQL_642 ESQL_682 ISQL_722 3 rows selected. SQL>
From version 22.1 onward we can include and exclude objects, and use name lists to target specific objects. You can get the full list of arguments here, or from the Help text below.
In the following example we use the -namelist flag, to perform a table-level import. We drop the table before importing.
drop table emp purge;
datapump import-
-schemas testuser1 -
-namelist TABLE=emp -
-directory tmp_dir -
-dumpfile emp.dmp -
-logfile emp.log
** Datapump Command Start ** at 2022.04.10-14.39.37
Initiating DATA PUMP
DATABASE TIME ZONE: VERSION:35 CON_ID:0
Log Location: TMP_DIR:emp.log
Master table "TESTUSER1"."ISQL_122" successfully loaded/unloaded
Starting "TESTUSER1"."ISQL_122":
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 "TESTUSER1"."EMP" 8.781 KB 14 rows
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "TESTUSER1"."ISQL_122" successfully completed at Sun Apr 10 13:40:10 2022 elapsed 0 00:00:29
DataPump Operation Status 'COMPLETED'
Jobname = ISQL_122
** Datapump Command End ** at 2022.04.10-14.40.13
SQL>
NoExec
In this example we repeat the original export command, but include the NOEXEC argument. This displays the PL/SQL code that would have been run for this command. It's mostly calls to the DBMS_DATAPUMP package.
datapump export -
-schemas testuser1 -
-directory tmp_dir -
-dumpfile testuser1.dmp -
-logfile testuser1.log -
-noexec
** Datapump Command Start ** at 2022.03.18-13.48.09
DECLARE
errorlog varchar2(8000):='';
getStatusOutput boolean:=false;
backtrace varchar2(8000):='';
BEGIN
DECLARE
h1 number;
s varchar2(1000):=NULL;
errorvarchar varchar2(100):= 'ERROR';
tryGetStatus number := 0;
success_with_info EXCEPTION;
PRAGMA EXCEPTION_INIT(success_with_info, -31627);
jobname varchar2(500):='ESQL_724';
schema varchar2(8000):='';
job_status VARCHAR2(30):='Not set';
cloud_service varchar2(1000);
success_with_info_warning_text varchar2(1000):=':DPEXEC_SUCCESS_WITH_INFO:';
new_line varchar2(10):='
';
get_status varchar2(1000):='dbms_datapump.get_status(';
wait_for_job_status varchar2(1000):=':DPEXEC_DATAPUMP_OPERATION_STATUS:';
begin
success_with_info_warning_text:=success_with_info_warning_text||new_line;
BEGIN
h1 := dbms_datapump.open (operation => :OPERATION, job_mode => :JOB_MODE, job_name => jobname, version => :VERSION);
EXCEPTION when success_with_info THEN errorlog:=errorlog||success_with_info_warning_text;
END;
tryGetStatus := 1;
BEGIN
dbms_datapump.add_file(handle => h1, filename => :FILENAME_DMP, directory => :DIRECTORY_DMP, filesize => :FILESIZE, filetype => 1, reusefile => 1);
EXCEPTION when success_with_info THEN errorlog:=errorlog||success_with_info_warning_text; END;
BEGIN
dbms_datapump.add_file(handle => h1, filename => :FILENAME_LOG, directory => :DIRECTORY_LOG, filetype => 3);
EXCEPTION when success_with_info THEN errorlog:=errorlog||success_with_info_warning_text; END;
BEGIN
dbms_datapump.metadata_filter(handle => h1, name => 'SCHEMA_EXPR', value => 'IN ('''||:SCHEMA1||''')');
EXCEPTION when success_with_info THEN errorlog:=errorlog||success_with_info_warning_text;
END;
BEGIN
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 1);
EXCEPTION when success_with_info THEN errorlog:=errorlog||success_with_info_warning_text;
END;
BEGIN
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0);
EXCEPTION when success_with_info THEN errorlog:=errorlog||success_with_info_warning_text;
END;
BEGIN
dbms_datapump.wait_for_job (handle => h1, job_state => job_status);
EXCEPTION when success_with_info THEN errorlog:=errorlog||success_with_info_warning_text; END;
errorlog:=errorlog||(wait_for_job_status||job_status||''''||new_line);
h1:=null;
BEGIN
IF (h1 IS NOT NULL)
THEN
dbms_datapump.detach(handle => h1);
END IF;
EXCEPTION when success_with_info THEN errorlog:=errorlog||success_with_info_warning_text; END;
errorvarchar := 'NO_ERROR';
:jobnameout:=jobname;
EXCEPTION
When Others then
backtrace:=backtrace||(SQLERRM)||new_line;
backtrace:=backtrace|| dbms_utility.format_error_backtrace || new_line;
IF ((S IS NOT NULL) AND (S!='COMPLETED')) THEN
errorlog:=errorlog||'WAIT_FOR_JOB JOB_STATE STATE='||s|| new_line;
END IF;
DECLARE
ind NUMBER;
percent_done NUMBER;
job_state VARCHAR2(30);
le ku$_LogEntry;
js ku$_JobStatus;
jd ku$_JobDesc;
sts ku$_Status;
BEGIN
if ((errorvarchar = 'ERROR')AND(tryGetStatus=1)) then
dbms_datapump.get_status(h1,
dbms_datapump.ku$_status_job_error +
dbms_datapump.ku$_status_job_status +
dbms_datapump.ku$_status_wip,-1,job_state,sts);
js := sts.job_status;
if (bitand(sts.mask,dbms_datapump.ku$_status_wip) != 0)
then
le := sts.wip;
else
if (bitand(sts.mask,dbms_datapump.ku$_status_job_error) != 0)
then
le := sts.error;
else
le := null;
end if;
end if;
if le is not null
then
ind := le.FIRST;
if ind is not null
then
errorlog:=errorlog||(get_status||h1||'...)')||new_line;
getStatusOutput:=true;
end if;
while ind is not null loop
errorlog:=errorlog||(le(ind).LogText)||new_line;
ind := le.NEXT(ind);
end loop;
end if;
END IF;
EXCEPTION
when others then null;
END;
BEGIN
IF ((errorvarchar = 'ERROR')AND(tryGetStatus=1)AND(h1 IS NOT NULL)) THEN
DBMS_DATAPUMP.DETACH(h1);
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END;
IF (getStatusOutput=false)
THEN
errorlog:=errorlog||backtrace;
END IF;
:errorlogout:=errorlog;
END;
GEN DIRECTORY_DMP: TMP_DIR
GEN DIRECTORY_LOG: TMP_DIR
GEN FILENAME_DMP: testuser1.dmp
GEN FILENAME_LOG: testuser1.log
GEN FILESIZE: 500MB
GEN JOB_MODE: SCHEMA
GEN OPERATION: EXPORT
GEN SCHEMA1: TESTUSER1
GEN VERSION: COMPATIBLE
Execution is disabled due to -noexec option
** Datapump Command End ** at 2022.03.18-13.48.09
SQL>
Help
The DATAPUMP HELP command displays the full usage details. As mentioned before, this release only supports a subset of the functionality possible using the DBMS_DATAPUMP package.
SQL> datapump help
DP | DATAPUMP
----------------
Usage: dp help [examples|syntax] |
export [,...] |
import [,...] |
:
File Arguments
--------------
-credential,-c Default: as specified on CLOUDSTORAGE command
CREDENTIAL for dump file access in ORACLE OBJECT STORE.
-directory,-d : Default: DATA_PUMP_DIR
Default database DIRECTORY for reading and writing dump and log files.
-dumpdirectory,-dd : Default: -directory if specified or DATA_PUMP_DIR
Database DIRECTORY for dump file.
-dumpfile,-f [,...] : Default .DMP
Dump file name(s) when using database DIRECTORY.
Multiple files can be specified whether parallelism is enabled or not.
The number of files specified should be at least as large as the degree of parallelism.
-dumpuri,-u [[,...] | [,...]] Default: object name is .DMP
: complete uri for the ORACLE OBJECT STORE file if a default is not set on CLOUDSTORAGE command
: name of the object, optionally qualified by the namespace and the bucket.
The qualified name concatenated to the uri specified on CLOUDSTORAGE command must fully identify the object uri.
Specify the option without a value when the full url is specified on CLOUDSTORAGE command.
CREDENTIAL must be set for direct read/write access to ORACLE OBJECT STORE from autonomous database.
For -copycloud between database DIRECTORY and ORACLE OBJECT STORE, OCI PROFILE must be set.
Multiple uris can be specified whether parallelism is enabled or not.
The number of files specified should be at least as large as the degree of parallelism.
-logdirectory,-ld Default: -directory if specified or DATA_PUMP_DIR
Database DIRECTORY for log file.
-logfile,-lf Default: .LOG
Log file name in the database DIRECTORY.
Command Arguments
-----------------
-noexec,-ne [TRUE|FALSE] Default: FALSE
TRUE : Validate and generate the PL/SQL, but do not execute it.
-verbose,-ve [TRUE|FALSE] Default: FALSE
TRUE : Show additional diagnostic output.
Common Arguments
----------------
-copycloud,-cc [TRUE|FALSE] Default: FALSE
[TRUE] : Copy the dumpfile between database directory and ORACLE OBJECT STORE
On EXPORT, copy the dumpfile from the database directory to ORACLE OBJECT STORE after the datapump job completes.
On IMPORT, copy the dumpfile from ORACLE OBJECT STORE to the database directory before the datapump job starts
OCI PROFILE must be set using OCI command or CLOUDSTORAGE command.
Cloud storage uri must be set using the CLOUDSTORAGE command, SET DATAPUMP command or DATAPUMP command.
-encryptionpassword,-enp Default: None
If password is not specified, a prompt for one will be given.
On export, encrypts the dump files using the password.
On import, the same password that was used for export must be provided.
-excludeexpr,-ex Default: None
Specify an expression identifying an object type or set of object types to exclude from the job.
Example: -excludeexpr "IN ('GRANT','INDEX','TRIGGER')"
-excludelist,-el [,...] Default: None
Specify a comma separated value list of object types to exclude from the job.
Example: -excludelist GRANT,INDEX,TRIGGER
-includeexpr,-ix Default: None
Specify an expression identifying an object type or set of object types to include in the job.
Only matching object types and their dependents will be included in the job.
Use -excludelist or -excludeexpr to exclude dependent objects.
Example: -includeexpr "IN ('TABLE','VIEW')"
-includelist,-il [,...] Default: None
Specify a comma separated value list of object types to include in the job.
Only matching object types and their dependents will be included in the job.
Use -excludelist or -excludeexpr to exclude dependent objects.
Example: -includelist TABLE,VIEW
-includemetadata,-im [TRUE|FALSE] Default: TRUE
TRUE : Include metadata in the job. Default: TRUE
-includerows,-ir [TRUE|FALSE] Default: TRUE
TRUE: Include data in the job. Default: TRUE
-jobname,-j Name for the datapump job. (default: ESQL_ | ISQL_) where n is a datapump generated number
Job Name is appended with a datapump generated number, unless it ends with a number.
Job Name is used when submitting the datapump job and as default name for dump and log filenames/objectnames.
-nameexpr,-nx {=}[;...] Default: None
For specified object type, provide an expression identifying a set of object names to include in the job.
Example: -nameexpr TABLE="IN ('EMPLOYEES', 'DEPARTMENTS')";PROCEDURE="IN ('ADD_JOB_HISTORY','SECURE_DML')"
-namelist,nl {=[,...]}[;...] Default: None
For specified object type, provide a comma separated value list of objects to include in the job.
Example: -namelist TABLE=employees,departments;PROCEDURE=add_job_history,secure_dml
-parallel,-p Default 1
Adjusts the degree of parallelism within a job allowing multiple processes simultaneously.
Specify the same number of files as the degree or some processes may remain idle.
-schemas,-s [,...] Default: On export, schema for the current connection.
Default: On import, full import.
The schema or comma separated value list of schemas to process.
Example: schema1,schema2.
-version,-v {|COMPATIBLE|LATEST} Default: COMPATIBLE
: A specific database version, for example, '11.0.0'.
When exporting from Oracle Database 11g release 2 (11.2.0.3) or later into an Oracle Database 12 c Release 1 (12.1):
Specify a value of 12 to allow all existing database features, components, and options to be exported.
This applies to either a multitenant container database (CDB) or a non-CDB.
COMPATIBLE : Uses metadata version from database compatibility level and the compatibility release level for feature.
LATEST : The version of the metadata corresponds to the database version.
-wait,-w [TRUE|FALSE] Default: TRUE
TRUE : Wait for the datapump job to finish and show summary results.
FALSE : Submit the datapump job without waiting and showing results.
Export Only Arguments
---------------------
-compression,-cm {ALL|DATA_ONLY|METADATA_ONLY|NONE} Default: METADATA_ONLY
Indicates if compression should be done on user data and metadata.
ALL : Compress user data and metadata.
DATA_ONLY : Compress only user data.
METADATA_ONLY : Compress only metadata.
NONE : Do not compress user data or metadata.
-encryption,-en {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE}
Default: NONE, or ALL if any other encryption option is specified.
Specifying any encryption option will turn encryption on if you do not specify -encryption NONE.
-encryptionalgorithm,-ena {AES128 | AES192 | AES256} Default: AES128
Identifies the cryptographic algorithm to be used.
-encryptionmode,-enm {DUAL | PASSWORD | TRANSPARENT}
Default(if encryption is on): TRANSPARENT, or PASSWORD if -encryptionpassword is specified.
Identifies the types of security used for encryption and decryption.
PASSWORD encrypts the dumpfiles using the provided password.
TRANSPARENT allows encryption if the Oracle Encryption Wallet is available.
DUAL allows import using the Oracle Encryption Wallet or the password.
When using DUAL, -encryptionpassword must be specified.
-estimate,-e {BLOCKS|STATISTICS}
Specifies the estimate method for the size of the tables should be performed before starting the job.
BLOCKS : Estimate is calculated using the count of blocks allocated to the user tables.
STATISTICS : Estimate is calculated using the statistics for each table.
If no statistics are available for a table, BLOCKS is used.
-filesize,-fs {{B|KB|MB|GB|TB}} Default: 500MB
Limit for the size of files.
-flashbackscn,-fb [TRUE|FALSE] Default: FALSE
TRUE : Use consistent database content based on scn at start time of execution.
-reusefile,-r [TRUE|FALSE] Default: TRUE
TRUE : Replace existing dump file(s) with new file.
Import Only Arguments
---------------------
-columnencryption,-ce [TRUE|FALSE] Default: TRUE if supported by database
TRUE : Include column encryption clause on table metadata.
FALSE : Omit column encryption clause.
-objectid,-oid [TRUE|FALSE] Default: TRUE
TRUE : Assign the exported OID.
FALSE : Assign a new OID.
-remapschemas,-rs {=[,...]}
objects in the job will be moved to .
Example: oldschema1=newschema1,oldschema2=newschema2.
-remaptablespaces,-rt {=[,...]}
storage segment in the job will be relocated to .
Example: oldtablespace1=newtablespace1,oldtablespace2=newtablespace2
-segmentattributes,-sa [TRUE|FALSE] Default TRUE
TRUE : Include segment attributes clauses (physical attributes, storage attributes, tablespace, logging).
-skipunusableindexes,-sui [TRUE|FALSE] Default: TRUE
TRUE : Tables or partitions with an index in the UNUSABLE state are loaded, as if the unusable index did not exist.
FALSE : Tables or partitions with an index in the UNUSABLE state are not loaded.
-storage,-st [TRUE|FALSE] Default TRUE
TRUE : Include storage clauses.
-tableexists,-te {APPEND|REPLACE|SKIP|TRUNCATE} Default: SKIP when -includemetadata=true, otherwise APPEND
Action to be taken if table exists on import.
APPEND : New rows are added to the existing rows in the table.
REPLACE : Before creating the new table, the old table is dropped.
SKIP : The preexisting table is left unchanged.
TRUNCATE : Rows are removed from a preexisting table before inserting rows from the IMPORT.
-tablecompression,-tc [TRUE|FALSE] Default: TRUE
TRUE : The table compression clause will be included if supported.
FALSE : The table will have the default compression for the tablespace.
SQL>
For more information see:
- Data Pump Support in SQLcl
- SQLcl : Data Pump
- SQLcl : Data Pump (Part 2)
- SQLcl : All Articles
- Data Pump API for PL/SQL (DBMS_DATAPUMP)
Hope this helps. Regards Tim...