Monday, August 17, 2009

Reorg Lobsegment/LobIndex

7) Lobsegment/LobIndex

alter table move lob ()
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 '' and segment_type like '%LOB%');



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 / @$FND_TOP/patch/115/sql/wfctqrec.sql

No comments: