Tuesday, October 22, 2013

How to generate scripts to move partitions to a new tablespace while updating the indexes

Probably many ways of doing this, but here is how I generated scripts to move table partitions from one tablespace to another:
-- Find table partitions with GLOBAL indexes:
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS UPDATE GLOBAL INDEXES;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name IN (
        SELECT  distinct table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
        AND     locality = 'GLOBAL'
)
ORDER BY table_name DESC;

-- Find table partitions with LOCAL indexes:
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS UPDATE INDEXES;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name IN (
        SELECT  distinct table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
        AND     locality = 'LOCAL'
)
ORDER BY table_name DESC;


-- Find table partitions with no index at all
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name NOT IN (
        SELECT  table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
)
ORDER BY table_name DESC;

No comments:

Post a Comment