Oracle | DBMS

Oracle DBMS_SQLHIST 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 SQL Query History tracking

AUTHID CURRENT_USER

Dependencies

DBMS_PERF	GV_$ALL_SQL_MONITOR	V$ALL_SQL_MONITOR
DBMS_SQLHIST_INTERNAL	ODCIVARCHAR2LIST	V$SESSION
DBMS_SQLTUNE_INTERNAL	PRVT_ADVISOR	V$SQL_HISTORY
DBMS_SQLTUNE_UTIL0	USER_ROLE_PRIVS	WRI$_REPRT_SQLHISTORY
DBMS_SQLTUNE_UTIL1	V$ALL_SQL_BIND_CAPTURE	XMLTYPE
DUAL

Documented No

First Available 23ai

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, READ_ONLY);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmssqlh.sql
{ORACLE_HOME}/rdbms/admin/prvtsqlh.plb

Subprograms

I_STRING_CONTAINS	REPORT_SQL_HISTORY_JSON
REPORT_SQL_HISTORY_DETAILS_JSON

I_STRING_CONTAINS (new 23ai)

Undocumented dbms_sqlhist.i_string_contains( str IN VARCHAR2, lst IN sys.odcivarchar2list) RETURN BOOLEAN;

TBD

REPORT_SQL_HISTORY_DETAILS_JSON (new 23ai)

Undocumented dbms_sqlhist.report_sql_history_details_json( sql_key IN NUMBER DEFAULT NULL, sql_id IN VARCHAR2 DEFAULT NULL, instance_id IN NUMBER DEFAULT NULL, sql_exec_id IN NUMBER DEFAULT NULL, sql_exec_start IN DATE DEFAULT NULL, last_active IN DATE DEFAULT NULL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', sql_plan_hash_value IN NUMBER DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, embedded_in_xml IN BOOLEAN DEFAULT NULL, con_name IN VARCHAR2 DEFAULT NULL, con_id IN NUMBER DEFAULT NULL) RETURN CLOB;

SELECT dbms_sqlhist.report_sql_history_details_json;

      

      REPORT_SQL_HISTORY_DETAILS_JSON

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

      {

       "USERNAME" : null,

       "STATUS" : "UNAVAILABLE",

       "DURATION" : null,

       "SQL_KEY" : null,

       "SQL_ID" : null,

       "ELAPSED_TIME" : null,

       "CPU_TIME" : null,

       "BUFFER_GETS" : null,

       "IO_INTERCONNECT_BYTES" : null,

       "PHYSICAL_READ_REQUESTS" : null,

       "PHYSICAL_READ_BYTES" : null,

       "PHYSICAL_WRITE_REQUESTS" : null,

       "PHYSICAL_WRITE_BYTES" : null,

       "PLSQL_EXEC_TIME" : null,

       "JAVA_EXEC_TIME" : null,

       "CLUSTER_WAIT_TIME" : null,

       "CONCURRENCY_WAIT_TIME" : null,

       "APPLICATION_WAIT_TIME" : null,

       "USER_IO_WAIT_TIME" : null,

       "IO_CELL_UNCOMPRESSED_BYTES" : null,

       "IO_CELL_OFFLOAD_ELIGIBLE_BYTES" : null,

       "SQL_TEXT" : null,

       "PLAN_HASH_VALUE" : null,

       "SQL_EXEC_ID" : null,

       "SQL_EXEC_START" : null,

       "LAST_ACTIVE_TIME" : null,

       "CHILD_NUMBER" : null,

       "SID" : null,

       "SESSION_SERIAL#" : null,

       "IS_FULL_SQLTEXT" : null,

       "ERROR_SIGNALLED" : null,

       "ERROR_NUMBER" : null,

       "ERROR_FACILITY" : null,

       "ERROR_MESSAGE" : null,

       "STATEMENT_TYPE" : null,

       "INSTANCE_ID" : null,

       "CON_ID" : null,

       "CON_NAME" : null,

       "PLAN_INFO" : [],

       "BIND_DATA" : []

      }

REPORT_SQL_HISTORY_JSON (new 23ai)

Undocumented dbms_sqlhist.report_sql_history_json( sql_id IN VARCHAR2 DEFAULT NULL, instance_id IN NUMBER DEFAULT NULL, begin_time IN DATE DEFAULT NULL, end_time IN DATE DEFAULT NULL, user_name IN VARCHAR2 DEFAULT NULL, sql_text IN VARCHAR2 DEFAULT NULL, session_id IN NUMBER DEFAULT NULL, session_serial IN NUMBER DEFAULT NULL, module_name IN VARCHAR2 DEFAULT NULL, action_name IN VARCHAR2 DEFAULT NULL, service_name IN VARCHAR2 DEFAULT NULL, sql_type IN VARCHAR2 DEFAULT NULL, status IN VARCHAR2 DEFAULT NULL, duration_lower IN NUMBER DEFAULT NULL, duration_upper IN NUMBER DEFAULT NULL, drop_lower IN NUMBER DEFAULT NULL, drop_upper IN NUMBER DEFAULT NULL, top_n_count IN NUMBER DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', con_name IN VARCHAR2 DEFAULT NULL, con_id IN NUMBER DEFAULT NULL) RETURN CLOB;

SELECT dbms_sqlhist.report_sql_history_json;

      

      REPORT_SQL_HISTORY_JSON

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

      []