Rename LOB Segments, partitions, sub partitions in Oracle 23ai

From Oracle 23ai, you can rename LOB (Large Object) segments, partitions and sub partitions using “ALTER TABLE RENAME LOG” statement. In previous versions of the database to rename LOB segment you need to move it using “ALTER TABLE MOVE” statement.

When renaming a segment make sure the segment is unique within the database, in case of any conflicts you will receive ORA-64233 or ORA-63223 error messages. To make sure segment available in the database you can query ALL_LOBS, ALL_PARTITIONS, ALL_LOG_SUBPARTITIONS.

You can use below syntax/example to rename LOB segment

ALTER TABLE <TABLE_NAME> RENAME LOB (<column_name>) <segment_name> to <new_segment_name>;

SQL> SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME FROM USER_LOBS WHERE TABLE_NAME = 'LOB_TAB1’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- ----------------------- -------------------------
LOB_TAB1        LOB_COL1            LOB_SEG1

SQL> ALTER TABLE lob_tab1 RENAME LOB (lob_col1) log_seg1 TO log_seg1_new;

SQL> SELECT TABLE_NAME, COLUMN_NAME, SEGMENT_NAME FROM USER_LOBS WHERE TABLE_NAME = 'LOB_TAB1’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME
-------------------- ----------------------- --------------------
LOB_TAB1        LOB_COL1            LOB_SEG1_NEW


You can also rename partition, sub partition segments using below examples

For Partition,
ALTER TABLE lob_tab2 RENAME LOB(lob_col2) PARTITION lob_seg2 TO lob_seg2_new;

For Sub partition,
ALTER TABLE lob_tab3 RENAME LOB(LOB_COL3) SUBPARTITION lob_seg3 TO lob_seg3_new;

The new capabilities simplify the management of LOBs, partitions by reducing the overhead in the Oracle Database.

Thanks & Regards,

https://oracleracexpert.com