You use DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX to create a JSON search index on files in Object Storage. You can configure a refresh rate that indicates the frequency in minutes at which the index is refreshed for any new uploads or deletes. See Create a JSON Search Index on JSON Documents in Files for more information.

The following objects are created when you create a JSON search index:

• A JSON search index index_name on the Object Storage JSON files.

• A local table INDEX_NAME$TXTIDX. This table is the mapping table between your documents in the files in Object Storage and the JSON search index that is created in the database. See JSON Search Index Reference Table for more information.

• A view with the standard name INDEX_NAME. You can utilize the view to perform a search using JSON_TEXTCONTAINS. This view is created on top of the INDEX_NAME$TXTIDX table. See JSON Search Index Reference View for more information.

• An error log table with the standard suffix index_name$txtidx_err. See Error Log Table for more information.

The JSON search index creation operation is logged in the ALL_SCHEDULER_JOB_RUN_DETAILS view. See Monitor Text Index Creation for more information.

Autonomous Database supports JSON search index creation for a variety of text files containing JSON documents. For example, you can index JSON files in both compressed and uncompressed format, or choose a different JSON document delimiter. See DBMS_CLOUD Package for more information about the file handling options.

See Indexing with Oracle Text for more information.

Use DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX to create a JSON search index on the JSON files stored in Object Storage.

1. Create a credential object to access the source location.

   See CREATE_CREDENTIAL Procedure for more information.

2. Run the DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX procedure to create a JSON search index on the object storage files.

   
   BEGIN 
    DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX (
           credential_name => 'OBJ_STORE_CRED',
           location_uri    => 'https://objectstorage.us-phoenix-1.oraclecloud.com/n/namespace-string/b/bucketname/o/vcnlogs/',
           index_name      => 'VNCLOGS',
           format          =>  JSON_OBJECT ('json_index' value 'true', 'json_index_doc_len' value 12000, 'file_compression' value 'gzip')
   );
   END;
   /

   This example creates the following:

   • A JSON search index VNCLOGS on the Object Storage files located at the URI specified in the location_uri parameter.

   • A local table VNCLOGS$TXTIDX. See JSON Search Index Reference Table for more information.

   • A view with the name VNCLOGS. This view is created on top of the VNCLOGS$TXTIDX table. You can utilize the view to perform a search using JSON_TEXTCONTAINS. See JSON Search Index Reference View for more information.

   • An error log table with the name VNC$TXTIDX_ERR. The table contains a list of invalid JSON documents in your files. See Error Log Table for more information.

   After creating the JSON search index, you can query the VNCLOGS view. For example:

   SELECT JSON_QUERY (data, '$' returning CLOB pretty) AS RECORD 
    FROM vnclogs WHERE JSON_TEXTCONTAINS(file_line_json, '$.tuples', '{XXX.XXX.0.1.10.10.10.10.null.XXX_345}');
   

   This query returns the log details for the specified IP addresses in the JSON_TEXTCONTAINS condition. Note that the JSON Search index criteria is specified on the file_line_json column, the indexed column in your JSON search index table in the database, while the JSON documents are streamed directly from Object Storage, represented through the data column. The data column contains the content of the Object Storage file.

   See CREATE_EXTERNAL_TEXT_INDEX Procedure for more information.

Use the DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX procedure to drop a JSON search index on the JSON files stored in Object Storage.

BEGIN 
 DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX (
  index_name => 'VCNLOGS',
);
END;
/

When you run DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX the JSON search index creation operation is logged in the ALL_SCHEDULER_JOB_RUN_DETAILS view.

You can query the ALL_SCHEDULER_JOB_RUN_DETAILS view to obtain the status and any error reported by the index creation job.

The name of the DBMS_SCHEDULER job is derived from the INDEX_NAME parameter specified when you call DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX.

To query the ALL_SCHEDULER_JOB_RUN_DETAILS view, you must be logged in as the ADMIN user or have READ privilege on the ALL_SCHEDULER_JOB_RUN_DETAILS view.

For example, the following SELECT statement with a WHERE clause on job_name shows the run details for the job:

SELECT status, additional_info 
   FROM all_scheduler_job_run_details WHERE LOWER(job_name) = LOWER('index_name$JOB');

You can also query for the existence of an index creation scheduler job.

SELECT status 
    FROM all_scheduler_jobs where LOWER(job_name) = LOWER('index_name$JOB');

See CREATE_EXTERNAL_TEXT_INDEX Procedure for more information.

A local table is created within your database with a standard suffix INDEX_NAME$TXTIDX. This table is created internally when you run DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX.

You can query the INDEX_NAME$TXTIDX table to search for a string using the JSON_TEXTCONTAINS keyword. For example, when you call DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX procedure the INDEX_NAME value as VNCLOGS, this creates the VNCLOGS$TXTIDX JSON search index reference table.

SELECT * FROM (SELECT object_name, object_path, length, offset 
 FROM vnclogs$txtidx 
 WHERE JSON_TEXTCONTAINS (file_line_json, '$.tuples', '{XXX.XXX.0.1.10.10.10.10.null.XXX_345}') 
 ORDER BY mtime DESC) 
 WHERE rownum < 3;
 

This query provides internal information about where in your files in Object Storage the relevant documents are located. The query does not provide the actual documents.

SELECT * FROM (SELECT object_name, object_path, data
 FROM vnclogs
 WHERE JSON_TEXTCONTAINS (file_line_json, '$.tuples', '{XXX.XXX.0.1.10.10.10.10.null.XXX_345}')
 ORDER BY mtime DESC)
 WHERE rownum < 3;
 

A view with the name INDEX_NAME is created when you run DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX to create a JSON index.