How to migrate table, index and LOB column to another tablespace
Recently we had to migrate multiple tables, indexes and lob columns from one tablespace to another. We have prepared scripts to generate necessary ALTER statements. First script is for tables:
|
1 2 3 4 5 6 7 |
/* Generate script to alter table from one tablespace to another */ SELECT 'ALTER TABLE ' || OWNER || '.' || SEGMENT_NAME || ' MOVE TABLESPACE <destination_tablespace>;' FROM dba_segments WHERE TABLESPACE_NAME = '<original_tablespace>' AND owner = '<owner>' AND SEGMENT_TYPE = 'TABLE' ORDER BY bytes DESC; |
If <destination_tablespace> is replaced with ‘NEW_TABLESPACE’, <original_tablespace> with ‘OLD_TABLESPACE’ and <owner> is ‘APP’ then executing this script will generate:
|
1 2 3 |
ALTER TABLE APP.TABLE_1 MOVE TABLESPACE NEW_TABLESPACE; ALTER TABLE APP.TABLE_2 MOVE TABLESPACE NEW_TABLESPACE; ... |
In another word this […]