This content has been marked as final. Show 12 replies
OK Joel... I just got more detail..
This may have been done in version 1.5 if it matters but they has around 200 concurrent users accessing the database through several APEX apps.. they were getting a time lull for the page rendering and started clearing the collections table, for example, before anyone came into work and notice an increase in speed.
I believe APEX automatically cleans up the sessions and collections tables through a job.. correct? if so, these jobs are in the flows user schema aren't they.. if so how are they executed by the scheduler? does APEX have its own scheduler?
Could my customer have disabled the automatic cleanup jobs?
Thank you very much for your help, I'm gaining more insight into this whole area by digging into it.
>> I believe APEX automatically cleans up the sessions and collections tables through a job.. correct?
Yes. Through the database job mentioned above.
>> if so, these jobs are in the flows user schema aren't they..
Yes again. They are jobs with DBA_JOBS.PRIV_USER = FLOWS_030000. This should be job number 4001.
>> if so how are they executed by the scheduler? does APEX have its own scheduler?
No. These are normal database jobs (not scheduler jobs).
>> Could my customer have disabled the automatic cleanup jobs?
Query DBA_JOBS and locate this job, identify the next date it is to run, and also ensure it's not broken or if there are failures. If there were any errors executing this database job, it would be recorded in the database alert log.
Sounds like somebody nuked them. There also should be a job to push the mail queue.
Assuming this is APEX 2.2, to recreate this one job which purges sessions, you should connect as SYS using SQL*Plus and run:
alter session set current_schema = flows_020200;Joel
dbms_job.isubmit(job => 4001,
what => 'wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);',
next_date => sysdate + 10/1440,
interval => 'sysdate + 8/24');
Just found out that they moved the job to a cron job executing at 5am and 10pm and clearing all but 2hrs of session instead of 24hrs. It does the same
alter session set current_schema = flows_020200;
and calls the 'wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 2);'
So I guess we are alright there... does the 'wwv_flow_cache.purge_sessions also remove the collections?