APEX: Who is doing what?

Ever wanted to know what a user in doing in Oracle APEX? Which pages he visited in his current session? What the values of his session variables are at this very moment? It’s possible! (I’m writing this while using APEX 5.1, but the info is also available in APEX 04.02 so the path to the option in APEX Development might differ somewhat from your APEX version.)

Where are Dashboards?

In the first place you can find a lot of information in the Oracle APEX Development environment. Go to “Monitor Activity” and then select “Active Sessions”.

Or open de “Dashboards”, Click on “Activities” and click on the Arrow right of “Recent Logins”.

The data shown in these APEX pages is accessible thru views that exist in the APEX_nnnnnn schema’s. Be aware that some of these views have an implied selection of a so-called security_group_id which is retrieved at runtime, so trying to see the data from SqlDeveloper will only work

  1. if you have the proper authorisations
  2. if you are logged-in as the user of the parsing schema.

If the parsing schema user has sufficient privileges you can ofcource access the data from APEX SQL Workshop when logging in to the proper workspace.

And the views you should look for are:

  • apex_050100.apex_workspace_access_log
    Shows all the login info of sessions that are still available in the running APEX-engine. These might not be all active sessions! Oracle APEX has it’s own rules for cleaning up sessions, and ofcourse you can use time-out and possible your own cleaning-up.
    This view does not contain an APEX session id.
  • apex_050100.apex_workspace_sessions
    Shows the current sessions and contains an APEX session id, which is important to be able to see the session variables. It also contains the session’s NLS settings like the territory.
  • apex_050100.wwv_flow_data_view
    This is the view where you can see in detail all the session variables of each session still available in the APEX runtime. For sake of security you should add a WHERE condition on the security_group_id, for instance  by limiting the rows to those where security_group_id = v(‘FLOW_SECURITY_GROUP_ID’);

These views should give you sufficient data to link it to your own user tables so you can add application info like full names, email and phonenumber in case you have to contact a certain user.

Regarding the implementation: Since the views you are referencing are APEX-owned views, you should consider building your own application-owned views on top of these APEX-views and only using those application-owned views. Then in case you have to upgrade to a new APEX version you only have to change these three application-owned views.

Comments are closed.

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy