8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Rename LOB Segments in Oracle Database 23c
The article demonstrates how to rename a LOB segment in Oracle 23c.
Setup
The examples in this article require the following table, which contains a large object column.
drop table if exists t1 purge; create table t1 ( id number generated always as identity primary key, blob_data blob );
The Problem (MOVE)
We check the current name of the LOB segment for the BLOB_DATA
column.
column table_name format a15 column column_name format a15 column segment_name format a30 column tablespace_name format a15 select table_name, column_name, segment_name, tablespace_name from user_lobs where table_name = 'T1'; TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME --------------- --------------- ------------------------------ --------------- T1 BLOB_DATA SYS_LOB0000089326C00002$$ USERS SQL>
In previous versions of the database to rename the lob segment we had to move it.
alter table t1 move lob(blob_data) store as t1_blob_data_segment online;
We can see our lob segment has been renamed as part of the move operation.
select table_name, column_name, segment_name, tablespace_name from user_lobs where table_name = 'T1'; TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME --------------- --------------- ------------------------------ --------------- T1 BLOB_DATA T1_BLOB_DATA_SEGMENT USERS SQL>
That worked fine, but for a large LOB segment it could represent a lot of work. The database would have to build a new version of the LOB segment to achieve this.
The Solution (RENAME)
In Oracle database 23c we can rename a LOB segment.
alter table t1 rename lob(blob_data) t1_blob_data_segment to t1_blob_data_new_segment;
As expected, the LOB segment has been renamed.
select table_name, column_name, segment_name, tablespace_name from user_lobs where table_name = 'T1'; TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME --------------- --------------- ------------------------------ --------------- T1 BLOB_DATA T1_BLOB_DATA_NEW_SEGMENT USERS SQL>
Since this is a meta data change, it doesn't require the whole LOB segment to be rebuilt, making renaming a LOB segment simple for large LOBs.
The rename operation is also possible for partitions and subpartitions.
-- Table alter table table_name rename lob(column_name) old_segment_name to new_segment_nane; -- Partition alter table table_name rename lob(column_name) partition old_segment_name to new_segment_nane; -- Subpartition alter table table_name rename lob(column_name) subpartition old_segment_name to new_segment_nane;
For more information see:
Hope this helps. Regards Tim...