Sample Automated Table Compression procedures:

1. Procedure to compress partition table:

CREATE OR REPLACE procedure compress_tab_par
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

DBMS_OUTPUT.put_line('Table Partitions compression for ' || own_name || '.' || tab_name || ' started...');
DBMS_OUTPUT.put_line(' ');

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Make Indexes partitions Unusable */
begin
for x1 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name
and index_type = 'BITMAP')
and status = 'USABLE')

loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY PARTITION '||x1.partition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index partitions Unusable COMPLETED.');


/* Step2 - Alter table Partitions to compress */
begin
for x2 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x2.table_name||' MODIFY PARTITION '||x2.partition_name||' COMPRESS';
end loop;
end;

DBMS_OUTPUT.put_line(' Modifying Table partitions to compress COMPLETED.');



/* Step3 - Table partitions compress */
begin
for x3 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE PARTITION '||x3.partition_name||' COMPRESS NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Table partitions compress COMPLETED.');


/* Step4 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name)
and status = 'UNUSABLE')
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD PARTITION '||x4.partition_name||' PARALLEL 8 NOLOGGING';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');


/* Step5 - Analyze the table and indexes */
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 8);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');


select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');
DBMS_OUTPUT.put_line(' NOTE: don''t forget to resize the datafiles !!!!!!!!!!');
DBMS_OUTPUT.put_line('Table Partitions compression for ' || own_name || '.' || tab_name || ' completed.');

EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end compress_tab_par;
/


2. Procedure to compress Subpartition table:

CREATE OR REPLACE procedure compress_tab_subpar
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Making Indexes partitions Unusable */
begin
for x1 in ( select index_name, subpartition_name
from user_ind_subpartitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name))
loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY SUBPARTITION '||x1.subpartition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index subpartitions Unusable COMPLETED.');


/* Step2 - Alter table to compress */
execute immediate 'ALTER TABLE '||tab_name||' COMPRESS';

DBMS_OUTPUT.put_line(' Altering the table to compress has been COMPLETED.');


/* Step3 - Modifying table partitions compress */
begin
for x2 in ( select table_name, partition_name
from user_tab_subpartitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x2.table_name||' MODIFY PARTITION '||x2.partition_name||' COMPRESS NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Modifying table partitions to compress COMPLETED.');


/* Step4 - Table subpartitions compress */
begin
for x3 in ( select table_name, subpartition_name
from user_tab_subpartitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE SUBPARTITION '||x3.subpartition_name||' NOPARALLEL';
end loop;
end;

DBMS_OUTPUT.put_line(' Table subpartitions compress COMPLETED.');


/* Step5 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, subpartition_name
from user_ind_subpartitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name))
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD SUBPARTITION '||x4.subpartition_name||' PARALLEL 8';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');


/* Step6 - Analyze the table and indexes*/
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 8);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');


select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');
DBMS_OUTPUT.put_line(' Note: Hai don''t forget to resize the datafiles !!!!!!!!!!');


EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end compress_tab_subpar;
/


3. Procedure to Back out compressed partition table:

CREATE OR REPLACE procedure EDW.nocompress_tab_par
(tab_name varchar2, own_name varchar2 DEFAULT 'EDW')
is
init_size number;
final_size number;

begin

select sum(bytes/1024/1024) i_size_MB
into init_size
from user_extents
where segment_name = tab_name;

/* Step1 - Alter table Partitions to NOcompress */
begin
for x3 in ( select table_name, partition_name
from user_tab_partitions
where table_name = tab_name)
loop
execute immediate 'ALTER TABLE '||x3.table_name||' MOVE PARTITION '||x3.partition_name||' NOCOMPRESS';
end loop;
end;

DBMS_OUTPUT.put_line(' Table partitions NOcompress COMPLETED.');


/* Step2 - Make Indexes partitions Unusable */
begin
for x1 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name
and index_type = 'BITMAP')
and status = 'USABLE')

loop
execute immediate 'ALTER INDEX '||x1.index_name||' MODIFY PARTITION '||x1.partition_name||' UNUSABLE';
end loop;
end;

DBMS_OUTPUT.put_line(' Making Index partitions Unusable COMPLETED.');


/* Step3 - Alter table to NOcompress */
execute immediate 'ALTER TABLE '||tab_name||' NOCOMPRESS';

DBMS_OUTPUT.put_line(' Altering the table to NOcompress has been COMPLETED.');


/* Step4 - Rebuild the Indexes partitions */
begin
for x4 in ( select index_name, partition_name
from user_ind_partitions
where index_name in (select index_name
from user_indexes
where table_name = tab_name)
and status = 'UNUSABLE')
loop
execute immediate 'ALTER INDEX '||x4.index_name||' REBUILD PARTITION '||x4.partition_name||' PARALLEL 8 NOLOGGING';
end loop;
end;

DBMS_OUTPUT.put_line(' Rebuild the Indexes partitions COMPLETED.');


/* Step5 - Analyze the table and indexes */
dbms_stats.gather_table_stats(ownname=> own_name, tabname=> tab_name, estimate_percent => 0.001, method_opt=> 'FOR ALL COLUMNS SIZE 1', cascade=> TRUE, degree=> 4);

DBMS_OUTPUT.put_line('DBMS stats COMPLETED.');


select sum(bytes/1024/1024) f_size_MB
into final_size
from user_extents
where segment_name = tab_name;

DBMS_OUTPUT.put_line(' '||tab_name||' table initial Size in MB is: '||init_size||' MB');
DBMS_OUTPUT.put_line(' '||tab_name||' table final Size in MB is: '||final_size||' MB');

EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(-20101,sqlerrm);
WHEN OTHERS THEN
raise_application_error(-20102,sqlerrm);

end nocompress_tab_par;
/