How To Export The Last Interval Partitions And Encrypt Data Using DBMS_DATAPUMP API? (Doc ID 1315613.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Goal

You have lots of interval partitioned tables in your schema and want to only export the most recent ones. Additionally, the sensitive text data in some specific tables should be encrypted during export

Let's follow the next example that demonstrates this:

-- create test user
connect / as sysdba

create or replace directory dpu as '/tmp';

create user test identified by test default tablespace users temporary tablespace temp;
grant connect, resource to test;
grant execute on sys.dbms_crypto to test;

connect test/test

-- create package/function used to encrypt text data
create or replace package hash_columns is
  function hash_varchar (col in varchar2) return varchar2;
end;
/

create or replace package body hash_columns is
  function hash_varchar (col in varchar2) return varchar2 is
    retval varchar2(4000);
  begin
    retval := utl_raw.cast_to_varchar2 (sys.dbms_crypto.hash (utl_raw.cast_to_raw (col), 1));

    return retval;
  end;
end;
/

-- create normal tables
create table tab001
(
   id   number,
   text varchar2(100)
);
insert into tab001 values (1, 'Text 1');
commit;

create table tab002
(
   id   number,
   text varchar2(10)
);
insert into tab002 values (1, 'Text 1');
insert into tab002 values (2, 'Text 2');
commit;

-- create range partitioned table
create table normal_part001
(
   id   number,
   text varchar2(100)
)
partition by range (id)
(
   partition p001 values less than (10),
   partition p002 values less than (100),
   partition p003 values less than (1000),
   partition p004 values less than (10000),
   partition p005 values less than (100000)
);
insert into normal_part001 values (1, 'Text 1');
insert into normal_part001 values (11, 'Text 11');
insert into normal_part001 values (101, 'Text 101');
insert into normal_part001 values (1001, 'Text 1001');
insert into normal_part001 values (10001, 'Text 10001');
commit;

-- create interval partitioned tables
create table interval_part001
(
   id   number,
   text varchar2(100)
)
partition by range (id)
interval (1000)
(
   partition p001 values less than (1000)
);
insert into interval_part001 values (1, 'Text 1');
insert into interval_part001 values (1001, 'Text 1001');
insert into interval_part001 values (2001, 'Text 2001');
insert into interval_part001 values (3001, 'Text 3001');
insert into interval_part001 values (4001, 'Text 4001');
commit;

create table interval_part002
(
   id   number,
   text varchar2(100)
)
partition by range (id)
interval (1000)
(
   partition p001 values less than (1000)
);
insert into interval_part002 values (1, 'Text 1');
insert into interval_part002 values (1001, 'Text 1001');
insert into interval_part002 values (2001, 'Text 2001');
insert into interval_part002 values (3001, 'Text 3001');
insert into interval_part002 values (4001, 'Text 4001');
commit;

create table interval_part003
(
   id   number,
   text varchar2(100)
)
partition by range (id)
interval (1000)
(
   partition p001 values less than (1000)
);
insert into interval_part003 values (1, 'Text 1');
insert into interval_part003 values (1001, 'Text 1001');
commit;

connect system/manager

-- the inserted tables/columns will be encrypted
create table hash_table_columns
(
   owner       varchar2(30),
   table_name  varchar2(30),
   column_name varchar2(30)
);
insert into hash_table_columns values ('TEST', 'TAB001', 'TEXT');
insert into hash_table_columns values ('TEST', 'TAB002', 'TEXT');
commit;

In conclusion, you have the following objects in schema TEST:

- normal tables TAB001 and TAB002. The columns TEXT in these 2 tables should be encrypted
- range partitioned table NORMAL_PART001 with partitions:

select table_name, partition_position, partition_name
from   dba_tab_partitions
where  table_owner = 'TEST' and
       table_name  = 'NORMAL_PART001'
order  by partition_position;

TABLE_NAME                     PARTITION_POSITION PARTITION_NAME
------------------------------ ------------------ --------------
NORMAL_PART001                                  1 P001
NORMAL_PART001                                  2 P002
NORMAL_PART001                                  3 P003
NORMAL_PART001                                  4 P004
NORMAL_PART001                                  5 P005

- interval partitioned tables INTERVAL_PART001, INTERVAL_PART002, INTERVAL_PART003 with partitions:

select table_name, partition_position, partition_name
from   dba_tab_partitions
where  table_owner = 'TEST' and
       table_name in ('INTERVAL_PART001', 'INTERVAL_PART002', 'INTERVAL_PART003')
order  by table_name, partition_position;

TABLE_NAME                     PARTITION_POSITION PARTITION_NAME
------------------------------ ------------------ --------------
INTERVAL_PART001                                1 P001
INTERVAL_PART001                                2 SYS_P381
INTERVAL_PART001                                3 SYS_P382
INTERVAL_PART001                                4 SYS_P383
INTERVAL_PART001                                5 SYS_P384
INTERVAL_PART002                                1 P001
INTERVAL_PART002                                2 SYS_P385
INTERVAL_PART002                                3 SYS_P386
INTERVAL_PART002                                4 SYS_P387
INTERVAL_PART002                                5 SYS_P388
INTERVAL_PART003                                1 P001
INTERVAL_PART003                                2 SYS_P389

You want to export the last 4 partitions from interval partitioned tables, so only:

INTERVAL_PART001 - SYS_P381, SYS_P382, SYS_P383, SYS_P384
INTERVAL_PART002 - SYS_P385, SYS_P386, SYS_P387, SYS_P388
INTERVAL_PART003 - P001, SYS_P389


Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms