-- 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;
Minimalistic Oracle contains a collection of practical examples from my encounters with Oracle technologies. When relevant, I also write about other technologies, like Linux or PostgreSQL. Many of the posts starts with "how to" since they derive directly from my own personal experience. My goal is to provide simple examples, so that they can be easily adapted to other situations.
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:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment