lob移表空间 oracle_关于oracle带lob对象的分区表，移动表空间的问题

最新推荐文章于 2025-06-27 17:18:13 发布

原创最新推荐文章于 2025-06-27 17:18:13 发布 · 636 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#lob移表空间 oracle

本文详细介绍了在Oracle数据库中遇到的一个问题：当尝试将带有LOB对象的分区表移动到新表空间后，LOB对象仍然显示在原始表空间。通过查询user_lob_partitions而非dba_lobs来正确查看LOB对象的位置。解决方法是逐个修改分区的表空间属性，并调整默认表空间为新表空间，确保新分区存储在正确的位置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

客户有个带lob对象的表空间，希望做表空间的move，可是等move之后，发现在dba_lobs里面查到的lob对象的表空间还是在原来的地方。

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 LOB1_SCES1INPUTS

LOB (LOBS1INPUT_GZ) STORE AS LOB2_SCES1INPUTS

PARTITION BY RANGE ( DATINSERTION )

(

PARTITION "SCES1INPUTS_200508" VALUES LESS THAN (to_date('01092005','ddmmyyyy')) ,

PARTITION "SCES1INPUTS_200509" VALUES LESS THAN (to_date('01102005','ddmmyyyy')) ,

PARTITION "SCES1INPUTS_200510" VALUES LESS THAN (to_date('01112005','ddmmyyyy')) ,

PARTITION "SCES1INPUTS_200511" VALUES LESS THAN (to_date('01122005','ddmmyyyy')) ,

PARTITION "SCES1INPUTS_200512" VALUES LESS THAN (to_date('01012006','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 8K PCTVERSION 0 NOCACHE )

4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K 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>

这里其实存在一个误区，对于分区表的lob对象，我们不应该去查user_lobs，而是应该去查

user_lob_partitions：

SQL> SELECT column_name,lob_name,partition_name,lob_partition_name,tablespace_name

2 FROM user_lob_partitions WHERE table_name='SCES1INPUTS';

COLUMN_NAME LOB_NAME PARTITION_NAME LOB_PARTITION_NAME TABLESPACE_NAME

-------------------- ------------------------------ ------------------------------ ------------------------------ --------------------

LOBS1INPUT LOB1_SCES1INPUTS SCES1INPUTS_200509 SYS_LOB_P133 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>

我们如果move了其表空间之后，还需要修改一下其默认表空间的属性：

--修改每个分区的表空间：

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 8K PCTVERSION 0 NOCACHE )

4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K 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 8K PCTVERSION 0 NOCACHE )

4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K 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 8K PCTVERSION 0 NOCACHE )

4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K 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 8K PCTVERSION 0 NOCACHE )

4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K 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 8K PCTVERSION 0 NOCACHE )

4 lob (LOBS1INPUT_GZ) store as (tablespace tbs_ogg ENABLE STORAGE IN ROW CHUNK 8K PCTVERSION 0 NOCACHE );

Table altered.

--但此时新的分区(如interval的自动生成的新分区)还是会使用原来的表空间。

--需要修改tablespace的attribute：

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>

此时，今后生成的新分区才会去新的表空间，而不是老的表空间。