1 2 Previous Next 20 Replies Latest reply on Jun 1, 2015 6:23 PM by Raja Mohan

    Can I increase the retention period for cleanup to re-flag items differently for deletion?

    Yanick Girouard

      We have 3 years worth of backlog (jobs, job_runs, job_results, etc...) that was never cleaned using the database cleanup jobs. The current retention policy is set to 180 days, which means that we have never been able to complete a full cleanupDatabase yet as it gets forever stuck on a simple select to fetch the data to delete to begin with:


      with base_data
                      as(select dm.module_id, j.job_id, j.job_version_id, 
      jr.job_run_id, jr.date_modified, jr.is_deleted, pbv.value as retention_val
                       from job j, property_set_instance psi, 
      prop_set_instance_prop_val psipv, primitive_bl_value pbv, V_PATCH_JOB_RUN 
      jr, dbm_module dm, dbm_module_object_types dmot
                       where j.bl_value_id = psi.bl_value_id
                       and psi.bl_value_id = psipv.bl_value_id and property_id = 
                       and psipv.instance_value_id = pbv.bl_value_id
                       and jr.job_id = j.job_id
                       and jr.job_version_id = j.job_version_id
                       and dm.module_id = 10
                       and dm.online_ind = 1
                       and dmot.module_id = dm.module_id
                       and dmot.object_type_id = j.object_type_id)
                      select module_id, job_id, job_version_id, job_run_id
                       from base_data
                       where (date_modified < (trunc(sysdate) - retention_val) 
      AND retention_val IS NOT NULL) or is_deleted = 1;


      So, in order to help resolve this problem we would like to extend the retention period to something like 730 days and try again, but I was wondering if this was even possible since the retention policy already run with 180 days so I assumed all items were already flagged?


      If we change the value of the retention and the database cleanup was never able to run (referring to the cleanupDatabase command) but the executeRetentionPolicy job did run more than once, is it too late?

        1 2 Previous Next