压缩表和索引的存储过程

在数据量非常大的情况下，可以将比较大的表和索引压缩，以节省空间，下面的2个过程分别对表和索引（分区表和分区索引也可以）进行压缩，另外在表压缩过程中默认将表上的索引也压缩了。
可以写到定时任务，也可以手工执行。
如果需要将表和索引的并行度改回为1，可以适当修改脚本
[code]CREATE OR REPLACE PROCEDURE "SYSTEM"."TABLE_COMPRESS" (p_owner varchar2,
p_table_name varchar2) is
v_owner varchar2(20) := p_owner;
v_table_name varchar2(50) := p_table_name;

v_table_partition varchar2(3);

begin
select partitioned
into v_table_partition
from dba_tables
where table_name = v_table_name
and owner = v_owner;

if (v_table_partition = 'NO') then

execute immediate 'alter table ' || v_owner || '.' || v_table_name ||
' move compress parallel 8 nologging';
commit;

else

declare
cursor c_table_partition is
select table_owner, table_name, partition_name
from dba_tab_partitions
where table_owner = v_owner
and table_name = v_table_name;
begin
for v_table_partition in c_table_partition loop
execute immediate 'alter table ' || v_owner || '.' || v_table_name ||
' move PARTITION ' ||
v_table_partition.partition_name ||
' compress parallel 8 nologging';
commit;
end loop;
end;

end if;

declare
cursor c_index is
select index_name
from dba_indexes
where owner = v_owner
and table_name = v_table_name;
begin
for v_index in c_index loop
system.index_compress(v_owner, v_index.index_name);
end loop;
end;

end table_compress;[/code]

[code]CREATE OR REPLACE PROCEDURE "SYSTEM"."INDEX_COMPRESS" (p_owner varchar2,
p_index_name varchar2) is
v_owner varchar2(20) := p_owner;
v_index_name varchar2(50) := p_index_name;

v_index_partition varchar2(3);
v_index_uniqueness varchar2(9);
v_index_column_count number;

begin
select partitioned
into v_index_partition
from dba_indexes
where owner = v_owner
and index_name = v_index_name;

select uniqueness
into v_index_uniqueness
from dba_indexes
where owner = v_owner
and index_name = v_index_name;

select count(*)
into v_index_column_count
from dba_ind_columns
where index_owner = v_owner
and index_name = v_index_name;

if (v_index_partition = 'NO') then
if (v_index_uniqueness = 'UNIQUE') then
if (v_index_column_count = 1) then
execute immediate 'alter index ' || v_owner || '.' || v_index_name ||
' rebuild parallel 8 nologging';
commit;
else
execute immediate 'alter index ' || v_owner || '.' || v_index_name ||
' rebuild compress parallel 8 nologging';
commit;
end if;
else
execute immediate 'alter index ' || v_owner || '.' || v_index_name ||
' rebuild compress parallel 8 nologging';
commit;
end if;

else

declare
cursor c_index_partition is
select index_owner, index_name, partition_name
from dba_ind_partitions
where index_owner = v_owner
and index_name = v_index_name;
begin
for v_index_partition in c_index_partition loop
execute immediate 'alter index ' || v_owner || '.' || v_index_name ||
' rebuild PARTITION ' ||
v_index_partition.partition_name ||
' parallel 8 nologging ';
commit;
end loop;
end;

end if;

end index_compress;[/code]