DBA - SQL History

Last Active Time	Parsing Schema Name	Sql Text	Module	Service	Elapsed Time	Rows Processed	Disk Reads	First Load Time
21-01-2026 15:32:34	APEX_240200	SELECT /+ result_cache / D.PLACEHOLDER, D.HAS_GRID_SUPPORT, D.HAS_REGION_SUPPORT, D.HAS_ITEM_SUPPORT, D.HAS_BUTTON_SUPPORT, D.MAX_FIXED_GRID_COLUMNS, INSTR(T.HEADER_TEMPLATE\|\|T.BOX\|\|T.FOOTER_TEMPLATE, :B3 \|\|D.PLACEHOLDER\|\|:B3 ) PLACEHOLDER_POSITION FROM WWV_FLOW_TEMPLATES T, WWV_FLOW_PAGE_TMPL_DISP_POINTS D WHERE T.ID = :B2 AND T.SECURITY_GROUP_ID IN ( :B1 , 11, 12 ) AND D.PAGE_TEMPLATE_ID = T.ID AND D.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID	TEDSTRUIK/APEX:APP 25384:1178	freepdb1	86608	5356	2	21-01-2026 01:29:22
21-01-2026 15:32:34	APEX_240200	SELECT 'Y' IS_PAGE_ITEM, ITEM.ID, ITEM.SECURITY_GROUP_ID, :B4 SCOPE, ITEM.NAME, ITEM.PROMPT, ITEM.DATA_TYPE, ITEM.RESTRICTED_CHARACTERS, ITEM.IS_PERSISTENT, ITEM.ENCRYPT_SESSION_STATE_YN, ITEM.DISPLAY_AS, ITEM.ATTRIBUTES. "send_on_page_submit" AS SEND_ON_PAGE_SUBMIT, ITEM.PROTECTION_LEVEL, ITEM.ESCAPE_ON_HTTP_OUTPUT, ITEM.MULTI_VALUE_TYPE, ITEM.MULTI_VALUE_SEPARATOR FROM WWV_FLOW_STEP_ITEMS ITEM WHERE ITEM.FLOW_ID = :B3 AND ITEM.FLOW_STEP_ID = :B2 AND ITEM.SECURITY_GROUP_ID = :B1	TEDSTRUIK/APEX:APP 25384:1007	freepdb1	132319	860	8	21-01-2026 07:06:24
21-01-2026 15:32:34	APEX_240200	SELECT /+ result_cache / D.PLACEHOLDER, D.HAS_GRID_SUPPORT, D.HAS_REGION_SUPPORT, D.HAS_ITEM_SUPPORT, D.HAS_BUTTON_SUPPORT, D.MAX_FIXED_GRID_COLUMNS, CASE WHEN INSTR(T.TEMPLATE, '#SUB_REGIONS#') = 0 THEN INSTR(REPLACE(T.TEMPLATE, '#BODY#', '#BODY##SUB_REGIONS#'), :B3 \|\|D.PLACEHOLDER\|\|:B3 ) ELSE INSTR(T.TEMPLATE, :B3 \|\|D.PLACEHOLDER\|\|:B3 ) END AS PLACEHOLDER_POSITION, INSTR( T.TEMPLATE, '#BODY#' ) BODY_POSITION FROM WWV_FLOW_PAGE_PLUG_TEMPLATES T, WWV_FLOW_PLUG_TMPL_DISP_POINTS D WHERE T.ID = :B2 AND T.SECURITY_GROUP_ID IN ( :B1 , 11, 12 ) AND D.PLUG_TEMPLATE_ID = T.ID AND D.SECURITY_GROUP_ID = T.SECURITY_GROUP_ID	TEDSTRUIK/APEX:APP 25384:1	freepdb1	565744	10595	15	21-01-2026 01:29:22
21-01-2026 15:32:34	TEDSTRUIK	begin declare v_text varchar2(1000); begin select 'Last Update: ' \|\| to_char(p.last_updated_on, 'YYYY-MM-DD') \|\| ' <span style="opacity: 0; cursor: default;">oracle apex application express sql pl/sql tedstruik.nl tedstruik-oracle.nl</span>' into v_text from ted_apex_application_pages_v p where p.application_id = :APP_ID and p.page_id = :APP_PAGE_ID ; htp.p(v_text); exception when no_data_found then null; end; end;	TEDSTRUIK/APEX:APP 25384:1007	freepdb1	2582367	529	94	10-01-2026 01:10:08
21-01-2026 15:32:34	APEX_240200	SELECT /+result_cache/ ID, TRUNC(FLOW_STEP_ID) AS PAGE_ID, BUTTON_NAME, TRUNC(BUTTON_PLUG_ID) AS BUTTON_PLUG_ID, BUTTON_SEQUENCE, BUTTON_TEMPLATE_ID, BUTTON_TEMPLATE_OPTIONS, BUTTON_IS_HOT, BUTTON_IMAGE_ALT, CASE BUTTON_POSITION WHEN 'TEMPLATE_DEFAULT' THEN NVL(:B1 , 'TOP') ELSE BUTTON_POSITION END BUTTON_POSITION, BUTTON_ALIGNMENT, BUTTON_CSS_CLASSES, ICON_CSS_CLASSES, CASE WHEN LOWER(BUTTON_REDIRECT_URL) LIKE 'javascript:%' THEN SUBSTR(BUTTON_REDIRECT_URL, 12) ELSE BUTTON_REDIRECT_URL END BUTTON_REDIRECT_URL, CASE WHEN LOWER(BUTTON_REDIRECT_URL) LIKE 'javascript:%' THEN 'JAVASCRIPT' ELSE BUTTON_ACTION END BUTTON_ACTION, DATABASE_ACTION, NVL( BUTTON_EXECUTE_VALIDATIONS, 'Y' ) AS BUTTON_EXECUTE_VALIDATIONS, WARN_ON_UNSAVED_CHANGES, SHOW_PROCESSING, CONFIRM_ENABLED, CONFIRM_MESSAGE, CONFIRM_STYLE, BUTTON_CONDITION, BUTTON_CONDITION2, BUTTON_CONDITION_TYPE, BUTTON_CATTRIBUTES, SECURITY_SCHEME, CASE WHEN BUTTON_CATTRIBUTES IS NOT NULL AND REGEXP_LIKE( BUTTON_CATTRIBUTES, q'~(^\|\s\|"\|')id	TEDSTRUIK/APEX:APP 25384:1193	freepdb1	96180	103	11	21-01-2026 01:29:22

1 - 5 of 436

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
	Location	Local Database
	Query Type	SQL 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

Column Actions

Info

Code

Region

Dynamic Action