How to Move Lob Segment and Lob Indexes to Another Tablespace in Oracle

Hi,

I will explain How to Move Lob Segment to Another Tablespace in Oracle in this post.

 

Move Lob Segment to Another Tablespace

Sometimes you may need to drop any tablespace, so you need to move all objects in this tablespace. Moving lob segments is difficult according to table and index.

 

 

Check any table if it has any lob segment using the following script.

SELECT table_name, column_name, segment_name
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'TABLE_NAME';

 

 

You can find the table name of any lob segment using the following script.

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE a.segment_name = '&logsegment';

 

 

Move Lob Segment to Another Tablespace in Oracle

You can move any lob segment to another tablespace as follows.

ALTER TABLE OWNER.TABLE_NAME MOVE LOB(LOB_COLUMN) STORE AS (TABLESPACE NEW_TABLESPACE_NAME);

 

You can generate the moving scripts for lob segments as follows.

select 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB( '||column_name||') STORE AS (TABLESPACE NEW_TABLESPACE_NAME) parallel 96;' from dba_lobs where tablespace_name='OLD_TABLESPACE_NAME';

 

 

Move Lob Indexes to Another Tablespace in Oracle

You can use the following scripts for Lob segment, lob indexes and lob partitions to move this lob segments to new tablespace.

 

select 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB( '||column_name||') STORE AS (TABLESPACE NEW_TABLESPACE_NAME) parallel 96;' from dba_lobs where tablespace_name='OLD_TABLESPACE_NAME';

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' tablespace NEW_TABLESPACE_NAME;' from dba_ind_partitions where tablespace_name='OLD_TABLESPACE_NAME';


select 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE partition '||Partition_name||' lob('||column_name||')'||' STORE AS (TABLESPACE NEW_TABLESPACE_NAME) ;' from dba_lob_partitions where TABLESPACE_NAME = 'OLD_TABLESPACE_NAME';

 

 

 

You can read the following post to learn how to move tables and Index to new tablespace.

How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle

 4,597 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected]. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Do NOT follow this link or you will be banned from the site!