Applies to:

Oracle Text - Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 14-Dec-2012***

Purpose

This script will provide output to show the overall health of the Oracle Text component for a particular instance.

Requirements

Oracle Text - Version: 9.2 to 11.2
Information in this document applies to any platform.

Configuring

Run the script as the SYSDBA user.

Instructions

Log in to the database using SQLPLUS.
Connect as the SYSDBA user.
Copy the script below into a text file and save it as text_hc.sql. Alternatively, download the attached text_hc.sql script.
Run the text_hc.sql script while saving output to a spool file:

SQL> SPOOL text_healthcheck.log
SQL> @text_hc.sql
SQL> SPOOL off
Review the output and provide the spool file to Support as necessary.

Caution

This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Script

SET LINESIZE 80;
SET SERVEROUTPUT ON FORMAT WRAP;
DECLARE
db_name     VARCHAR2(30);
db_version  VARCHAR2(30);
v_count     NUMBER := 0;
ctx_check   NUMBER := 0;
v_ver_dict  VARCHAR2(10);
v_ver_code  VARCHAR2(10);
v_dri_ver   VARCHAR2(10);
v_stmt VARCHAR2 (250);

CURSOR c_feat IS SELECT comp_name,status,version
   FROM dba_registry ORDER BY comp_id;
CURSOR c_inval IS SELECT * FROM dba_objects
   WHERE status !='VALID' AND OWNER = 'CTXSYS' ORDER BY object_type, object_name;
CURSOR c_other_objects IS SELECT owner,object_name,object_type,status
   FROM dba_objects
   WHERE owner = 'SYS'
     AND (object_name like 'CTX_%' or object_name like 'DRI%');
CURSOR c_count_obj IS SELECT object_type, count(*) count FROM dba_objects
   WHERE owner='CTXSYS' GROUP BY object_type ORDER BY 1;
CURSOR c_text_indexes IS
   SELECT c.*, i.status,i.domidx_status,i.domidx_opstatus
   FROM ctxsys.ctx_indexes c, dba_indexes i
   WHERE c.idx_owner = i.owner
     AND c.idx_name = i.index_name
   ORDER BY 2,3;
CURSOR c_errors IS SELECT * FROM ctxsys.ctx_index_errors
   ORDER BY err_timestamp DESC, err_index_owner, err_index_name;

PROCEDURE display_banner
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE( '**********************************************************************');
END display_banner;

BEGIN
DBMS_OUTPUT.ENABLE(900000);
SELECT name INTO db_name FROM v$database;
SELECT version INTO db_version FROM v$instance;

