News

  1. Home
  2. Technology
Technology by Team Ajara

To Disable advanced compression on table partitions

How To Disable advanced compression on table partitions?

Step 1 : Verify Advanced compression

. oraenv
ORCLP
$sqlplus “/as sysdba”
SQL>

col table_name form a32
col compress_for form a20
col PARTITION_NAME form a31
set lines 199
set pages 100
select a.table_name,a.COMPRESSION,a.COMPRESS_FOR,b.NUM_ROWS
from dba_tab_partitions a,dba_tables b,v$instance i,v$database d
where a.compression=’ENABLED’ and a.COMPRESS_FOR=’ADVANCED’ and a.table_name=b.table_name;

 

TABLE_NAME PARTITION_  COMPRESS COMPRESS_FOR NUM_ROWS
————— ———- ————— ————— ———- ——————————
CALL_DET          P2013          ENABLED                ADVANCED     4244980

 

Step 2: export data in to dump file

Parfile

Directory=DATA_PUMP_DIR
dumpfile=expdp_CALL_DET_Partiiton_P2013_ADVCOMP_%U.dmp
logfile=expdp_CALL_DET_Partiiton_P2013_ADVCOMP_apr20.log
parallel=2
tables=HC_OWNER.CALL_DET:P2013

 

run the script in background

nohup expdp \”/ as sysdba\” parfile=expdp_CALL_DET_P2013.par &

export log

. . exported “HC_OWNER”.”CALL_DET”:”P2013″ 1.162 GB 4244980 rows

 

Step 3: Truncate data

ALTER TABLE “HC_OWNER”.”CALL_DET” TRUNCATE PARTITION P2013;

 

Step 4: Disable Advanced compression for partition

ALTER TABLE HC_OWNER.CALL_DET MOVE PARTITION P2013 nocompress;

 

Step 5: Rebuild Indexes

alter session set current_schema=HC_OWNER;

ALTER INDEX CALL_DET_MC_IDX REBUILD parallel 6;

ALTER INDEX CALL_DET_SYMBOL_IDX REBUILD parallel 6;

 

Step 6: Import data into partition with nocompress option in impdp

parfile contents

Directory=DATA_PUMP_DIR
dumpfile=expdp_CALL_DET_Partiiton_P2013_ADVCOMP_%U.dmp
logfile=impdp_CALL_DET_Partiiton_P2013_ADVCOMP.log
parallel=4
table_exists_action=append
transform=table_compression_clause:nocompress
transform=disable_archive_logging:y
tables=HC_OWNER.CALL_DET:P2013

nohup impdp \”/ as sysdba\” parfile=impdp_CALL_DET_P2013_nocompress.par &

. . imported “HC_OWNER”.”CALL_DET”:”P2013″ 1.162 GB 4244980 rows

 

Step 7: Verify Advanced compression

SQL> @check_adv_compression.sql
col table_name form a32
col compress_for form a20
col PARTITION_NAME form a31
set lines 199
set pages 100
select a.table_name,PARTITION_NAME,a.COMPRESSION,a.COMPRESS_FOR,b.NUM_ROWS
from dba_tab_partitions a,dba_tables b,v$instance i,v$database d
where a.compression=’ENABLED’ and a.COMPRESS_FOR=’ADVANCED’ and a.table_name=b.table_name;

no rows selected

 

 

step 8: Verify the count of the records:

SQL> select count(*) from HC_OWNER.CALL_DET partition(p2013);

COUNT(*)
———-
4244980

 

Note: Repeat the steps for all other partitions.

 

See also: