Skip to content

Alter Table Move LOB to Another Tablespace

  • by

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 more efficiently using storage. So a better practice I would recommend to you is that you should move 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 makes parallelism apply only to this move, not to the table.

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.

Please note that, alter table move lob online is impossible, which throws ORA-14808: table does not support ONLINE MOVE TABLE.

Leave a Reply

Your email address will not be published.