Monday, May 25, 2009

APEX Logs: Storing Log Data

For those of you that use the APEX Logs you may not be aware that they store at best 4 weeks of data and at worst 2 weeks of data. They're actually 2 log tables, each one gets purged roughly every 2 weeks. For those of you who don't know about or use the APEX logs I suggest you read up on this.

You can get a list of the APEX logs by running the following query:


SELECT *
FROM apex_dictionary
WHERE column_id = 0
AND apex_view_name LIKE '%LOG%'


I strongly recommend that you explicitly store the log data into your own tables. I've encountered several instances where the APEX Logs have helped get me out of some sticky situations. It can also help you get some usage stats and page stats.

Here's how to keep a copy of the APEX log tables:

Note: You'll need to run this in each of your schemas that you have APEX applications in since the APEX Log Views only display application information who's parsing schema matches the current Oracle user

1- Create the APEX log tables

-- Login information
CREATE TABLE tapex_workspace_access_log
AS SELECT * FROM apex_workspace_access_log;

-- Page access information
CREATE TABLE tapex_workspace_activity_log
AS SELECT * FROM apex_workspace_activity_log;


2- Update the APEX log tables

Note: You may want to store this in a procedure and run as a nightly scheduled job so you don't forget to update the tables


INSERT INTO tapex_workspace_access_log
(workspace, application_id, application_name, user_name, authentication_method, application_schema_owner,
access_date, ip_address, authentication_result, custom_status_text, workspace_id)
SELECT alog.workspace,
alog.application_id,
alog.application_name,
alog.user_name,
alog.authentication_method,
alog.application_schema_owner,
alog.access_date,
alog.ip_address,
alog.authentication_result,
alog.custom_status_text,
alog.workspace_id
FROM apex_workspace_access_log alog,
tapex_workspace_access_log x
WHERE alog.access_date = x.access_date(+)
AND x.ROWID IS NULL
AND alog.application_schema_owner = USER;

INSERT INTO tapex_workspace_activity_log
(workspace, apex_user, application_id, application_name, application_schema_owner, page_id, page_name,
view_date, think_time, log_context, elapsed_time, rows_queried, ip_address, AGENT, apex_session_id,
error_message, error_on_component_type, error_on_component_name, page_view_mode, regions_from_cache,
workspace_id)
SELECT alog.workspace,
alog.apex_user,
alog.application_id,
alog.application_name,
alog.application_schema_owner,
alog.page_id,
alog.page_name,
alog.view_date,
alog.think_time,
alog.log_context,
alog.elapsed_time,
alog.rows_queried,
alog.ip_address,
alog.AGENT,
alog.apex_session_id,
alog.error_message,
alog.error_on_component_type,
alog.error_on_component_name,
alog.page_view_mode,
alog.regions_from_cache,
alog.workspace_id
FROM apex_workspace_activity_log alog,
tapex_workspace_activity_log x
WHERE alog.view_date = x.view_date(+)
AND alog.apex_session_id = x.apex_session_id(+)
AND alog.application_schema_owner = USER
AND x.ROWID IS NULL;

2 comments:

  1. hi martin very very useful info .....

    thanks for these posts

    thanks
    sagar

    ReplyDelete
  2. It seems that if you don't want to have a job scheduled in each schema you can remove "AND alog.application_schema_owner = USER"?

    And also if you want to run the job every night you might want to specify to copy the rows from the previous day only!

    Very helpful blog thanks.

    ReplyDelete