Re: Un-able to upload xml file - Error:ORA-14400: inserted partition key does not map to any partition

From: Robert Hanuschke <robert.hanuschke_at_gmail.com>
Date: Wed, 8 Aug 2012 08:38:09 +0200
Message-ID: <CAL6WDh3rKWJtpQfaUnYn9AkkTN9mzbfcc3vshLwe=x+AWvEaLg_at_mail.gmail.com>

setting scheduler_disabled to true apparently does prevent dbms_jobs from running:

(For the sake of quick testing, used codes from following sites:
http://ss64.com/orap/DBMS_JOB.html
http://gavinsoorma.com/2009/07/script-list-status-of-all-submitted-dbms-jobs/ Thanks to the authors.)

SQL> create user scott identified by tiger;

User created.

SQL> DECLARE
 jobno NUMBER;
 BEGIN
 DBMS_JOB.submit
 (job => jobno,
 what => 'DBMS_STATS.gather_schema_stats(ownname => ''scott'', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);',  next_date => trunc(sysdate)+8.5/24,
 interval => 'SYSDATE + 1',
 no_parse => TRUE );

 DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR
(jobno));

 COMMIT;
 END;
 /

PL/SQL procedure successfully completed.

SQL> select to_char(sysdate,'DD.MM.YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD

---

08.08.2012 08:27:34

SQL> SELECT
 'Job:'|| job,
 WHAT,

 'Next:'|| to_char(NEXT_DATE,'dd-Mon-yyyy HH24:MI'),
 ' Last:'|| to_char(LAST_DATE,'dd-Mon-yyyy HH24:MI'),
 ' Broken:'|| BROKEN

 FROM dba_jobs;

'JOB:'||JOB

---

WHAT

---

'NEXT:'||TO_CHAR(NEXT_ 'LAST:'||TO_CHAR(LAST_D 'BROKEN:'

---------------------- ----------------------- ---------

Job:2
DBMS_STATS.gather_schema_stats(ownname => 'scott', cascade => TRUE, estimate_per
cent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Next:08-Aug-2012 08:30  Last:                   Broken:N

SQL> exec
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

PL/SQL procedure successfully completed.

SQL> show parameter job_queue_processes;

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
job_queue_processes                  integer     10

• waited until after 08:30

SQL> select to_char(sysdate,'DD.MM.YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD

---

08.08.2012 08:32:56

SQL> select
  job,

  log_user                   subu,
  what                       proc,
  to_char(last_date,'MM/DD') lsd,
  substr(last_sec,1,5)       lst,
  to_char(next_date,'MM/DD') nrd,
  substr(next_sec,1,5)       nrt,
  failures                   fail,

  decode(broken,'Y','N','Y') ok
from
  sys.dba_jobs;

       JOB SUBU

---------- ------------------------------

PROC

---

LSD LST NRD NRT FAIL O
----- ----- ----- ----- ---------- -

         2 SYS
DBMS_STATS.gather_schema_stats(ownname => 'scott', cascade => TRUE, estimate_per
cent => DBMS_STATS.AUTO_SAMPLE_SIZE);

            08/08 08:30 Y

-> next run time in the past

Best regards,
Robert
http://robertvsoracle.blogspot.com

On Wed, Aug 8, 2012 at 8:16 AM, Niall Litchfield <niall.litchfield_at_gmail.com > wrote:

> Hence the probablys and I thinks in my mail! Can you double check that
> dbms_jobs don't run with that setting, I expect it is me misrembering, but
> possibly it was a bug ( certainly we didn't expect the new scheduler to
> disable the old job subsystem. )
> On Aug 8, 2012 7:09 AM, "Robert Hanuschke" <robert.hanuschke_at_gmail.com>
> wrote:
>
>> Hi Niall,
>>
>> was just trying that in one of our test databases:
>>
>> SQL> show parameter job_queue_processes;
>>
>> NAME                                 TYPE        VALUE
>> ------------------------------------ -----------
>> ------------------------------
>> job_queue_processes                  integer     10
>> SQL> exec
>> dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');
>>
>> PL/SQL procedure successfully completed.
>>
>> SQL> show parameter job_queue_processes;
>>
>> NAME                                 TYPE        VALUE
>> ------------------------------------ -----------
>> ------------------------------
>> job_queue_processes                  integer     10
>>
>>
>> At least disabling the scheduler that way did not modify the parameter.
>> Did you possibly do it another way?
>>
>> Best regards,
>> Robert
>> http://robertvsoracle.blogspot.com
>>
>> On Wed, Aug 8, 2012 at 8:01 AM, Niall Litchfield <
>> niall.litchfield_at_gmail.com> wrote:
>>
>>> One unsupported (probably) way of having this happen is to turn off the
>>> scheduler via its enabled/disabled property IIRC. We certainly had a db
>>> where disabling the scheduler disabled the dbms_job system as well, I
>>> *think* with the symptoms you describe.
>>>
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 08 2012 - 01:38:09 CDT