You Asked

We start getting alerts about a tablespace users that it was getting full, tablespace users contains 3 datafiles of 32GB each, but we found out that in one of the tables in tablespace users it has a Lob(67 GB), so we decided to moved that Lob to have it owns tablespace. Now we want to skink users tablespace and reduced to one datafile instead of 3. how we can do that?

We are trying to moved all db objects in those datafiles, to a new tablespace, but the problem seems like when we try to move the above table that contains the lob column doing the
alter table APEX_UNISOURCE.UOS_DOCUMENTS move ONLINE TABLESPACE USERS_NEW;
but seems like is moving also the lob column to the new tablespace.
How we can move that table excluding lob column?

and Chris said...

I'm not sure what's happening here. Moving a table should leave the LOBs where they are. This is the documented behaviour and what I observe on 19.28:

create tablespace lob_data;
create tablespace table_data;

create table t ( c1 int, c2 clob )
lob ( c2 ) store as securefile ( tablespace lob_data )
tablespace table_data;

insert into t values ( 1, 'test' );
commit;


select segment_name, tablespace_name 
from   user_segments
where  segment_name = 'T'
or     segment_name in (
  select segment_name from user_lobs where table_name = 'T'
);

SEGMENT_NAME                 TABLESPACE_NAME    
SYS_LOB0000095289C00002$$    LOB_DATA           
T                            TABLE_DATA  

alter table t move tablespace users;

select segment_name, tablespace_name 
from   user_segments
where  segment_name = 'T'
or     segment_name in (
  select segment_name from user_lobs where table_name = 'T'
);

SEGMENT_NAME                 TABLESPACE_NAME    
SYS_LOB0000095296C00002$$    LOB_DATA           
T                            USERS

If this is working differently for you, please share a test case demonstrating the LOB moving at the same time as the table.

Alternatively:

The MOVE clause does allow you to specify LOB attributes at the same time. So you can specify the LOB's current tablespace to ensure it stays where you want it:

alter table t move 
tablespace table_data
lob ( c2 ) store as securefile ( tablespace lob_data );

select segment_name, tablespace_name 
from   user_segments
where  segment_name = 'T'
or     segment_name in (
  select segment_name from user_lobs where table_name = 'T'
);

SEGMENT_NAME                 TABLESPACE_NAME    
SYS_LOB0000095296C00002$$    LOB_DATA           
T                            TABLE_DATA

Rating

(1 rating)

Comments

Carlos, December 12, 2025 - 4:25 pm UTC

Hi Chris,
thank you for your response, I just tried again, but same results:

alter table APEX_UNISOURCE.UOS_DOCUMENTS move ONLINE TABLESPACE USERS_NEW lob (DOCUMENT) store as securefile (tablespace APEX_DOC);

But seems like is moving again the lob(document) on Apex_doc tablespace, the command failed, because the lob tablespace got full, no sure why still moving lob.

Error starting at line : 1 in command -
alter table APEX_UNISOURCE.UOS_DOCUMENTS move ONLINE TABLESPACE USERS_NEW lob (DOCUMENT) store as securefile (tablespace APEX_DOC)
Error report -
ORA-01652: unable to extend temp segment by 1024 in tablespace APEX_DOC
01652. 00000 - "unable to extend temp segment by %s in tablespace %s"
*Cause: Failed to allocate an extent of the required number of blocks for
a temporary segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

CLOB/BLOB/etc

How to move a table in oracle database 19c excluding Lob column

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

CLOB/BLOB/etc