DBA Hub

📋Steps in this guide1/5

SQLcl : Data Pump

In this article we demonstrate the Data Pump functionality in SQLcl.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Setup

The examples in this article require the following tables. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
-- 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;

-- 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
2

Export

The USER_OBJECTS view shows us the contents of the test schema. We have two tables, with their respective primary key indexes. We use the 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. 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. 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 flag, to perform a table-level export.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
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>

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>

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>

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>
3

Import

We drop the and tables so we can import them. This time we use the command, with the same arguments as before. Once the operation completes we can see we imported the two tables. 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. 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 flag, to perform a table-level import. We drop the table before importing.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
drop table emp purge;
drop table dept purge;

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>

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>

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>
4

NoExec

In this example we repeat the original export command, but include the argument. This displays the PL/SQL code that would have been run for this command. It's mostly calls to the package.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
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>
5

Help

The command displays the full usage details. As mentioned before, this release only supports a subset of the functionality possible using the package. 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...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!