How to enable FND_HELP search by creating an index on FND_LOBS efficiently

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