Oracle DBMS_SQLDIAG
Version 26ai

General Information
Library Note Morgan's Library Page Header
The best Oracle News for FY2026

Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
Purpose Provides an API to the SQL Diagnosability functionality used by the OEM Diagnostic Pack
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_SQLDIAG_LIB DBMS_XPLAN
ANYDATA DBMS_SQLPA DBMS_XPLAN_INTERNAL
DBMS_ADVISOR DBMS_SQLTCB_INTERNAL DUAL
DBMS_ASSERT DBMS_SQLTUNE PLITBLM
DBMS_AUTO_INDEX_INTERNAL 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_REDEFINITION DBMS_STATS V_$SQL
DBMS_SMB DBMS_STATS_INTERNAL XMLSEQUENCE
DBMS_SMB_INTERNAL DBMS_STATS_INTERNAL_AGG XMLTYPE
DBMS_SPM DBMS_SYS_ERROR XQSEQUENCE
DBMS_SQLDIAG_INTERNAL DBMS_VECTOR_INTERNAL  
Documented Yes
Exceptions
Error Code Reason
ORA-13830 SQL profile or patch with category DEFAULT already exists for this SQL statement
First Available 11.1
Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, UNSUPPORTED);
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 EXPORT_SQL_TESTCASE REPLAY_SQL_TESTCASE
ALTER_SQL_PATCH EXPORT_SQL_TESTCASE_DIR_BY_INC REPORT_DIAGNOSIS_TASK
CANCEL_DIAGNOSIS_TASK EXPORT_SQL_TESTCASE_DIR_BY_TXT REPORT_SQL
CREATE_DIAGNOSIS_TASK GETSQL RESET_DIAGNOSIS_TASK
CREATE_SQL_PATCH GET_FIX_CONTROL RESUME_DIAGNOSIS_TASK
CREATE_STGTAB_SQLPATCH IMPORT_SQL_TESTCASE SET_DIAGNOSIS_TASK_PARAMETER
DROP_DIAGNOSIS_TASK INCIDENTID_2_SQL SET_TCB_TRACING
DROP_SQL_PATCH INTERRUPT_DIAGNOSIS_TASK SQL_DIAGNOSE_AND_REPAIR
DUMP_TRACE LOAD_SQLSET_FROM_TCB UNPACK_STGTAB_SQLPATCH
EXECUTE_DIAGNOSIS_TASK PACK_STGTAB_SQLPATCH Demo
EXPLAIN_SQL_TESTCASE    
 
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;
TBD
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;
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.
 
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);
TBD
 
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;
/

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;
/
*
ORA-39087: directory name TCBORA$PIPE$008C07960001 is invalid

Note these are not the same as ADRCI indcident numbers: The docs not clear 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;
TBD
 
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
 
REPORT_SQL (new 23ai)
Generates an HTML diagnostic report for the specified SQL statement dbms_sqldiag.report_sql(
sql_id    IN VARCHAR2,
directory IN VARCHAR2 DEFAULT NULL,
level     IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;
TBD
 
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');

Related Topics
Built-in Functions
Built-in Packages
DBMS_ADVISOR
DBMS_SQLTUNE
DBMS_SQLTUNE_UTIL0
DBMS_SQLTUNE_UTIL1
DBMS_SQLTUNE_UTIL2
What's New In 21c
What's New In 26ai

Morgan's Library Page Footer
This site is maintained by Daniel Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2026 Daniel A. Morgan All Rights Reserved