客户有个带lob对象的表空间，希望做表空间的move，可是等move之后，发现在dba_lobs里面查到的lob对象的表空间还是在原来的地方。
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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了其表空间之后，还需要修改一下其默认表空间的属性：
|
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 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
--修改每个分区的表空间： 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> |
此时，今后生成的新分区才会去新的表空间，而不是老的表空间。
2条评论
alter table SCES1INPUTS modify default attributes tablespace tbs_ogg; 是不是其他的非lob字段也变成这个tbs_ogg表空间了？
re 王亮：是的。操作的目的就是将所有的对象move到别的表空间，将原来的表空间下线。