7 Replies Latest reply: Apr 24, 2013 2:00 PM by spajdy RSS

    Schedule SQL plus or SQL script

    963638
      How may I schedule this SQL plus script? I currently have Oracle SQL Developer 3.2 installed

      I could save it as a .sql file but how may I schedule it? Not sure if this is feasible

      or How can i turn it into a PL/SQL Procedure so I may just call the procedure when scheduling the job?

      Script:
      spool soa11g_purge_script.log

      set echo on
      set verify on
      set timing on

      -- delete all instances older than 30 days
      define days=30

      alter session set current_schema=dev_soainfra;
      alter session set nls_date_format='yyyymmdd hh24mi';

      variable cur_datetime varchar2(13)
      exec select (sysdate - &days) into :cur_datetime from dual;

      prompt Purging data until:
      print cur_datetime
      --
      -- Purge the MEDIATOR data
      --
      delete from mediator_case_instance a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from mediator_audit_document a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from mediator_callback a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from mediator_group_status a where exists (select b.id from mediator_instance b where b.group_id = a.group_id and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));

      delete from mediator_payload where modify_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_deferred_message where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_resequencer_message where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_case_detail where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_correlation where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from mediator_instance where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      commit;

      --
      -- Purge the BPEL data
      --
      delete from headers_properties where modify_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from ag_instance where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from audit_counter where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from audit_trail where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from audit_details where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from ci_indexes where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from work_item where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from wi_fault where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from xml_document_ref a where exists (select b.document_id from xml_document b where b.document_id = a.document_id and b.doc_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from xml_document where doc_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from document_dlv_msg_ref where dlv_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from document_ci_ref where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from dlv_subscription where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from dlv_message where receive_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from rejected_msg_native_payload where rm_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from instance_payload where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from test_details a where exists (select b.cikey from cube_instance b where b.cikey = a.cikey and b.creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from cube_scope where modify_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from cube_instance where creation_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      commit;

      --
      -- Purge the BPM data
      --
      delete from bpm_audit_query where create_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_measurement_actions where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_measurement_action_exceps where ci_partition_date < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_cube_auditinstance where cipartitiondate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_cube_taskperformance where creationdate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from bpm_cube_processperformance where creationdate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      commit;

      --
      -- Purge the WORKFLOW data
      --
      delete from wftask_tl a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskhistory a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskhistory_tl a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfcomments a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfmessageattribute a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfattachment a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfassignee a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfreviewer a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfcollectiontarget a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfroutingslip a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfnotification a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftasktimer a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskerror a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfheaderprops a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wfevidence a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskassignmentstatistic a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from wftaskaggregation where taskcreateddate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from wftask where createddate < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      commit;

      --
      -- Purge the COMPOSITE data
      --
      delete from composite_sensor_value where date_value < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from composite_instance_assoc where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from attachment c where exists (select a.key from attachment_ref a where a.key = c.key and exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi')));
      delete from attachment_ref a where exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi'));
      delete from composite_instance_fault where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from reference_instance where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from component_instance where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');
      delete from composite_instance where created_time < to_date(:cur_datetime, 'yyyymmdd hh24mi');

      commit;

      --
      -- Reclaim disk space
      --
      alter table mediator_case_instance enable row movement;
      alter table mediator_case_instance shrink space;
      alter table mediator_case_instance disable row movement;
      alter table mediator_audit_document enable row movement;
      alter table mediator_audit_document shrink space;
      alter table mediator_audit_document disable row movement;
      alter table mediator_callback enable row movement;
      alter table mediator_callback shrink space;
      alter table mediator_callback disable row movement;
      alter table mediator_group_status enable row movement;
      alter table mediator_group_status shrink space;
      alter table mediator_group_status disable row movement;
      alter table mediator_payload enable row movement;
      alter table mediator_payload shrink space;
      alter table mediator_payload disable row movement;
      alter table mediator_deferred_message enable row movement;
      alter table mediator_deferred_message shrink space;
      alter table mediator_deferred_message disable row movement;
      alter table mediator_resequencer_message enable row movement;
      alter table mediator_resequencer_message shrink space;
      alter table mediator_resequencer_message disable row movement;
      alter table mediator_case_detail enable row movement;
      alter table mediator_case_detail shrink space;
      alter table mediator_case_detail disable row movement;
      alter table mediator_correlation enable row movement;
      alter table mediator_correlation shrink space;
      alter table mediator_correlation disable row movement;
      alter table mediator_instance enable row movement;
      alter table mediator_instance shrink space;
      alter table mediator_instance disable row movement;
      alter table headers_properties enable row movement;
      alter table headers_properties shrink space;
      alter table headers_properties disable row movement;
      alter table ag_instance enable row movement;
      alter table ag_instance shrink space;
      alter table ag_instance disable row movement;
      alter table audit_counter enable row movement;
      alter table audit_counter shrink space;
      alter table audit_counter disable row movement;
      alter table audit_trail enable row movement;
      alter table audit_trail shrink space;
      alter table audit_trail disable row movement;
      alter table audit_details enable row movement;
      alter table audit_details shrink space;
      alter table audit_details disable row movement;
      alter table ci_indexes enable row movement;
      alter table ci_indexes shrink space;
      alter table ci_indexes disable row movement;
      alter table work_item enable row movement;
      alter table work_item shrink space;
      alter table work_item disable row movement;
      alter table wi_fault enable row movement;
      alter table wi_fault shrink space;
      alter table wi_fault disable row movement;
      alter table xml_document_ref enable row movement;
      alter table xml_document_ref shrink space;
      alter table xml_document_ref disable row movement;
      alter table document_dlv_msg_ref enable row movement;
      alter table document_dlv_msg_ref shrink space;
      alter table document_dlv_msg_ref disable row movement;
      alter table document_ci_ref enable row movement;
      alter table document_ci_ref shrink space;
      alter table document_ci_ref disable row movement;
      alter table dlv_subscription enable row movement;
      alter table dlv_subscription shrink space;
      alter table dlv_subscription disable row movement;
      alter table dlv_message enable row movement;
      alter table dlv_message shrink space;
      alter table dlv_message disable row movement;
      alter table rejected_msg_native_payload enable row movement;
      alter table rejected_msg_native_payload shrink space;
      alter table rejected_msg_native_payload disable row movement;
      alter table instance_payload enable row movement;
      alter table instance_payload shrink space;
      alter table instance_payload disable row movement;
      alter table test_details enable row movement;
      alter table test_details shrink space;
      alter table test_details disable row movement;
      alter table cube_scope enable row movement;
      alter table cube_scope shrink space;
      alter table cube_scope disable row movement;
      alter table cube_instance enable row movement;
      alter table cube_instance shrink space;
      alter table cube_instance disable row movement;
      alter table bpm_audit_query enable row movement;
      alter table bpm_audit_query shrink space;
      alter table bpm_audit_query disable row movement;
      alter table bpm_measurement_actions enable row movement;
      alter table bpm_measurement_actions shrink space;
      alter table bpm_measurement_actions disable row movement;
      alter table bpm_measurement_action_exceps enable row movement;
      alter table bpm_measurement_action_exceps shrink space;
      alter table bpm_measurement_action_exceps disable row movement;
      alter table bpm_cube_auditinstance enable row movement;
      alter table bpm_cube_auditinstance shrink space;
      alter table bpm_cube_auditinstance disable row movement;
      alter table bpm_cube_taskperformance enable row movement;
      alter table bpm_cube_taskperformance shrink space;
      alter table bpm_cube_taskperformance disable row movement;
      alter table bpm_cube_processperformance enable row movement;
      alter table bpm_cube_processperformance shrink space;
      alter table bpm_cube_processperformance disable row movement;
      alter table wftask_tl enable row movement;
      alter table wftask_tl shrink space;
      alter table wftask_tl disable row movement;
      alter table wftaskhistory enable row movement;
      alter table wftaskhistory shrink space;
      alter table wftaskhistory disable row movement;
      alter table wftaskhistory_tl enable row movement;
      alter table wftaskhistory_tl shrink space;
      alter table wftaskhistory_tl disable row movement;
      alter table wfcomments enable row movement;
      alter table wfcomments shrink space;
      alter table wfcomments disable row movement;
      alter table wfmessageattribute enable row movement;
      alter table wfmessageattribute shrink space;
      alter table wfmessageattribute disable row movement;
      alter table wfattachment enable row movement;
      alter table wfattachment shrink space;
      alter table wfattachment disable row movement;
      alter table wfassignee enable row movement;
      alter table wfassignee shrink space;
      alter table wfassignee disable row movement;
      alter table wfreviewer enable row movement;
      alter table wfreviewer shrink space;
      alter table wfreviewer disable row movement;
      alter table wfcollectiontarget enable row movement;
      alter table wfcollectiontarget shrink space;
      alter table wfcollectiontarget disable row movement;
      alter table wfroutingslip enable row movement;
      alter table wfroutingslip shrink space;
      alter table wfroutingslip disable row movement;
      alter table wfnotification enable row movement;
      alter table wfnotification shrink space;
      alter table wfnotification disable row movement;
      alter table wftasktimer enable row movement;
      alter table wftasktimer shrink space;
      alter table wftasktimer disable row movement;
      alter table wftaskerror enable row movement;
      alter table wftaskerror shrink space;
      alter table wftaskerror disable row movement;
      alter table wfheaderprops enable row movement;
      alter table wfheaderprops shrink space;
      alter table wfheaderprops disable row movement;
      alter table wfevidence enable row movement;
      alter table wfevidence shrink space;
      alter table wfevidence disable row movement;
      alter table wftaskassignmentstatistic enable row movement;
      alter table wftaskassignmentstatistic shrink space;
      alter table wftaskassignmentstatistic disable row movement;
      alter table wftaskaggregation enable row movement;
      alter table wftaskaggregation shrink space;
      alter table wftaskaggregation disable row movement;
      alter table wftask enable row movement;
      alter table wftask shrink space;
      alter table wftask disable row movement;
      alter table composite_sensor_value enable row movement;
      alter table composite_sensor_value shrink space;
      alter table composite_sensor_value disable row movement;
      alter table composite_instance_assoc enable row movement;
      alter table composite_instance_assoc shrink space;
      alter table composite_instance_assoc disable row movement;
      alter table attachment enable row movement;
      alter table attachment shrink space;
      alter table attachment disable row movement;
      alter table attachment_ref enable row movement;
      alter table attachment_ref shrink space;
      alter table attachment_ref disable row movement;
      alter table composite_instance_fault enable row movement;
      alter table composite_instance_fault shrink space;
      alter table composite_instance_fault disable row movement;
      alter table reference_instance enable row movement;
      alter table reference_instance shrink space;
      alter table reference_instance disable row movement;
      alter table component_instance enable row movement;
      alter table component_instance shrink space;
      alter table component_instance disable row movement;
      alter table composite_instance enable row movement;
      alter table composite_instance shrink space;
      alter table composite_instance disable row movement;

      alter table audit_details modify lob (bin) (shrink space);
      alter table composite_instance_fault modify lob (error_message) (shrink space);
      alter table composite_instance_fault modify lob (stack_trace) (shrink space);
      alter table cube_scope modify lob (scope_bin) (shrink space);
      alter table reference_instance modify lob (error_message) (shrink space);
      alter table reference_instance modify lob (stack_trace) (shrink space);
      alter table test_definitions modify lob (definition) (shrink space);
      alter table wi_fault modify lob (message) (shrink space);
      alter table xml_document modify lob (document) (shrink space);

      alter index ad_pk rebuild online;
      alter index at_pk rebuild online;
      alter index ci_creation_date rebuild online;
      alter index ci_custom3 rebuild online;
      alter index ci_ecid rebuild online;
      alter index ci_name_rev_state rebuild online;
      alter index ci_pk rebuild online;
      alter index composite_instance_cidn rebuild online;
      alter index composite_instance_co_id rebuild online;
      alter index composite_instance_created rebuild online;
      alter index composite_instance_ecid rebuild online;
      alter index composite_instance_id rebuild online;
      alter index composite_instance_state rebuild online;
      alter index cs_pk rebuild online;
      alter index dm_conversation rebuild online;
      alter index dm_pk rebuild online;
      alter index doc_dlv_msg_guid_index rebuild online;
      alter index doc_store_pk rebuild online;
      alter index ds_conversation rebuild online;
      alter index ds_conv_state rebuild online;
      alter index ds_fk rebuild online;
      alter index ds_pk rebuild online;
      alter index header_properties_pk rebuild online;
      alter index instance_payload_key rebuild online;
      alter index reference_instance_cdn_state rebuild online;
      alter index reference_instance_co_id rebuild online;
      alter index reference_instance_ecid rebuild online;
      alter index reference_instance_id rebuild online;
      alter index reference_instance_state rebuild online;
      alter index reference_instance_time_cdn rebuild online;
      alter index state_type_date rebuild online;
      alter index wf_crdate_cikey rebuild online;
      alter index wf_crdate_type rebuild online;
      alter index wf_fk2 rebuild online;
      alter index wifault_pk rebuild online;
      alter index wi_expired rebuild online;
      alter index wi_key_crdate_state rebuild online;
      alter index wi_pk rebuild online;
      alter index wi_stranded rebuild online;
      alter index xml_doc_reference_pk rebuild online

      spool off

      Edited by: 960635 on Apr 22, 2013 8:24 AM

      Edited by: 960635 on Apr 22, 2013 8:48 AM
        • 1. Re: Schedule SQL plus or SQL script
          spajdy
          1/ simple for OS level
          Based on your OS scheduler an OS level job (On Unix cron, on Windows AT/Task scheduler) create a job that run this command line:
          sqlplus <username>/<password>@<alias> @a.sql
          Be sure that last line in a.sql is SQL*Plus command exit.
          2/ use ORACLE Scheduler on crate external job that run same OS command line command as in variant 1/
          3/ put your code into PL/SQL procedure an crate ORACLE Scheduler job that execute this procedure.
          Some SQL commands can be placed directly to PL/SQL code (DDL, DCL,...) for example CREATE TABLE, ALTER SESION and so on.
          To run this SQL in PL/SQL you have to use dynamic SQL.
          For this there are two posibilities in PL/SQL
          a/ execute immediate 'CREATE TABLE ...' or execute immediate l_str hwre l_satr is VARCHAR2 variable containing string with SQL.
          b/ package dbms_sql
          create or replalace procedure p_test as
          -- delete all instances older than 30 days
          l_days numebr :=30;
          l_cur_datetime date;
          
          begin
          execute immediate 'alter session set current_schema=dev_soainfra';
          
          l_cur_datetime := sysdate - l_days;
          
          --
          -- Purge the MEDIATOR data
          -- 
          delete from mediator_case_instance a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < l_cur_datetime);
          delete from mediator_audit_document a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < l_cur_datetime);
          ...
          commit;
          
          --
          -- Purge the BPEL data
          -- 
          delete from headers_properties where modify_date < l_cur_datetime;
          commit;
          
          --
          -- Reclaim disk space
          --
          execute immediate 'alter table mediator_case_instance enable row movement';
          ...
          end;
          /
          My code is only a sample how to rewrite it to PL/SQL. Because we are in PL/SQL we can use variable of DATE data type and no need yo use to_date conversion function.
          • 2. Re: Schedule SQL plus or SQL script
            963638
            Got this error when trying to execute the PL/SQL

            nnecting to the database Sandbox_soainfra.
            ORA-10631: SHRINK clause should not be specified for this object
            ORA-06512: at "DEV_SOAINFRA.P_TEST", line 129
            ORA-06512: at line 2
            Process exited.
            Disconnecting from the database Sandbox_soainfra.

            I did this below:


            create or replace procedure p_test as
            -- delete all instances older than 30 days
            l_days number :=30;
            l_cur_datetime date;

            begin
            execute immediate 'alter session set current_schema=dev_soainfra';

            l_cur_datetime := sysdate - l_days;
            --
            -- Purge the MEDIATOR data
            --
            delete from mediator_case_instance a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < l_cur_datetime);
            delete from mediator_audit_document a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < l_cur_datetime);
            delete from mediator_callback a where exists (select b.id from mediator_instance b where b.id = a.instance_id and b.created_time < l_cur_datetime);
            delete from mediator_group_status a where exists (select b.id from mediator_instance b where b.group_id = a.group_id and b.created_time < l_cur_datetime);

            delete from mediator_payload where modify_date < l_cur_datetime;
            delete from mediator_deferred_message where creation_date < l_cur_datetime;
            delete from mediator_resequencer_message where creation_date < l_cur_datetime;
            delete from mediator_case_detail where created_time < l_cur_datetime;
            delete from mediator_correlation where creation_date < l_cur_datetime;
            delete from mediator_instance where created_time < l_cur_datetime;
            commit;

            --
            -- Purge the BPEL data
            --
            delete from headers_properties where modify_date < l_cur_datetime;
            delete from ag_instance where creation_date < l_cur_datetime;
            delete from audit_counter where ci_partition_date < l_cur_datetime;
            delete from audit_trail where ci_partition_date < l_cur_datetime;
            delete from audit_details where ci_partition_date < l_cur_datetime;
            delete from ci_indexes where ci_partition_date < l_cur_datetime;
            delete from work_item where creation_date < l_cur_datetime;
            delete from wi_fault where creation_date < l_cur_datetime;
            delete from xml_document_ref a where exists (select b.document_id from xml_document b where b.document_id = a.document_id and b.doc_partition_date < l_cur_datetime);
            delete from xml_document where doc_partition_date < l_cur_datetime;
            delete from document_dlv_msg_ref where dlv_partition_date < l_cur_datetime;
            delete from document_ci_ref where ci_partition_date < l_cur_datetime;
            delete from dlv_subscription where ci_partition_date < l_cur_datetime;
            delete from dlv_message where receive_date < l_cur_datetime;
            delete from rejected_msg_native_payload where rm_partition_date < l_cur_datetime;
            delete from instance_payload where created_time < l_cur_datetime;
            delete from test_details a where exists (select b.cikey from cube_instance b where b.cikey = a.cikey and b.creation_date < l_cur_datetime);
            delete from cube_scope where modify_date < l_cur_datetime;
            delete from cube_instance where creation_date < l_cur_datetime;
            commit;

            --
            -- Purge the BPM data
            --
            delete from bpm_audit_query where create_time < l_cur_datetime;
            delete from bpm_measurement_actions where ci_partition_date < l_cur_datetime;
            delete from bpm_measurement_action_exceps where ci_partition_date < l_cur_datetime;
            delete from bpm_cube_auditinstance where cipartitiondate < l_cur_datetime;
            delete from bpm_cube_taskperformance where creationdate < l_cur_datetime;
            delete from bpm_cube_processperformance where creationdate < l_cur_datetime;
            commit;

            --
            -- Purge the WORKFLOW data
            --
            delete from wftask_tl a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wftaskhistory a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wftaskhistory_tl a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfcomments a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfmessageattribute a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfattachment a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfassignee a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfreviewer a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfcollectiontarget a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfroutingslip a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfnotification a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wftasktimer a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wftaskerror a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfheaderprops a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wfevidence a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wftaskassignmentstatistic a where exists (select b.taskid from wftask b where a.taskid = b.taskid and b.createddate < l_cur_datetime);
            delete from wftaskaggregation where taskcreateddate < l_cur_datetime;
            delete from wftask where createddate < l_cur_datetime;
            commit;

            --
            -- Purge the COMPOSITE data
            --
            delete from composite_sensor_value where date_value < l_cur_datetime;
            delete from composite_instance_assoc where created_time < l_cur_datetime;
            delete from attachment c where exists (select a.key from attachment_ref a where a.key = c.key and exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < l_cur_datetime));
            delete from attachment_ref a where exists (select b.ecid from composite_instance b where b.ecid = a.ecid and b.created_time < l_cur_datetime);
            delete from composite_instance_fault where created_time < l_cur_datetime;
            delete from reference_instance where created_time < l_cur_datetime;
            delete from component_instance where created_time < l_cur_datetime;
            delete from composite_instance where created_time < l_cur_datetime;

            commit;

            --
            -- Reclaim disk space
            --
            execute immediate 'alter table mediator_case_instance enable row movement';
            execute immediate 'alter table mediator_case_instance shrink space';
            execute immediate 'alter table mediator_case_instance disable row movement';
            execute immediate 'alter table mediator_audit_document enable row movement';
            execute immediate 'alter table mediator_audit_document shrink space';
            execute immediate 'alter table mediator_audit_document disable row movement';
            execute immediate 'alter table mediator_callback enable row movement';
            execute immediate 'alter table mediator_callback shrink space';
            execute immediate 'alter table mediator_callback disable row movement';
            execute immediate 'alter table mediator_group_status enable row movement';
            execute immediate 'alter table mediator_group_status shrink space';
            execute immediate 'alter table mediator_group_status disable row movement';
            execute immediate 'alter table mediator_payload enable row movement';
            execute immediate 'alter table mediator_payload shrink space';
            execute immediate 'alter table mediator_payload disable row movement';
            execute immediate 'alter table mediator_deferred_message enable row movement';
            execute immediate 'alter table mediator_deferred_message shrink space';
            execute immediate 'alter table mediator_deferred_message disable row movement';
            execute immediate 'alter table mediator_resequencer_message enable row movement';
            execute immediate 'alter table mediator_resequencer_message shrink space';
            execute immediate 'alter table mediator_resequencer_message disable row movement';
            execute immediate 'alter table mediator_case_detail enable row movement';
            execute immediate 'alter table mediator_case_detail shrink space';
            execute immediate 'alter table mediator_case_detail disable row movement';
            execute immediate 'alter table mediator_correlation enable row movement';
            execute immediate 'alter table mediator_correlation shrink space';
            execute immediate 'alter table mediator_correlation disable row movement';
            execute immediate 'alter table mediator_instance enable row movement';
            execute immediate 'alter table mediator_instance shrink space';
            execute immediate 'alter table mediator_instance disable row movement';
            execute immediate 'alter table headers_properties enable row movement';
            execute immediate 'alter table headers_properties shrink space';
            execute immediate 'alter table headers_properties disable row movement';
            execute immediate 'alter table ag_instance enable row movement';
            execute immediate 'alter table ag_instance shrink space';
            execute immediate 'alter table ag_instance disable row movement';
            execute immediate 'alter table audit_counter enable row movement';
            execute immediate 'alter table audit_counter shrink space';
            execute immediate 'alter table audit_counter disable row movement';
            execute immediate 'alter table audit_trail enable row movement';
            execute immediate 'alter table audit_trail shrink space';
            execute immediate 'alter table audit_trail disable row movement';
            execute immediate 'alter table audit_details enable row movement';
            execute immediate 'alter table audit_details shrink space';
            execute immediate 'alter table audit_details disable row movement';
            execute immediate 'alter table ci_indexes enable row movement';
            execute immediate 'alter table ci_indexes shrink space';
            execute immediate 'alter table ci_indexes disable row movement';
            execute immediate 'alter table work_item enable row movement';
            execute immediate 'alter table work_item shrink space';
            execute immediate 'alter table work_item disable row movement';
            execute immediate 'alter table wi_fault enable row movement';
            execute immediate 'alter table wi_fault shrink space';
            execute immediate 'alter table wi_fault disable row movement';
            execute immediate 'alter table xml_document_ref enable row movement';
            execute immediate 'alter table xml_document_ref shrink space';
            execute immediate 'alter table xml_document_ref disable row movement';
            execute immediate 'alter table document_dlv_msg_ref enable row movement';
            execute immediate 'alter table document_dlv_msg_ref shrink space';
            execute immediate 'alter table document_dlv_msg_ref disable row movement';
            execute immediate 'alter table document_ci_ref enable row movement';
            execute immediate 'alter table document_ci_ref shrink space';
            execute immediate 'alter table document_ci_ref disable row movement';
            execute immediate 'alter table dlv_subscription enable row movement';
            execute immediate 'alter table dlv_subscription shrink space';
            execute immediate 'alter table dlv_subscription disable row movement';
            execute immediate 'alter table dlv_message enable row movement';
            execute immediate 'alter table dlv_message shrink space';
            execute immediate 'alter table dlv_message disable row movement';
            execute immediate 'alter table rejected_msg_native_payload enable row movement';
            execute immediate 'alter table rejected_msg_native_payload shrink space';
            execute immediate 'alter table rejected_msg_native_payload disable row movement';
            execute immediate 'alter table instance_payload enable row movement';
            execute immediate 'alter table instance_payload shrink space';
            execute immediate 'alter table instance_payload disable row movement';
            execute immediate 'alter table test_details enable row movement';
            execute immediate 'alter table test_details shrink space';
            execute immediate 'alter table test_details disable row movement';
            execute immediate 'alter table cube_scope enable row movement';
            execute immediate 'alter table cube_scope shrink space';
            execute immediate 'alter table cube_scope disable row movement';
            execute immediate 'alter table cube_instance enable row movement';
            execute immediate 'alter table cube_instance shrink space';
            execute immediate 'alter table cube_instance disable row movement';
            execute immediate 'alter table bpm_audit_query enable row movement';
            execute immediate 'alter table bpm_audit_query shrink space';
            execute immediate 'alter table bpm_audit_query disable row movement';
            execute immediate 'alter table bpm_measurement_actions enable row movement';
            execute immediate 'alter table bpm_measurement_actions shrink space';
            execute immediate 'alter table bpm_measurement_actions disable row movement';
            execute immediate 'alter table bpm_measurement_action_exceps enable row movement';
            execute immediate 'alter table bpm_measurement_action_exceps shrink space';
            execute immediate 'alter table bpm_measurement_action_exceps disable row movement';
            execute immediate 'alter table bpm_cube_auditinstance enable row movement';
            execute immediate 'alter table bpm_cube_auditinstance shrink space';
            execute immediate 'alter table bpm_cube_auditinstance disable row movement';
            execute immediate 'alter table bpm_cube_taskperformance enable row movement';
            execute immediate 'alter table bpm_cube_taskperformance shrink space';
            execute immediate 'alter table bpm_cube_taskperformance disable row movement';
            execute immediate 'alter table bpm_cube_processperformance enable row movement';
            execute immediate 'alter table bpm_cube_processperformance shrink space';
            execute immediate 'alter table bpm_cube_processperformance disable row movement';
            execute immediate 'alter table wftask_tl enable row movement';
            execute immediate 'alter table wftask_tl shrink space';
            execute immediate 'alter table wftask_tl disable row movement';
            execute immediate 'alter table wftaskhistory enable row movement';
            execute immediate 'alter table wftaskhistory shrink space';
            execute immediate 'alter table wftaskhistory disable row movement';
            execute immediate 'alter table wftaskhistory_tl enable row movement';
            execute immediate 'alter table wftaskhistory_tl shrink space';
            execute immediate 'alter table wftaskhistory_tl disable row movement';
            execute immediate 'alter table wfcomments enable row movement';
            execute immediate 'alter table wfcomments shrink space';
            execute immediate 'alter table wfcomments disable row movement';
            execute immediate 'alter table wfmessageattribute enable row movement';
            execute immediate 'alter table wfmessageattribute shrink space';
            execute immediate 'alter table wfmessageattribute disable row movement';
            execute immediate 'alter table wfattachment enable row movement';
            execute immediate 'alter table wfattachment shrink space';
            execute immediate 'alter table wfattachment disable row movement';
            execute immediate 'alter table wfassignee enable row movement';
            execute immediate 'alter table wfassignee shrink space';
            execute immediate 'alter table wfassignee disable row movement';
            execute immediate 'alter table wfreviewer enable row movement';
            execute immediate 'alter table wfreviewer shrink space';
            execute immediate 'alter table wfreviewer disable row movement';
            execute immediate 'alter table wfcollectiontarget enable row movement';
            execute immediate 'alter table wfcollectiontarget shrink space';
            execute immediate 'alter table wfcollectiontarget disable row movement';
            execute immediate 'alter table wfroutingslip enable row movement';
            execute immediate 'alter table wfroutingslip shrink space';
            execute immediate 'alter table wfroutingslip disable row movement';
            execute immediate 'alter table wfnotification enable row movement';
            execute immediate 'alter table wfnotification shrink space';
            execute immediate 'alter table wfnotification disable row movement';
            execute immediate 'alter table wftasktimer enable row movement';
            execute immediate 'alter table wftasktimer shrink space';
            execute immediate 'alter table wftasktimer disable row movement';
            execute immediate 'alter table wftaskerror enable row movement';
            execute immediate 'alter table wftaskerror shrink space';
            execute immediate 'alter table wftaskerror disable row movement';
            execute immediate 'alter table wfheaderprops enable row movement';
            execute immediate 'alter table wfheaderprops shrink space';
            execute immediate 'alter table wfheaderprops disable row movement';
            execute immediate 'alter table wfevidence enable row movement';
            execute immediate 'alter table wfevidence shrink space';
            execute immediate 'alter table wfevidence disable row movement';
            execute immediate 'alter table wftaskassignmentstatistic enable row movement';
            execute immediate 'alter table wftaskassignmentstatistic shrink space';
            execute immediate 'alter table wftaskassignmentstatistic disable row movement';
            execute immediate 'alter table wftaskaggregation enable row movement';
            execute immediate 'alter table wftaskaggregation shrink space';
            execute immediate 'alter table wftaskaggregation disable row movement';
            execute immediate 'alter table wftask enable row movement';
            execute immediate 'alter table wftask shrink space';
            execute immediate 'alter table wftask disable row movement';
            execute immediate 'alter table composite_sensor_value enable row movement';
            execute immediate 'alter table composite_sensor_value shrink space';
            execute immediate 'alter table composite_sensor_value disable row movement';
            execute immediate 'alter table composite_instance_assoc enable row movement';
            execute immediate 'alter table composite_instance_assoc shrink space';
            execute immediate 'alter table composite_instance_assoc disable row movement';
            execute immediate 'alter table attachment enable row movement';
            execute immediate 'alter table attachment shrink space';
            execute immediate 'alter table attachment disable row movement';
            execute immediate 'alter table attachment_ref enable row movement';
            execute immediate 'alter table attachment_ref shrink space';
            execute immediate 'alter table attachment_ref disable row movement';
            execute immediate 'alter table composite_instance_fault enable row movement';
            execute immediate 'alter table composite_instance_fault shrink space';
            execute immediate 'alter table composite_instance_fault disable row movement';
            execute immediate 'alter table reference_instance enable row movement';
            execute immediate 'alter table reference_instance shrink space';
            execute immediate 'alter table reference_instance disable row movement';
            execute immediate 'alter table component_instance enable row movement';
            execute immediate 'alter table component_instance shrink space';
            execute immediate 'alter table component_instance disable row movement';
            execute immediate 'alter table composite_instance enable row movement';
            execute immediate 'alter table composite_instance shrink space';
            execute immediate 'alter table composite_instance disable row movement';

            execute immediate 'alter table audit_details modify lob (bin) (shrink space)';
            execute immediate 'alter table composite_instance_fault modify lob (error_message) (shrink space)';
            execute immediate 'alter table composite_instance_fault modify lob (stack_trace) (shrink space)';
            execute immediate 'alter table cube_scope modify lob (scope_bin) (shrink space)';
            execute immediate 'alter table reference_instance modify lob (error_message) (shrink space)';
            execute immediate 'alter table reference_instance modify lob (stack_trace) (shrink space)';
            execute immediate 'alter table test_definitions modify lob (definition) (shrink space)';
            execute immediate 'alter table wi_fault modify lob (message) (shrink space)';
            execute immediate 'alter table xml_document modify lob (document) (shrink space)';

            execute immediate 'alter index ad_pk rebuild online';
            execute immediate 'alter index at_pk rebuild online';
            execute immediate 'alter index ci_creation_date rebuild online';
            execute immediate 'alter index ci_custom3 rebuild online';
            execute immediate 'alter index ci_ecid rebuild online';
            execute immediate 'alter index ci_name_rev_state rebuild online';
            execute immediate 'alter index ci_pk rebuild online';
            execute immediate 'alter index composite_instance_cidn rebuild online';
            execute immediate 'alter index composite_instance_co_id rebuild online';
            execute immediate 'alter index composite_instance_created rebuild online';
            execute immediate 'alter index composite_instance_ecid rebuild online';
            execute immediate 'alter index composite_instance_id rebuild online';
            execute immediate 'alter index composite_instance_state rebuild online';
            execute immediate 'alter index cs_pk rebuild online';
            execute immediate 'alter index dm_conversation rebuild online';
            execute immediate 'alter index dm_pk rebuild online';
            execute immediate 'alter index doc_dlv_msg_guid_index rebuild online';
            execute immediate 'alter index doc_store_pk rebuild online';
            execute immediate 'alter index ds_conversation rebuild online';
            execute immediate 'alter index ds_conv_state rebuild online';
            execute immediate 'alter index ds_fk rebuild online';
            execute immediate 'alter index ds_pk rebuild online';
            execute immediate 'alter index header_properties_pk rebuild online';
            execute immediate 'alter index instance_payload_key rebuild online';
            execute immediate 'alter index reference_instance_cdn_state rebuild online';
            execute immediate 'alter index reference_instance_co_id rebuild online';
            execute immediate 'alter index reference_instance_ecid rebuild online';
            execute immediate 'alter index reference_instance_id rebuild online';
            execute immediate 'alter index reference_instance_state rebuild online';
            execute immediate 'alter index reference_instance_time_cdn rebuild online';
            execute immediate 'alter index state_type_date rebuild online';
            execute immediate 'alter index wf_crdate_cikey rebuild online';
            execute immediate 'alter index wf_crdate_type rebuild online';
            execute immediate 'alter index wf_fk2 rebuild online';
            execute immediate 'alter index wifault_pk rebuild online';
            execute immediate 'alter index wi_expired rebuild online';
            execute immediate 'alter index wi_key_crdate_state rebuild online';
            execute immediate 'alter index wi_pk rebuild online';
            execute immediate 'alter index wi_stranded rebuild online';
            execute immediate 'alter index xml_doc_reference_pk rebuild online';



            end;
            /

            Edited by: 960635 on Apr 22, 2013 1:37 PM
            • 3. Re: Schedule SQL plus or SQL script
              spajdy
              You are try to shink object that couldn't be shinked.
              For details see this thread ORA-10631: SHRINK clause should not be specified for this object or ORACLE doc.
              • 4. Re: Schedule SQL plus or SQL script
                963638
                That's odd because when i run the script without it embedded in PL/SQL it executes but when embedded it throws that error.
                • 5. Re: Schedule SQL plus or SQL script
                  spajdy
                  Theoretically problem could be in privileges and error message is misleading.
                  PL/SQL ingore roles so check if you DB user have granted some roles and try to grant privileges to DB user directly not thought roles. After that run PL/SQL procedure.
                  • 6. Re: Schedule SQL plus or SQL script
                    963638
                    Hmm I have Create job priviledges and I am the owner of the procedure...any other privileges needed?
                    • 7. Re: Schedule SQL plus or SQL script
                      spajdy
                      Object privileges=tables etc.