Moving the LOB from one Tablespace to another Tablespace

As a part of changing the Bigfile Tablespace to normal Tablespace, :

Post activities considerations:

Check the size of LOB in the Tablespace which we are going to move.

check the count of objects using dba_lobs, dba_indexes, dba_tables with corresponding Tablespace.

Create the necessary tablespace and datafiles with sufficient space.

Change the default tablespace if needed to the new one.

Give quota to the user as unlimited for the created tablespace.

use the below script and generate the .sql file for the movement.

Moving LOB:

SQL> spool /home/oracle/movelob.sql

SET HEADING OFF

SET pagesize 200

SET linesize 200

select 'ALTER TABLE <owner>.'||TABLE_NAME||' MOVE LOB('||COLUMN_NAME||') STORE AS (TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_lobs where TABLESPACE_NAME='<Tablespace_name>';

Note: The above query will include all the LOB,LOBSEGMENT,LOBINDEXES

Moving Table:

SQL> spool /home/oracle/moveTables.sql

SET HEADING OFF

SET PAGESIZE 200

SET LINESIZE 200

select ' ALTER TABLE <owner>.'||TABLE_NAME||' MOVE TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_tables where owner='<owner name>';

Moving Index:

SQL> spool /home/oracle/moveIndex.sql

SET HEADING OFF

SET long 9999

SET linesize 200

select 'alter index <owner>.'||index_name||' from dba_indexes 'rebuild tablespace <Tablespace_name>)   online parallel 3 nologging;' where owner='<owner>.';

Run the queries and generate the sql scripts 

Make sure that there is no unwanted space in the sql script.

Move the Huge size LOB separately and the corresponding tables and indexes to make sure that huge volumes moves first.

Next move the Remaining LOB then Tables and Index correspondingly using the scripts.

Then check with the old tablespace if there are any objects left, if so move those to the new tablespace

Check the counts with previously taken counts if every this is fine ,then if needed drop the tablespace with the datafiles.

Have Your LOB in new tablespace :-)