DBMS_OUTPUT.PUT_LINE( 'Oracle Text Health Check Tool              ' || TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('.');
display_banner;
DBMS_OUTPUT.PUT_LINE('Database:');
display_banner;
DBMS_OUTPUT.PUT_LINE ('--&gt name:                    ' || db_name );
DBMS_OUTPUT.PUT_LINE ('--&gt version:                 ' || db_version );
DBMS_OUTPUT.PUT_LINE ('.');

display_banner;
DBMS_OUTPUT.PUT_LINE ( 'Database Components:');
display_banner;
FOR v_feat IN c_feat LOOP
   DBMS_OUTPUT.PUT_LINE( '--&gt ' || rpad(v_feat.comp_name, 35) || ' '
     || rpad(v_feat.version, 10) || '   ' || rpad(v_feat.status, 10));
END LOOP;
DBMS_OUTPUT.PUT_LINE ('.');

display_banner;
DBMS_OUTPUT.PUT_LINE ('Oracle Text Status and Version:');
display_banner;
SELECT * INTO v_ver_dict, v_ver_code FROM ctxsys.ctx_version;
DBMS_OUTPUT.PUT_LINE('.. CTXSYS data dictionary version (Ver Dict) : '||
  v_ver_dict);
DBMS_OUTPUT.PUT_LINE('.. Linked-in code version (Ver Code) : '||
  v_ver_code);
SELECT substr(ctxsys.dri_version,1,10) INTO v_dri_ver FROM dual;
DBMS_OUTPUT.PUT_LINE('.. DRI Version : '|| v_dri_ver);
DBMS_OUTPUT.PUT_LINE ('.');

display_banner;
DBMS_OUTPUT.PUT_LINE ( 'Invalid Objects in CTXSYS Schema:');
display_banner;
FOR v_inval IN c_inval LOOP
   DBMS_OUTPUT.PUT_LINE( '.. CTXSYS.' || rpad(v_inval.object_name,30) ||
     ' -  ' || v_inval.object_type );
   v_count := c_inval%ROWCOUNT;
END LOOP;
IF v_count = 0 THEN
   DBMS_OUTPUT.PUT_LINE('There are no invalid objects in the CTXSYS schema');
   DBMS_OUTPUT.PUT_LINE ('.');
END IF;

display_banner;
DBMS_OUTPUT.PUT_LINE ( 'Possible Text-related Objects under the SYS schema:');
display_banner;
v_count := 0;
FOR v_other_objects IN c_other_objects LOOP
   DBMS_OUTPUT.PUT_LINE( '.. ' || v_other_objects.owner || '.' ||
    v_other_objects.object_name || ' -  ' || v_other_objects.object_type ||
    ' -  ' || v_other_objects.status );
   v_count := c_other_objects%ROWCOUNT;
END LOOP;
IF v_count = 0 THEN
   DBMS_OUTPUT.PUT_LINE('There are no Text-related Objects under the SYS schema');
ELSE
   DBMS_OUTPUT.PUT_LINE ('  ');
   DBMS_OUTPUT.PUT_LINE('If objects listed above are INVALID, see:');
   DBMS_OUTPUT.PUT_LINE(' Note 558894.1 - Invalid Oracle Text Object under user SYS');
   DBMS_OUTPUT.PUT_LINE('If Oracle Text is invalid, open a Service Request.');
END IF;
DBMS_OUTPUT.PUT_LINE ('.');

display_banner;
DBMS_OUTPUT.PUT_LINE ( 'Summary count of CTXSYS schema objects:');
display_banner;
FOR v_count_obj IN c_count_obj LOOP
   DBMS_OUTPUT.PUT_LINE('.. ' || rpad(v_count_obj.object_type,14) ||
                        '   ' || lpad(v_count_obj.count,3));
END LOOP;
DBMS_OUTPUT.PUT_LINE ('.');

display_banner;
DBMS_OUTPUT.PUT_LINE ('Text Indexes:');
display_banner;
v_count := 0;
FOR v_text_indexes IN c_text_indexes LOOP
DBMS_OUTPUT.PUT('.. ' || v_text_indexes.idx_owner ||
   '.' || v_text_indexes.idx_name || ' is ');
IF (v_text_indexes.status != 'VALID' OR
     v_text_indexes.domidx_status != 'VALID' OR
     v_text_indexes.domidx_opstatus != 'VALID') THEN
   DBMS_OUTPUT.PUT_LINE('INVALID');
   DBMS_OUTPUT.PUT_LINE('.... INDEX STATUS => '||v_text_indexes.status);
   DBMS_OUTPUT.PUT_LINE('.... DOMAIN INDEX STATUS => '||v_text_indexes.domidx_status);
   DBMS_OUTPUT.PUT_LINE('.... DOMAIN INDEX OPERATION STATUS => '
     ||v_text_indexes.domidx_opstatus);
ELSE
   DBMS_OUTPUT.PUT_LINE('VALID');
END IF;
DBMS_OUTPUT.PUT('.... Table: ' || v_text_indexes.idx_table_owner
   || '.' || v_text_indexes.idx_table);
DBMS_OUTPUT.PUT_LINE(', Indexed Column: ' || v_text_indexes.idx_text_name);
DBMS_OUTPUT.PUT_LINE('.... Index Type: ' || v_text_indexes.idx_type);
v_count := c_text_indexes%ROWCOUNT;
END LOOP;
IF v_count = 0 then
   DBMS_OUTPUT.PUT_LINE('There are no Text indexes');
END IF;
DBMS_OUTPUT.PUT_LINE ('.');

display_banner;
DBMS_OUTPUT.PUT_LINE ('Ten (10) most recent text index errors (ctx_index_errors):');
display_banner;
v_count := 0;
FOR v_errors IN c_errors LOOP
   EXIT WHEN (c_errors%NOTFOUND) OR (c_errors%ROWCOUNT > 9);
   DBMS_OUTPUT.PUT_LINE(to_char(v_errors.ERR_TIMESTAMP,'Dy Mon DD HH24:MI:SS YYYY'));
   DBMS_OUTPUT.PUT_LINE('.. Index name: ' || v_errors.err_index_owner
     || '.' || v_errors.err_index_name || '     Rowid: ' || v_errors.err_textkey);
   DBMS_OUTPUT.PUT_LINE('.. Error: ');
   DBMS_OUTPUT.PUT_LINE('   '||
     rtrim(replace(v_errors.err_text,chr(10),chr(10)||'   '),chr(10)||'   '));
   v_count := c_errors%ROWCOUNT;
END LOOP;
IF v_count = 0 THEN
   DBMS_OUTPUT.PUT_LINE('There are no errors logged in CTX_INDEX_ERRORS');
END IF;
DBMS_OUTPUT.PUT_LINE ('.');

display_banner;
DBMS_OUTPUT.PUT_LINE ('Testing Text Index Creation:');
display_banner;
-- Create text_healthcheck user
SELECT COUNT (1) INTO v_count FROM dba_users
  WHERE username = 'TEXT_HEALTHCHECK';
IF v_count != 0 THEN
  DBMS_OUTPUT.PUT_LINE ('..Dropping user TEXT_HEALTHCHECK');
  EXECUTE IMMEDIATE ('DROP USER text_healthcheck CASCADE');
  DBMS_OUTPUT.PUT_LINE ('....User TEXT_HEALTHCHECK dropped successfully');
END IF;
DBMS_OUTPUT.PUT_LINE ('..Creating user TEXT_HEALTHCHECK');
v_stmt := 'GRANT connect,resource,ctxapp TO text_healthcheck IDENTIFIED BY zexz_healthcheck';
EXECUTE IMMEDIATE (v_stmt);
DBMS_OUTPUT.PUT_LINE ('....User TEXT_HEALTHCHECK created successfully');
-- Create context index
DBMS_OUTPUT.PUT_LINE ('..Testing creation of Text index type CONTEXT');
v_stmt :=
     'CREATE TABLE text_healthcheck.text_hc_tab (quick_id NUMBER '
   || 'constraint text_hc_pk PRIMARY KEY, '
   || 'text VARCHAR2(80))';
DBMS_OUTPUT.PUT_LINE('....Creating table TEXT_HC_TAB');
EXECUTE IMMEDIATE(v_stmt);
DBMS_OUTPUT.PUT_LINE('....Inserting test data');
v_stmt :=
      'INSERT INTO text_healthcheck.text_hc_tab VALUES (1,'
   || '''The cat sat on the mat'')';
EXECUTE IMMEDIATE(v_stmt);
v_stmt :=
      'INSERT INTO text_healthcheck.text_hc_tab VALUES (2,'
   || '''The quick brown fox jumps over the lazy dog'')';
