8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Exclude ENCRYPTION Clause on Import in Oracle Database 19c

The OMIT_ENCRYPTION_CLAUSE option has been added to the import TRANSFORM parameter in Oracle database 19c. The value of "Y" makes Data Pump supress column encryption clauses for resulting tables. As a result columns that were encrypted in the source table will not be encrypted in the destination table. The default value is "N", making column encryption of the destination table match that of the source table.

Create a Key Management User
Wallet Setup
Create Table With Encrypted Column
Export Table
Import Table
Compare Columns

Create a Key Management User

conn sys/SysPassword1@//localhost:1521/cdb1 as sysdba

create user c##km_user identified by MyPassword1;
grant syskm to c##km_user container=all;

Wallet Setup

We create a location to hold our wallet.

mkdir -p /u01/wallets/cdb1

Edit the "sqlnet.ora" file, setting the correct location for the wallet.

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/wallets/cdb1/)))

If you are using a read-only Oracle home, the "sqlnet.ora" file is stored outside the main Oracle home.

-- Normal Oracle home.
$ORACLE_HOME/network/admin/sqlnet.ora

-- Read-only Oracle home.
$(orabasehome)/network/admin/sqlnet.ora

We connect to the root container using the key management user and create a new wallet.

conn c##km_user/MyPassword1@//localhost:1521/cdb1 as syskm

administer key management create keystore '/u01/wallets/cdb1' identified by "WalletPassword1";
administer key management set keystore open identified by "WalletPassword1" container=all;
administer key management set encryption key identified by "WalletPassword1" with backup container=all;

We can see the wallet listed in the V$ENCRYPTION_WALLET view.

column wrl_parameter format a20

select wallet_type, wrl_parameter, status
from   v$encryption_wallet;

WALLET_TYPE          WRL_PARAMETER        STATUS
-------------------- -------------------- ------------------------------
PASSWORD             /u01/wallets/cdb1/   OPEN

SQL>

Create Table With Encrypted Column

Create and populate a table with an encrypted column.

conn testuser1/testuser1@pdb1

create table t1 (
  id    number(10),
  data  varchar2(50) encrypt
);

insert into t1 values (1, 'This is a secret!');
commit;

Export Table

First we create a directory object and grant access to our test user.

conn sys/SysPassword1@//localhost:1521/pdb1 as sysdba

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

Next we export the table.

expdp testuser1/testuser1@pdb1 \
      tables=t1 \
      directory=tmp_dir \
      logfile=t1-exp.log \
      dumpfile=t1.dmp

Import Table

We create the T2 table by importing the dump file and remapping the T1 table to the T2 table. We use the OMIT_ENCRYPTION_CLAUSE option in the TRANSFORM parameter.

impdp testuser1/testuser1@pdb1 \
      tables=t1 \
      directory=tmp_dir \
      logfile=t1-imp.log \
      dumpfile=t1.dmp \
      remap_table=testuser1.t1:t2 \
      transform=omit_encryption_clause:y

Compare Columns

When we compare the descriptions of the two tables we can see the DATA column in the T1 table is encrypted, but the DATA column in the T2 table is not encrypted.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 DATA                                               VARCHAR2(50) ENCRYPT

SQL> desc t2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)
 DATA                                               VARCHAR2(50)

SQL>

As a result, only the T1.DATA column is listed in the USER_ENCRYPTED_COLUMNS view.

column table_name format a20
column column_name format a20

select table_name, column_name
from   user_encrypted_columns;

TABLE_NAME           COLUMN_NAME
-------------------- --------------------
T1                   DATA

SQL>

We can see the data is still consistent in both the T1 and T2 tables.

select * from t1;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

SQL>


select * from t2;

        ID DATA
---------- --------------------------------------------------
         1 This is a secret!

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.