Creating Custom Activity Reports Using APEX_ACTIVITY_LOG

The APEX_ACTIVITY_LOG view records all activity in a workspace, including developer activity and application runtime activity. You can use APEX_ACTIVITY_LOG to view to query all activity for the current workspace. For example, you can use this view to develop monitoring reports within a specific application to provide real-time performance statistics.

Table 17-1 describes the columns in the APEX_ACTIVITY_LOG view.

Table 17-1 Columns in APEX_ACTIVITY_LOG

Column	Type	Description
`time_stamp`	DATE	Date and time that activity was logged at the end of the page view.
`component_type`	VARCHAR2(255)	Reserved for future use.
`component_name`	VARCHAR2(255)	Reserved for future use.
`component_attribute`	VARCHAR2(4000)	Title of page.
`information`	VARCHAR2(4000)	Reserved for future use.
`elap`	NUMBER	Elapsed time of page view in seconds.
`num_rows`	NUMBER	Number of rows processed on page.
`userid`	VARCHAR2(255)	User ID performing page view.
`ip_address`	VARCHAR2(4000)	IP address of client.
`ir_report_id`	NUMBER	Interactive report ID
`ir_search`	VARCHAR2	Interactive report search criteria entered by users.
`user_agent`	VARCHAR2(4000)	web browser user agent of client.
`flow_id`	NUMBER	Application ID.
`step_id`	NUMBER	Page number.
`session_id`	NUMBER	Oracle Application Express session identifier.
`sqlerrm`	VARCHAR2(4000)	SQL Error message.
`sqlerrm_component_type`	VARCHAR2(255)	Reserved for future use.
`sqlerrm_component_name`	VARCHAR2(255)	Reserved for future use.

To conserve space in the activity log, only the first log entry of each unique session contains the IP address and web browser user agent.

The following example demonstrates how to create a report that displays the total number of page views and the average page view time in the past 24 hours for application 9529, and grouped by userid:

SELECT COUNT(*), AVG(elap), userid
    FROM APEX_ACTIVITY_LOG
 WHERE time_stamp > (SYSDATE-1)
   AND flow_id = 9529
GROUP BY userid

Keep in mind that logging of activity in an Oracle Application Express instance is rotated between two different log tables. Because there are two log tables, logging information is only as current as the oldest available entry in the logs. To persist your application specific log information for all time, you must either copy the log information into your own application table or implement logging directly in your application.