USE COMPRESSION FOR TABLE/INDEXES IN 12.1.0.2.0 PDB

Hi Readers, in this blogpost i am sharing steps to enable compression for tables/indexes with minimal DML (INSERT/UPDATE/DELETE) activity in order to reclaim tablespace free space in a WAREHOUSE environment.

Note – Please check your licensing for using compression features (only available in Enterprise Edition).

Here, we had moved 21 tables in same tablespace with compress option –

TABLESPACE stats before the activity – (free space 2522 GB)

TABLESPACE stats post activity – (3 TB APPROX FREE SPACE WAS RECLAIMED)

I am only showing here relative gain for tables – (remaining space gain was achieved from index rebuild)

STEPS –

1. DISABLE ARCHIVELOGGING IF POSSIBLE (STANDALONE DEV. ENV.) FOR THIS ACTIVITY TO AVOID HIGH ARCHIVE GENERATION).
2. SAVE STATS OF TABLE/INDEX SIZES PRE-ACTIVITY TO COMPARE GAIN.
3. MOVE THE TABLE USING PARALLEL SESSIONS TO SAVE TIME.
4. USE SHELL SCRIPT TO RUN THE OPERATION IN NOHUP.
5. Remember to rebuild indexes of related tables otherwise they will remain unusable.

SHELL SCRIPT EXAMPLE –

export ORACLE_SID=DB_INSTANCE_NAME
export ORACLE_BASE=/u02/app/oracle/product
export ORACLE_HOME=/u02/app/oracle/product/12.1.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
NOW=$(date +”%m-%d-%Y”)
/u02/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus “/ as sysdba” << EOF
ALTER SESSION SET CONTAINER=PDBNAME;
@/home/oracle/table_compress_reorg_2.sql
exit
EOF

sql file code –

spool /home/oracle/table_compress_reorg_181022_2.log
set echo on
set time on
set timing on
set feedback on
column owner format a30
column segment_name format a40
column segment_type format a30
set lines 1000 pages 1000
!date
select owner,segment_name,segment_type,bytes/(1024*1024*1024)”Size(GB)” from dba_segments where segment_name in(‘TABLE_NAME’);
alter table OWNER.TABLE_NAME enable row movement;
alter table OWNER.TABLE_NAME move tablespace TBSNAME compress parallel 32;
ALTER TABLE OWNER.TABLE_NAME NOPARALLEL;
ALTER TABLE OWNER.TABLE_NAME disable ROW MOVEMENT;
select owner,segment_name,segment_type,bytes/(1024*1024*1024)”Size(GB)” from dba_segments where segment_name in(‘TABLE_NAME’);
Prompt “OWNER.TABLE_NAME reorg completed”

–FOR INDEXED TABLES

alter index OWNER.INDEX_NAME rebuild parallel 32 compress;
alter index OWNER.INDEX_NAME noparallel;

Now run the shell script in nohup –

nohup sh scriptname.sh &

In order to check which table/indexes have been compressed – (COMPRESSION COLUMN WILL SHOW ENABLED)

set lines 300 pages 200
col owner for a13
col index_name for a30
col table_name for a40
col tablespace_name for a20
SELECT INDEX_NAME,TABLESPACE_NAME,status,TABLE_NAME,PARTITIONED,COMPRESSION,INI_TRANS,DEGREE,OWNER FROM DBA_INDEXES WHERE TABLE_NAME IN (NAMES) ORDER BY 1;

set lines 300 pages 200
col table_name for a30
col TABLESPACE_NAME for a20
COL OWNER FOR A12
col compression for a11

select table_name,INI_TRANS,owner,tablespace_name,degree,PARTITIONED,row_movement,COMPRESSION from dba_tables where
table_name IN (NAMES) ORDER BY 1;

Below are some suggestions found in oracle support docs regarding compression implementation –