<%@ LANGUAGE="VBScript" %> Open Sessions

Open Sessions

<% Set Connection = Server.CreateObject("ADODB.Connection") Connection.Mode = adModeRead db_name=UCase(CStr(Request("db_name"))) If db_name="" Then db_name="PHDB" End If Set Connection = Server.CreateObject("ADODB.Connection") Connection.Mode = adModeRead Select Case db_name Case "PHDB" Connection.Open phdb_rd Case Else Connection.Open phdb_rd End Select ' ' check how many sessions and open cursors we have... ' SQLStmt = "select count(*) open_cursors from v$open_cursor" Set RS0 = Connection.Execute(SQLStmt) open_cursors=CInt(RS0("open_cursors")) Set RS0=nothing SQLStmt = "select count(sid) sessions from v$session" Set RS0 = Connection.Execute(SQLStmt) sessions=CInt(RS0("sessions")) Set RS0=nothing %>

There are <%=sessions%> sessions with <%=open_cursors%> open cursors.

<% SQLStmt = " select max(sid) sid,max(username) username,max(osuser) osuser,max(program) program,max(machine) machine,max(ldate) ldate,max(logon_time) logon_time,max(cpu) cpu,count(osid) ncursors from " SQLStmt = SQLStmt & " (select vv.sid,nvl(vv.USERNAME,'ORACLE PROC') username,nvl(vv.osuser,' ') osuser,vv.program,vv.machine,to_char(vv.logon_time,'MM/DD HH24:MI:SS') ldate,logon_time,oc.sid osid,cpu " SQLStmt = SQLStmt & " from v$session vv,v$open_cursor oc,(select sid,value cpu from v$sesstat where statistic#=12) vcpu " SQLStmt = SQLStmt & " where vv.sid=oc.sid(+) and vv.sid=vcpu.sid(+)) " SQLStmt = SQLStmt & " group by sid " SQLStmt = SQLStmt & " order by logon_time desc" ' response.write(SQLStmt) Set RS = Connection.Execute(SQLStmt) nopen=0 Do While Not RS.EOF nopen=nopen+1 sid=CInt(RS("sid")) ncursors=CInt(RS("ncursors")) cpu=CStr(RS("cpu")) %> <% RS.Movenext Loop %>
Login SID Curs CPU Name OS user Machine Program
<%=RS("ldate")%> <%=sid%> <% If ncursors>0 Then %> <%=ncursors%> <% Else %> 0 <% End If %> <%=cpu%> <% If Not IsNull(RS("username")) Then response.write(RS("username")) End If %> <% If Not IsNull(RS("osuser")) Then response.write(RS("osuser")) End If %> <%=RS("machine")%> <%=RS("program")%>
<% Connection.Close Set Connection=nothing Set RS=nothing %>