Friday 26 May 2017

Move LOB column of a partition Table to a new Tablespace Oracle || dba_lob_partitions

To move LOB column of a partition table to a new Tablespace in the same Database use bellow approach.


alter table Schema_name.Table_name move partition Partition_ name lob(Column_name) store as (tablespace Tablespace_name); 


Replace red marked names with Object names in your ENV. 

Schema_name = Schema of the table of which you want to move the Lob column to new Tablespace.
Table_Name = Table name of which you want to move the Lob column to new Tablespace.
Partition_name= Partition name of the Table if you just want to move column of a specific partition.
Tablespace_name= Name of the table space where you want to move the Lob column.


To check the tablespace name after column move user below.

select TABLE_NAME,COLUMN_NAME,PARTITION_NAME,LOB_PARTITION_NAME,TABLESPACE_NAME from  dba_lob_partitions where  table_name='TABLE_NAME';