Forum Stats

  • 3,828,047 Users
  • 2,260,848 Discussions
  • 7,897,437 Comments

Discussions

Categories

apex_workspace_activity_log purge rate

Scott Wesley
Scott Wesley Member Posts: 6,203 Gold Crown
edited Jan 17, 2017 9:34AM in APEX Discussions

Can we modify settings to hold workspace activity log data for 2 months?

Perhaps I'm misinterpreting what the switch interval is, but if I executed something like this

exec apex_instance_admin.SET_LOG_SWITCH_INTERVAL('ACTIVITY',7*8);

And I saw these results

select * from apex_050000.WWV_FLOW_LOG_NUMBERS;CURRENT_LOG_NUMBER LOG_SWITCHED_DATE     LOG_SWITCH_AFTER_DAYS LOG_NAME   ------------------ -------------------- ---------------------- -----------                 2 01/SEP/2016 00:00:00                     14 ACCESS                      1 18/AUG/2016 00:00:00                     56 ACTIVITY                    1 20/AUG/2015 00:00:00                     14 CLICKTHRU                   1 23/AUG/2016 00:00:00                     14 DEBUG

Shouldn't I see data from 18th August? Not data capped at 14 days old?

select sysdate,sysdate-14, min(view_date) from apex_workspace_activity_log;SYSDATE              SYSDATE-14           MIN(VIEW_DATE)     -------------------- -------------------- --------------------05/SEP/2016 13:17:01 22/AUG/2016 13:17:01 22/AUG/2016 13:34:54

Edit: Forgot to mention I'm on 5.0.1

(I often forget to practice what I preach, sorry)

I thought once upon a time a dba_job could have it's frequency changed, but now it seems it's all done in here: WWV_FLOW_MAINT.DAILY_MAINTENANCE ?

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • joelkallman-Oracle
    joelkallman-Oracle Senior Director, Software Development Posts: 4,082 Employee
    edited Jan 17, 2017 7:25AM Answer ✓

    Hi Scott,

    You're referring to two different views here, APEX_WORKSPACE_ACTIVITY_LOG and APEX_ACTIVITY_LOG.  APEX_ACTIVITY_LOG is simply on top of the "raw" underlying database views, but APEX_WORKSPACE_ACTIVITY_LOG is the preferred one.  When you see references to columns like FLOW_ID, you'll know that this is not the preferred view (even though the documentation references it!).

    1)  As far as APEX_WORKSPACE_ACTIVITY_LOG, you're correct - the WHERE clause of this was modified in 5.1 to remove the restriction to the last 14 days.

    2)  The "raw" view APEX_ACTIVITY_LOG never had this limit in the WHERE clause.

    3)  In APEX 5.1, the views APEX_ACTIVITY_LOG and APEX_WORKSPACE_ACTIVITY_LOG views will return between N and N*2 days of activity, where N is your log switch interval. 

    Joel

    · Share on TwitterShare on Facebook

