How to move LOB segment to another tablespace



Here we are presenting ways to move/rebuild LOB segment
First take the initial size of the LOB segment
SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = ‘TABLE_NAME’;
Now lets take a look at various ways to rebuild the lob
1) Alter table move command
If you just want to move the table without impacting LOB Segments,then use belowSQL>ALTER TABLE table_name MOVE;

Or
SQL>ALTER TABLE table_name MOVE TABLESPACE tablespace_name;

With this statement it does not affect any of the lob segments associated with the lob columns in the table.

To move log segments to different tablespace use below command.But it is important to mention that it will also rebuild the table in its own tablespace

ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);

Along with the log segment you can also move the table as well as storage attribute of table and log by following query,

ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lobcol) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));

2) Shrink command

a) Shrinking both the table and lob segments

ALTER TABLE table_name ENABLE ROW MOVEMENT;

Table altered.

ALTER TABLE table_name SHRINK SPACE CASCADE;

Table altered.

b) Shrink the lob segment only

ALTER TABLE table_name ENABLE ROW MOVEMENT;

Table altered.

ALTER TABLE <table_name> MODIFY LOB (<lob_column>) (SHRINK SPACE);

 

Finally we can measure the after size using below query and get the spaced freed value as a result of this rebuild

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

Related Articles

How to find how much space is actually used by the lobsegments

Information on FND_LOBS

How to rebuild the table in oracle

How to alter table add column oracle

What you need to know about oracle create tablespace statement




Leave a Reply