DBA Hub

📋Steps in this guide1/8

Data Pump (expdp, impdp) Enhancements in Oracle Database 21c

This article gives an overview of the Data Pump enhancements in Oracle Database 21c.

oracle 21cconfigurationintermediate
by OracleDba
15 views
1

Setup

Create a test user in your pluggable database. Create a new directory object and grant access to the test user. Create and populate the following table in your test schema.

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
conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

--drop user testuser1 cascade;
create user testuser1 identified by testuser1 quota unlimited on users;
grant connect, resource to testuser1;
grant select_catalog_role to testuser1;

create or replace directory tmp_dir as '/tmp/';
grant read, write on directory tmp_dir to testuser1;

conn testuser1/testuser1@//localhost:1521/pdb1

-- drop table t1 purge;

create table t1 (
  id         number generated always as identity,
  json_data  json,
  constraint ta_pk primary key (id)
);

insert into t1 (json_data) values (json('{"fruit":"apple","quantity":10}'));
insert into t1 (json_data) values (json('{"fruit":"orange","quantity":20}'));
commit;
2

JSON Data Type Support

The export and import utilities include support for the new JSON data type. The following example exports the table using the utility. Remember, the table contains a column defined using the new JSON data type. We import the dump file, remapping the table name to .

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
$ expdp testuser1/testuser1@//localhost:1521/pdb1 \
    tables=t1 \
    directory=tmp_dir \
    dumpfile=t1.dmp \
    logfile=expdp_t1.log \
    exclude=statistics

