Have you ever tried to use the search option of Online Help in Oracle Applications?
Our users did... and they got "The page cannot be found" message... I checked it on firefox, hope to get more accurate message, and I got this:
"Not Found. The requested URL /pls/DEV/fnd_help.search was not found on this server".
 I checked fnd_help package and it's compiled and looks fine. These errors appeared in error_log_pls: [Mon Jul 28 10:34:54 2008] [warn] mod_plsql: Stale Connection due to Oracle error 20000
[Mon Jul 28 10:34:54 2008] [error] mod_plsql: /pls/DEV/fnd_help.search ORA-20000
ORA-20000: Oracle Text error:
DRG-10599: column is not indexed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1209
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "APPS.FND_HELP", line 1043
ORA-06512: at "APPS.FND_HELP", line 873
ORA-06512: at line 20 I found note 306239.1 - "Cannot Search Online Help After Fresh Install of 11.5.10" which suggest that Applications interMedia Text indexes are corrupt. In my instance it even wasn't exist... I followed this note which instructs to: - drop index FND_LOBS_CTX;
- Rebuild the index using aflobbld.sql
But aflobbld.sql had been running for more than 10 hours and the size of DR$FND_LOBS_CTX$I table has reached to 35 GB !
I had been wondering how it can be that fnd_lobs table is less than 1GB and the index on it is 35 GB and counting.... ?! Note 396803.1 - "FND_LOBS_CTX is having huge size, how to reduce the sizeof the index?" suggests it's a bug, indexing all documents in FND_LOB table, also the binary files, while using wrong filter. So how can we make aflobbld.sql to index only FND_HELP documents? For each row in FND_LOBS table the file_format column is populated with one of the following values: IGNORE, BINARY, TEXT.
aflobbld.sql will index only rows that have this column set to BINARY or TEXT.
If we set all rows to IGNORE except FND_HELP rows, we could index them only. Note 397757.1 - "How to Speed Up Index Creation on FND_LOBS by indexing Only FND_HELP Data" suggests the steps to do it. These are the steps: - Backup the fnd_lobs table before updating it, we will use it later:
create table fnd_lobs_bk as select * from fnd_lobs;
** you can create a backup of this table and omit the file_date column to make this backup faster
- Drop index FND_LOBS_CTX if exists:
drop index applsys.FND_LOBS_CTX;
- Update all rows to IGNORE except FND_HELP rows:
update fnd_lobs
set file_format = 'IGNORE'
where nvl(program_name,'@') <> 'FND_HELP' ;
- Execute aflobbld.sql from OS terminal:
sqlplus apps/sppas @$FND_TOP/sql/aflobbld.sql applsys apps;
- Since I'm not sure about the impact of leaving the FND_LOBS rows as IGNORE, I updated them back to the previous state:
create unique index fnd_lobs_bk_u1 on fnd_lobs_bk (file_id);
update (select fl.file_format ffo,flb.file_format ffb
from fnd_lobs fl
,fnd_lobs_bk flb
where fl.file_id = flb.file_id)
set ffo=ffb;
drop table fnd_lobs_bk;
- Check the search option.... it should work now.
You are welcome to leave a comment . Aviad | 
