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

客户有个带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>
此时，今后生成的新分区才会去新的表空间，而不是老的表空间。