Migrating Away from Oracle Advanced Compression: Export/Import and AWS DMS
There are many reasons moving enterprise workloads away from Oracle’s proprietary technologies may be necessary. One of these technologies is Advanced Compression that reduces storage costs by compressing data blocks. Use of this feature ties your workload to Oracle’s ecosystem and licensing model and is generally licensed on a per-core basis, which can become expensive for growing workloads.
This blog outlines how to migrate an Oracle table between two AWS Oracle databases — removing the dependency on Advanced Compression using two methods:
- Oracle’s export/import utilities
- AWS DMS (Database Migration Service)
Our use case involved a customer who was previously using Oracle Advanced Compression but wanted to transition away from it. Instead, they opted to pay for additional AWS io2 storage, which proved to be more cost-effective than the licensing fees required for Advanced Compression. The following steps outline the process of migrating the table TEST_TBL from the source database to the target database, ensuring that the Advanced Compression feature is not used in thetarget database.
Prerequisites
The following tools need to be available in your environment to run the migration:
- SQL*Plus or any Oracle SQL client - should be installed already with the Oracle database software
- AWS CLI configured with access to the source and target RDS instances
- AWS DMS configured with access to the source and target RDS instances
- Oracle Data Pump utilities (if using the export/import method) - should be installed already with the Oracle database software
Pre-Migration Assessment (Applies to both methods)
Before migrating, you must have two aws oracle rds databases (source and target) and a s3 bucket must be created to store the exported data. This bucket will be used to transfer the data between the source and target databases.
- The following command will create an S3 bucket named
867392104558-data-archivein theus-west-2region using the AWS CLI:
aws
s3api create
-bucket
\
--bucket 867392104558-data-archive \
--region us-west-2 \
--create-bucket-configuration LocationConstraint=us-west-2
- The following AWS CLI command will create a directory in the S3 bucket to store the exported data:
aws s3api put-object
\
--bucket 867392104558-data-archive \
--key oracle/
- In a terminal, connect to the SOURCE database using SQL*Plus , run:
sqlplus
user1/'blah'@//oracle1.x9f3b2z8q721.us-west-2.rds.amazonaws.com:1521/ORACLE1
- In a separate terminal from the command above, connect to the TARGET database, by running:
sqlplus
user1/'blah'@//oracle2.x9f3b2z8q721.us-west-2.rds.amazonaws.com:1521/ORACLE2
- Run the following SQL to understand the table characteristics, including compression settings. Specicifally you are verifying that the table is using Advanced Compression.
SQL>
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A20
COLUMN compression FORMAT A12
SELECT owner, table_name, compression
FROM dba_tables
WHERE owner = 'USER1'
AND table_name = 'TEST_TBL';
TABLE_NAME
COMPRESSION COMPRESS_FOR
-------------------- ------------ ---------------
TEST_TBL ENABLED ADVANCED
- Get the DDL for the table and indexes, which will be used to create the table on the TARGET database without Advanced Compression. Run the following SQL on the SOURCE database:
SQL>
SET LONG 1000000
SET PAGESIZE 0
SET LINESIZE 32767
SET LONGCHUNKSIZE 1000000
SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST_TBL')
FROM DUAL;
CREATE TABLE "USER1"."TEST_TBL"
(
"GDOBNM" NCHAR(10),
"GDTXKY" NVARCHAR2(254),
"GDMOSEQN" NUMBER(15,0),
"GDGTMOTYPE" NUMBER(15,0),
"GDLNGP" NCHAR(2),
"GDUSER" NCHAR(10),
"GDUPMJ" NUMBER(6,0),
"GDTDAY" NUMBER,
"GDGTITNM" NCHAR(50),
"GDQUNAM" NCHAR(30),
"GDGTFILENM" NCHAR(254),
"GDGTFUTS1" NCHAR(10),
"GDGTFUTS2" NCHAR(10),
"GDGTFUTS3" NCHAR(50),
"GDGTFUTS4" NCHAR(50),
"GDGTFUTM1" NUMBER,
"GDGTFUTM2" NUMBER,
"GDTXFT" BLOB,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
ROW STORE COMPRESS ADVANCED LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
LOB ("GDTXFT") STORE AS SECUREFILE (
TABLESPACE "TEST_TBSP" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
CREATE UNIQUE INDEX "USER1"."TEST_TBL_0" ON "USER1"."TEST_TBL" ("GDOBNM", "GDTXKY", "GDMOSEQN") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
ALTER TABLE "USER1"."TEST_TBL"
ADD CONSTRAINT "TEST_TBL_PK" PRIMARY KEY ("GDOBNM", "GDTXKY", "GDMOSEQN") USING INDEX "USER1"."TEST_TBL_0" ENABLE
SQL>
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name)
FROM user_indexes
WHERE table_name = 'TEST_TBL'
AND table_owner = 'USER1';
CREATE UNIQUE INDEX "USER1"."SYS_IL0000026816C00018$$" ON "USER1"."TEST_TBL" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
PARALLEL (DEGREE 0 INSTANCES 0)
CREATE UNIQUE INDEX "USER1"."TEST_TBL_0" ON "USER1"."TEST_TBL" ("GDOBNM", "GDTXKY", "GDMOSEQN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
CREATE INDEX "USER1"."TEST_TBL_2" ON "USER1"."TEST_TBL" ("GDOBNM", "GDTXKY", "GDGTMOTYPE", "GDMOSEQN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
CREATE INDEX "USER1"."TEST_TBL_3" ON "USER1"."TEST_TBL" ("GDOBNM", "GDTXKY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
CREATE INDEX "USER1"."TEST_TBL_4" ON "USER1"."TEST_TBL" ("GDLNGP", "GDOBNM", "GDTXKY", "GDGTMOTYPE", "GDMOSEQN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
CREATE INDEX "USER1"."TEST_TBL_5" ON "USER1"."TEST_TBL" ("GDQUNAM", "GDGTFILENM")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
Option 1: Export/Import RDS Method
- To export the table TEST_TBL from the SOURCE database, you can use the Oracle Data Pump utility. The following PL/SQL block will create a Data Pump job to export the table:
SQL>
DECLARE
v_hdnl NUMBER;
BEGIN
v_hdnl
:= DBMS_DATAPUMP.OPEN(
operation => 'EXPORT',
job_mode => 'TABLE',
job_name => null
);
DBMS_DATAPUMP.ADD_FILE
(
handle => v_hdnl ,
filename => 'exp_testtbl.dmp',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file
);
DBMS_DATAPUMP.ADD_FILE
(
handle => v_hdnl,
filename => 'exp_testtbl.log',
directory => 'DATA_PUMP_DIR' ,
filetype => dbms_datapump.ku$_file_type_log_file
);
DBMS_DATAPUMP.METADATA_FILTER
(v_hdnl,'NAME_EXPR','IN (''TEST_TBL'')');
DBMS_DATAPUMP.METADATA_FILTER
(v_hdnl,'SCHEMA_EXPR','IN (''USER1'')');
DBMS_DATAPUMP.START_JOB
(v_hdnl);
END;
/
PL/SQL procedure successfully completed.
- To verify that the export was successful, you can check the log file generated by the Data Pump job. The following SQL query will read the log file:
SQL>
SELECT *
FROM TABLE(rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR', 'exp_testtbl.log'));
TEXT
--------------------------------------------------------------------------------
Starting "USER1"."SYS_EXPORT_TABLE_01":
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "USER1"."TEST_TBL" 24.28 KB 10 rows
Master table "USER1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
TEXT
--------------------------------------------------------------------------------
Dump file set for USER1.SYS_EXPORT_TABLE_01 is:
/rdsdbdata/datapump/exp_testtbl.dmp
Job "USER1"."SYS_EXPORT_TABLE_01" successfully completed at Fri Jun 20 15:02:46
2025 elapsed 0 00:00:11
14 rows selected.
- Next, you can confirm that the export files are present in the DATA_PUMP_DIR directory. The following SQL query will list the files in the directory:
SQL>
set pages 999
set lines 1000
COLUMN filename FORMAT A40
COLUMN type FORMAT A10
COLUMN "filesize mb" FORMAT 9999990.00
COLUMN mtime FORMAT A20
SELECT filename,
type,
round(filesize / 1024 / 1024, 2) AS "filesize mb",
to_char(mtime, 'DD-MON-YY HH24:MI:SS') AS "mtime"
FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'))
ORDER BY mtime;
FILENAME
TYPE filesize mb mtime
---------------------------------------- ---------- ----------- --------------------
datapump/ directory 0.00 20-JUN-25 15:02:35
exp_testtbl.dmp file 0.25 20-JUN-25 15:02:46
exp_testtbl.log file 0.00 20-JUN-25 15:02:46
- Then, you can remove the export files from the DATA_PUMP_DIR directory if you need to redo the export. The following PL/SQL blocks will remove the files:
begin
UTL_FILE.FREMOVE
('DATA_PUMP_DIR', 'exp_testtbl.dmp');
end;
/
begin
UTL_FILE.FREMOVE
('DATA_PUMP_DIR', 'exp_testtbl.log');
end;
/
- Once the export is complete, you can upload the exported dump file to an S3 bucket using the RDS S3 tasks package. The following SQL query will upload the dump file to the specified S3 bucket:
SQL>
SELECT rdsadmin.rdsadmin_s3_tasks.upload_to_s3(
p_bucket_name = > '867392104558-data-archive',
p_directory_name = > 'DATA_PUMP_DIR',
p_s3_prefix = > 'oracle/',
p_prefix = > 'exp_testtbl.dmp'
) AS task_id
FROM DUAL;
TASK_ID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1750453513027-1302
SQL>
COLUMN text FORMAT A200 WORD_WRAPPED
SELECT text
FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-1750453513027-1302.log'));
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2025-06-20 21:05:13.106 UTC [INFO ] File #1: Uploading the file /rdsdbdata/datapump/exp_testtbl.dmp to Amazon S3 with bucket name 867392104558-data-archive and key oracle/exp_testtbl.dmp.
2025-06-20 21:05:13.193 UTC [INFO ] The file /rdsdbdata/datapump/exp_testtbl.dmp was uploaded to Amazon S3 with bucket name 867392104558-data-archive and key oracle/exp_testtbl.dmp.
2025-06-20 21:05:13.193 UTC [INFO ] The task finished successfully.
- To download the exported dump file from the S3 bucket to the TARGET database, you can use the RDS S3 tasks package. The following SQL query will download the dump file from the specified S3 bucket:
SQL>
SELECT rdsadmin.rdsadmin_s3_tasks.download_from_s3(
p_bucket_name = > '867392104558-data-archive',
p_s3_prefix = > 'oracle/exp_testtbl.dmp',
p_directory_name = > 'DATA_PUMP_DIR',
p_decompression_format = > 'NONE',
p_error_on_zero_downloads = > 'TRUE'
) AS task_id
FROM DUAL;
TASK_ID
--------------------------------------------------------------------------------
1750455554159-33
- After downloading the dump file, you can remove the export files from the DATA_PUMP_DIR directory on the TARGET database. The following PL/SQL blocks will remove the files:
begin
UTL_FILE.FREMOVE
('DATA_PUMP_DIR', 'exp_testtbl.dmp');
end;
/
begin
UTL_FILE.FREMOVE
('DATA_PUMP_DIR', 'exp_testtbl.log');
end;
/
- To verify that the download was successful, you can check the log file generated by the RDS S3 tasks package. The following SQL query will read the log file:
SQL>
COLUMN text FORMAT A200 WORD_WRAPPED
SELECT text
FROM table(rdsadmin.rds_file_util.read_text_file('BDUMP', 'dbtask-1750455554159-33.log'));
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2025-06-20 21:39:14.228 UTC [INFO ] This task is about to list the Amazon S3 objects for AWS Region us-west-2, bucket name 867392104558-data-archive, and prefix oracle/exp_testtbl.dmp.
2025-06-20 21:39:14.271 UTC [INFO ] The task successfully listed the Amazon S3 objects for AWS Region us-west-2, bucket name 867392104558-data-archive, and prefix oracle/exp_testtbl.dmp.
2025-06-20 21:39:14.285 UTC [INFO ] This task is about to download the Amazon S3 object or objects in /rdsdbdata/datapump from bucket name 867392104558-data-archive and key oracle/exp_testtbl.dmp.
2025-06-20 21:39:14.436 UTC [INFO ] The task successfully downloaded the Amazon S3 object or objects from bucket name 867392104558-data-archive with key oracle/exp_testtbl.dmp to the location
/rdsdbdata/datapump.
2025-06-20 21:39:14.436 UTC [INFO ] The task finished successfully.
- To confirm that the dump file is present in the DATA_PUMP_DIR directory on the TARGET database, you can run the following SQL query:
SQL>
set pages 999
set lines 1000
COLUMN filename FORMAT A40
COLUMN type FORMAT A10
COLUMN "filesize mb" FORMAT 9999990.00
COLUMN mtime FORMAT A20
SELECT filename,
type,
round(filesize / 1024 / 1024, 2) AS "filesize mb",
to_char(mtime, 'DD-MON-YY HH24:MI:SS') AS "mtime"
FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR'))
ORDER BY mtime;
FILENAME
TYPE filesize mb mtime
---------------------------------------- ---------- ----------- --------------------
datapump/ directory 0.00 20-JUN-25 15:39:14
exp_testtbl.dmp file 0.25 20-JUN-25 15:39:14
- To import the dump file into the TARGET database, you can use the Oracle Data Pump utility. The following PL/SQL block will create a Data Pump job to import the table without Advanced Compression:
SQL>
DECLARE
v_hdnl NUMBER;
BEGIN
v_hdnl
:= DBMS_DATAPUMP.OPEN(
operation => 'IMPORT',
job_mode => 'TABLE',
job_name => null);
DBMS_DATAPUMP.ADD_FILE
(
handle => v_hdnl,
filename => 'exp_testtbl.dmp',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE
(
handle => v_hdnl,
filename => 'imp_testtbl.log',
directory => 'DATA_PUMP_DIR',
filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_TRANSFORM
(
handle => v_hdnl,
name => 'LOB_STORAGE',
value => 'DEFAULT'
);
DBMS_DATAPUMP.METADATA_TRANSFORM
(
handle => v_hdnl,
name => 'TABLE_COMPRESSION_CLAUSE',
value => 'NONE'
);
DBMS_DATAPUMP.METADATA_TRANSFORM
(
handle => v_hdnl,
name => 'SEGMENT_ATTRIBUTES',
value => 0
);
DBMS_DATAPUMP.METADATA_FILTER
(v_hdnl,'SCHEMA_EXPR','IN (''USER1'')');
DBMS_DATAPUMP.METADATA_FILTER
(v_hdnl,'NAME_EXPR','IN (''TEST_TBL'')');
DBMS_DATAPUMP.METADATA_FILTER
(v_hdnl,'EXCLUDE_PATH_EXPR','IN (''STATISTICS'')');
DBMS_DATAPUMP.START_JOB
(v_hdnl);
END;
/
PL/SQL procedure successfully completed.
- To verify that the import was successful, you can check the log file generated by the Data Pump job. The following SQL query will read the log file:
SQL>
COLUMN text FORMAT A200 WORD_WRAPPED
SELECT *
FROM TABLE(rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR', 'imp_testtbl.log'));
TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Master table "USER1"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "USER1"."SYS_IMPORT_TABLE_01":
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER1"."TEST_TBL" 24.28 KB 10 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "USER1"."SYS_IMPORT_TABLE_01" successfully completed at Fri Jun 20 15:42:07 2025 elapsed 0 00:00:03
8 rows selected.
- To confirm that the compression is disabled on the TARGET database, you can run the following SQL query:
SQL>
SET LINESIZE 200
SET PAGESIZE 50
COLUMN owner FORMAT A20
COLUMN table_name FORMAT A20
COLUMN compression FORMAT A12
SELECT owner, table_name, compression
FROM dba_tables
WHERE owner = 'USER1'
AND table_name = 'TEST_TBL';
OWNER
TABLE_NAME COMPRESSION
-------------------- -------------------- ------------
USER1 TEST_TBL DISABLED
SQL>
COLUMN owner FORMAT A15
COLUMN index_name FORMAT A35
COLUMN compression FORMAT A20
select owner, index_name, compression
from dba_indexes
where table_owner = 'USER1'
and table_name = 'TEST_TBL';
OWNER
INDEX_NAME COMPRESSION
--------------- ----------------------------------- --------------------
USER1 SYS_IL0000027425C00018$$ DISABLED
USER1 TEST_TBL_4 DISABLED
USER1 TEST_TBL_0 DISABLED
USER1 TEST_TBL_5 DISABLED
USER1 TEST_TBL_3 DISABLED
USER1 TEST_TBL_2 DISABLED
6 rows selected.
SQL>
SELECT owner, table_name, column_name, securefile, compression
FROM dba_lobs
WHERE securefile = 'YES'
AND compression IN ('LOW', 'MEDIUM', 'HIGH')
ORDER BY owner, table_name, column_name;
no rows selected
Option 2: DMS Method
- To migrate the table TEST_TBL from the SOURCE database to the TARGET database using AWS DMS, you first need to extract the clean DDL without Advanced Compression settings. This DDL will be used to create the table on the TARGET database.
SQL>
CREATE
BIGFILE TABLESPACE TEST_TBSP DATAFILE
SIZE 10M AUTOEXTEND ON NEXT 100M;
SET
LONG 1000000
SET PAGESIZE 0
SET LINESIZE 32767
SET LONGCHUNKSIZE 1000000
SELECT DBMS_METADATA.GET_DDL('TABLE', 'TEST_TBL')
FROM DUAL;
CREATE TABLE "USER1"."TEST_TBL"
(
"GDOBNM" NCHAR(10),
"GDTXKY" NVARCHAR2(254),
"GDMOSEQN" NUMBER(15,0),
"GDGTMOTYPE" NUMBER(15,0),
"GDLNGP" NCHAR(2),
"GDUSER" NCHAR(10),
"GDUPMJ" NUMBER(6,0),
"GDTDAY" NUMBER,
"GDGTITNM" NCHAR(50),
"GDQUNAM" NCHAR(30),
"GDGTFILENM" NCHAR(254),
"GDGTFUTS1" NCHAR(10),
"GDGTFUTS2" NCHAR(10),
"GDGTFUTS3" NCHAR(50),
"GDGTFUTS4" NCHAR(50),
"GDGTFUTM1" NUMBER,
"GDGTFUTM2" NUMBER,
"GDTXFT" BLOB,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
ROW STORE COMPRESS ADVANCED LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
LOB ("GDTXFT") STORE AS SECUREFILE (
TABLESPACE "TEST_TBSP" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES
STORAGE(INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
CREATE UNIQUE INDEX "USER1"."TEST_TBL_0" ON "USER1"."TEST_TBL" ("GDOBNM", "GDTXKY", "GDMOSEQN") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
ALTER TABLE "USER1"."TEST_TBL"
ADD CONSTRAINT "TEST_TBL_PK" PRIMARY KEY ("GDOBNM", "GDTXKY", "GDMOSEQN") USING INDEX "USER1"."TEST_TBL_0" ENABLE
SQL>
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name)
FROM user_indexes
WHERE table_name = 'TEST_TBL'
AND table_owner = 'USER1';
CREATE UNIQUE INDEX "USER1"."SYS_IL0000026816C00018$$" ON "USER1"."TEST_TBL" (
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
PARALLEL (DEGREE 0 INSTANCES 0)
CREATE UNIQUE INDEX "USER1"."TEST_TBL_0" ON "USER1"."TEST_TBL" ("GDOBNM", "GDTXKY", "GDMOSEQN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
CREATE INDEX "USER1"."TEST_TBL_2" ON "USER1"."TEST_TBL" ("GDOBNM", "GDTXKY", "GDGTMOTYPE", "GDMOSEQN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
CREATE INDEX "USER1"."TEST_TBL_3" ON "USER1"."TEST_TBL" ("GDOBNM", "GDTXKY")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
CREATE INDEX "USER1"."TEST_TBL_4" ON "USER1"."TEST_TBL" ("GDLNGP", "GDOBNM", "GDTXKY", "GDGTMOTYPE", "GDMOSEQN")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
CREATE INDEX "USER1"."TEST_TBL_5" ON "USER1"."TEST_TBL" ("GDQUNAM", "GDGTFILENM")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED HIGH
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TEST_TBSP"
- In the TARGET database, create the table TEST_TBL without Advanced Compression by running the modified DDL from above.
sqlplus
user1/'blah'@//oracle2.x9f3b2z8q721.us-west-2.rds.amazonaws.com:1521/ORACLE2
- To migrate the data from the SOURCE database to the TARGET database using AWS DMS, you need to create a DMS task with the appropriate rules. The following JSON configuration specifies that only the TEST_TBL table in the USER1 schema should be included in the migration:
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "USER1",
"table-name": "TEST_TBL"
},
"rule-action": "include"
}
]
}
Summary
Migrating away from Oracle Advanced Compression using Oracle Data Pump and AWS DMS is an effective strategy to remove dependence on proprietary technologies and potentially save you future licensing costs at the expense of added storage costs.