Export: Release 21.0.0.0.0 - Production on Sun Sep 5 08:41:15 2021
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "TESTUSER1"."SYS_EXPORT_TABLE_01":  testuser1/********@//localhost:1521/pdb1
  tables=t1 directory=tmp_dir dumpfile=t1.dmp logfile=expdp_t1.log exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TESTUSER1"."T1"                            6.070 KB       2 rows
Master table "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_TABLE_01 is:
  /tmp/t1.dmp
Job "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully completed at Sun Sep 5 08:41:45 2021 elapsed 0 00:00:28

$

$ impdp testuser1/testuser1@//localhost:1521/pdb1 \
    tables=t1 \
    directory=tmp_dir \
    dumpfile=t1.dmp \
    logfile=impdp_t1.log \
    remap_table=testuser1.t1:t1_copy

Import: Release 21.0.0.0.0 - Production on Sun Sep 5 08:46:32 2021
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_TABLE_01":  testuser1/********@//localhost:1521/pdb1 
  tables=t1 directory=tmp_dir dumpfile=t1.dmp logfile=impdp_t1.log remap_table=testuser1.t1:t1_copy
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."T1_COPY"                       6.070 KB       2 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"TESTUSER1"."TA_PK" already exists

Job "TESTUSER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sun Sep 5 08:46:39 2021 elapsed 0 00:00:05

$
3

CHECKSUM, CHECKSUM_ALGORITHM, VERIFY_ONLY and VERIFY_CHECKSUM Parameters

Calculating checksums takes time. The bigger the dump file, the more effort it takes to compute a checksum. The and parameters have been added to prevent tampering of data in dump files when they are at rest on disk. If we set the parameter, then the parameter defaults to yes. If neither are set, the parameter defaults to no. The parameter can be set to CRC32, SHA256, SHA384 or SHA512, with SHA256 being the default. In the following example we enable the , and explicitly set the to the default value for a schema export. We can validate the checksum of a dumpfile using the parameter. We use the parameter to verify the checksum during the import. If the verification fails, the import doesn't take place. If we don't use the parameter, the import will continue, even if the checksum is incorrect.

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
$ expdp testuser1/testuser1@//localhost:1521/pdb1 \
    schemas=testuser1 \
    directory=tmp_dir \
    dumpfile=testuser1.dmp \
    logfile=expdp_testuser1.log \
    exclude=statistics \
checksum=yes
\
checksum_algorithm=SHA256
Export: Release 21.0.0.0.0 - Production on Sun Sep 5 08:58:55 2021
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "TESTUSER1"."SYS_EXPORT_SCHEMA_01":  testuser1/********@//localhost:1521/pdb1
  schemas=testuser1 directory=tmp_dir dumpfile=testuser1.dmp logfile=expdp_testuser1.log exclude=statistics checksum=yes checksum_algorithm=SHA256
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TESTUSER1"."T1"                            6.070 KB       2 rows
. . exported "TESTUSER1"."T1_COPY"                       6.078 KB       2 rows
Master table "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
Generating checksums for dump file set
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_SCHEMA_01 is:
  /tmp/testuser1.dmp
Job "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 5 08:59:38 2021 elapsed 0 00:00:41

$

$ impdp testuser1/testuser1@//localhost:1521/pdb1 \
    directory=tmp_dir \
    dumpfile=testuser1.dmp \
verify_only=yes
Import: Release 21.0.0.0.0 - Production on Sun Sep 5 09:10:55 2021
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Verifying dump file checksums
Master table "TESTUSER1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
dump file set is complete
verified checksum for dump file "/tmp/testuser1.dmp"
dump file set is consistent
Job "TESTUSER1"."SYS_IMPORT_FULL_01" successfully completed at Sun Sep 5 09:10:57 2021 elapsed 0 00:00:01

$

$ impdp testuser1/testuser1@//localhost:1521/pdb1 \
    tables=t1 \
    directory=tmp_dir \
    dumpfile=testuser1.dmp \
    logfile=impdp_t1_copy_again.log \
    remap_table=testuser1.t1:t1_copy_again \
verify_checksum=yes
Import: Release 21.0.0.0.0 - Production on Sun Sep 5 09:16:24 2021
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Verifying dump file checksums
Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_TABLE_01":  testuser1/********@//localhost:1521/pdb1
  tables=t1 directory=tmp_dir dumpfile=testuser1.dmp logfile=impdp_t1_copy_again.log
  remap_table=testuser1.t1:t1_copy_again verify_checksum=yes
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."T1_COPY_AGAIN"                 6.070 KB       2 rows
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-31684: Object type CONSTRAINT:"TESTUSER1"."TA_PK" already exists

Job "TESTUSER1"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at Sun Sep 5 09:16:30 2021 elapsed 0 00:00:04

$
4

INCLUDE and EXCLUDE in the Same Operation

In Oracle database 21c, and parameters can be part of the same command. In previous releases and parameters were mutually exclusive. The following example combines and parameters in a single command. We have to escape some of the quotes for the command line.

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
$ expdp testuser1/testuser1@//localhost:1521/pdb1 \
    schemas=testuser1 \
    directory=tmp_dir \
    dumpfile=testuser1.dmp \
    logfile=expdp_testuser1.log \
include="table:\"in ('T1')\""
\
exclude="table:\"in ('T1_COPY','T1_COPY_AGAIN')\""
\
exclude=statistics
Export: Release 21.0.0.0.0 - Production on Sun Sep 5 10:54:03 2021
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "TESTUSER1"."SYS_EXPORT_SCHEMA_01":  testuser1/********@//localhost:1521/pdb1
  schemas=testuser1 directory=tmp_dir dumpfile=testuser1.dmp logfile=expdp_testuser1.log
  include=table:"in ('T1')" exclude=table:"in ('T1_COPY','T1_COPY_AGAIN')" exclude=statistics
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TESTUSER1"."T1"                            6.070 KB       2 rows
Master table "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_SCHEMA_01 is:
  /tmp/testuser1.dmp
Job "TESTUSER1"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Sep 5 10:54:31 2021 elapsed 0 00:00:27

$
5

Index Compression

In Oracle database 21c we can optionally compress indexes on import using the parameter and the . Create a test table with some indexes. Check the compression for the table and indexes. Export the table. Remove the table, so we can reimport it. Import the table from the dump file, using the parameter to compress the table using the and the indexes using the . Check the compression for the table and indexes. We can see both the table and indexes have now been compressed. You can read the full description for the parameter here . For information about index compression see the statement here .

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
conn testuser1/testuser1@//localhost:1521/pdb1

-- drop table t2 purge;

create table t2 as
  select level as id,
         'Description for ' || level as col1,
         case mod(level, 2)
           when 0 then 'one'
           else 'two'
         end as col2,
         trunc(dbms_random.value(0,10)) as col3,
         trunc(dbms_random.value(0,20)) as col4
  from   dual
  connect by level <= 10000;

alter table t2 add constraint t2_pk primary key (id);
create index t2_col1_idx on t2(col1);
create index t2_col2_idx on t2(col2);
create index t2_col3_idx on t2(col3);
create index t2_col4_idx on t2(col4);

select compression
from   user_tables
where  table_name = 'T2';

COMPRESS
--------
DISABLED

SQL>


column index_name format a12

select index_name,
       compression
from   user_indexes
where  table_name = 'T2'
order by 1;

INDEX_NAME   COMPRESSION
------------ -------------
T2_COL1_IDX  DISABLED
T2_COL2_IDX  DISABLED
T2_COL3_IDX  DISABLED
T2_COL4_IDX  DISABLED
T2_PK        DISABLED

SQL>

$ expdp testuser1/testuser1@//localhost:1521/pdb1 \
    tables=t2 \
    directory=tmp_dir \
    dumpfile=t2.dmp \
    logfile=expdp_t2.log \
    exclude=statistics

Export: Release 21.0.0.0.0 - Production on Sun Sep 5 11:57:18 2021
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Starting "TESTUSER1"."SYS_EXPORT_TABLE_01":  testuser1/********@//localhost:1521/pdb1
  tables=t2 directory=tmp_dir dumpfile=t2.dmp logfile=expdp_t2.log exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TESTUSER1"."T2"                            384.8 KB   10000 rows
Master table "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TESTUSER1.SYS_EXPORT_TABLE_01 is:
  /tmp/t2.dmp
Job "TESTUSER1"."SYS_EXPORT_TABLE_01" successfully completed at Sun Sep 5 11:57:35 2021 elapsed 0 00:00:14

$

conn testuser1/testuser1@//localhost:1521/pdb1

drop table t2 purge;

$ impdp testuser1/testuser1@//localhost:1521/pdb1 \
    tables=t2 \
    directory=tmp_dir \
    dumpfile=t2.dmp \
    logfile=impdp_t2.log \
transform=table_compression_clause:\"compress basic\"
\
transform=index_compression_clause:\"compress advanced low\"
Import: Release 21.0.0.0.0 - Production on Sun Sep 5 12:02:22 2021
Version 21.3.0.0.0

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

Connected to: Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Master table "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TESTUSER1"."SYS_IMPORT_TABLE_01":  testuser1/********@//localhost:1521/pdb1
  tables=t2 directory=tmp_dir dumpfile=t2.dmp logfile=impdp_t2.log
  transform=table_compression_clause:"compress basic" transform=index_compression_clause:"compress advanced low"
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER1"."T2"                            384.8 KB   10000 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "TESTUSER1"."SYS_IMPORT_TABLE_01" successfully completed at Sun Sep 5 12:02:29 2021 elapsed 0 00:00:05

$

conn testuser1/testuser1@//localhost:1521/pdb1

select compression
from   user_tables
where  table_name = 'T2';

COMPRESS
--------
ENABLED

SQL>


column index_name format a12

select index_name,
       compression
from   user_indexes
where  table_name = 'T2'
order by 1;

INDEX_NAME   COMPRESSION
------------ -------------
T2_COL1_IDX  ADVANCED LOW
T2_COL2_IDX  ADVANCED LOW
T2_COL3_IDX  ADVANCED LOW
T2_COL4_IDX  ADVANCED LOW
T2_PK        DISABLED

SQL>
6

Transportable Tablespace Enhancements

In Oracle 21c transportable tablespace exports ( ) and imports ( ) can now use the parameter to parallelize the operations. In Oracle 21c Data Pump can resume failed transportable tablespace jobs at, or near, the point of failure. In previous releases transportable tablespace jobs could not be resumed.
7

Export from Oracle Autonomous Database

We can use a local Oracle 21.3 installation to export data from the autonomous database to an object store using the utility. You can read about this functionality in this article. - Oracle Cloud : Autonomous Database (ADW or ATP) - Export Data to an Object Store (expdp)
8

Export to and Import From Cloud Object Stores

Data Pump supports cloud object stores as a dumpfile location for on-prem installations from Oracle 21c onward. For more information see: Hope this helps. Regards Tim...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!