Rename LOB – Oracle database 23c New Feature

In Oracle database 23c we can rename LOB segment name by using below command without moving within or other tablespaces.

alter table "TABLE_NAME" rename lob("LOB_COLUMN_NAME") "LOB_SEGMENT_NAME" to "LOB_RENAME_NAME";

LOB_COLUMN_NAME, LOB_SEGMENT_NAME details -

select TABLE_NAME, COLUMN_NAME, SEGMENT_NAME from dba_lobs where table_name='TAB_NAME';

Example

Create table with LOB column and rename the LOB Segment in 23c.

Note – Column “ID” is Oracle identity column (Introduced in 12c) similar to “AUTO_INCREMENT” column in MySQL or “IDENTITY” column in SQL Server. Column “LOB_STREAM” will be blob.

SQL> drop table if exists TEST_LOB purge;
Table dropped.

SQL> create table TEST_LOB (
ID number generated always as identity primary key using index tablespace INDEX01,
LOB_STREAM  blob
);

Table created.

SQL> set linesize 999;
col TABLE_NAME format A15;
col COLUMN_NAME format A15;
col SEGMENT_NAME format A40;
SQL> select TABLE_NAME, COLUMN_NAME, SEGMENT_NAME from dba_lobs where table_name='TEST_LOB'  and owner='SCOTT';

TABLE_NAME      COLUMN_NAME     SEGMENT_NAME
--------------- --------------- -------------------------
TEST_LOB        LOB_STREAM      SYS_LOB0000078978C00002$$

SQL> alter table TEST_LOB rename lob("LOB_STREAM") "SYS_LOB0000078978C00002$$" to "TEST_LOB_NEW_SEGMENT";
Table altered.

SQL> select TABLE_NAME, COLUMN_NAME, SEGMENT_NAME from dba_lobs where table_name='TEST_LOB'  and owner='SCOTT';
TABLE_NAME      COLUMN_NAME     SEGMENT_NAME
--------------- --------------- --------------------
TEST_LOB        LOB_STREAM      TEST_LOB_NEW_SEGMENT
SQL>

How to Rename LOB segments in 21c or earlier versions?

In 21c or previous version we need to perform “MOVE” operation to rename lob segments.

SQL> drop table TEST_LOB purge;
Table dropped.

SQL> create table TEST_LOB (
  id          number generated always as identity primary key using index tablespace INDEX01,
  LOB_STREAM  blob
);
Table created.

SQL> set linesize 999;
col TABLE_NAME format A15;
col COLUMN_NAME format A15;
col SEGMENT_NAME format A40;
SQL> select TABLE_NAME, COLUMN_NAME, SEGMENT_NAME from dba_lobs where table_name='TEST_LOB';
TABLE_NAME      COLUMN_NAME     SEGMENT_NAME
--------------- --------------- ----------------------------------------
TEST_LOB        LOB_STREAM      SYS_LOB0000075817C00002$$

SQL> alter table TEST_LOB move lob(LOB_STREAM) store as TEST_LOB_NEW_SEGMENT online;
Table altered.

SQL> select TABLE_NAME, COLUMN_NAME, SEGMENT_NAME from dba_lobs where table_name='TEST_LOB';
TABLE_NAME      COLUMN_NAME     SEGMENT_NAME
--------------- --------------- ----------------------------------------
TEST_LOB        LOB_STREAM      TEST_LOB_NEW_SEGMENT

SQL>

Leave a Comment