3 Replies Latest reply: May 16, 2013 1:16 PM by user109389 RSS

    View existing policies

    user109389
      Once a policy is created as follows:
      BEGIN
        CTX_DDL.CREATE_POLICY(
           policy_name     => 'TEST_EXTRACT_POLICY',
           filter          => 'CTXSYS.AUTO_FILTER',
           section_group     => 'CTXSYS.NUL_SECTION_GROUP');
      END;
      /
      What view can I look at to determine which policies have been created in the database? I looked in the following views, but they did not return any rows.
      DR$POLICY_TAB
      CTX_EXTRACT_POLICIES
      CTX_EXTRACT_POLICY_VALUES
      CTX_USER_EXTRACT_POLICIES
        • 1. Re: View existing policies
          Barbara Boehmer
          The following demonstrates how to find the tables and views, then selects from two of them.
          SCOTT@orcl_11gR2> -- create test_extract_policy:
          SCOTT@orcl_11gR2> BEGIN
            2    CTX_DDL.CREATE_POLICY(
            3            policy_name     => 'TEST_EXTRACT_POLICY',
            4            filter          => 'CTXSYS.AUTO_FILTER',
            5            section_group   => 'CTXSYS.NULL_SECTION_GROUP',
            6            stoplist          => 'CTXSYS.EMPTY_STOPLIST');
            7  END;
            8  /
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> -- find tables and views that list test_extract_policy:
          SCOTT@orcl_11gR2> DECLARE
            2    v_count     NUMBER;
            3  BEGIN
            4    FOR r IN
            5        (SELECT table_name, column_name
            6         FROM      all_tab_columns
            7         WHERE  owner = 'CTXSYS')
            8    LOOP
            9        BEGIN
           10          v_count := 0;
           11          EXECUTE IMMEDIATE
           12            'SELECT COUNT(*) FROM CTXSYS."' || r.table_name ||
           13            '" WHERE "' || r.column_name || '" = ''TEST_EXTRACT_POLICY'''
           14            INTO v_count;
           15          IF v_count > 0 THEN
           16            DBMS_OUTPUT.PUT_LINE (v_count || ' ctxsys.' || r.table_name || '.' || r.column_name);
           17          END IF;
           18        EXCEPTION
           19          WHEN OTHERS THEN NULL;
           20        END;
           21    END LOOP;
           22  END;
           23  /
          1 ctxsys.DR$INDEX.IDX_NAME
          1 ctxsys.CTX_INDEXES.IDX_NAME
          7 ctxsys.CTX_INDEX_OBJECTS.IXO_INDEX_NAME
          4 ctxsys.CTX_INDEX_VALUES.IXV_INDEX_NAME
          1 ctxsys.CTX_USER_INDEXES.IDX_NAME
          7 ctxsys.CTX_USER_INDEX_OBJECTS.IXO_INDEX_NAME
          4 ctxsys.CTX_USER_INDEX_VALUES.IXV_INDEX_NAME
          
          PL/SQL procedure successfully completed.
          
          SCOTT@orcl_11gR2> -- select stored objects and values:
          SCOTT@orcl_11gR2> SELECT * FROM ctxsys.CTX_USER_INDEX_OBJECTS
            2  WHERE  IXO_INDEX_NAME = 'TEST_EXTRACT_POLICY'
            3  /
          
          IXO_INDEX_NAME                 IXO_CLASS                      IXO_OBJECT
          ------------------------------ ------------------------------ ------------------------------
          TEST_EXTRACT_POLICY            DATASTORE                      DIRECT_DATASTORE
          TEST_EXTRACT_POLICY            FILTER                         AUTO_FILTER
          TEST_EXTRACT_POLICY            SECTION_GROUP                  NULL_SECTION_GROUP
          TEST_EXTRACT_POLICY            LEXER                          BASIC_LEXER
          TEST_EXTRACT_POLICY            WORDLIST                       BASIC_WORDLIST
          TEST_EXTRACT_POLICY            STOPLIST                       BASIC_STOPLIST
          TEST_EXTRACT_POLICY            STORAGE                        BASIC_STORAGE
          
          7 rows selected.
          
          SCOTT@orcl_11gR2> COLUMN ixv_value FORMAT A30
          SCOTT@orcl_11gR2> SELECT * FROM ctxsys.CTX_USER_INDEX_VALUES
            2  WHERE  IXV_INDEX_NAME = 'TEST_EXTRACT_POLICY'
            3  /
          
          IXV_INDEX_NAME                 IXV_CLASS                      IXV_OBJECT
          ------------------------------ ------------------------------ ------------------------------
          IXV_ATTRIBUTE                  IXV_VALUE
          ------------------------------ ------------------------------
          TEST_EXTRACT_POLICY            WORDLIST                       BASIC_WORDLIST
          STEMMER                        ENGLISH
          
          TEST_EXTRACT_POLICY            WORDLIST                       BASIC_WORDLIST
          FUZZY_MATCH                    GENERIC
          
          TEST_EXTRACT_POLICY            STORAGE                        BASIC_STORAGE
          R_TABLE_CLAUSE                 lob (data) store as (cache)
          
          TEST_EXTRACT_POLICY            STORAGE                        BASIC_STORAGE
          I_INDEX_CLAUSE                 compress 2
          
          
          4 rows selected.
          • 2. Re: View existing policies
            Ebalthes-Oracle
            | What view can I look at to determine which policies have been created in the database?
            A policy is like an index but no CONTEXT index is created and every policy has an entry in ctx_user_indexes/ctx_indexes ... views
            SQL> select idx_owner, idx_name from ctxsys.ctx_indexes where idx_name like 'TEST_EXT%';

            IDX_OWNE IDX_NAME
            -------- --------------------------
            TEXTUSER TEST_EXTRACT_POLICY
            • 3. Re: View existing policies
              user109389
              Great. Thank you so much !