Re: Un-able to upload xml file - Error:ORA-14400: inserted partition key does not map to any partition
- From: Robert Hanuschke <robert.hanuschke@xxxxxxxxx>
- To: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- Date: Wed, 8 Aug 2012 08:38:09 +0200
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@xxxxxxxxx
> 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@xxxxxxxxx>
> 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@xxxxxxxxx> 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.
>>>
>>> --
>>> //www.freelists.org/webpage/oracle-l
>>>
>>>
>>>
>>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