EXECUTE IMMEDIATE(v_stmt);
EXECUTE IMMEDIATE('COMMIT');
v_stmt :=
      'CREATE INDEX text_healthcheck.text_hc_idx '
   || 'ON text_healthcheck.text_hc_tab(text) INDEXTYPE IS CTXSYS.CONTEXT';
DBMS_OUTPUT.PUT_LINE('....Creating text index TEXT_HC_IDX');
EXECUTE IMMEDIATE(v_stmt);
DBMS_OUTPUT.PUT_LINE ('....Text index TEXT_HC_IDX created successfully');
DBMS_OUTPUT.PUT_LINE ('  ');
DBMS_OUTPUT.PUT_LINE ('..Dropping user TEXT_HEALTHCHECK');
EXECUTE IMMEDIATE ('DROP USER text_healthcheck CASCADE');
DBMS_OUTPUT.PUT_LINE ('....User TEXT_HEALTHCHECK dropped successfully');
DBMS_OUTPUT.PUT_LINE ('  ');
DBMS_OUTPUT.PUT_LINE ('Text Index Creation Test complete');
DBMS_OUTPUT.PUT_LINE ('.');

display_banner;

EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT('....');
   DBMS_OUTPUT.PUT_LINE (SQLERRM);
   display_banner;

END;
/
SET SERVEROUTPUT OFF

Sample Output

