Oracle | DBMS

Oracle DBMS_SQLHIST Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose SQL Query History tracking

AUTHID CURRENT_USER

Dependencies

DBMS_ASSERT	DUAL	V$SQL_HISTORY
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
DBMS_XPLAN	V$SESSION

Documented No

First Available 23.1

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

REPORT_SQL_HISTORY_DETAILS_JSON

REPORT_SQL_HISTORY_JSON

REPORT_SQL_HISTORY_DETAILS_JSON (new 23c)

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, 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 23c)

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, sql_type IN VARCHAR2 DEFAULT NULL, status IN VARCHAR2 DEFAULT NULL, duration_lower IN NUMBER DEFAULT NULL, duration_upper IN NUMBER DEFAULT NULL, top_n_count IN NUMBER DEFAULT NULL, report_level IN VARCHAR2 DEFAULT 'TYPICAL', base_path IN VARCHAR2 DEFAULT NULL, con_id IN NUMBER DEFAULT NULL) RETURN CLOB;

SELECT dbms_sqlhist.report_sql_history_json;

      

      REPORT_SQL_HISTORY_JSON

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

      []