Database Monitor

-------------------------------Tablespace Status Check ------------------------------------

select tablespace_name  "TABLESPACE NAME",
       round (sum (a.cursize) / (1024*1024)) "SIZE (MB)",
       round ((sum (a.cursize) - sum (a.curfree)) / (1024*1024)) "USED (MB)",
       round (sum (a.curfree) / (1024*1024)) "FREE (MB)",
       round (sum (a.curfree) * 100 / sum (a.cursize), 1) as "FREE %",
       100 - round (sum (a.curfree) * 100 / sum (a.cursize), 1) as "USED %",
       CASE
         WHEN (100 - round (sum (a.curfree) * 100 / sum (a.cursize), 1))>=75
         AND  (100 - round (sum (a.curfree) * 100 / sum (a.cursize), 1))<85 THEN 'LOW'
         WHEN (100 - round (sum (a.curfree) * 100 / sum (a.cursize), 1))>=85
         AND  (100 - round (sum (a.curfree) * 100 / sum (a.cursize), 1))<95 THEN 'SERIOUS'
         WHEN (100 - round (sum (a.curfree) * 100 / sum (a.cursize), 1))>=95 THEN 'CATASTROPHIC'
       ELSE NULL
       END AS "SEVERITY"  
from ( select tablespace_name, sum(bytes) cursize, 0 curfree, 0 chunks, 0 biggest
       from sys.dba_data_files
       group by tablespace_name
       union
       select tablespace_name, 0, sum (bytes), count (block_id), max (bytes)
       from sys.dba_free_space
       group by tablespace_name ) a
group by tablespace_name
order by tablespace_name;


---------------------------------------Temporary Tablespace --------------------------------

SELECT   A.tablespace_name tablespace, D.mb_total,
         SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
         D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM     v$sort_segment A,
         (
         SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
         FROM     v$tablespace B, v$tempfile C
         WHERE    B.ts#= C.ts#
         GROUP BY B.name, C.block_size
         ) D
WHERE    A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


--------------------------------- ASM Disk Space Check ---------------------------------

SELECT name, type, total_mb, free_mb, required_mirror_free_mb, usable_file_mb FROM V$ASM_DISKGROUP;




------------------------------------------  LOCKING    -------------------------------------------

SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess_sid
FROM V$LOCK WHERE (id1, id2, type) IN (  SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
      (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request


select sid,serial#,action,logon_time
from v$session
where sid in
                 (select session_id from dba_locks
             where blocking_others like 'Blocking')
           

select * from v$session where sid in (106)

select SID, SERIAL#, USERNAME, STATUS, SCHEMANAME ,OSUSER ,MODULE,ACTION,LOGON_TIME ,LAST_CALL_ET
from v$session where sid in (106)

alter system kill session 'sid,serial#';





------------------------------------  Active Form Session ------ -----------------------------


SELECT machine,sid,serial#, SUBSTR(program,1,20) program, action,status,
TO_CHAR(logon_time,'DD-MON-YY HH24:SS') Login_Time,
TO_CHAR(SYSDATE-last_call_et/86400,'DD-MON-YY HH24:SS')
Last_Activity,
process pid
FROM v$session
--WHERE action LIKE 'FRM%';


----------------------------------------  Inactive Session ----------------------------------------

SELECT
    p.spid,
    s.process,
    s.status,
    s.machine,
    s.osuser,
    s.username,
    to_char(s.logon_time,'dd-Mon-yy hh24:mi:ss') Logon_Time,
    round(s.last_call_et/3600, 2) Last_Call_ET,
    s.action,
    s.module,
    s.sid,
    s.serial#
FROM
    V$SESSION s, V$PROCESS p
WHERE
    s.paddr = p.addr
AND s.username IS NOT NULL
--AND s.username = 'APPS'
AND s.osuser = 'oracle'
AND s.last_call_et/3600 > 3
--and s.action like 'FRM%'
-- and s.module in ('INVTTMTX','INVTVQOH','INVTOTRX')
and s.status='INACTIVE' order by logon_time;



-----------------------------------------Running SQL ---------------------------------------------

Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username like 'user_name')
 --and sql_text like '%TBLINVOICEDATA%'


Select  SA.PARSING_SCHEMA_NAME, sa.sql_text, SA.APPLICATION_WAIT_TIME, SA.BUFFER_GETS, SA.CPU_TIME,
        SA.ELAPSED_TIME, SA.EXECUTIONS, SA.LAST_ACTIVE_TIME, SA.LAST_LOAD_TIME
from   v$sqlarea sa
where  (address, hash_value) in
(select sql_address, sql_hash_value
        from v$session
        where username in ('user_name1','username2'))


Select  SA.PARSING_SCHEMA_NAME, sa.sql_text, SA.APPLICATION_WAIT_TIME, SA.BUFFER_GETS, SA.CPU_TIME,
        SA.ELAPSED_TIME, SA.EXECUTIONS, SA.LAST_ACTIVE_TIME, SA.LAST_LOAD_TIME
from   v$sqlarea sa
where last_active_time between to_date('07-MAY-2012 2:45 PM', 'dd-mon-yyyy hh:mi PM')  and to_date('07-MAY-2012 3:15 PM', 'dd-mon-yyyy hh:mi PM')
and sql_text like '%TBLMACCOUNT%'






-------------------------------------- Parallel Query Check -----------------------------------



SELECT * from v$pq_sysstat;

SELECT * FROM v$px_process;

SELECT slave_name,status, cpu_secs_total   FROM v$pq_slave;

SELECT * FROM v$pq_slave;

SELECT * FROM v$px_sesstat;

SELECT * FROM v$px_process_sysstat;

select name, value from v$sysstat where name like 'Parallel%'

SELECT sid, event, seq#,p1,p2,p3, wait_time
      FROM v$session_wait
      WHERE upper(event) like ('PX%')
      ORDER BY 1;

select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(s.sid) "SID",
to_char(px.inst_id) "Slave INST",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST",
px.req_degree "Req. DOP",
px.degree "Actual DOP"
from gv$px_session px,
gv$session s ,
gv$px_process pp
where px.sid=s.sid (+)
and px.serial#=s.serial#(+)
and px.inst_id = s.inst_id(+)
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by 6 , 1 desc


select
  decode(px.qcinst_id,NULL,username,
        ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
  decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
  to_char( px.server_set) "Slave Set",
  to_char(s.sid) "SID",
  decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
  px.req_degree "Requested DOP",
  px.degree "Actual DOP"
from
  v$px_session px,
  v$session s
where
  px.sid=s.sid (+)
 and
  px.serial#=s.serial#
order by 5 , 1 desc