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:

/* 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:

ALTER TABLE APP.TABLE_1 MOVE TABLESPACE NEW_TABLESPACE;
ALTER TABLE APP.TABLE_2 MOVE TABLESPACE NEW_TABLESPACE;
...

In another word this script is looking for any table in ‘OLD_TABLESPACE’ where owner is ‘APP’ and output are ALTER TABLEs which move these tables to ‘NEW_TABLESPACE’.

Second script is for indexes:

/* Generate script to rebuild index in another tablespace */
SELECT 'ALTER INDEX ' || OWNER || '.' || SEGMENT_NAME || ' REBUILD TABLESPACE <destination_tablespace>;' 
FROM dba_segments 
WHERE TABLESPACE_NAME = '<original_tablespace>' 
  AND owner = '<owner>' 
  AND SEGMENT_TYPE = 'INDEX' 
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:

ALTER INDEX APP.SYS_C0010000 REBUILD TABLESPACE NEW_TABLESPACE;
ALTER INDEX APP.SYS_C0010001 REBUILD TABLESPACE NEW_TABLESPACE;
…

Similarly, to the previous script, this one looks for any indexes in ‘OLD_TABLESPACE’ where owner is ‘APP’ and output are ALTER INDEXes to rebuild these indexes in ‘NEW_TABLESPACE’.

Third and the last script is for migrating LOB columns:

At the beginning a little bit of theory. LOB (Large OBject) is data structure which holds an unstructured large data such as graphic images, texts, videos and so on. A LOB is made of LOB data segment (LOBSEGMENT) for storing LOB data and a LOB index (LOBINDEX) used to access LOB data. By default, LOB data segment name starts with SYS_LOBxxxx and index starts with SYS_ILxxxx. So, when we create a table with a LOB column in it, Oracle implicitly creates a LOB data segment and a LOB index for that LOB column in the same tablespace. LOB is then simply a pointer to an index. Index points to the chunks of data that make up the LOB. LOB index and LOB segment are bound together so you can’t move one without other.

Enough of theory. Above scripts will move tables to new tablespace but won’t move the LOB columns. This is because LOB data are by default stored outside of the table. Here is the last script for LOBs:

/* Generate script to alter LOB from one tablespace to another */
SELECT 'ALTER TABLE <owner>.' || TABLE_NAME || ' MOVE LOB (' || COLUMN_NAME || ') STORE AS (TABLESPACE <destination_tablespace>);' 
FROM dba_segments db_seg
JOIN dba_lobs db_lob ON db_seg.segment_name = db_lob.segment_name
WHERE db_seg.TABLESPACE_NAME = '<original_tablespace>' 
  AND db_seg.owner='<owner>' 
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:

ALTER TABLE APP.TABLE1 MOVE LOB (CONTENT) STORE AS (TABLESPACE NEW_TABLESPACE);
ALTER TABLE APP.TABLE1 MOVE LOB (HEADER_IMG) STORE AS (TABLESPACE NEW_TABLESPACE);
...

This script is looking for any LOB in ‘OLD_TABLESPACE’ where owner is ‘APP’. You can see that this time we need ‘COLUMN_NAME’ so we must join dba_lobs table.

Rebuilding Indexes

Sometimes during the migration, you can encounter so called “ORA-01502: index or partition of such index is in usable state”. This mean that “an attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation“. The relevant index is then needing to be rebuilt. Specifically, from Oracle documentation: “After you move a LOB column any existing table indexes must be rebuilt.“ A very important thing to keep in mind.

Here is the script that will help you

/* Generate script to rebuild indexes in UNUSABLE state */
SELECT 'ALTER INDEX ' || index_name || ' REBUILD;' 
FROM user_indexes 
WHERE STATUS = 'UNUSABLE';

This script returns ALTER INDEXes to all user-specific indexes that are in UNUSABLE state, and you can correct them with the generated ALTERs:

ALTER INDEX SYS_C0010000 REBUILD;
ALTER INDEX SYS_C0010001 REBUILD;
...

This applies to column indexes not to LOB indexes because LOB index cannot be renamed, rebuilt, or modified. If you ever try that you’ll get an “ORA-02327: Cannot Create Index on Expression with Datatype String”.

Good luck with your migrations.