This discussion is archived
6 Replies Latest reply: Apr 20, 2013 6:44 PM by rp0428 RSS

ORA-166512: string value too long for attribute "job_action"

963638 Newbie
Currently Being Moderated
Hello I am trying to set up a schedule to execute daily for the following purge script however when i attempted to use the oracle sql developer job wizard i got the error ORA-166512 in the thread topic.

Here is the script i am referring to

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
  • 1. Re: ORA-166512: string value too long for attribute "job_action"
    L-MachineGun Pro
    Currently Being Moderated
    For one, job action is limited to 4000 characters:
    TYPE job_definition IS OBJECT (
      job_name                       VARCHAR2(100),
      job_class                      VARCHAR2(32),
      job_style                      VARCHAR2(11),
      program_name                   VARCHAR2(100),
      job_action                     VARCHAR2(4000),
      job_type                       VARCHAR2(20),
     ...  Etc ...
    :p
  • 2. Re: ORA-166512: string value too long for attribute "job_action"
    rp0428 Guru
    Currently Being Moderated
    Whenever you post provide your 4 digit Oracle version.
    >
    Hello I am trying to set up a schedule to execute daily for the following purge script however when i attempted to use the oracle sql developer job wizard i got the error ORA-166512 in the thread topic.

    Here is the script i am referring to
    >
    But you didn't post the code you are actually having the problem with!

    Post the code you are using to 'set up a schedule to execute daily'. That is where the problem is.
  • 3. Re: ORA-166512: string value too long for attribute "job_action"
    963638 Newbie
    Currently Being Moderated
    Here is the code; also yes i realized job action is limited to 4000 characters; I am new to this


    BEGIN
    SYS.DBMS_SCHEDULER.CREATE_JOB (
    job_name => '"DEV_SOAINFRA"."insert"',
    job_type => 'PLSQL_BLOCK',
    job_action => '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
    ',
    number_of_arguments => 0,
    start_date => NULL,
    repeat_interval => 'FREQ=DAILY;BYHOUR=12;BYMINUTE=0;BYSECOND=0',
    end_date => NULL,
    job_class => '"SYS"."DEFAULT_JOB_CLASS"',
    enabled => FALSE,
    auto_drop => FALSE,
    comments => 'insert table');





    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '"DEV_SOAINFRA"."insert"',
    attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);


    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '"DEV_SOAINFRA"."insert"',
    attribute => 'max_run_duration', value => INTERVAL '1' HOUR);
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE(
    name => '"DEV_SOAINFRA"."insert"',
    attribute => 'schedule_limit', value => INTERVAL '1' HOUR);
    SYS.DBMS_SCHEDULER.enable(
    name => '"DEV_SOAINFRA"."insert"');
    END;
    /
  • 4. Re: ORA-166512: string value too long for attribute "job_action"
    L-MachineGun Pro
    Currently Being Moderated
    You cannot put those statements inside an anonymous block (BEGIN/END structure).
    You should put all these statements in a sql file, code an os script to execute this sql file and then schedule the execution of the os script.
    :p
  • 5. Re: ORA-166512: string value too long for attribute "job_action"
    963638 Newbie
    Currently Being Moderated
    How may this be done? Do you have a sample? I am literally new to this.
  • 6. Re: ORA-166512: string value too long for attribute "job_action"
    rp0428 Guru
    Currently Being Moderated
    >
    How may this be done? Do you have a sample? I am literally new to this.
    >
    How do you create a script on your server file system? Open an editor and type it in. Or use cut & paste.

    As far as the scheduler part create a job with a job_type of EXECUTABLE and a job_action that provides the script name to Oracle.

    Search for EXECUTABLE in the DBMS_SCHEDULER in the packages and types doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm

    There are plenty of examples on the web of running scripts using the scheduler.

Legend

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