DBA Hub

📋Steps in this guide1/3

Rename LOB Segments in Oracle Database 23ai/26ai

The article demonstrates how to rename a LOB segment in Oracle 23ai/26ai.

oracle 23configurationintermediate
by OracleDba
15 views
1

Setup

The examples in this article require the following table, which contains a large object column.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
drop table if exists t1 purge;

create table t1 (
  id         number generated always as identity primary key,
  blob_data  blob
);
2

The Problem (MOVE)

We check the current name of the LOB segment for the column. In previous versions of the database to rename the lob segment we had to move it. We can see our lob segment has been renamed as part of the move operation. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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>

alter table t1 move lob(blob_data) store as t1_blob_data_segment online;

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

The Solution (RENAME)

In Oracle database 23ai/26ai we can rename a LOB segment. As expected, the LOB segment has been renamed. 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. For more information see: - Rename LOB Segments - ALTER TABLE BNF Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
alter table t1 rename lob(blob_data) t1_blob_data_segment to t1_blob_data_new_segment;

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>

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

Comments (0)

Please to add comments

No comments yet. Be the first to comment!