I've found note *Bug 15861775 ORA-1000 cursor leak from some V$ / X$ table accesses* but it doesn't exaplain why once it work fine and once it fails* .* On Thu, Jul 31, 2014 at 11:58 PM, amihay gonen <agonenil@xxxxxxxxx> wrote: > I've this test case of OEL 5 , 11.2.0.4 + with ASM > > drop /creating job result in ORA-1000 maximum cursor reached , this is > repoduiable , I wonder if anyone faced simliar problem. > I'll open SR at oracle ofcourse ... but I wonder if someone got this > problem > > > grant connect to test identified by test; > grant create procedure to test; > grant select on v_$ASM_DISKGROUP to test; > GRANT MANAGE SCHEDULER TO test; > > connect test/test > create or replace procedure test as > L_FREE_PCT number; > begin > SELECT ROUND(FREE_MB/TOTAL_MB*100) > INTO L_FREE_PCT > FROM SYS.V_$ASM_DISKGROUP > WHERE NAME='DATA'; > end; > / > > > exec DBMS_SCHEDULER.PURGE_LOG > exec SYS.DBMS_SCHEDULER.DROP_JOB( JOB_NAME => 'T') > exec SYS.DBMS_SCHEDULER.CREATE_JOB( JOB_NAME => 'T', JOB_TYPE => > 'STORED_PROCEDURE', JOB_ACTION => 'TEST', REPEAT_INTERVAL => > 'FREQ=SECONDLY;INTERVAL=1' , START_DATE => SYSTIMESTAMP, JOB_CLASS => > '"DEFAULT_JOB_CLASS"', AUTO_DROP => FALSE, ENABLED => true); > > exec dbms_lock.sleep(5); > select error#,status from USER_SCHEDULER_JOB_RUN_DETAILS where > job_name='T'; > > > *SQL> select error#,status from USER_SCHEDULER_JOB_RUN_DETAILS where > job_name='T';* > > * ERROR# STATUS* > *---------- ------------------------------* > * 1000 FAILED* > * 1000 FAILED* > * 1000 FAILED* > * 1000 FAILED* > > > exec DBMS_SCHEDULER.PURGE_LOG > exec SYS.DBMS_SCHEDULER.DROP_JOB( JOB_NAME => 'T') > exec SYS.DBMS_SCHEDULER.CREATE_JOB( JOB_NAME => 'T', JOB_TYPE => > 'STORED_PROCEDURE', JOB_ACTION => 'TEST', REPEAT_INTERVAL => > 'FREQ=SECONDLY;INTERVAL=1' , START_DATE => SYSTIMESTAMP, JOB_CLASS => > '"DEFAULT_JOB_CLASS"', AUTO_DROP => FALSE, ENABLED => true); > > exec dbms_lock.sleep(5); > select error#,status from USER_SCHEDULER_JOB_RUN_DETAILS where > job_name='T'; > > *"* > *SQL> select error#,status from USER_SCHEDULER_JOB_RUN_DETAILS where > job_name='T';* > > * ERROR# STATUS* > *---------- ------------------------------* > * 0 SUCCEEDED* > * 0 SUCCEEDED* > * 0 SUCCEEDED* > * 0 SUCCEEDED* > * 0 SUCCEEDED* > *"* >