Find Current open Cursors in Oracle

oracleoracle10g

What is the query to find the no. of current open cursors in an Oracle Instance?

Also, what is the accuracy/update frequency of this data?

I am using Oracle 10gR2

Best Answer

Try this:

--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current';

I'm not sure but it should be live data.

From: http://www.orafaq.com/node/758

Related Solutions

ORA-12705: Cannot access NLS data files or invalid environment specified

I figured out that that you could pass that two params to your Java app to resolve the issue:

-Duser.country=en -Duser.language=en

You could configure the values at environment variable level as well (depends from your OS).

Oracle Account Not Responding

Try the following:

select s.username, s.sid, p.spid 
  from v$session s join v$process p on addr=paddr
where s.username = 'USER2';

Using the spid value from the above query, log on to the server and issue the following command from a DOS command prompt:

orakill YOURSID spid

YOURSID is the SID of the database instance you're dealing with.

orakill often works when the GUI tools fail to kill processes. Here is a nice overview of orakill. Note reason #1 for using orakill - it might explain why you can't use your GUI tool:

The alter system statement will not clear any locks that exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.

Update:

You can also try:

select sid, serial#, program from v$session;
alter system kill session '<SID>,<SERIAL#>' immediate;

although I wouldn't hold out a lot of hope...

Best Answer

Related Solutions

ORA-12705: Cannot access NLS data files or invalid environment specified

Oracle Account Not Responding

Related Topic