Find out which tables have LOB objects:
SELECT TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME,INDEX_NAME FROM DBA_LOBS WHERE OWNER = 'USR1'; Genrate a "move table" script: select 'alter table ' || owner || '.' || table_name || ' move lob(' || column_name ||') store as (tablespace LOB_DATA);' from dba_tab_columns c where c.owner = 'USR1' and c.data_type like '%LOB%';
Query returns the following statements:
alter table USR1.LG_LOG move lob(MESSAGE_CONTEXT) store as (tablespace LOB_DATA);
alter table USR1.TFW_TEST_RESULT move lob(SQL_NUM_MATCHED_ERROR_ROWS) store as (tablespace LOB_DATA);
Note:
The LOB index is an internal structure that is strongly associated with LOB storage.
This implies that a user may not drop the LOB index or rebuild it.
No comments:
Post a Comment