Re: Q: ORA-1000 when querying v$asm_diskgroup

From: amihay gonen <agonenil_at_gmail.com>
Date: Fri, 1 Aug 2014 00:14:16 +0300
Message-ID: <CAKb+SBUHz_1WA8f=BGf4ho38TcrF54QocnMHi_chP45CRfGb0w_at_mail.gmail.com>

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_at_gmail.com> 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*
> *"*
>

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

Received on Thu Jul 31 2014 - 23:14:16 CEST