Go to primary content
Oracle® Application Express Application Builder User's Guide
Release 5.0
E39147-04
  Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
 
Next
Next
 

23.8 Creating Custom Activity Reports Using APEX_ACTIVITY_LOG

This section describes how to use the use APEX_ACTIVITY_LOG view to query activity for the current workspace.

23.8.1 Enabling Logging for an Application

You enable logging for an application on the Edit Application Definition page.

To edit the application definition:

  1. On the Workspace home page, click the Application Builder icon.

  2. Select an application.

  3. Click the Edit Application Properties button to the right of the application name.

    The Edit Application Definition page appears.

  4. Under Properties, locate Logging. Select Yes or No.

    When set to Yes, every page view is logged, enabling an administrator to monitor user activity for each application. Disabling logging may be advisable for high volume applications.


Tip:

You can only modify the Logging attribute if the Application Activity Logging attribute in Oracle Application Express Administration Services is set to Use Application Setting.


See Also:

"Enabling Application Activity" in Logging in Oracle Application Express Administration Guide

23.8.2 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 and 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 23-1 describes the columns in the APEX_ACTIVITY_LOG view.

Table 23-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.

23.8.3 APEX_ACTIVITY_LOG Sample Query

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 activity logging 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.