This chapter describes Oracle Text administration. The following topics are covered:
While any user can create an Oracle Text index and enter a CONTAINS query, Oracle Text provides the CTXSYS user for administration and the CTXAPP role for application developers.
The CTXSYS user is created during installation 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
Note:
In previous releases of Oracle Text,CTXSYS had SYSDBA privileges, and only CTXSYS could perform certain functions, such as modifying system-defined preferences or setting system parameters.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 enter a Text query. The CTXAPP role enables users to create preferences and use the PL/SQL packages.
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.
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 viewsUse the CTX_OUTPUT PL/SQL package to log indexing and document service requests.
See Also:
Oracle Text Reference for more information about this packageUse the CTX_REPORT package to produce reports on indexes and queries. These reports can help you fine-tune or troubleshoot your applications.
See Also:
Oracle Text Reference for more information about this packageThe 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):
begin
ctx_ddl.create_preference('"TDRBPRX0_DST"','DIRECT_DATASTORE');
end;
/
...
/
begin
ctx_ddl.create_section_group('"TDRBPRX0_SGP"','NULL_SECTION_GROUP');
end;
/
...
begin
ctx_ddl.create_preference('"TDRBPRX0_WDL"','BASIC_WORDLIST');
ctx_ddl.set_attribute('"TDRBPRX0_WDL"','STEMMER','ENGLISH');
ctx_ddl.set_attribute('"TDRBPRX0_WDL"','FUZZY_MATCH','GENERIC');
end;
/
begin
ctx_ddl.create_stoplist('"TDRBPRX0_SPL"','BASIC_STOPLIST');
ctx_ddl.add_stopword('"TDRBPRX0_SPL"','teststopword');
end;
/
...
/
begin
ctx_output.start_log('TDRBPRX0_LOG');
end;
/
create index "DR_TEST"."TDRBPRX0"
on "DR_TEST"."TDRBPR"
("TEXT2")
indextype is ctxsys.context
parameters('
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):
=================================================================
INDEX SIZE FOR DR_TEST.TDRBPRX10
=================================================================
TABLE: DR_TEST.DR$TDRBPRX10$I
TABLESPACE NAME: DRSYS
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
TABLESPACE NAME: DRSYS
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
TABLESPACE NAME: DRSYS
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 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 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).
Oracle Enterprise Manager provides Text Manager for configuring, maintaining, and administering Oracle Text indexes. With Text Manager you can perform all of the basic configuration and administration tasks for Oracle Text indexes. You can monitor the overall health of Text indexes for a single Oracle database instance or for the Oracle Real Application Clusters environment. Text Manager provides summaries of critical information and enables you to drill down to the level of detail that you want, to resolve issues, and to understand any actions that may need to occur. You access Text Manager by clicking the Schema tab from the database home page in Oracle Enterprise Manager, and then selecting Text Indexes under the Text Manager group. On the Text Indexes page, select an index name and click View to see information and attributes for that index.
The Text Indexes page shows the jobs that are in progress, scheduled within the last seven days, or are experiencing problems. From this page you can go to the Job Scheduler to see a summary of all jobs for this database instance, and to manage selected jobs. The online help in Oracle Enterprise Manager provides details and procedures for using each Text Manager feature.
Note:
You cannot create an Oracle Text index with Text Manager. Use theCREATE INDEX statement to create an Oracle Text index as described in Chapter 3, " Indexing with Oracle Text" under Creating Oracle Text Indexes.From the main Text Manager page, you can perform the following actions on the selected index from the Actions list:
Synchronize
Optimize
Rebuild
Resume Failed Operation
Show Logs
Show Errors
You can also schedule jobs for the specified index.
To access Text Manager:
Log on to the database with a user account that is authorized to access Database Control. For example, this could be SYS or SYSTEM, with the password that you specified during database installation.
Database Control displays the Database Home page.
Select the Schema tab from the Database Home page.
Click Text Indexes located under Text Manager.
The Text Indexes page appears with a list of Text indexes for this database instance.
When you select a Text index from the Text Indexes page, options become available for that index for you to edit or perform actions. For example, to configure attributes for searching, click Edit for the selected index. From the Edit Text Index page, you can set attributes, including: Wild Card Maximum Term, Fuzzy Score, and Number of Fuzzy Expansions. You can change index and partition names, as well as specify settings for URL_DATASTORE in addition to other options.
You can use the View Text Index page to see general information about a specific index: index type, parallel degree, synchronization mode, wild card limit, fuzzy score, fuzzy numeric result, datastore, and so forth. Information about any partitions on the index is also available.
To view general information for a Text index:
From the Text Indexes page, click the name of the index in the list of Text indexes.
The View Text Index page opens with the General tab selected.
From here you can select actions to perform maintenance tasks.
You use the Text Indexes page in Text Manager to see the list of Text indexes and general health of the Text indexes for the database instance to help you understand any critical actions that may need to be taken in order to make sure that the entire application is performing properly. Information is displayed such as the status of the indexes and jobs submitted by users during the last seven days. Key information about the Text indexes is also displayed in a tabular form.
Use the Text Indexes page to see:
The number of Text indexes that contain invalid partitions, and which are, therefore, invalid. The number of partitions that are invalid, if any, for all Text indexes is also shown.
The number of indexes that are in an in-progress state, and the number of partitions, if any, that are in an in-progress state.
The number of indexes where all partitions are valid and no activity is in progress.
Sum total of the Text indexes found for this database instance.
Additionally, use the Text Indexes page to see the index type for each Text index, the owner, the number of documents that are not synchronized, total number of documents, and percentage of fragmentation.
You select a Text index from the list and then options become available for that index for you to edit or perform actions.
You index documents and enter 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, or from Text Manager in Oracle Enterprise Manager.
See Also:
Chapter 3, " Indexing with Oracle Text" for more information about indexing and index synchronizationDatabase Feature Usage statistics in Oracle Enterprise Manager provide an approximation of how often various database features are used. Tracking this information is potentially useful for application development as well as for auditing. You access Database Feature Usage by clicking the Server tab in Oracle Enterprise Manager, and then selecting Database Feature Usage under the Database Configuration group.
The following information is gathered for Oracle Text:
For package usage statistics, Database Feature Usage captures information about how often, if ever, and when the following packages have been used:
CTX_ADM
CTX_CLS
CTX_DDL
CTX_DOC
CTX_OUTPUT
CTX_QUERY
CTX_REPORT
CTX_THES
CTX_ULEXER
For index usage statistics, Database Feature Usage captures the number of existing indexes in the database. The statistics are captured separately for each index type: CONTEXT, CTXCAT, and CTXRULE.
For SQL operator usage statistics, Database Feature Usage captures whether the user has ever used the CONTAINS, CATSEARCH, and MATCHES operators.
Note:
The feature usage tracking statistics might not be 100% accurate.Oracle Text queries can be parallelized across Oracle RAC nodes for maximum throughput and performance for OLAP applications. You can manage Oracle Text indexes on Oracle RAC nodes with Text Manager in Oracle Enterprise Manager as described in the previous section "Text Manager in Oracle Enterprise Manager".