Move a LOBSEGMENT (Large Object Segment) to another tablespace in an Oracle database

To move a LOBSEGMENT (Large Object Segment) to another tablespace in an Oracle database, you can follow these steps:

 

Get lob Segment Details:

SELECT table_name, column_name, segment_name, tablespace_name

FROM dba_lobs

WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

 

Move to other tablespace:

ALTER TABLE your_schema.your_table MOVE LOB(column_name) STORE AS (TABLESPACE new_tablespace);

Replace 'your_schema' with the schema name, 'your_table' with the table name, 'column_name' with the name of the LOB column, and 'new_tablespace' with the name of the new tablespace where you want to move the LOB segment.

After the LOB segment has been moved, you can verify

SELECT table_name, column_name, segment_name, tablespace_name  FROM dba_lobs  WHERE owner = 'YOUR_SCHEMA' AND table_name = 'YOUR_TABLE';

Ensure that the tablespace_name column shows the name of the new tablespace where the LOB segment resides.

 

Note: Moving a LOB segment is a resource-intensive operation, and it should be performed during a maintenance window or a period of low database activity to minimize impact. Additionally, take appropriate backups before making any changes to critical database objects.