Move LOB Segment
Whenever tablespace is crowded, the first thought in my mind is to move INDEX to another tablespace, because it's very flexible to rebuild an index in another place with very little side effect.
If the situation sustains, we would consider to move table to another tablespace, but be careful, the large object (LOB) including Binary Large Object (BLOB) and Character Large Object (CLOB) in that table will not be moved, we have to move them separately.
In general, data like BLOB or CLOB is mostly cold data. They should be moved out of tablespaces based on SSD or NVMe disks for using storage more efficiently. So a better practice I would recommend to you is that you should move LOB first, then TABLE.
Please note that, if you have only the name of LOB, you should find the owner table of the LOB segment first.
We check the table definition first.
SQL> desc pm.print_media;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT_ID NOT NULL NUMBER(6)
AD_ID NOT NULL NUMBER(6)
AD_COMPOSITE BLOB
AD_SOURCETEXT CLOB
AD_FINALTEXT CLOB
AD_FLTEXTN NCLOB
AD_TEXTDOCS_NTAB PM.TEXTDOC_TAB
AD_PHOTO BLOB
AD_GRAPHIC BINARY FILE LOB
AD_HEADER PM.ADHEADER_TYP
We'd like to move the column AD_PHOTO, a BLOB column to another tablespace.
In this post, we introduce 4 levels of moving LOB in depth.
- ALTER TABLE MOVE LOB TABLESPACE
- ALTER TABLE MOVE LOB PARALLEL
- ALTER TABLE MOVE LOB ONLINE, Can we?
- ALTER TABLE MOVE LOB UPDATE INDEXES
ALTER TABLE MOVE LOB TABLESPACE
To move a large object, we combine move_table_clause and LOB_storage_clause of ALTER TABLE syntax.
SQL> alter table PM.PRINT_MEDIA move lob(AD_PHOTO) store as (tablespace USERS);
Table altered.
ALTER TABLE MOVE LOB PARALLEL
We use some degree of parallelism (DOP) to move the segment.
SQL> alter table PM.PRINT_MEDIA move lob(AD_PHOTO) store as (tablespace USERS) parallel 16;
Table altered.
Not like moving an index parallelly, specifying the parallel_clause in conjunction with the move_table_clause makes parallelism apply only to this move, not to the table.
ALTER TABLE MOVE LOB ONLINE, Can we?
"Alter table move lob online" is impossible, which throws ORA-14808: table does not support ONLINE MOVE TABLE.
That is to say, the operation acquires an exclusive lock of the table, all queued transactions have to wait for the end of it. As we have mentioned above, we can use PARALLEL to shorten the waiting time.
ALTER TABLE MOVE LOB UPDATE INDEXES
Moving any LOB segment makes all dependent indexes of the table UNUSABLE, although LOB has nothing to do with ROWID of the table.
Since release 12.2, to prevent dependent indexes from being UNUSABLE, we can specify UPDATE INDEXES to rebuild them automatically after the LOB segment has been moved.
SQL> alter table PM.PRINT_MEDIA move lob(AD_PHOTO) store as (tablespace USERS) parallel 16 update indexes;
Table altered.
For 12.1 and earlier releases, you have to manually rebuild all dependent indexes to make them valid again.