Square DBA – Oracle DBA Talk

Do you want to move partitions to a new tablespace in Oracle? Here is some helpful SQL:

Find a partitioned table’s default table space:

SELECT def_tablespace_name FROM dba_part_tables
where owner = ‘O1DW’
and table_name = ‘ACME_FAILED_DETAIL’;

Change a partitioned table’s tablespace:

ALTER TABLE O1DW.ACME_FAILED_DETAIL
MODIFY DEFAULT ATTRIBUTES TABLESPACE ACME_FAILED_BIG1;

Move existing partitions to a new tablespace and compress the data:

select ‘ALTER TABLE ‘||table_owner||’.'||table_name
||’ move partition ‘||partition_name
||’ TABLESPACE ACME_FAILED_BIG1 COMPRESS FOR ALL OPERATIONS update indexes NOLOGGING;’
from dba_tab_partitions
where table_owner = ‘O1DW’
and table_name = ‘ACME_FAILED_DETAIL’
and TABLESPACE_NAME <> ‘ACME_FAILED_BIG1′
order by PARTITION_POSITION;