客户有个带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,而是应该去 […]