Write for OTN
Earn money and promote your technical skills by writing a technical article for Oracle Technology Network.
Learn more
Stay Connected
OTN Architect Community
OTN ArchBeat Blog Facebook Twitter YouTube Podcast Icon

Oracle Identity Manager 11gR2 Catalog: Domain Index Best Practices and FAQs

by Lokesh Gupta

Optimizations and best practices for domain-based indexing with the Access Request Catalog feature in Oracle Identity Management.

May 2014

download-icon13-1Oracle Identity Manager


This document provides information about the extensively used Access Request Catalog feature in Oracle Identity Management (OIM), including optimizations and FAQs required for domain-based indexing used in cataloging. This feature is built on the relational database management system (RDBMS) component, Oracle Text.

OIM Access Request Catalog

The Access Request Catalog is a searchable, categorized collection of entities that are requestable in OIM. Any authenticated user can access and search the catalog using one or more keywords and search operators, add catalog items to a shopping cart, and submit a request for themselves and others.

Key elements of the Access Request Catalog feature:

  • Extensible catalog schema that lets administrators add attributes and specify how they are rendered using a simple browser-based UI
  • Automated harvesting of roles, applications, and entitlements
  • Automated loading of catalog metadata using a CSV file
  • Powerful search using keywords with support for complex search operators
  • Flexible categorization model that allows the catalog to be organized based on customer choice
  • Catalog search results secured based on viewer privileges of the requester
  • Catalog item data available via a web service for use in workflows

Oracle Text

Built on the RDBMS Oracle Text component, Oracle Text (previously known as interMedia Text and ConText) is an extensive full-text indexing technology that lets you efficiently query free text and produce document classification applications. It provides indexing, word and theme searching, and viewing capabilities for text.

Oracle Text has various options for domain-based indexes:
  1. ConText
  2. CatSearch
  3. CtxRule

Of these, only ConText is in the scope of OIM's Oracle Text.

Oracle ConText

The CONTEXT index type is used to index large amounts of text, such as Word, PDF, XML, HTML or plain text documents. In the example that follows, we will store the data in a binary large object (BLOB) column, which allows us to store binary documents like Word and PDF as well as plain text. Using a character large object (CLOB) is preferable if only plain text documents are being used.

Best practices for Catalog

Access Request Catalog uses the "Oracle Text" option in the Oracle database for text search capabilities.

The Catalog table, which contains catalog items, is indexed using the Context index type of Oracle Text. Though the Oracle Text index operates like a regular database index, the architecture and processing behind it highlights the importance of best practices when creating the Text Index and performing ongoing maintenance.

Important Guidelines

  1. Choose Oracle Text when installing Oracle RDBMS. Oracle Text is mandatory for OIM 11g R2 and beyond. Upgrade customers must install Oracle Text manually if it is not installed on their database.
  2. Follow Metalink DOC ID 970473.1 to install Oracle Text manually. You must verify that Oracle Text is installed correctly and that CTXSYS has no INVALID objects.
  3. Customers should never analyze token tables (DR$), but the text index can be analyzed.

Supported Search Operators for OIM

