Skip to Main Content
Ted Struik - Oracle
Home
Ted Struik - Oracle
Search
Site Map
Statistics
Page Errors
Apex
General
Autocomplete
Conditions
Errors
Items used in PL/SQL
Validations
Dynamic Actions
Logs
Version
App-wide Items
Error Handling
Apex Views
Apex Upgrade 4.2 to 5.1
Database Dependencies
Show Source Code
URLs
Authorization
Modal Dialogs
Apex Office Print (AOP)
Apex-Sert
Reports
Checkboxes
Download
Errors
Nowrap Columns
Substitution Strings
Use IR results in pl/sql
Clickable Row
Row Highlight
Translate Filters etc.
Bar Charts
Column Usage
Reset Pagination
Hide IR Actions menu items
Get Classic Report query
Interactive Grid
ORA-20987
ERR-1002
Copying Text
IG Info
Images & Fonts
Standard Icons & Fonts
WebDav
Font Awesome
Icons/image popup LOV
Lists
Hierarchy
LOVs
Definition Bug
Set 1-item LOV to Disabled
With Clause Errors
Modal LOV Plugin
HTML / CSS / JavaScript
Vertical Align
Legend CSS
Geolocation
Apex.confirm
Pie Charts
Codemirror
Theme Roller
Catching Key Strokes
Browser Cache
Locale
RequireJS
Collapsible Regions
Import / Export
Dynamic Translations
Translated Pages
APEXExport (java)
apex_export (pl/sql)
Component Settings
Charts
Translate Charts
[Reports] Bar Charts
Shuttles
Shuttle Sorting
Disable Shuttle Items
Menus
Accordion-Like
Scroll bug
Authorization
Session State
About Session State
Rollback & Commit
Performance of v()
Apex Clone Session
Request
Collections
ORA-20101 & ORA-20104
ORA-00001
Max Row Count
(PL)SQL
External Tables
SQL Plus
Conditions
Long
ORDImage
Date & Timestamp
CGI environment
Sys_context
Rounding percentages
Apex_string.split in SQL
Dbms_metadata
Instr
Regular Expressions
Rollback to Savepoint
Use table aliases
(Pipelined) Bulk Collect
JSON
Source Code Search (PL/SQL)
Source Code Search (SQL)
Format Models
Pipelined Table Functions
Hint no_unnest
Listagg
DBA
Backup & Restore
Users
Export & Import
DBMS Jobs
Tablespaces
Table Size
Flush cache
Access Control List (ACL)
Locks
Java
Constraints
Object Dependencies
Package Dependencies 1
Package Dependencies 2
Grants
SQL History
Startup Errors
ORDS
Miscellaneous
Holidays
E-mail
NLExtract
Windows
VPN
VMware
Links
Google
Visualization Orgchart
Hide Search Results
Reverse Proxy
To-do List
Cytoscape
Graphics
Upscaling
Amazon Prices
Domoticz
DDoS
Hobbies
Motorcycles
'95 Kawasaki ZZR 600
'94 Kawasaki ZZR 1100
'91 Honda CBR 600 F2
'95 Kawasaki ZZR 1100
'99 Honda CBR 1100 XX
'00 Honda CBR 1100 XX
'02 Kawasaki ZX-12R (NL)
'05 Ducati 999 (NL)
'08 Honda Fireblade
'07 Suzuki Bandit 1250A
'18 Suzuki GSX-S 1000 F
MotoGP
Garfield
Lyrics
Skydive
Woodworking
Pantorouter
16 inch Bandsaw
Introduction
Frame
Wheels
Wheel mounts
Blade guides
Trunions and table
Enclosure
Alignment and more
Box Joint Jig
Foldable Sawhorses
Strip Sander XL
Photography
Sony RX10 IV
Search For
apex_workspace_activity_log & apex_workspace_access_log
Combining these 2 Apex views gives a nice overview of user activity.
Grouped per application/user, order by date/page_id:
select wag.application_id , wag.application_name , wag.apex_user , wag.page_id , wag.page_name , wag.view_date , wag.elapsed_time , to_char(wag.rows_queried) as result , wag.ip_address , wag.agent , wag.apex_session_id , wag.error_message , wag.error_on_component_type , wag.error_on_component_name from apex_workspace_activity_log wag where wag.application_id not between 4000 and 4999 -- exclude dev builder et al and wag.page_id not in (0, 101) -- exclude global and login pages union all select wag2.application_id as application_id , wag2.application_name as application_name , wag2.user_name as apex_user , null as page_id , null as page_name , wag2.access_date as view_date , null as elapsed_time , wag2.authentication_result as result , wag2.ip_address as ip_address , null as agent , null as apex_session_id , null as error_message , null as error_on_component_type , null as error_on_component_name from apex_workspace_access_log wag2 where wag2.application_id not between 4000 and 4999 -- exclude dev builder et al and wag2.authentication_result = 'AUTH_SUCCESS' order by application_id , apex_user , view_date , page_id nulls first
Ordered by date/application/user/page_id:
with acc as ( select a3.* , nvl( lead (a3.access_date, 1) over (partition by a3.workspace, a3.application_id, a3.user_name order by a3.access_date), sysdate) as access_date_next from ( select distinct a2.workspace , a2.application_id , a2.user_name , a2.access_date , a2.ip_address from apex_workspace_access_log a2 where a2.authentication_result = 'AUTH_SUCCESS' ) a3 ) select acc.access_date as acc_access_date , wag.view_date , wag.workspace , wag.application_id , wag.page_id , wag.apex_user , wag.apex_session_id , acc.ip_address , wag.error_message , wag.error_on_component_type , wag.error_on_component_name from apex_workspace_activity_log wag , acc where wag.workspace = acc.workspace and wag.application_id = acc.application_id and wag.apex_user = acc.user_name and wag.view_date between acc.access_date and acc.access_date_next and wag.application_id not between 4000 and 4999 -- exclude dev builder et al and wag.page_id not in (0, 101) -- exclude global and login pages order by acc.access_date desc , wag.view_date desc , wag.workspace , wag.application_id , wag.apex_user , wag.page_id nulls first
Storing Log Data
Martin Giffy D'Souza has a nice page on how to store the data from these views into tables, you can find it at
https://www.talkapex.com/2009/05/apex-logs-storing-log-data/
.
I used his suggestions recently, but since that page is over 10 years old (!), a few little things have changed since way back then :-)
CREATE TABLE tapex_workspace_activity_log AS SELECT * FROM apex_workspace_activity_log;
will raise an exception "ORA-01723: zero-length columns are not allowed" in newer Apex versions.
The cause is the application_info column, if you exclude that column (by listing all other columns manually) it will work.
I wouldn't use the "left join" constructions in the insert statements. Multiple entries in the same second can (and therefore will) happen, so you're going to miss some data.
Instead I would query the max(access_date) from my table, and then use something like "where x.access_date > max_access-date". Not bulletproof either, but better; you might still loose entries in the same second of the insert, but that's not very likely.
Same goes for the activity log; but note that newer Apex versions contain a view_timestamp column, which is a better option.
And finally, if your log table is located in another schema than the Apex application(s) you want to monitor, check out this Oracle document on the APEX_ADMINISTRATOR_READ_ROLE role:
https://docs.oracle.com/database/apex-18.1/HTMDB/about-utilizing-database-reporting.htm#HTMDB29893
.
“If you are granted the APEX_ADMINISTRATOR_READ_ROLE or APEX_ADMINISTRATOR_ROLE then you can query across the entire instance, rather than just the workspace your schema user is associated with. You should grant APEX_ADMINISTRATOR_READ_ROLE to monitoring users, while APEX_ADMINISTRATOR_ROLE should be used for instance administrators, who manage instance parameters, workspaces, and so on.”