DBA - SQL History

Last Active TimeDescending	Parsing Schema Name	Sql Text	Module	Service	Elapsed Time	Rows Processed	Disk Reads	First Load Time
06-07-2020 20:13:25	APEX_200100	SELECT /+ result_cache / GRID_TYPE, GRID_MAX_COLUMNS, GRID_ALWAYS_USE_MAX_COLUMNS, GRID_HAS_COLUMN_SPAN, GRID_ALWAYS_EMIT, GRID_EMIT_EMPTY_LEADING_COLS, GRID_EMIT_EMPTY_TRAILING_COLS, GRID_DEFAULT_LABEL_COL_SPAN, GRID_TEMPLATE, GRID_ROW_TEMPLATE, GRID_COLUMN_TEMPLATE, GRID_FIRST_COLUMN_ATTRIBUTES, GRID_LAST_COLUMN_ATTRIBUTES, GRID_JAVASCRIPT_DEBUG_CODE FROM WWV_FLOW_TEMPLATES WHERE ID = :B3 AND FLOW_ID = :B2 AND SECURITY_GROUP_ID = :B1	TEDSTRUIK/APEX:APP 25384:1081	xepdb1	163818	2177	18	06-07-2020 01:07:03
06-07-2020 20:13:25	APEX_200100	INSERT INTO WWV_FLOW_WORKSHEET_COMPUTATION (FLOW_ID, PAGE_ID, WORKSHEET_ID, WEBSHEET_ID, REPORT_ID, DB_COLUMN_NAME, COLUMN_IDENTIFIER, COMPUTATION_EXPR, FORMAT_MASK, COLUMN_TYPE, COLUMN_LABEL, REPORT_LABEL) (SELECT :B5 , PAGE_ID, :B4 WORKSHEET_ID, WEBSHEET_ID, :B3 REPORT_ID, DB_COLUMN_NAME, COLUMN_IDENTIFIER, COMPUTATION_EXPR, FORMAT_MASK, COLUMN_TYPE, COLUMN_LABEL, REPORT_LABEL FROM WWV_FLOW_WORKSHEET_COMPUTATION WHERE REPORT_ID = :B2 AND SECURITY_GROUP_ID = :B1 )	TEDSTRUIK/APEX:APP 25384:1044	xepdb1	14599	0	0	06-07-2020 15:26:28
06-07-2020 20:13:25	APEX_200100	SELECT /+ result_cache / I.ID, TRUNC(I.FLOW_STEP_ID) PAGE_ID, I.NAME, I.IS_PERSISTENT, NVL(I.IS_REQUIRED, 'N') IS_REQUIRED, I.IS_PRIMARY_KEY, I.IS_QUERY_ONLY, I.ITEM_SEQUENCE, I.ITEM_PLUG_ID, I.ITEM_DEFAULT, I.ITEM_DEFAULT_TYPE, I.ITEM_SOURCE_PLUG_ID, I.SOURCE_DATA_TYPE, T.TEMPLATE_BODY1\|\|I.PROMPT\|\| T.TEMPLATE_BODY2 PROMPT, I.PROMPT PLAIN_LABEL, CASE WHEN INSTR( T.TEMPLATE_BODY1, '#LABEL_ID#' ) > 0 THEN I.NAME \|\| '_LABEL' END AS LABEL_ID, I.PLACEHOLDER, TRUNC(T.ID) TEMPLATE_ID, T.BEFORE_ITEM, T.AFTER_ITEM, T.BEFORE_ELEMENT, T.AFTER_ELEMENT, T.HELP_LINK, T.INLINE_HELP_TEXT TEMPLATE_INLINE_HELP_TEXT, T.ERROR_TEMPLATE, I.PRE_ELEMENT_TEXT, I.POST_ELEMENT_TEXT, T.ITEM_PRE_TEXT PRE_ELEMENT_TEMPLATE, T.ITEM_POST_TEXT POST_ELEMENT_TEMPLATE, I.FORMAT_MASK, I.ITEM_CSS_CLASSES, I.ITEM_ICON_CSS_CLASSES, I.ITEM_TEMPLATE_OPTIONS, T.DEFAULT_TEMPLATE_OPTIONS, I.SOURCE, I.SOURCE_TYPE, I.SOURCE_POST_COMPUTATION, I.DISPLAY_AS, I.NAMED_LOV, I.LOV, I.LOV_DISPLAY_EXTRA, I.LOV_DISPLAY_NULL, I.LOV_NULL_TEXT	TEDSTRUIK/APEX:APP 25384:1081	xepdb1	555023	4100	117	06-07-2020 01:07:03
06-07-2020 20:13:25	APEX_200100	SELECT 'Y' IS_PAGE_ITEM, ID, SECURITY_GROUP_ID, :B4 SCOPE, NAME, PROMPT, DATA_TYPE, RESTRICTED_CHARACTERS, IS_PERSISTENT, ENCRYPT_SESSION_STATE_YN, DISPLAY_AS, ATTRIBUTE_01, ATTRIBUTE_02, ATTRIBUTE_03, PROTECTION_LEVEL, ESCAPE_ON_HTTP_OUTPUT FROM WWV_FLOW_STEP_ITEMS WHERE FLOW_ID = :B3 AND FLOW_STEP_ID = :B2 AND SECURITY_GROUP_ID = :B1	TEDSTRUIK/APEX:APP 25384:1036	xepdb1	58842	186	21	06-07-2020 02:08:38
06-07-2020 20:13:25	APEX_200100	SELECT ID, ICON_IMAGE, ICON_SUBTEXT, REPLACE( REPLACE( REPLACE(ICON_TARGET, '&LOGOUT_URL.','&LOGOUT_UR$.'), '&LOGOUT_URL','&LOGOUT_UR$.'), '&LOGOUT_UR$.','&LOGOUT_URL.'), ICON_IMAGE_ALT, ICON_HEIGHT, ICON_WIDTH, ICON_BAR_DISP_COND, ICON_BAR_DISP_COND_TYPE, ICON_BAR_FLOW_COND_INSTR, NVL(BEGINS_ON_NEW_LINE,'NO') BEGINS_ON_NEW_LINE, NVL(CELL_COLSPAN,'1') CELL_COLSPAN, ONCLICK, SECURITY_SCHEME, DECODE(NVL(NAV_ENTRY_IS_FEEDBACK_YN,'N'), 'Y',1,0) IS_NAV FROM WWV_FLOW_ICON_BAR WHERE FLOW_ID = :B3 AND (REQUIRED_PATCH IS NULL OR (REQUIRED_PATCH > 0 AND (:B2 IS NULL OR INSTR(:B2 ,':'\|\|TO_CHAR(REQUIRED_PATCH)\|\|':') = 0) ) OR (REQUIRED_PATCH < 0 AND (:B1 IS NULL OR INSTR(:B1 ,':'\|\|TO_CHAR(0-REQUIRED_PATCH)\|\|':') = 0) ) ) ORDER BY ICON_SEQUENCE, ID	TEDSTRUIK/APEX:APP 25384:1081	xepdb1	369386	2175	11	06-07-2020 01:07:03

