Examine DBMS_SEARCH Indexes Using Dictionary Views

In this example, you can see how to use various dictionary views to query information about your DBMS_SEARCH indexes, such as index name, corresponding schema owner name, data source name added to indexes, or corresponding source owner name and ID.

1. Connect to Oracle AI Database as a local user.

   1. Log in to SQL*Plus as the SYS user, connecting as SYSDBA:

      conn sys/password as sysdba

      CREATE TABLESPACE tbs1
      DATAFILE 'tbs5.dbf' SIZE 20G AUTOEXTEND ON
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO;

      SET ECHO ON
      SET FEEDBACK 1
      SET NUMWIDTH 10
      SET LINESIZE 80
      SET TRIMSPOOL ON
      SET TAB OFF
      SET PAGESIZE 10000
      SET LONG 10000

   2. Create a local user (docuser) and grant necessary privileges:

      DROP USER docuser cascade;

      GRANT DB_DEVELOPER_ROLE, DEFAULT TABLESPACE tbs1 quota unlimited on tbs1
      TO docuser IDENTIFIED BY password;

   3. Connect as the local user (docuser):

      CONN docuser/password

2. Create the departments and employees tables.

   CREATE TABLE departments
           ( department_id    NUMBER(5) primary key,
   
             department_name  VARCHAR2(30),
             manager_id       NUMBER(6)
           );

   CREATE TABLE employees
           ( employee_id    NUMBER(6) primary key,
   
             first_name     varchar2(4000),
             last_name      varchar2(4000),
             department_id  NUMBER(4)
           );

3. Populate the tables with data.

   INSERT INTO departments VALUES(10, 'Administration', 100);
   INSERT INTO employees VALUES  (100, 'Robert', 'Smith', 10);
   INSERT INTO employees VALUES  (101, 'James', 'Martin' ,10);
   INSERT INTO employees VALUES  (102, 'John', 'Doe', 10);
   
   commit;

4. Create a ubiquitous search index named MY_SEARCH_INDEX.

   EXEC DBMS_SEARCH.CREATE_INDEX('DOCUSER.MY_SEARCH_INDEX', NULL, 'JSON');

5. Add the tables as data sources to your index.

   exec dbms_search.add_source('DOCUSER.MY_SEARCH_INDEX', 'DEPARTMENTS');
   
   exec dbms_search.add_source('DOCUSER.MY_SEARCH_INDEX', 'EMPLOYEES');

6. View information about the DBMS_SEARCH indexes that are created in a user’s schema.

   select * from user_dbms_search_indexes;

   IDX_NAME
   
   ---------------------------------------------------
   MY_SEARCH_INDEX

7. View information about the data sources that are added to the DBMS_SEARCH indexes, created in a user’s schema.

   select * from user_dbms_search_index_sources;

   Here, the source type T implies a “table” source:

   IDX_NAME
   
   ---------------------------------------------------
   SRC_OWNER
   
   ---------------------------------------------------
   SRC_NAME
   
   ---------------------------------------------------
   SRC_TYPE   SRC_ID
   
   ---------  ----------
   MY_SEARCH_INDEX
   DOCUSER
   EMPLOYEES
   T       2
   
   IDX_NAME
   
   ----------------------------------------------------
   SRC_OWNER
   
   ----------------------------------------------------
   SRC_NAME
   
   ----------------------------------------------------
   SRC_TYPE   SRC_ID
   
   ---------  ----------
   MY_SEARCH_INDEX
   DOCUSER
   DEPARTMENTS
   T       1

8. View information about all existing DBMS_SEARCH indexes, corresponding to each index owner.

   select * from all_dbms_search_indexes;

   IDX_OWNER
   
   --------------------------------------------------
   IDX_NAME
   
   --------------------------------------------------
   DOCUSER
   MY_SEARCH_INDEX
   
   CUSTOMER
   CUSTOMER_SEARCH_INDEX

9. View information about all existing data sources added to various DBMS_SEARCH indexes, corresponding to each index owner.

   select * from all_dbms_search_index_sources;

   Here, the source types T and J imply “table” and “JSON Duality view” sources, respectively.

   IDX_OWNER
   
   --------------------------------------------------------
   IDX_NAME
   
   --------------------------------------------------------
   SRC_OWNER
   
   --------------------------------------------------------
   SRC_NAME
   
   --------------------------------------------------------
   SRC_TYPE   SRC_ID
   
   ---------  ----------
   DOCUSER
   MY_SEARCH_INDEX
   DOCUSER
   EMPLOYEES
   T          2
   
   IDX_OWNER
   
   ----------------------------------------------------------
   IDX_NAME
   
   ----------------------------------------------------------
   SRC_OWNER
   
   ----------------------------------------------------------
   SRC_NAME
   
   ----------------------------------------------------------
   SRC_TYPE   SRC_ID
   
   ---------  ----------
   DOCUSER
   MY_SEARCH_INDEX
   DOCUSER
   DEPARTMENTS
   T      1
   
   IDX_OWNER
   
   -----------------------------------------------------------
   IDX_NAME
   
   -----------------------------------------------------------
   SRC_OWNER
   
   -----------------------------------------------------------
   SRC_NAME
   
   -----------------------------------------------------------
   SRC_TYPE   SRC_ID
   
   ---------  ----------
   CUSTOMER
   CUSTOMER_SEARCH_INDEX
   CUSTOMER
   MY_EMP_VIEW
   J      1

Related Topics

• USER_DBMS_SEARCH_INDEXES

• USER_DBMS_SEARCH_INDEX_SOURCES

• ALL_DBMS_SEARCH_INDEXES

• ALL_DBMS_SEARCH_INDEX_SOURCES