Answers

  • joelkallman-Oracle
    joelkallman-Oracle Senior Director, Software Development Posts: 4,082 Employee
    edited Sep 6, 2016 9:43AM

    Hi Scott,

    When did you issue the call to apex_instance_admin.SET_LOG_SWITCH_INTERVAL?  Was it on or after 22-AUG?

    Joel

    · Share on TwitterShare on Facebook
  • Scott Wesley
    Scott Wesley Member Posts: 6,203 Gold Crown
    edited Oct 25, 2016 9:25PM

    Sorry for the delay.

    I believe it was changed in at least enough time for more than 2 weeks to pass.

    As it stands now I have

    CURRENT_LOG_NUMBER LOG_SWITCHED_DATE    LOG_SWITCH_AFTER_DAYS LOG_NAME ------------------- -------------------- --------------------- ----------                  1 13/OCT/2016 00:00:00                    14 ACCESS                     2 13/OCT/2016 00:00:00                    56 ACTIVITY                   1 20/AUG/2015 00:00:00                    14 CLICKTHRU                  1 18/OCT/2016 00:00:00                    14 DEBUG   SYSDATE              SYSDATE-14          MIN(VIEW_DATE)   -------------------- -------------------- --------------------26/OCT/2016 09:24:34 12/OCT/2016 09:24:34 12/OCT/2016 09:36:07

    So my live activity data remains at a rolling 2 weeks.

    Scott

    · Share on TwitterShare on Facebook
  • joelkallman-Oracle
    joelkallman-Oracle Senior Director, Software Development Posts: 4,082 Employee
    edited Oct 26, 2016 9:00AM

    Scott,

    Let's please wait to check it on 28-OCT, okay?

    Joel

    · Share on TwitterShare on Facebook
  • Scott Wesley
    Scott Wesley Member Posts: 6,203 Gold Crown
    edited Jan 16, 2017 9:58PM

    Hi Joel,

    I've finally had a chance to revisit this, now that it's been running for a while and I have a moment to consider it.

    I think I've found a/the bug, but it appears to be rectified in 5.1.

    Here is some feedback from the Manage Instance regarding the status of my logs

    switch_logs.png

    So instead of tables having the default switch of 14 days, this is successfully switching at 56 days, no problem there.

    The wwv_flow_activity_log view combines these two physical tables, which by default would have up to 28 days of combined data, now it has between 56 and 112.

    The apex_workspace_activity_log view definition in <= 5.0 has this filter, which I think is causing the disruption

    l.time_stamp > sysdate - 14

    This filter is missing/removed in 5.1

    So is it safe to say that based on the documentation here, and the analysis above

    Creating Custom Activity Reports Using APEX_ACTIVITY_LOG

    That the view will now return (by default) between 14 and 28 days of activity logs?

    Scott.

    · Share on TwitterShare on Facebook
  • joelkallman-Oracle
    joelkallman-Oracle Senior Director, Software Development Posts: 4,082 Employee
    edited Jan 17, 2017 7:25AM Answer ✓

    Hi Scott,

    You're referring to two different views here, APEX_WORKSPACE_ACTIVITY_LOG and APEX_ACTIVITY_LOG.  APEX_ACTIVITY_LOG is simply on top of the "raw" underlying database views, but APEX_WORKSPACE_ACTIVITY_LOG is the preferred one.  When you see references to columns like FLOW_ID, you'll know that this is not the preferred view (even though the documentation references it!).

    1)  As far as APEX_WORKSPACE_ACTIVITY_LOG, you're correct - the WHERE clause of this was modified in 5.1 to remove the restriction to the last 14 days.

    2)  The "raw" view APEX_ACTIVITY_LOG never had this limit in the WHERE clause.

    3)  In APEX 5.1, the views APEX_ACTIVITY_LOG and APEX_WORKSPACE_ACTIVITY_LOG views will return between N and N*2 days of activity, where N is your log switch interval. 

    Joel

    · Share on TwitterShare on Facebook
  • Scott Wesley
    Scott Wesley Member Posts: 6,203 Gold Crown
    edited Jan 17, 2017 8:05AM

    Ahh, that documentation I linked at the end was mostly referring to the final paragraph on the page which I thought was applicable to both views. It didn't occur to me it was about APEX_ACTIVITY_LOG (which I don't use).

    Thank you for clarifying point 3. I've been using Martin's technique for archiving logs but it SQL needs tuning for when history is large.

    I'm trying to write a neat monitoring application that uses a balance of fresh, live data and historical. Two weeks wasn't long enough for fresher queries. I'm glad JET has arrived so I can use decent charts.

    Now I know how to control the balance, and see the correct output ;p

    · Share on TwitterShare on Facebook
  • joelkallman-Oracle
    joelkallman-Oracle Senior Director, Software Development Posts: 4,082 Employee
    edited Jan 17, 2017 9:34AM

    Hi Scott,

    Just for the sake of completeness in my response, please be aware of view APEX_WORKSPACE_LOG_SUMMARY.  This is a view against the aggregated and archived activity log (computed once daily, via the database job ORACLE_APEX_DAILY_MAINTENANCE).  The data in this view is preserved for all time, it doesn't "rotate" like the operational activity logs.

    I hope this helps.

    Joel

    · Share on TwitterShare on Facebook
This discussion has been closed.