Hacks and Workarounds

Debugging issues seen during purge script execution
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue1 :(Generic)
error "ORA-01861 literal does not match format string"

Solution:
date format giving in your purge script may be wrong please check for the correct format as listed below.

min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');

or like this

MIN_CREATION_DATE timestamp := TIMESTAMP'2011-09-19 02:00:00';
MAX_CREATION_DATE timestamp := TIMESTAMP'2011-09-20 00:00:00';

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue2 :(specific to Parallel Purge)

02-JAN-2012 03:57:10 : jobs running = 2
02-JAN-2012 03:57:10
: ERROR(delete_instances_in_parallel. Error Code = -20001,
Error Message = ORA-20001: Jobs still running or not shut down properly
DECLARE
*
ERROR at line 1:
ORA-20001: Jobs still running or not shut down properly
ORA-06512: at "DB7607_SOAINFRA.SOA", line 661
ORA-06512: at line 11

Solution:
Delete all records from job_flow_control table;
Delete from job_flow_control
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 3: (Partition drop)
Partition cannot be dropped if there are some b2b records associated with the table.

Solution:
log into soainfra user and run "exec b2b_disable_constraints" command.
then rerun verify script that was generated, you will be able to see all b2b related tables getting altered

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Issue 4 : obtained when soa_exec_verify.sql is executed
Enter value for 1: 1
old 19: if &1 = 1 then
new 19: if 1 = 1 then
DECLARE
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at "DB2813_SOAINFRA.VERIFY_SOA", line 252
ORA-06512: at line 20

Solution:

1. Make sure you have given permissions as specified in Partitioning Concept blog
2. Make sure the directory is created for dumping sql logs
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Issue 5 : cannot drop xml_document table partition
SQL> @/tmp/verify/SOA_P03_2012_RESULT_2
SQL> REM Auto generated by the SOA verify script
SQL> REM Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved.
SQL> REM DESCRIPTION
SQL> REM This script is generated by the SOA verify sctipts. It is used to drop partitions from the SOA Table.
SQL> REM This script should only be run by DBA who is familiar with the SOA Schema.
SQL> REM
SQL> -- COMPONENT: FABRIC
SQL> -- COMPONENT: BPEL
SQL> ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES;
ALTER TABLE XML_DOCUMENT drop partition P03_2012 UPDATE GLOBAL INDEXES
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Solution:
Reason : XML_DOCUMENT is referred by B2B related tables also and hence the partition cannot be dropped so easily.

solution : Run b2b disable constraints as below then run drop partition command
login as soainfra user and run
$SQL>exec B2B_DISABLE_CONSTRAINTS

to enable B2B constraint after dropping run below command
$SQL> exec B2B_ENABLE_CONSTRAINTS

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
================================================================================
Disclaimer: The views expressed on this blog are solely our own and do not necessarily reflect the views of Oracle.
================================================================================