How Oracle Alter Table Move LOB Column

Alter Table Move LOB

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 without side effect.

If the situation sustains, we would consider to move table, 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 by ourselves.

Secondly, data like BLOB or CLOB is mostly cold data, they should be moved out of tablespaces based on NVMe storage. So a better practice I would recommend to you is that moving LOB first, then TABLE.

First of all, we connect to the database.

SQL> conn system/welcome1@orcl
Connected.

1. Check Current Tablespace

We should 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. Let's check the current tablespace of the column.

SQL> select tablespace_name from dba_lobs where owner = 'PM' and table_name = 'PRINT_MEDIA' and column_name = 'AD_PHOTO';

TABLESPACE_NAME
------------------------------
EXAMPLE

2. Move LOB

To move a large object, we combine move_table_clause and LOB_storage_clause of ALTER TABLE syntax.

Normal Move

SQL> alter table PM.PRINT_MEDIA move lob(AD_PHOTO) store as (tablespace USERS);

Table altered.

Parallel Move

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, then the parallelism applies only to this move, not to the table. This is a good design.

3. Check the Result

SQL> select tablespace_name from dba_lobs where owner = 'PM' and table_name = 'PRINT_MEDIA' and column_name = 'AD_PHOTO';

TABLESPACE_NAME
------------------------------
USERS

We have moved the LOB.