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 is now availble. If you haven't you downloaded already: Why?

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

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

      []