This discussion is archived
7 Replies Latest reply: Apr 24, 2013 12:00 PM by spajdy RSS

Schedule SQL plus or SQL script

963638 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    Object privileges=tables etc.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points