About oracle partition tables with lob objects and moving tablespaces

The customer has a tablespace with a lob object and wants to move the tablespace. However, after moving, the tablespace of the lob object found in dba_lobs is still in the original place.

Create table SCES1INPUTS
(
CODREQUEST VARCHAR2 (9 BYTE) not null,
LOBS1INPUT clob not null,
CODLAYOUT VARCHAR2 (20 BYTE) not null,
Datinsertion date default sysdate not null,
CODINSERTIONUSER VARCHAR2 (10 BYTE) not null,
CODINSERTIONFUNCTION VARCHAR2 (5 BYTE) not null,
Dathistory date default sysdate not null,
LOBS1INPUT_GZ BLOB
)
LOB (LOBS1INPUT) store as lobw.sces1inputs
LOB (LOBS1INPUT_GZ) store as LOB2_SCES1INPUTS
Partition by range (DATINSERTION)
(
PARTITION "SCES1INPUTS_200508" values less than (to_date ('20170101', 'ddmmyyyy ')),
PARTITION "SCES1INPUTS_200509" values less than (to_date ('123', 'ddmmyyyy ')),
PARTITION "SCES1INPUTS_200510" values less than (to_date ('20170101', 'ddmmyyyy ')),
PARTITION "SCES1INPUTS_200511" values less than (to_date ('20170101', 'ddmmyyyy ')),
PARTITION "SCES1INPUTS_200512" values less than (to_date ('20170101', 'ddmmyyyy '))
)
/
 
 
 
SQL> SELECT table_name, column_name, segment_name, tablespace_name, index_name
2 from Dba_Lobs WHERE table_name = 'sces1inputs ';
 
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
----------------------------------------------------------------------------------------------------------------------------------
SCES1INPUTS LOBS1INPUT LOB1_SCES1INPUTS USERS SYS_IL0000018502C00002 $
SCES1INPUTS LOBS1INPUT_GZ LOB2_SCES1INPUTS USERS SYS_IL0000018502C00008 $
 
SQL>
 
-- Move tablespace:
SQL> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200508 tablespace USERS
3 lob (LOBS1INPUT) STORE as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE)
4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE );
 
Table altered.
 
SQL>
 
SQL> SELECT table_name, column_name, segment_name, tablespace_name, index_name
2 from Dba_Lobs WHERE table_name = 'sces1inputs ';
 
TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
----------------------------------------------------------------------------------------------------------------------------------
SCES1INPUTS LOBS1INPUT LOB1_SCES1INPUTS USERS SYS_IL0000018502C00002 $
SCES1INPUTS LOBS1INPUT_GZ LOB2_SCES1INPUTS USERS SYS_IL0000018502C00008 $
 
SQL>

There is actually a misunderstanding here. For the partition table's lob object, we should not check user_lobs, but should check
User_lob_partitions:

SQL> SELECT column_name, lob_name, partition_name, lob_partition_name, tablespace_name
2 FROM user_lob_partitions WHERE table_name = 'sces1effects ';
 
COLUMN_NAME LOB_NAME PARTITION_NAME LOB_PARTITION_NAME TABLESPACE_NAME
----------------------------------------------------------------------------------------------------------------------------------
LOBS1INPUT LOB1_SCES1INPUTS SCES1INPUTS_200509 sys_lob_p1_users
LOBS1INPUT LOB1_SCES1INPUTS SCES1INPUTS_200510 SYS_LOB_P134 USERS
LOBS1INPUT LOB1_SCES1INPUTS SCES1INPUTS_200508 SYS_LOB_P152 TBS_OGG
LOBS1INPUT LOB1_SCES1INPUTS SCES1INPUTS_200511 SYS_LOB_P135 USERS
LOBS1INPUT_GZ LOB2_SCES1INPUTS SCES1INPUTS_200508 SYS_LOB_P154 TBS_OGG
LOBS1INPUT LOB1_SCES1INPUTS SCES1INPUTS_200512 SYS_LOB_P136 USERS
LOBS1INPUT_GZ LOB2_SCES1INPUTS SCES1INPUTS_200509 SYS_LOB_P143 USERS
LOBS1INPUT_GZ LOB2_SCES1INPUTS SCES1INPUTS_200510 SYS_LOB_P144 USERS
LOBS1INPUT_GZ LOB2_SCES1INPUTS SCES1INPUTS_200511 SYS_LOB_P145 USERS
LOBS1INPUT_GZ LOB2_SCES1INPUTS SCES1INPUTS_200512 SYS_LOB_P146 USERS
 
10 rows selected.
 
SQL>

If we move the tablespace, we need to modify the attributes of the default tablespace:

-- Modify the tablespace of each partition:
SQL> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200508 tablespace tbs_ogg
3 lob (LOBS1INPUT) STORE as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE)
4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE );
 
Table altered.
 
SQL> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200509 tablespace tbs_ogg
3 lob (LOBS1INPUT) STORE as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE)
4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE );
 
Table altered.
 
SQL> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200510 tablespace tbs_ogg
3 lob (LOBS1INPUT) STORE as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE)
4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE );
 
Table altered.
 
SQL> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200511 tablespace tbs_ogg
3 lob (LOBS1INPUT) STORE as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE)
4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE );
 
Table altered.
 
SQL> alter table SCES1INPUTS
2 move partition SCES1INPUTS_200512 tablespace tbs_ogg
3 lob (LOBS1INPUT) STORE as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE)
4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg enable storage in row chunk 8 k pctversion 0 NOCACHE );
 
Table altered.
 
 
-- At this time, the new partition (such as the new partition automatically generated by interval) still uses the original tablespace.
-- You need to modify the attribute of tablespace:

SQL> alter table SCES1INPUTS modify default attributes tablespace tbs_ogg;
 
Table altered.
 
SQL>
SQL> SELECT def_tablespace_name FROM user_part_tables WHERE table_name = 'sces1inputs ';
 
DEF_TABLESPACE_NAME
------------------------------
TBS_OGG
 
SQL>
At this time, the new partitions generated in the future will go to the new tablespace instead of the old tablespace.