1 - 5 of 164

My take on a query to list historical SQL statements. Play around with the where clause to change this to your needs.

Either run this query as sys/system, or give the user this privilege:

grant select on v_$sql to tedstruik;

Note that you can only select from v$sql; you cannot grant to v$sql or any other fixed views. So here we need to give the grant directly to the base table v_$sql instead.

The Dynamic Action in the Code section below is included as an example of how to get Codemirror to work in reports - more info on my Codemirror page. It is not needed for the rest of this example, but it does make it look nice.

Region

Identification
	Sequence	20
	Title	SQL History
	Type	Interactive Report
Source
	Source Type	DYNAMIC_QUERY
	Region Source	with s as ( select 'SelecT' as search_text from dual ) select v.last_active_time , v.parsing_schema_name , v.sql_text --, v.sql_fulltext -- clob containing full SQL , v.module , v.service , v.elapsed_time , v.rows_processed , v.disk_reads , to_date(v.first_load_time, 'YYYY-MM-DD/HH24:MI:SS') as first_load_time from s , v$sql v where ( dbms_lob.instr (v.sql_fulltext , lower(s.search_text) ) <> 0 or dbms_lob.instr (v.sql_fulltext , upper(s.search_text) ) <> 0 or instr (lower(v.sql_text), lower(s.search_text) ) <> 0 ) -- not elegant, and won't find 'SelecT' if the query > 1000 characters, but the best we can do here (and still get some performance) and v.last_active_time >= trunc(sysdate - 1) -- limit the date range and v.parsing_schema_name not in ('SYSTEM', 'SYS', 'SYSMAN', 'DBSNMP', 'CTXSYS') -- exclude some schemas and instr(v.module, 'APEX:APP 25384') <> 0 -- find activity for this Apex app

Dynamic Action

Identification
Identification	Name	codemirror
Execution Options
Execution Options	Sequence	10
When
	Event	After Refresh
	Selection Type	Region
	Region	SQL History
Advanced
Advanced	Event Scope	Static
True Action
True Action
Identification
Identification	Action	Execute JavaScript Code
Settings
Settings	Code	var arrayLength = this.affectedElements.length; var p_mode = ""; var p_readOnly = true; for (var i = 0; i < arrayLength; i++) { if (this.affectedElements[i].classList.contains("codemirror-plsql")) {p_mode = "text/x-plsql"} else if (this.affectedElements[i].classList.contains("codemirror-sql")) {p_mode = "text/x-sql"} else if (this.affectedElements[i].classList.contains("codemirror-javascript")) {p_mode = "text/javascript"} else if (this.affectedElements[i].classList.contains("codemirror-html")) {p_mode = "text/html"} else if (this.affectedElements[i].classList.contains("codemirror-css")) {p_mode = "text/css"} else if (this.affectedElements[i].classList.contains("codemirror-sh")) {p_mode = "text/x-sh"} else if (this.affectedElements[i].classList.contains("codemirror-shell")) {p_mode = "text/x-sh"} else if (this.affectedElements[i].classList.contains("codemirror-xml")) {p_mode = "application/xml"} else if (this.affectedElements[i].classList.contains("codemirror-java")) {p_mode = "text/x-java"} else {p_mode = ""} ; if (this.affectedElements[i].classList.contains("codemirror-edit")) {p_readOnly = false} else {p_readOnly = true} ; if (this.affectedElements[i].classList.contains("codemirror-nowrap")) {p_lineWrapping = false} else {p_lineWrapping = true} ; codemirrorFromTextArea ( this.affectedElements[i] , p_mode , p_readOnly , p_lineWrapping ) ; }
Execution Options
	Sequence	10
	Fire On Initialization	No
Affected Elements
	Selection Type	jQuery Selector
	jQuery Selector	.a-IRR-table [class*="codemirror-"]

SQL History

Info

Code

Region

Dynamic Action