Square DBA – Oracle DBA Talk

I recently wrote a script to compress certain partitions in a table. Basically any partition that was loaded and analyzed I wanted to move to a new tablespace and compress the data.

The script basically spools out the SQL into a file and then runs that file. Here is the code specifically related to the compression.

select 'alter table '||table_owner||'.'||table_name||' move partition
 '||partition_name||' TABLESPACE DW_CDR_DETAIL_BIG
COMPRESS FOR ALL OPERATIONS update indexes NOLOGGING;'
from dba_tab_partitions
where table_owner='O1DW' and table_name='CDR_DETAIL' and
COMPRESSION = 'DISABLED'
order by NUM_ROWS desc;

Pretty simple.