alter table
store as (tablespace
To find the table_name and lob_column
select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where SEGMENT_NAME like 'lobsegment_name'
SYS_LOB0000195332C00032$$ LOBSEGMENT
OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------
SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
IEO IEO_ICSM_QUEUE_TBL_1
USER_PROP
SYS_LOB0000195332C00032$$ SYS_IL0000195332C00032$$ APPS_TS_QUEUES
So from above output , following will be the command to move lobsegment
alter table IEO.IEO_ICSM_QUEUE_TBL_1 move lob (USER_PROP) store as (tablespace APPS_TS_QUEUES_NEW);
Note that , once the LOBSEGMENT is moved ,corresponding LOBINDEX will
Also move. You don't need to run the command seperately for LOBINDEX.
Here is the dynamic query which will give the command to move LOBSEGMENT/INDEX
select 'alter table ' ||owner || '.' || table_name||' move lob ('||column_name||') store as (tablespace APPS_TS_QUEUES_NEW);'
from dba_lobs where SEGMENT_NAME in ( select segment_name from dba_segments where tablespace_name like '
Follow this only for lobsegment on AQ$_WF_CONTROL_D.
SQL> select segment_name, segment_Type,owner from dba_segments WHERE tablespace_name
='APPS_TS_QUEUES';
SEGMENT_NAME
SEGMENT_TYPE OWNER
---------------------------------------------------------------------------------
------------------ ------------------------------
SYS_IL0000583185C00006$$
LOBINDEX APPLSYS
SYS_LOB0000583185C00006$$
LOBSEGMENT APPLSYS
SQL> alter table APPLSYS.AQ$_WF_CONTROL_D move lob (RSUBS) store as
2 lobsegment (tablespace APPS_TS_QUEUES_NEW);
alter table APPLSYS.AQ$_WF_CONTROL_D move lob (RSUBS) store as
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online
Found Note 418238.1
1> First stop WF related services like WF Mailer Agent, WF listener, etc.
2> Then run following SQL :
sqlplus
No comments:
Post a Comment