8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

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...

Back to the Top.