how to move lob segment from one tablespace to another

Here we are presenting ways on how to move lob segment from one tablespace to another

(1) 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';

More queries can be found at

How to find space usage of lob segments in Oracle database

(2) Suppose you know the lob segment name and you want to know which tables it belongs to.

Here is how to find table name for lob segment

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';

(3) Now let’s take a look at various ways to rebuild the lob

Alter table move command

If you just want to move the table without impacting LOB Segments, then use below

SQL>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 the 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-col) 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 the following query,

ALTER TABLE table_name MOVE
TABLESPACE new_tablespace STORAGE(new_storage)
LOB (lob-col) STORE AS
(TABLESPACE new_tablespace STORAGE (new_storage));

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);

(4) Finally we can measure the after size using the 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';

Note

We dont talk about LOBINDEX here as it just points to the chunks that make up the LOB.It is just created to find the chunks for the lob fast and a segment that holds the lob data (chunks).

We have NO control over the lob index, it is an internal structure and goes where LOBSEGMENT goes.
Hence LOBINDEX also moves when you move the LOB segment. So we dont need worry about it

Hope you like this post on how to move lob segment from one tablespace to another. Please do provide the feedback to improve

Reader Interactions

Leave a Reply Cancel reply