A
Oracle Text Reference

Oracle Files uses Oracle Text to facilitate full-text search and other advanced capabilities. The speed with which results are returned depends on several factors, including the quality of the Oracle Text index used with Oracle Files (IFS_TEXT), free space on the disk for objects required during the search, and other such details. In addition, the end user's experience of the "performance" of the search can depend on how much time you let elapse before an in-progress search times out.

This appendix provides information about how to maintain the Oracle Text index to ensure optimal Oracle Files performance, and includes these topics:

• Oracle Text Tablespaces and Disk Utilization
• Creating and Maintaining the Oracle Text Index
• Enabling Theme Indexing
• Modifying the Search Timeout Parameter
• Troubleshooting Oracle Text Problems

Previous names for Oracle Text include Oracle Context and Oracle interMedia Text. Many of the underlying indexes, views, tables, and various PL/SQL packages referred to in much of the administrator and application developer documentation still use Context or interMedia-related nomenclature. For example, the database schema that owns all Oracle Text objects, such as the indexes, is CTXSYS.

For detailed information about Oracle Text, visit the Oracle Technology Network (http://otn.oracle.com/products/text/).

Oracle Text Tablespaces and Disk Utilization

Disk space for Oracle Text is divided among three distinct tablespaces:

• The Oracle Text Data tablespace contains tables that hold text tokens (separate words) that exist within the various indexed documents. The storage for these text tokens is roughly proportional to the ASCII content of the document. The ASCII content percentage will vary depending on the format of the original document. Text files only have white space as their non-ASCII content and, therefore, will incur a greater per-document percentage overhead. Document types such as Microsoft Word or PowerPoint contain large amounts of data required for formatting that does not qualify as text tokens. The per-document percentage on these types of documents will, therefore, be lower. On a system with diverse content types, the expected overhead is approximately 8 percent of the sum of the original sizes of the indexed documents.
• The Oracle Text Keymap tablespace contains the tables and indexes required to translate from the Oracle Files locator of a document (the Oracle Files DocID) to the Oracle Text locator of that same document (the Oracle Text DocID). The expected space utilization for this tablespace is approximately 70 bytes per indexed document.
• The Oracle Text Index tablespace contains the B*tree database index that is used against the text token information stored in the Oracle Text Data tablespace. This will grow as a function of the ASCII content just as the Oracle Text Data tablespace does. On a system with diverse content types, the expected overhead is approximately 4 percent of the sum of the ASCII content of the documents, or approximately 1 percent of the sum of the total sizes of the indexed documents.

Use this information to estimate and plan disk storage needs for your Oracle Files instance.

Creating and Maintaining the Oracle Text Index

At the end of the configuration process for Oracle Files, the Configuration Assistant uses the SQL scripts shown in Table A-1 to create and populate the IFS_TEXT index.

These scripts are located in the \install\admin\sql directory of the Oracle Collaboration Suite CD. If the installation process fails and you need to run these scripts manually, you should execute the scripts at a SQL*Plus prompt after logging on as the Oracle Files schema user (by default, IFSSYS).

Table A-1 SQL Scripts for Creating Oracle Text Index

Script	Usage
CreateContextFunnelProcedure.sql	Creates the procedure used by USER DATASTORE.
GrantContextToIFS.sql	Grants the Oracle Files user (schema) privileges on the Oracle Text-specific commands required to maintain the index.
CreateContextPreferences.sql	Tablespace and other text preferences are created by the Oracle Files user.
CreateContextIndex.sql	Creates the IFS_TEXT index based on the text preferences.

Maintaining the IFS_TEXT Index By Using the Oracle Text PL/SQL Packages

Oracle recommends using the PL/SQL packages provided with Oracle Text for maintaining the index. Unlike a regular database index, an Oracle Text index is not dynamically updated with each insert or update of information. Rather, you must refresh (or synchronize) the index periodically, using the Oracle Text stored procedure ctx_ddl.sync_index.

The ctx_ddl.sync_index procedure does not rebuild the entire index, but adds and delete records that have changed since the last synchronization. Since the changes are incremental, the more frequently you run this procedure, the faster it goes. However, over the course of time, the index can become fragmented, so a companion procedure (ctx_ddl.optimize_index) is provided to optimize the index.

These two stored procedures to sync and optimize the IFS_TEXT index can be run automatically and simultaneously by configuring either the DBMS_JOB package or by using the job system of Oracle Enterprise Manager. See the Oracle9i Database Administrator's Guide for information.

Sync Existing IFS_TEXT Index

ctx_ddl.sync_index(indexname);

Optimize IFS_TEXT Index

ctx_ddl.optimize_index(indexname, 'FAST'|'FULL', maxtime );

Monitoring Oracle Text Indexing of Oracle Files Documents

Oracle Files provides some utility-type SQL scripts to facilitate interaction with Oracle Text (see Table A-2). Read each .sql file for additional usage details. All scripts are available in:

$ORACLE_ HOME/9ifs/admin/sql

Table A-2 SQL Scripts for Monitoring Oracle Text Indexing

Script	Usage
ViewContextErrors.sql	Script that decodes the operating system specific errors that were generated during Oracle Text indexing.
SyncContextIndex.sql	Script that enables you to monitor the Oracle Text synchronization process. Uncomment the first two lines in the script, which includes call to ctx_output.add_event(), to monitor on a rowid by rowid basis.
ViewDocumentByRowID.sql	Script that enables you to view additional information about a document that is indexed by Oracle Text. Use the docid from the Oracle Text log with this script.

Indexing Non-Standard Content Types

Oracle Files does not by default index every file that's moved into the system, but you can configure it to index any type of content you choose by designating the MIME type as "To Be Indexed" on the Create Format (or Format page, if it already exists).

For example, you may want to index all your .java source code files. To do so:

1. Use the Oracle Files Manager tool to add the .java mimetype and designate it as "To Be Indexed" using the Create Format dialog.
2. Insert the files into the repository.
3. Sync the index using the procedure discussed in "Sync Existing IFS_TEXT Index".

Enabling Theme Indexing

Enabling or disabling Oracle Text theme indexing causes all documents in Oracle Files to be re-indexed, which can take considerable time. To reduce the impact on your user community, be sure to perform either of these tasks after hours or when system usage is low.

Use SQL*Plus to connect as the Oracle Files schema user, and enter:

exec ctx_ddl.set_attribute('ifs_default_lexer', 'index_themes', 'yes');
exec ctx_ddl.set_attribute('ifs_default_lexer', 'theme_language','english');
alter index ifs_text rebuild parameters('replace lexer ifs_global_lexer');

Disabling Theme Indexing

In SQL*Plus, connect as the Oracle Files schema user, and enter:

exec ctx_ddl.set_attribute('ifs_default_lexer', 'index_themes', 'no');
alter index ifs_text rebuild parameters('replace lexer ifs_global_lexer');

The index is rebuilt and theme indexing is disabled.

For more information on themes, see Oracle Text Application Developer's Guide.

Modifying the Search Timeout Parameter

The IFS.SERVICE.SESSION.DefaultSearchTimeoutPeriod service configuration parameter specifies the timeout period for a running search that has not yet returned results. The default setting for this parameter (in the default service configurations) is 60 seconds. Increase this value and users will wait longer than a minute before a search times out; decrease the value to shorten the time in which a running search will time out.

Troubleshooting Oracle Text Problems

This section provides Oracle Text troubleshooting information.

Table A-3 Troubleshooting Oracle Text Problems

Problem	Probable Cause	Corrective Action
Cannot search on document contents of any documents.	Oracle Text server is not running.	Start the database instance and make sure that Oracle Text is running.
Server is slow only on content-based search activity.	Probable cause #1: Oracle Text tablespaces are on the same disk as other database files. Probable cause #2: Oracle Text indexes have become fragmented.	Move the Oracle Text tablespaces to other disks. See the Oracle Collaboration Suite Installation Guide. See the Oracle9i Administrator's Guide for more information on moving tablespaces. Regularly optimize the text Oracle index GLOBALINDEXEDBLOB_I (for new schemas). For more information, see "Optimize IFS_TEXT Index".
Oracle Files rows never get processed and never leave the Oracle Text view ctx_user_pending.	Oracle Files documents are corrupt or do not have the correct extension.	Follow the steps in"Oracle Files rows show up in the Oracle Text view ctx_user_index_errors." to determine which Oracle Files documents are being referred to, substituting ctx_user_pending for ctx_user_index_errors and pnd_rowid for err_textkey. Examine this document, looking for these problems: Is the file damaged in any way? Is the file name extension correct for this document? Is the character set of the document correct? If no obvious problems are found, send the document to your Oracle Support Representative for further diagnosis. Delete the document from Oracle Files.

Problem	Probable Cause	Corrective Action
Searching on the contents of new documents stops working.	A recent document has caused Oracle Text server to fail.	Log into SQL*Plus as ifssys/<schema-pwd>, and issue: select count(*) from ctx_user_pending; If there are any rows in that view and the rows are not changing, then a recent document has caused Oracle Text to stop indexing. To determine which Oracle Files documents these rows refer to, see the problem "Oracle Files rows show up in the Oracle Text view ctx_user_index_errors." Still in SQL*Plus, issue the command: select * from ctx_user_index_errors; to see if there are any recent errors. Resolve any of these errors. To determine which Oracle Files documents these rows refer to, see the problem "Oracle Files rows show up in the Oracle Text view ctx_user_index_errors." Check again to see if there are any rows in ctx_user_pending and, if so, that the rows are changing. If this does not resolve the issue, contact your Oracle Support representative for further assistance.
Oracle Files rows show up in the Oracle Text view ctx_user_index_errors.	Oracle Files documents are corrupt or do not have the correct extension.	Determine which Oracle Files document is being referred to. SQL*Plus ifssys/ifssys select du.uniquename, vd.name, co.contentsize, cs.id, vd.id from odmv_document vd, odm_ contentobject co, odmm_contentstore cs, odm_document od, odm_directoryuser du where vd.id = od.id and od.contentobject = co.id and co.content = cs.id and du.id = vd.owner and cs.id in ( select distinct od.id from ctx_user_index_errors cp, odmm_ contentstore od where od.rowid = err_textkey ) order by cs.id; cd $ORACLE_HOME/9ifs/bin ifslogin system/manager ifsshowallpaths -id <vd.id> replacing <vd.id> with the vd.id returned from the Select statement above. This command will display the absolute path of the document. Examine this document, looking for these problems: Is the file damaged in any way? Is the file name extension correct for this document? Is the character set of the document correct? If no obvious problems are found, send the document to your Oracle Support Representative for further diagnosis.