The scenario is using DataPump to refresh a PDB that has compression from a PDB that does not have compression.
The issue is implementing Advanced Compression across an ecosystem of about 13 PDBs, while still performing refreshes of those PDBs weekly. So we would run into issues where we wanted to refresh a PDB that had been compressed, from a PDB that had not been compressed yet.
--Main Settings for Compression
alter system set db_index_compression_inheritance=tablespace;
alter table scott.table_name ENABLE ROW MOVEMENT;
alter tablespace tablespace_name DEFAULT ROW STORE COMPRESS ADVANCED;
alter table scott.table_name ROW STORE COMPRESS ADVANCED;
--Parfile --Network Import with TRANSFORM and EXCLUDE tables with 0 rows
logfile=&logfile#
SCHEMAS=SCOTT
network_link=IMPDP_TMP
ACCESS_METHOD=DIRECT_PATH
directory=impdp_log
FLASHBACK_TIME=systimestamp
TABLE_EXISTS_ACTION=REPLACE
TRANSFORM=TABLE_COMPRESSION_CLAUSE:\"ROW STORE COMPRESS ADVANCED\"
parallel=8
commit=N
buffer = 64000
EXCLUDE=STATISTICS
EXCLUDE=TABLE_STATISTICS
EXCLUDE=TABLE:\"IN(select table_name from dba_tables where owner='SCOTT' and num_rows=0)\"
--Parfile -- Export with Compression
dumpfile=&dumpfile#
logfile=&logfile#
directory=SCOTT_EXPORT
exclude=statistics
FLASHBACK_TIME=systimestamp
COMPRESSION=ALL
tables=(
'SCOTT.TABLE_NAME'
)
--Parfile -- Import with TRANSFORM for Compression
dumpfile=&dumpfile#
logfile=&logfile#
directory=SCOTT_EXPORT
exclude=statistics
TABLE_EXISTS_ACTION=REPLACE
transform=table_compression_clause:\"ROW STORE COMPRESS ADVANCED\"
tables=(
'SCOTT.TABLE_NAME'
)
Comments