HCC and Transportable Tablespaces - Alex Zaballa

What happens if you are running a database in a source system that supports HCC (Hybrid Columnar Compression) and need to move the tablespace to another system that does not support HCC using the transportable tablespace method?

For a regular Data Pump export/import, you can use transform=table_compression_clause:none.

For a physical standby, if you need to perform a switchover or failover, you can use the command ALTER TABLE x MOVE to uncompress the table before accessing it.

Let’s test for TTS.

create tablespace mydata datafile '+data' size 1M;

create user myapp identified by XXXXXXXX;

alter user myapp quota unlimited on mydata;

CREATE TABLE myapp.CUSTOMER_DATA (
  CUSTOMER_ID NUMBER PRIMARY KEY,
  FIRST_NAME  VARCHAR2(50),
  LAST_NAME   VARCHAR2(50),
  EMAIL       VARCHAR2(100)
)
COMPRESS FOR QUERY HIGH TABLESPACE mydata;

insert into myapp.CUSTOMER_DATA values(1,'Alex','Zaballa','my@email.com');
commit;

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'MYDATA', incl_constraints => TRUE);

SELECT * FROM transport_set_violations;

ALTER TABLESPACE MYDATA READ ONLY;

select * from dba_data_files where tablespace_name='MYDATA';

CREATE DIRECTORY dgroup AS '+DATA/ORCL_6R5_IAD/2B7453F9BE634A83E063D900000A8F7D/DATAFILE';

CREATE DIRECTORY mydir  AS '/home/oracle/files';

BEGIN
   DBMS_FILE_TRANSFER.COPY_FILE('DGROUP','mydata.280.1193932973',
'MYDIR','mydata.dbf');
END;
/

Let’s export:

expdp az@pdb1  DIRECTORY=MYDIR DUMPFILE=mydata.dmp TRANSPORT_TABLESPACES=MYDATA ENCRYPTION_PASSWORD=MySuperPasswd

You can transfer the dump file and the datafile from the source database to the target database using SCP.

My target database is not using ASM, so I will move the datafile to the correct location (/u02):

Let’s import the metadata and plug in the datafile in the new database:

impdp az@pdb1 DIRECTORY= MYDIR DUMPFILE= mydata.dmp TRANSPORT_DATAFILES='/u02/oradata/CDB1/pdb1/mydata.dbf' ENCRYPTION_PASSWORD=MySuperPasswd

As you can see, an error occurs when attempting to create the table because HCC is not supported in this environment.

In case you face the error “ORA-29340: export file appears to be corrupted: [comp type] [0] [1]” try to check this MOS Note: ORA-29340: export file appears to be corrupted: [comp type] [0] [1] (Doc ID 2943771.1) and make sure the binaries on the target database are equal or higher than the source.

If I try to use transform=table_compression_clause:none, it fails:

Conclusion:

You need to decompress the tables using HCC on the source system before exporting them using TTS.