Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 399 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
apex_workspace_activity_log purge rate

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 ?
Best 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
Answers
-
Hi Scott,
When did you issue the call to apex_instance_admin.SET_LOG_SWITCH_INTERVAL? Was it on or after 22-AUG?
Joel
-
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
-
Scott,
Let's please wait to check it on 28-OCT, okay?
Joel
-
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
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.
-
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
-
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
-
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