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:

If <destination_tablespace> is replaced with ‘NEW_TABLESPACE’, <original_tablespace> with ‘OLD_TABLESPACE’ and <owner> is ‘APP’ then executing this script will generate:

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:

If <destination_tablespace> is replaced with ‘NEW_TABLESPACE’, <original_tablespace> with ‘OLD_TABLESPACE’ and <owner> is ‘APP’ then executing this script will generate:

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:

If <destination_tablespace> is replaced with ‘NEW_TABLESPACE’, <original_tablespace> with ‘OLD_TABLESPACE’ and <owner> is ‘APP’ then executing this script will generate:

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

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

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.

One thought on “How to migrate table, index and LOB column to another tablespace

Leave a Reply to ballout Cancel reply

Your email address will not be published.