Oracle Text Health Check Tool              05-12-2009 14:44:46
.
**********************************************************************
Database:
**********************************************************************
--&gt name:                    XDBTEST
--&gt version:                 10.2.0.4.0
.
**********************************************************************
Database Components:
**********************************************************************
--&gt OLAP Catalog                        10.2.0.4.0   VALID
--&gt OLAP Analytic Workspace             10.2.0.4.0   VALID
--&gt Oracle Database Catalog Views       10.2.0.4.0   VALID
--&gt Oracle Database Java Packages       10.2.0.4.0   VALID
--&gt Oracle Database Packages and Types  10.2.0.4.0   VALID
--&gt Oracle Text                         10.2.0.4.0   VALID
--&gt Oracle Enterprise Manager           10.2.0.4.0   VALID
--&gt Oracle Expression Filter            10.2.0.4.0   VALID
--&gt JServer JAVA Virtual Machine        10.2.0.4.0   VALID
--&gt Oracle Data Mining                  10.2.0.4.0   VALID
--&gt Oracle interMedia                   10.2.0.4.0   VALID
--&gt Oracle Workspace Manager            10.2.0.4.3   VALID
--&gt Oracle Rule Manager                 10.2.0.4.0   VALID
--&gt Spatial                             10.2.0.4.0   VALID
--&gt Oracle Ultra Search                 10.2.0.4.0   VALID
--&gt Oracle XML Database                 10.2.0.4.0   VALID
--&gt Oracle XDK                          10.2.0.4.0   VALID
--&gt Oracle OLAP API                     10.2.0.4.0   VALID
.
**********************************************************************
Oracle Text Status and Version:
**********************************************************************
.. CTXSYS data dictionary version (Ver Dict) : 10.2.0.4.0
.. Linked-in code version (Ver Code) : 10.2.0.4.0
.. DRI Version : 10.2.0.4.0
.
**********************************************************************
Invalid Objects in CTXSYS Schema:
**********************************************************************
There are no invalid objects in the CTXSYS schema
.
**********************************************************************
Invalid Text-related Objects owned by SYS:
**********************************************************************
There are no invalid Text-related Objects owned by SYS
.
**********************************************************************
Summary count of CTXSYS schema objects:
**********************************************************************
.. FUNCTION           2
.. INDEX             46
.. INDEXTYPE          4
.. LIBRARY            1
.. LOB                1
.. OPERATOR           6
.. PACKAGE           67
.. PACKAGE BODY      56
.. PROCEDURE          3
.. SEQUENCE           3
.. TABLE             37
.. TYPE              27
.. TYPE BODY          7
.. VIEW              54
.
**********************************************************************
Text Indexes:
**********************************************************************
.. APPUSER.TEST_INDEX is VALID
.... Table: APPUSER.TEST_TABLE, Indexed Column: BLOB_DATA
.... Index Type: CONTEXT
.. CTXTEST.AUCTION_TITLEX is VALID
.... Table: CTXTEST.AUCTION, Indexed Column: TITLE
.... Index Type: CTXCAT
.. CTXTEST.BASICSECT_IDX is VALID
.... Table: CTXTEST.BASICSECT, Indexed Column: TEXT
.... Index Type: CONTEXT
.. CTXTEST.FUZZYMATCHX is VALID
.... Table: CTXTEST.FUZZYMATCH, Indexed Column: TEXT
.... Index Type: CONTEXT
.. NEWUSER.QUICK_TEXT is VALID
.... Table: NEWUSER.QUICK, Indexed Column: TEXT
.... Index Type: CONTEXT
.. SCOTT.CTX_QUICK is VALID
.... Table: SCOTT.QUICK, Indexed Column: TEXT
.... Index Type: CONTEXT
.
**********************************************************************
Ten (10) most recent text index errors (ctx_index_errors):
**********************************************************************
Wed Jan 07 15:27:21 2009
.. Index name: APPUSER.TEST_INDEX     Rowid: AAARoAAAFAAACe+AAB
.. Error:
   DRG-12604: execution of user datastore procedure has failed
   DRG-50857: oracle error in drsinopen
   ORA-28817: PL/SQL function returned an error.
   ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 90
   ORA-06512: at "SYS.DBMS_CRYPTO", line 51
   ORA-06512: at "APPUSER.TOOLKIT", line 26
   ORA-06512: at "CTXSYS.FILTER_FILE", line 16
   ORA-06512: at line 1
Wed Jan 07 15:27:21 2009
.. Index name: APPUSER.TEST_INDEX     Rowid: AAARoAAAFAAACe+AAA
.. Error:
   DRG-12604: execution of user datastore procedure has failed
   DRG-50857: oracle error in drsinopen
   ORA-28817: PL/SQL function returned an error.
   ORA-06512: at "SYS.DBMS_CRYPTO_FFI", line 90
   ORA-06512: at "SYS.DBMS_CRYPTO", line 51
   ORA-06512: at "APPUSER.TOOLKIT", line 26
   ORA-06512: at "CTXSYS.FILTER_FILE", line 16
   ORA-06512: at line 1
.
**********************************************************************
Testing Text Index Creation:
**********************************************************************
..Creating user TEXT_HEALTHCHECK
....User TEXT_HEALTHCHECK created successfully
..Testing creation of Text index type CONTEXT
....Creating table TEXT_HC_TAB
....Inserting test data
....Creating text index TEXT_HC_IDX
....Text index TEXT_HC_IDX created successfully

..Dropping user TEXT_HEALTHCHECK
....User TEXT_HEALTHCHECK dropped successfully

Text Index Creation Test complete
.
**********************************************************************

References

Attachments

Oracle Text Health Check script(8.48 KB)

Oracle Text Health Check (Doc ID 823649.1)

Applies to:

Purpose

Requirements

Configuring

Instructions

Caution

Script

Sample Output

References

Attachments

Related

Products

Keywords

Errors

相關文章