Oracle DBMS_SQLDIAG Built-In Package
Versions 11.1.0.6 - 19.3

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 whereby SQL can be hinted without requiring that the person or session altering the SQL statement have access to the source code and 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 perform the 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

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
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.

SQL> SELECT /* CREATE_PATCH1 */ COUNT(*), MAX(siid)
  2  FROM uwclass.serv_inst
  3  WHERE srvr_id = :srvrid;

  COUNT(*) MAX(SIID)
---------- ----------
        22   3714862

SQL> SELECT sql_id, sql_text
  2  FROM v$sqlarea
  3  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


SQL> SELECT is_bind_aware
  2  FROM v$sql
  3  WHERE sql_id = '0taz20gu81tvd';

I
-
N

SQL> DECLARE
  2   stxt CLOB := 'SELECT /* CREATE_PATCH2 */ COUNT(*), MAX(siid) ' ||
                   'FROM uwclass.serv_inst WHERE srvr_id = :srvrid';
  3   htxt CLOB := 'BIND_AWARE';
  4   retVal VARCHAR2(60);
  5  BEGIN
  6    retVal := sys.dbms_sqldiag.create_sql_patch(stxt, htxt);
  7    dbms_output.put_line(retVal);
  8  END;
  9  /
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 (new 18c)
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');

Related Topics
DBMS_ADVISOR
DBMS_ADVANCED_REWRITE
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL1
DBMS_SQLTUNE_UTIL2