Use the Catalog field to specify a (case-insensitive) keyword for searching or browsing the request catalog. Here are the supported search operators:

  • One or more keywords (sample value: administrator)

    This search condition finds all catalog items that contain the term "administrator."

    Sample value for more than one keyword: web administrator

    This search condition finds all catalog items that contain the terms web and administrator. Because a space character between keywords behaves as an AND operator, this search automatically applies the AND operator to the search keywords. Alternatively, you can use an & operator to denote an AND relationship explicitly.

    For example, web administrator and web & administrator return catalog items that contain both web and administrator.
  • Phrase: To search for catalog items that contain the exact phrase that you enter, you must specify the search condition within double quotes ("). For example, searching for "web administrator" returns catalog items containing the phrase "web administrator."

  • OR [|] search: Use the OR [|] operator to search for catalog items containing any of the search keywords.

    Sample value 1: web | administrator

    This search condition returns catalog items containing the term web or administrator.

    Sample value 2: "vision purchasing" | administrator

    This search condition returns catalog items containing the phrase "vision purchasing" or the term administrator.

  • Wildcard search: You can use the asterisk (*) symbol as the wildcard to perform search operations. However, the catalog search does not support a search condition that begins with the asterisk (*) symbol. Searching with only * returns all items based upon the option chosen in roles, entitlements, application instances, or all.

    For example, admin* returns catalog items beginning with admin, such as administrator and administration.

One-time Optimizations for OIM Oracle Text Index

When you install OIM, the Text Index for Access Request Catalog is created with possible optimizations. However, Oracle Text has other optimizations that are better applied, based on the characteristics of the deployment. Following are the optimizations that you should consider applying in order to improve Access Request Catalog search performance.

You will be unable to use Access Request Catalog when applying these optimizations, which therefore should be performed during a scheduled maintenance window. Note: Catalog Synchronization job and Access Request Catalog should be down when these one-time optimizations are applied.

Storage of Text Index

Oracle Text Index data is stored in relational tables (DR$), which presently reside in the default tablespace of the OIM schema. You should separate them out to their own dedicated tablespace by using the following commands. Below are steps for illustration.

  1. Log in to SYS schema and create a new tablespace to hold the Text Index internal tables. You can use the following sample command to do this; replace DATA_DIR with the directory in which you want to store the data file and adjust the size and other parameters as necessary for your environment.

    CREATE TABLESPACE catalog_text_ind_tables
    DATAFILE 'DATA_DIR/catalog_text_ind_tables_01.dbf' SIZE 2048M REUSE
  2. Connect to the database using the OIM schema.
  3. Create a storage preference using the commands below. Oracle recommends that you be familiar with the BASIC_STORAGE clause of Oracle Text and add more storage clauses if required. You can find more info on BASIC_STORAGE in the Oracle Text Reference document.

    Ctx_Ddl.Create_Preference('cat_storage', 'BASIC_STORAGE');
    catalog_text_ind_tables storage (initial 5M next 5M)');
    ctx_ddl.set_attribute('cat_storage', 'K_TABLE_CLAUSE','tablespace 
    catalog_text_ind_tables storage (initial 5M next 5M)');
    ctx_ddl.set_attribute('cat_storage', 'R_TABLE_CLAUSE','tablespace 
    catalog_text_ind_tables storage (initial 1M) lob (data) store as (cache)');
    ctx_ddl.set_attribute('cat_storage', 'N_TABLE_CLAUSE','tablespace 
    catalog_text_ind_tables storage (initial 1M)');
    ctx_ddl.set_attribute('cat_storage', 'I_INDEX_CLAUSE','tablespace 
    catalog_text_ind_tables storage (initial 1M) compress 2');
  4. Apply the new storage preference using the command below. Make sure the Text Index status is VALID after this step.

    ALTER INDEX CAT_TAGS rebuild parameters ('replace storage cat_storage');
  5. Verify that the tables above are moved to the new tablespace by querying USER_SEGMENTS table.

KEEP Pool Settings for Text Index

It is recommended that you put all the tables that make up the Text Index in the database KEEP pool to improve the performance of Access Request Catalog search. You must size the KEEP pool (DB_KEEP_CACHE_SIZE) correctly so that these Text Index tables and other OIM objects are retained in the KEEP pool.

  1. Connect to the database using the OIM schema.

  2. Compute the size of the Text Index using the query below and use that to set/adjust DB_KEEP_CACHE_SIZE accordingly.

    SELECT ctx_report.index_size('CAT_TAGS') FROM dual;
  3. Run the following commands as an OIM schema user to put the tables in the KEEP pool.

    ALTER INDEX DR$CAT_TAGS$X STORAGE (buffer_pool keep);
    ALTER TABLE DR$CAT_TAGS$R STORAGE (buffer_pool keep);
    ALTER TABLE DR$CAT_TAGS$R STORAGE (buffer_pool keep) MODIFY lob 
    (data) (STORAGE (buffer_pool keep));
    ALTER TABLE DR$CAT_TAGS$K STORAGE (buffer_pool keep);
    ALTER TABLE DR$CAT_TAGS$I STORAGE (buffer_pool keep);

Ongoing Maintenance for Oracle Text Index in OIM

Text Index Optimization

The Text Index can become fragmented due to ongoing "Catalog Synchronization." Regularly optimizing the Text Index removes the old data and minimizes the fragmentations, and can improve the search performance of Access Request Catalog.

For this purpose, OIM introduced the following Oracle Database scheduler jobs:


These jobs reside in the OIM database schema and are disabled by default. Oracle strongly recommends that you view these jobs, make schedule changes if needed, and enable them. When changing the schedule, make sure the new schedule is set on the same line as the default schedule.

FAST_OPTIMIZE_CAT_TAGS is meant to be running frequently-by default, it is scheduled to run once a day at 1am.

REBUILD_OPTIMIZE_CAT_TAGS, which does a full optimization and rebuilds the Text Index, is not meant to be running on a frequent basis. By default, it is scheduled to run every Sunday at 2am. Optimization may take a long time if your Text Index is large.

Here is how to change the schedule and/or enable these jobs:

  1. Make sure the default schedule (1am daily for FAST and every Sunday 2am for REBUILD) is acceptable to your environment. If not, change the schedule. (If you are not sure, keep the default schedule and change when needed.)

  2. Enable the jobs using these commands:


Note: Text Index optimization can be performed when the server is up and Access Request Catalog is being searched.

Gather Schema Stats for OIM

OIM Schema stats are to be gathered as per the guidelines for optimum SQL performance mentioned in FMW Tuning guide.

Sample command for DB Schema stats collection:

Exec dbms_stats.gather_schema_stats(OWNNAME=> 
options=>'GATHER AUTO',degree => 8,cascade=>TRUE);

OIM Catalog FAQs

Problem 1: Troubleshooting Oracle Text errors

This view displays the indexing errors for the current user and is queryable by all users.

The ctx_user_index_errors view will tell the rowid of the failed row in the source table. Use this rowid to update the row in the source table that failed. Specifically, perform a dummy update against the column that holds the text index (i.e., update table x set columnx = columnx where rowid = ?). This will generate a row in the ctx_user_pending view. Then perform ctx_ddl.sync_index, which will index the rows in the ctx_user_pending view.

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.

SELECT err_timestamp, err_text  FROM ctx_user_index_errors  ORDER BY err_timestamp

SELECT pnd_index_name, pnd_rowid, to_char( pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss'

        ) timestamp FROM ctx_user_pending;

Problem 2: Recreating the OIM catalog's Text Index

  1. Check the current status of the OIM index CAT_TAGS and make a note of it.

  2. Run this SQL against the OIM schema:

  3. Run the PL/SQL blocks below, in the order in which they are given, to drop and recreate the Text Index CAT_TAGS. Again, run them against the OIM schema.

    Drop Text Index
      P_DROPINDEX := 1; 
      P_CREATEINDEX := 0; 
    Recreate Text Index
      P_DROPINDEX := 0; 
      P_CREATEINDEX := 1; 
  4. Re-run the SQL in Step 1. You should see the output below:

    CAT_TAGS    CATALOG    VALID     sync (on commit) lexer 

    Problem 3: Catalog Search of entitlements with "_" (underscore) character are not displayed correctly

    A catalog search for items with an underscore returns incorrect results.

    1. Create several requestable items with names like A_Req01, A_Req02, B_Req01, C_Req01.
    2. After catalog sync is done, search the catalog for A_Req*.
    3. Results: Expected results would be: A_Req01 and A_Req02, but instead, all of the above items are returned.

    Cause: This is happening because, in Oracle Text, A, D, I, S and T are stopwords (which catalog search uses). Stopwords are not indexed.

    What that means is "A_Role1" is interpreted as "A followed by Role1" and since A is a stopword, this transforms to just "Role1" which matches any of "<something>_Role1". If you search for "B_Role1", this is interpreted as "B followed by Role1", and since B is NOT a stopword, this is treated as a phrase search and won't match any of the other strings.


    1. Drop the following characters/words from the default stoplist:

      a, d, i, s, t, II, non, one

      You can do that using the following command after login to DB using CTXSYS user:


      The query below confirms if these words have been removed or not. You can run the same query before running the drop command to see if they are there.

      select * from ctx_stopwords where spw_word in (<all of the above words>)
    2. Run the OIM_PKG_CATALOG_INDEX.OIM_SP_CreateDropCatalogIndex to drop the text index on TAGS column of CATALOG table.

      Pass the value for P_DROPINDEX parameter as 1.

    3. Run the OIM_PKG_CATALOG_INDEX.OIM_SP_CreateDropCatalogIndex to create the text index on TAGS column of CATALOG table.

      Pass the value for P_CREATEINDEX parameter as 1.

    Problem 4: Wild card search with huge data matching errors out



    This is default functionality in interMedia (Oracle Text). We cannot use a wild card (like % or *) in the search string. Oracle Text interrelates this as a wild card by itself and tries to return all products as if you were searching using just %. This causes the error from Oracle Text.

    In 11g the maximum number of distinct tokens (not rows) that a wildcard can match without producing an error is 50,000.


    We can use the wildcard_maxterms property of the wordlist if we decide to return more than 20,000 distinct tokens in 11g and 5,000 distinct tokens in 10gr2.


    Specify the maximum number of terms in a wildcard (%) expansion. Use this parameter to keep wildcard query performance within an acceptable limit. Oracle returns an error when the wildcard query expansion exceeds this number.

    To implement the solution, execute the following steps:

    1. Issue the following statements from SQL*Plus logged on as the Portal schema:

      ctx_ddl.set_attribute('<Pref_name>','WILDCARD_MAXTERMS', 50000);
      alter index WWSBR_THING_CTX_INDX rebuild parameters 
      ('replace metadata wordlist WWSBR_WDL');
    2. Repeat the search.

      Here, 15,000 is the maximum number if the database version is lower than, or 50,000 if the database version is or higher.

      The number can be set to a number between 5,000 (default value) and 15,000/50,000, based on the database version, to any number that enables a successful search. However, the higher you set this value, the more memory it will take.

    Problem 5: How to handle the break/escape character

    We can use the backslash "\" character to escape characters with special meaning and treat them as regular text.


    Sql> BEGIN
    CTX_DDL.SET_ATTRIBUTE ('my_lexer', 'PRINTJOINS', '~!@$%^&*()-_=+|;:,"./');
    CTX_DDL.SET_ATTRIBUTE ('my_lexer', 'SKIPJOINS', '`-=[];''\,./~!@#$%^&*()_+{}:"|?§'"½¼¾¤Φ£€©™®');

    Problem 6: How to collect additional data for Oracle Text Index


     select  CTX_REPORT.CREATE_INDEX_SCRIPT('CAT_TAGS') from dual; 
     select  CTX_REPORT.DESCRIBE_INDEX('CAT_TAGS') from dual;

    About the Author

    Lokesh Gupta is a Project Lead with Oracle Server Technology group for Oracle Identity Manager, where he focuses on issues related to database, enterprise performance and sizing engineering, and security.
    LinkedIn Facebook