Cursors

CURSORS

------------------/* OPEN CURSOR (HIGHEST_OPEN_CUR) */--------------------------
select max(a.value) as highest_open_cur, p.value as max_open_cur
  from v$sesstat a, v$statname b, v$parameter p
  where a.statistic# = b.statistic#  and b.name = 'opened cursors current' and p.name= 'open_cursors'
 group by p.value;
--------------------------------------     open cursors with machine name
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,s.OSUSER,s.username,s.machine ,name
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid and b.name like '%opened cursors%'
group by s.username, s.machine,s.OSUSER,name
order by 1 desc;
------------------------------------------------------------------
--------------------------total cursors open, by session
select sum(value) from ( 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' );

----------------------------------------------/* HIGHEST OPEN CURSORS */---------------------------
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current' and p.name= 'open_cursors'
group by p.value;
--SELECT * FROM V$DATAFILE WHERE NAME LIKE '%GOLD%'
--ALTER TABLESPACE GOLD32K ADD DATAFILE '/usr/oracle/oracle/product/10.2.0/db_1/oradata/proddb/GOLD32K37.dbf' SIZE 4096M
--------------------------------------/* OPEN CURSROS 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 = 'session cursor cache count' ;
----------------------------------------/* OPEN CURSORS WITH SQL TEXT */---------------------
select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id  -- for 9i and earlier use: c.address=sql.address
and c.sid=&sid;
------------------------------------------------/* SESSION CACHE CURSORS */----------------------
select cach.value cache_hits, prs.value all_parses, prs.value-cach.value sess_cur_cache_not_used
from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
where cach.statistic# = nm1.statistic# and nm1.name = 'session cursor cache hits' and prs.statistic#=nm2.statistic# and nm2.name= 'parse count (total)'
and cach.sid= &sid and prs.sid= cach.sid ;
----------------------------------------------/* COMPARE CACHE */---------------------------------
--session cached cursors, for a given SID, compared to max
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic#  and s.sid=a.sid and a.sid=&sid and p.name='session_cached_cursors' and b.name = 'session cursor cache count';
--------------------------------/* LIST ALL OBJECTS CORRUPTED  */-----------------------
select a.FILE#,a.block#,segment_name,owner,tablespace_name
from V$DATABASE_BLOCK_CORRUPTION a, dba_extents b where a.file#=b.file_id and a.block#=b.block_id ;

----------------------------------------------------------------------------------------------------------------------
--1.------- TO FIND CURSORS
----select USERNAME,count(USERNAME) from v$session where osuser = 'root'  group by USERNAME
select distinct NAME from V$STATNAME where NAME like '%cursor%';

--------2.
select --sum(to_number(PROCESS))
distinct SID,USER#,USERNAME,OSUSER,PROCESS,program,module from v$session; ----v$sysstat;

--------3. select *from v$open_cursor
------------4. TO FIND CURSOR VALUE
--SELECT v$session.SID,USERNAME, VALUE FROM V$SESSTAT , V$STATNAME ,v$session
--WHERE V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC# and NAME LIKE 'opened cursors current'
--and V$SESSTAT.sid=v$session .sid

SELECT v$session.SID,USERNAME, VALUE,name FROM V$SESSTAT , V$STATNAME ,v$session
WHERE V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC# and NAME LIKE '%cursors%'and V$SESSTAT.sid=v$session .sid ;

----------5. MAIN QUERY FOR SUM OF OPENED CURSORS FOR EACH USER

SELECT distinct USERNAME, sum(VALUE),name FROM V$SESSTAT , V$STATNAME ,v$session
WHERE V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC# and NAME LIKE '%cursors%'
and V$SESSTAT.sid=v$session .sid and username is not null
group by USERNAME,name order by 1 ;

SELECT distinct USERNAME, sum(VALUE) FROM V$SESSTAT , V$STATNAME ,v$session
WHERE V$SESSTAT.STATISTIC# = V$STATNAME.STATISTIC# and NAME LIKE '%cursors%'
and V$SESSTAT.sid=v$session .sid and username is not null
group by USERNAME order by 1 ;

----------1.  SUM ALL OPENED CURSORS
select   user_process username,"Recursive Calls","Opened Cursors","Current Cursors"
from  (    select   nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
                                    sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
                                    sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
                                    sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
            from      v$session ss, v$sesstat se, v$statname sn
            where    se.STATISTIC# = sn.STATISTIC#
            and       (NAME  like '%opened cursors current%' or NAME  like '%recursive calls%' or NAME  like '%opened cursors cumulative%')
            and       se.SID = ss.SID
            and       ss.USERNAME is not null
            group    by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
) orasnap_user_cursors
order     by USER_PROCESS,"Recursive Calls";
------------------------------------------------------/* SIZE OF SCHEMAS */---------------------------
----SELECT * FROM CL_CONTRACTS WHERE ROWNUM<10
--SELECT * FROM DBA_TABLESPACES

-----------------------------
--SELECT ---tablespace_name,
--Sum(bytes)/1024/1024 AS total_size_mb,Sum(bytes)/1024/1024/1024 AS total_size_gb
--FROM dba_segments;
--------------------------
----SELECT tablespace_name,
----Sum(bytes)/1024/1024 AS total_size_mb,Sum(bytes)/1024/1024/1024 AS total_size_gb
----FROM dba_segments
----group by tablespace_name
---------------------------- SIZE OF SCHEMAS
SELECT owner,Sum(bytes)/1024/1024 AS total_size_mb,Sum(bytes)/1024/1024/1024 AS total_size_gb
FROM dba_segments
group by owner order by 1 ;
----------------------------------------------------/* KILL THE SESSION */------------------
----select * from dual
--SELECT s.sid,       s.serial#,       s.osuser,       s.program FROM   v$session s;
select s.sid,s.serial#,s.program,sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,s.OSUSER,s.username,s.machine ,name
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name like '%opened cursors%' --and OSUSER like '%vkrishna%'
group by s.username, s.machine,s.OSUSER,name,s.sid,s.serial#,s.program
order by 4 desc;


select   b.username,   a.sql_text
from    v$sqltext_with_newlines a,   v$session b,   v$process c
where   c.spid = to_number('300', 'xxx') and    c.addr = b.paddr and   b.sql_address = a.address;

------------------/*   DISPLAYS ALL CONNECTED SESSIONS  */-------------

select rpad(c.name||':',11)||rpad(' current logons='|| (to_number(b.sessions_current)),20)||'cumulative logons='||
      rpad(substr(a.value,1,10),10)||'highwater mark='|| b.sessions_highwater Information
from   v$sysstat a,   v$license b,   v$database c
where a.name = 'logons cumulative';

--------------------------ttitle "dbname Database|UNIX/Oracle Sessions";
select 'Sessions on database '||substr(name,1,8) from v$database;
select substr(a.spid,1,9) pid,substr(b.sid,1,5) sid,substr(b.serial#,1,20) ser#,substr(b.machine,1,30) box,substr(b.username,1,10) username,-- b.server,
   substr(b.osuser,1,8) os_user,substr(b.program,1,30) program
from v$session b,v$process a
where b.paddr = a.addr and type='USER'
order by spid;
--------ttitle off;
----set heading off;
----select 'To kill, enter SQLPLUS> ALTER SYSTEM KILL SESSION',
----''''||'SID, SER#'||''''||';' from dual;

Select user,OSUSER,MACHINE,'alter system kill session '''||sid||','||serial#||''''
From v$session
Where --status = 'INACTIVE'
status = 'ACTIVE';
----And last_call_et > 7200;
alter system kill session '1857,44693'  IMMEDIATE;;
desc v$session;
--------------The SID and SERIAL# values of the relevant session can then be substituted into the following statement:

------ALTER SYSTEM KILL SESSION 'sid,serial#'
--alter system kill session '300,4035';

--In some situations the Oracle.exe is not able to kill the session immediately. In these cases the session will be "marked for kill".
--It will then be killed as soon as possible.Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session.
--If the marked session persists for some time you may consider killing the process at the operating system level.
--However, this is not recommended as it is dangerous and can lead to instance failures.
--
--It is possible to force the kill by adding the IMMEDIATE keyword:

--ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE
--ALTER SYSTEM KILL SESSION '3267,1025' IMMEDIATE;

/* Kill Sessions Script */
--set serveroutput on
--BEGIN
--IF USER = 'SYS' THEN
--FOR i IN (SELECT sid ,s.serial# ,p.spid ,osuser ,status ,round((last_call_et / 60)) AS idle_time
--FROM v$session s ,v$process p WHERE p.addr = s.paddr AND status = 'INACTIVE' AND round((last_call_et / 60)) > 15
--ORDER BY osuser)
--LOOP dbms_output.put_line(rpad(i.osuser, 20, '.') || ' SPID ' || rpad(i.spid, 10, '.') || ' SID ' || rpad(i.sid, 10, '.') || ' SERIAL# ' || rpad(i.serial#, 10, '.'));
--EXECUTE IMMEDIATE 'Alter system kill session ' || '''' || i.sid || ',' || i.serial# || '''';
--END LOOP;
--END IF;
--END;
--/ exit; /* Batch file */ Save above script in file named killsession.sql. Save following string into file named killsession.bat
--and add .bet file into window scheduler. sqlplus sys/sgoracle@db15 as sysdba @killsession.sql
-----------------------------/* FIND THE LOCKED OBJECTS */---------------------

--------To avoid having to stare at the table and cross-compare ID1's and ID2's, put this in a query:
--select l1.sid, ' IS BLOCKING ', l2.sid
--from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;
--
--
--select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
--from v$locked_object a ,v$session b,dba_objects c
--where b.sid = a.session_id and a.object_id = c.object_id;
--
--alter system kill session '2141,211'  IMMEDIATE;;

----  SELECT *FROM V$SESSION WHERE STATUS='ACTIVE'
--SELECT * FROM V$SQLAREA WHERE SQL_ID='ggn0bnsv2849y'