Skip to Main Content

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:
Ordered by date/application/user/page_id:

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 :-) 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.”