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.
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_TYPWe'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
------------------------------
EXAMPLE2. 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.
Online Move?
Alter table move lob online is impossible, which throws ORA-14808: table does not support ONLINE MOVE 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
------------------------------
USERSWe have moved the LOB.