Move LOB objects and LOB indexes to different Tablespace

I received a question from my junior DBA to fix a block crorruption issue for LOB indexes in a tablespace which I want to drop. But he unable to find the same in DBA_LOBS table.

Here are some views to find LOB objects and LOB indexes:

-- To find LOB objects from a tablespace:
e.g.,
SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
  FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME = 'PAYROLL'
   AND SEGMENT_TYPE LIKE 'LOB%'
 ORDER BY 1;

Sample out:

SEGMENT_NAME                SEGMENT_TYPE       OWNER TABLESPACE_NAME
------------------------------ ------------------ ---------- -------
SYS_IL0000077613C00006$$       LOBINDEX           PAYROLL    PAYROLL
SYS_IL0000077671C00006$$       LOBINDEX           PAYROLL    PAYROLL
SYS_IL0000078212C00001$$       LOBINDEX           PAYROLL    PAYROLL
SYS_IL0000078458C00006$$       LOBINDEX           PAYROLL    PAYROLL
SYS_LOB0000077613C00006$$      LOBSEGMENT         PAYROLL    PAYROLL
SYS_LOB0000077671C00006$$      LOBSEGMENT         PAYROLL    PAYROLL
SYS_LOB0000078212C00001$$      LOBSEGMENT         PAYROLL    PAYROLL
SYS_LOB0000078458C00006$$      LOBSEGMENT         PAYROLL    PAYROLL

8 rows selected

Note: Segments prefixed with 'SYS_IL....' is log index and segements prefixed with 'SYS_LOB.....' is table.

-- To find Table Name for Log segment
e.g.,
SQL> select owner,table_name,column_name,tablespace_name
  2  from dba_lobs
  3  where segment_name='SYS_LOB0000077613C00006$$';

OWNER      TABLE_NAME                     COLUMN_NAME        TABLESPACE_NAME
---------- ------------------------------ ----------------------------------
PAYROLL    PAY_LOAN                       INSTALLMENT        PAYROLL
SQL>

-- To find Table name for LB Index:
e.g.,
SQL> select owner, table_name, column_name, segment_name, index_name
  from dba_lobs
 where index_name = 'SYS_IL0000077613C00006$$';

OWNER      TABLE_NAME COLUMN_NAME     SEGMENT_NAME             INDEX_NAME
---------- ---------- ---------------  -------------------------
PAYROLL    PAY_LOAN   INSTALLMENT     SYS_LOB0000077613C00006$$      SYS_IL0000077613C00006$$
SQL>

A LOB is made up of a LOB data segment storing LOB data and a LOB index segment used to access LOB data. SYS_LOBxxx is the LOB data segment and SYS_ILxxx is the LOB index segment. As a consequence it is meaningless to try to drop only the LOB index segment (and I don't think it is possible to do so): you can only drop the LOB column to drop data and index segment or maybe try to move the column to be stored in row only if LOB size allows.

How to move lobsegment and  lobindex to a different Tablespace?

when I am executing the following statement in order to move the LOBINDEX, I am getting the errors listed below:

SQL> ALTER INDEX SYS_IL0000265968C00002$$ REBUILD TABLESPACE TEST;
ALTER INDEX SYS_IL0000265968C00002$$ REBUILD TABLESPACE TEST
*
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB


See my workaround:

-- create a table in USERS tablespace

CREATE TABLE TEST_TBL
(
TEST_ID NUMBER NOT NULL,
TEST_NAME CLOB,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
) tablespace USERS;

-- see the tables's tablespace name

SQL> col owner format a5;
SQL> column segment_name format a10;
SQL> col segment_type format a10;
SQL> col tablespace_name format a10;
SQL> select owner,segment_name,segment_type,tablespace_name
  2   from dba_segments where segment_name='TEST_TBL';

OWNER SEGMENT_NA SEGMENT_TY TABLESPACE
----- ---------- ---------- ----------
SYS   TEST_TBL   TABLE      USERS

SQL>

-- See the LOB index

SQL> SELECT index_name, tablespace_name
  2  FROM user_indexes WHERE table_name = 'TEST_TBL';

INDEX_NAME                     TABLESPACE
------------------------------ ----------
SYS_IL0000265968C00002$$       USERS
PK_TEST                        USERS

SQL>

-- Move table to different tablespace;

SQL> ALTER TABLE TEST_TBL MOVE TABLESPACE TEST;

Table altered.

SQL> select owner,segment_name,segment_type,tablespace_name
  2  from dba_segments where segment_name='TEST_TBL';

OWNER SEGMENT_NA SEGMENT_TY TABLESPACE
----- ---------- ---------- ----------
SYS   TEST_TBL   TABLE      TEST

SQL>

SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'TEST_TBL';

INDEX_NAME                     TABLESPACE
------------------------------ ----------
SYS_IL0000265968C00002$$       USERS
PK_TEST                        USERS

SQL>

See the above results, table is moved from 'USERS' tablespace to 'TEST' tablespace but index remain in same USERS tablespace.  This is because LOB data is stored outside of the table.

The below example, TEST_NAME is the CLOB column which we want to move to new tablespace and EXAMPLE is target tablespace. Above command will successfully move LOB segments to the new tablespace. We can verify it by issuing same sql again.


SQL> ALTER TABLE TEST_TBL MOVE LOB(TEST_NAME) STORE AS (TABLESPACE TEST);

Table altered.

SQL> SELECT index_name, tablespace_name FROM user_indexes WHERE table_name = 'TEST_TBL';

INDEX_NAME                     TABLESPACE
------------------------------ ----------
PK_TEST                        USERS
SYS_IL0000265968C00002$$       TEST

Note : "small" LOBs stored inline (ie in the row itself) are not in a seperate LOBSEGMENT at all. That is called STORAGE IN ROW and is the default for LOBs of 4000bytes or less.