RE: How to identify sql texts which are having open cursor?

select, by user, from v$sqlarea, a list of cursors in the pool from a typical user. (see sql_addr and hash_value for joins).

I am sure "someone" "somewhere" has a script you can run to do this.

mit gluck

Hans

|| -----Original Message-----
|| From: Shantanu Talukder [mailto:shantanu_t_at_yahoo.com]
|| Sent: Tuesday, June 05, 2001 6:14 PM
|| To: Multiple recipients of list ORACLE-L
|| Subject: How to identify sql texts which are having open cursor?
||
||
|| Our application is using up open_cursor limits of 500.
|| We are suspecting that application code written in
|| Java
|| are not closing cursors explicitly.
|| Is there a way to idntify those sql code which are not
|| closing cursor?
|| We just don't want to increase open_cursor parameter
|| in init.ora and bounce the db without identifying sql.
||
||
|| HTH,
|| Shantanu
||
|| --- "Kirsh, Gary" <gary.kirsh_at_gs.com> wrote:
|| > Jared,
|| >
|| > I think that the default java_pool_size is 20M, not
|| > 30M.
|| >
|| > Gary
|| >
|| >
|| > -----Original Message-----
|| > Sent: Tuesday, June 05, 2001 4:01 PM
|| > To: Multiple recipients of list ORACLE-L
|| >
|| >
|| >
|| > Take a look at the SGA with:
|| >
|| > select * from v$sgastat
|| > order by 1,2;
|| >
|| > You will likely see 30M dedicated to the
|| > java pool, as that is the default. This
|| > can be cut back substantially if you are
|| > not making use of java.
|| >
|| > Jared
|| >
|| >
|| > On Tuesday 05 June 2001 05:01, C.S.Venkata
|| > Subramanian wrote:
|| > > One of my co-worker came with this ques. In the
|| > init.ora file he had set
|| > > the shared_pool_size to 10M and
|| > db_block_buffers=1024 and redolog buffer
|| > to
|| > > 32768. but when he did sho sga in the sql*plus
|| > prompt he got the following
|| > > output
|| > >
|| > > Total System Global Area 41297948 bytes
|| > > Fixed Size 75804 bytes
|| > > Variable Size 32755712 bytes
|| > > Database Buffers 8388608 bytes
|| > > Redo Buffers 77824 bytes
|| > >
|| > > Doing a select on V$SGA also gave the same result.
|| > >
|| > > My shared pool size is only 10M, why it is showing
|| > nearly 40M. From where
|| > > did oracle derive the extra MB's? I ran throu the
|| > oracle manual and got
|| > > only answer for database buffers.
|| > >
|| > > Can any one tell from where these extra MB's come
|| > to Oracle from the OS.
|| > >
|| > > TIA
|| > >
|| > > Venkat
|| > >
|| > >
|| > > Get 250 color business cards for FREE!
|| > > http://businesscards.lycos.com/vp/fastpath/
|| > --
|| > Please see the official ORACLE-L FAQ:
|| > http://www.orafaq.com
|| > --
|| > Author: Jared Still
|| > INET: jkstill_at_cybcon.com
|| >
|| > Fat City Network Services -- (858) 538-5051 FAX:
|| > (858) 538-5051
|| > San Diego, California -- Public Internet
|| > access / Mailing Lists
|| >
|| --------------------------------------------------------------------
|| > To REMOVE yourself from this mailing list, send an
|| > E-Mail message
|| > to: ListGuru_at_fatcity.com (note EXACT spelling of
|| > 'ListGuru') and in
|| > the message BODY, include a line containing: UNSUB
|| > ORACLE-L
|| > (or the name of mailing list you want to be removed
|| > from). You may
|| > also send the HELP command for other information
|| > (like subscribing).
|| > --
|| > Please see the official ORACLE-L FAQ:
|| > http://www.orafaq.com
|| > --
|| > Author: Kirsh, Gary
|| > INET: gary.kirsh_at_gs.com
|| >
|| > Fat City Network Services -- (858) 538-5051 FAX:
|| > (858) 538-5051
|| > San Diego, California -- Public Internet
|| > access / Mailing Lists
|| >
|| --------------------------------------------------------------------
|| > To REMOVE yourself from this mailing list, send an
|| > E-Mail message
|| > to: ListGuru_at_fatcity.com (note EXACT spelling of
|| > 'ListGuru') and in
|| > the message BODY, include a line containing: UNSUB
|| > ORACLE-L
|| > (or the name of mailing list you want to be removed
|| > from). You may
|| > also send the HELP command for other information
|| > (like subscribing).
||
||
|| __________________________________________________
|| Do You Yahoo!?
|| Get personalized email addresses from Yahoo! Mail - only $35
|| a year! http://personal.mail.yahoo.com/
|| --
|| Please see the official ORACLE-L FAQ: http://www.orafaq.com
|| --
|| Author: Shantanu Talukder
|| INET: shantanu_t_at_yahoo.com
||
|| Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
|| San Diego, California -- Public Internet access /
|| Mailing Lists
|| --------------------------------------------------------------------
|| To REMOVE yourself from this mailing list, send an E-Mail message
|| to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
|| the message BODY, include a line containing: UNSUB ORACLE-L
|| (or the name of mailing list you want to be removed from). You may
|| also send the HELP command for other information (like subscribing).
||

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohan, Ross
  INET: MohanR_at_STARS-SMI.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).