Symptoms : DR$PENDING and DR$WAITING have too many rows.
Cause : The FND_LOBS is a table that has the index FND_LOBS_CTX
This table grows and the index is not being synchronized. Then the pending rows to be synchronized
are inserted into dr$pending and dr$waiting.
Solution : To implement the solution, please execute the following steps:
1. Purge FND_LOBS following instructions in these notes:
Note 298698.1 Note 171272.1
2. Sync the index FND_LOBS_CTX periodically:
This note explains how to syncronize a textNote 171272.1
Note 119172.1
3. Execute the following query to find out what are the indexes that still have pending rows in
DR$PENDING:
connect as ctxsys:
select u.username, i.idx_name
from dr$index i, dba_users u
where u.user_id=i.idx_owner#
and idx_id in
(select pnd_cid
from dr$pending);
4. Synchronize those indexes
(1) exec ctx_ddl.sync_index('USERNAME.INDEX');
(2) context process 이용 방법
Context server를 구동하여 두고 자동으로 indexing을 실행하는 방법이다.
$ nohup ctxsrv -user ctxsys/ctxsys -personality M -logfile ctx01.log &
$ exec ctx_adm.shutdown;
5. Remove any rows remaining in DR$PENDING and DR$WAITING
$FND_TOP/sql/aflobbld.sql -- index rebuild
$FND_TOP/patch/115/sql/AFLOBCX3.sql applsys apps -- index recreate
SQL> alter table applsys.fnd_lobs modify lob (file_data)
(storage (pctincrease 0));
SQL> alter table applsys.DR$FND_LOBS_CTX$I modify lob (TOKEN_INFO)
(storage (next 2m pctincrease 0));
LOBINDEX의 storage는 자동으로 변경됨(next, pctincrease)