10 Administering Oracle Text

This chapter describes Oracle Text administration. The following topics are covered:

10.1 Oracle Text Users and Roles

While any user can create an Oracle Text index and issue a CONTAINS query, Oracle Text provides the CTXSYS user for administration and the CTXAPP role for application developers.

10.1.1 CTXSYS User

The CTXSYS user is created at install time. CTXSYS can do the following:

  • View all indexes

  • Sync all indexes

  • Run ctxkbtc, the knowledge base extension compiler

  • Query all system-defined views

  • Perform all the tasks of a user with the CTXAPP role


In previous releases of Oracle Text, CTXSYS had DBA privileges, and only CTXSYS could perform certain functions, such as modifying system-defined preferences or setting system parameters.

During a manual installation, after installation of the CTXSYS schema is complete, you may want to run dr0lsys.sql to lock and expire the CTXSYS schema for security reasons. Alternatively, you can choose a good password for CTXSYS when running dr0csys.sql.

10.1.2 CTXAPP Role

The CTXAPP role is a system-defined role that enables users to do the following:

  • Create and delete Oracle Text preferences

  • Use the Oracle Text PL/SQL packages

Any user can create an Oracle Text index and issue a Text query. The CTXAPP role enables users to create preferences and use the PL/SQL packages.

10.1.3 Granting Roles and Privileges to Users

The system uses the standard SQL model for granting roles to users. To grant a Text role to a user, use the GRANT statement.

In addition, to allow application developers to call procedures in the Oracle Text PL/SQL packages, you must explicitly grant to each user EXECUTE privileges for the Oracle Text package.

10.2 DML Queue

When there are inserts, updates, or deletes to documents in your base table, the DML queue stores the requests for documents waiting to be indexed. When you synchronize the index with CTX_DDL.SYNC_INDEX, requests are removed from this queue.

Pending DML requests can be queried with the CTX_PENDING and CTX_USER_PENDING views.

DML errors can be queried with the CTX_INDEX_ERRORS or CTX_USER_INDEX_ERRORS view.

See Also:

Oracle Text Reference for more information about these views

10.3 The CTX_OUTPUT Package

Use the CTX_OUTPUT PL/SQL package to log indexing and document service requests.

See Also:

Oracle Text Reference for more information about this package

10.4 The CTX_REPORT Package

Use the CTX_REPORT package to produce reports on indexes and queries. These reports can help you fine-tune or troubleshoot your applications.

See Also:

The CTX_REPORT chapter in the Oracle Text Reference

The CTX_REPORT package contains the following procedures:


These procedures create reports that describe an existing index or policy, including the settings of the index metadata, the indexing objects used, the settings of the attributes of the objects, and (for CTX_REPORT.DESCRIBE_INDEX) index partition information, if any. These procedures are especially useful for diagnosing index-related problems.

This is sample output from DESCRIBE_INDEX, run on a simple context index:

                        INDEX DESCRIPTION
index name:                      "DR_TEST"."TDRBPRX0"
index id:                        1160
index type:                      context
base table:                      "DR_TEST"."TDRBPR"
primary key column:              ID
text column:                     TEXT2
text column type:                VARCHAR2(80)
language column:
format column:
charset column:
                          INDEX OBJECTS
datastore:                       DIRECT_DATASTORE
filter:                          NULL_FILTER
section group:                   NULL_SECTION_GROUP
lexer:                           BASIC_LEXER
wordlist:                        BASIC_WORDLIST
   stemmer:                         ENGLISH
   fuzzy_match:                     GENERIC
stoplist:                        BASIC_STOPLIST
   stop_word:                       teststopword
storage:                         BASIC_STORAGE
   r_table_clause:                  lob (data) store as (cache)
   i_index_clause:                  compress 2

CREATE_INDEX_SCRIPT creates a SQL*Plus script that can create a duplicate of a given text index. Use this when you have an index but don't have the original script (if any) used to create that script and want to be able to re-create the index. For example, if you accidentally drop a script, CREATE_INDEX_SCRIPT can re-create it; likewise, CREATE_INDEX_SCRIPT can be useful if you have inherited indexes from another user but not the scripts that created them.

CREATE_POLICY_SCRIPT does the same thing as CREATE_INDEX_SCRIPT, except that it enables you to re-create a policy instead of an index.

This is sample output from CREATE_INDEX_SCRIPT, run on a simple context index (not a complete listing):

create index "DR_TEST"."TDRBPRX0"
  indextype is ctxsys.context
    datastore       "TDRBPRX0_DST"
    filter          "TDRBPRX0_FIL"
    section group   "TDRBPRX0_SGP"
    lexer           "TDRBPRX0_LEX"
    wordlist        "TDRBPRX0_WDL"
    stoplist        "TDRBPRX0_SPL"
    storage         "TDRBPRX0_STO"

This procedure creates a report showing the names of the internal index objects, along with their tablespaces, allocated sizes, and used sizes. It is useful for DBAs who may need to monitor the size of their indexes (for example, when disk space is at a premium).

Sample output from this procedure looks like this (partial listing):

TABLE:                          DR_TEST.DR$TDRBPRX10$I
BLOCKS ALLOCATED:                                            4
BLOCKS USED:                                                 1
BYTES ALLOCATED:                               8,192 (8.00 KB)
BYTES USED:                                    2,048 (2.00 KB)

INDEX (LOB):                    DR_TEST.SYS_IL0000023161C00006$$
TABLE NAME:                     DR_TEST.DR$TDRBPRX10$I
BLOCKS ALLOCATED:                                            5
BLOCKS USED:                                                 2
BYTES ALLOCATED:                             10,240 (10.00 KB)
BYTES USED:                                    4,096 (4.00 KB)

INDEX (NORMAL):                 DR_TEST.DR$TDRBPRX10$X
TABLE NAME:                     DR_TEST.DR$TDRBPRX10$I
BLOCKS ALLOCATED:                                            4
BLOCKS USED:                                                 2
BYTES ALLOCATED:                               8,192 (8.00 KB)
BYTES USED:                                    4,096 (4.00 KB)

INDEX_STATS produces a variety of calculated statistics about an index, such as how many documents are indexed, how many unique tokens the index contains, average size of its tokens, fragmentation information for the index, and so on. An example of a use of INDEX_STATS might be in optimizing stoplists.

See the Oracle Text Reference for an example of the output of this procedure.


This procedure creates a report of logged queries, which you can use to perform simple analyses. With query analysis, you can find out:

  • which queries were made

  • which queries were successful

  • which queries were unsuccessful

  • how many times each query was made

You can combine these factors in various ways, such as determining the 50 most frequent unsuccessful queries made by your application.

See the Oracle Text Reference for an example of the output of this procedure.


TOKEN_INFO is used mainly to diagnose query problems; for instance, to check that index data is not corrupted. As an example, you can use it to find out which documents are producing unexpected or bad tokens.


This is a lookup function, used mainly as input to other functions (CTX_DDL.OPTIMIZE_INDEX, CTX_REPORT.TOKEN_INFO, and so on).

10.5 Servers

You index documents and issue queries with standard SQL. No server is needed for performing batch DML. You can synchronize the CONTEXT index with the CTX_DDL.SYNC_INDEX procedure.

See Also:

Chapter 3, " Indexing with Oracle Text" for more information about indexing and index synchronization

10.6 Administration Tool

Oracle Text Manager is a Java application integrated with Oracle Enterprise Manager.

The Text Manager enables administrators to create preferences, stoplists, sections, and indexes. This tool also enables administrators to perform DML.

See Also:

the online help shipped with this tool for more information about the Oracle Text Manager