Script: Some Useful Oracle Database Monitoring Commands for DBA's - PART1

Find Blocking session:

select INST_ID,sid,serial#,username,status,BLOCKING_SESSION_STATUS,
terminal,program,sql_id,BLOCKING_SESSION,EVENT
 from gv$session a where BLOCKING_SESSION  IS NOT NULL;

Find Blocked Object:

select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
from gv$locked_object a ,gv$session b,dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;

Identify the Hung Materialized View: 

select VS.INST_ID,VL.SID||','||VS.SERIAL#,VS.USERNAME,ao.object_name,
'alter system kill session '''||vl.SID||','||vs.SERIAL#|| ',@'||vs.inst_id ||''' IMMEDIATE;'
  from GV$LOCK VL, Gv$session VS, all_objects ao
 where vl.type = 'JI' and vl.Lmode = 6 and VS.SID=VL.SID
 and VS.INST_ID=VL.inst_id and vl.ID1=ao.object_id;

Find Pending Transaction:

select * from pending_trans$ where state='prepared'
select 'commit force '''||local_tran_id||''';' from pending_trans$ where state='prepared'
execute DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('transanction_id');

Total Active Sessions Count - EXCLUDING BACKGROUND SESSIONS:

select inst_id,count(1)  from gv$session g
where type != 'BACKGROUND' and status='ACTIVE' GROUP BY INST_ID order by inst_id;

Active Session Count Excluding Background Sessions:

select inst_id,username,count(1)   from gv$session
 where type != 'BACKGROUND' and status='ACTIVE' and username is not null
 group by inst_id,username order by count(1) desc;

Program-wise Session count:

select inst_id,program,count(1)
from gv$session where type<>'BACKGROUND' AND USERNAME IS NOT NULL
group by inst_id,program order by 2 desc;

w3wp.exe sessions - Worker Process:

select inst_id,machine,count(1) from gv$session
where program='w3wp.exe' and status='INACTIVE'
group by inst_id,machine
order by 2;

User Session Consuming Concurrency, Cluster, User I/O:

Select inst_id "Inst",SID||','||serial# "SidS#",username,PROGRAM,sql_id,wait_class,status,machine,terminal,logon_time
from gv$session where type != 'BACKGROUND'   and status = 'ACTIVE'
and username is not null and wait_class in
('Concurrency', 'System I/O', 'User I/O','Network','Apllication')
order by inst_id, username;

Find FRA Size:

select name,
       space_limit / 1024 / 1024 / 1024 as Total_size,
       space_used / 1024 / 1024 / 1024 as Used,
       SPACE_RECLAIMABLE / 1024 / 1024 / 1024 as reclaimable,
       NUMBER_OF_FILES as "number"
  from V$RECOVERY_FILE_DEST;

Thats it..Hope this will help you.. :)

Best Regards,