Alter Table Move LOB to Another Tablespace

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.

1. ALTER TABLE MOVE LOB TABLESPACE
2. ALTER TABLE MOVE LOB PARALLEL
3. ALTER TABLE MOVE LOB ONLINE, Can we?
4. 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.