Monitoring Scripts
--Begin--
set serveroutput on size 50000
set echo off feed off veri off
accept SID prompt 'Enter Oracle SID: '
DECLARE
v_sid number;
s sys.v_$session%ROWTYPE;
p sys.v_$process%ROWTYPE;
BEGIN
begin
select sid into v_sid
from sys.v_$session s
where sid = &&SID;
exception
when no_data_found then
dbms_output.put_line('Unable to find SID &&SID!!!');
return;
when others then
dbms_output.put_line(sqlerrm||'naaaaaaaaaaaaaaaaaaaaa');
return;
end;
select * into s from sys.v_$session where sid = v_sid;
select * into p from sys.v_$process where addr = s.paddr;
dbms_output.put_line('=====================================================================');
dbms_output.put_line('SID/Serial : '|| s.sid||','||s.serial#);
dbms_output.put_line('Foreground : '|| 'PID: '||s.process||' - '||s.program);
dbms_output.put_line('Shadow : '|| 'PID: '||p.spid||' - '||p.program);
dbms_output.put_line('Terminal : '|| s.terminal || '/ ' || p.terminal);
dbms_output.put_line('OS User : '|| s.osuser||' on '||s.machine);
dbms_output.put_line('Ora User : '|| s.username);
dbms_output.put_line('Status Flags: '|| s.status||' '||s.server||' '||s.type);
dbms_output.put_line('Tran Active : '|| nvl(s.taddr, 'NONE'));
dbms_output.put_line('Login Time : '|| to_char(s.logon_time, 'Dy HH24:MI:SS'));
dbms_output.put_line('Last Call : '|| to_char(sysdate-(s.last_call_et/60/60/24), 'Dy HH24:MI:SS') || ' - ' || to_char(s.last_call_et/60, '9999999999.0') || ' min');
dbms_output.put_line('Lock/ Latch : '|| nvl(s.lockwait, 'NONE')||'/ '||nvl(p.latchwait, 'NONE'));
dbms_output.put_line('Latch Spin : '|| nvl(p.latchspin, 'NONE'));
dbms_output.put_line('Current SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.sql_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Previous SQL statement:');
for c1 in ( select * from sys.v_$sqltext
where HASH_VALUE = s.prev_hash_value order by piece) loop
dbms_output.put_line(chr(9)||c1.sql_text);
end loop;
dbms_output.put_line('Session Waits:');
for c1 in ( select * from sys.v_$session_wait where sid = s.sid) loop
dbms_output.put_line(chr(9)||c1.state||': '||c1.event);
end loop;
dbms_output.put_line('Connect Info:');
for c1 in ( select * from sys.v_$session_connect_info where sid = s.sid) loop
dbms_output.put_line(chr(9)||': '||c1.network_service_banner);
end loop;
dbms_output.put_line('Locks:');
for c1 in ( select
decode(l.type,
-- Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
-- Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', o.object_name,
'TD', o.object_name,
'TM', o.object_name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE', 'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) objname
from sys.v_$lock l, dba_objects o
where sid = s.sid
and l.id1 = o.object_id(+) ) loop
dbms_output.put_line(chr(9)||c1.type||' H: '||c1.lmode||' R: '||c1.lrequest||' - '||c1.objname);
end loop;
dbms_output.put_line('=====================================================================');
END;
/
undef SID
=====END===
____________________________________________________
To Check Activity of a SID
Monitoring Session(s), Performance, Load, Killing Session etc
Tablespace Monitoring Scripts
select df.tablespace_name, df.file_name, round(df.bytes/1024/1024) TotalSizeMB, nvl(round(usedBytes/1024/1024), 0) UsedMB, nvl(round(freeBytes/1024/1024), 0) AvailableMB,
nvl(round(freeBytes/df.bytes * 100), 0) freePerc, df.autoextensible
from dba_data_files df
left join (
select file_id, sum(bytes) usedBytes
from dba_extents
group by file_id
) ext on df.file_id = ext.file_id
left join (
select file_id, sum(bytes) freeBytes
from dba_free_space
group by file_id
) free on df.file_id = free.file_id
order by df.tablespace_name, df.file_name
select a.TABLESPACE_NAME,
(select file_name from dba_temp_files where FILE_ID=a.file_id) filen,
(a.bytes_used)/1024/1024 "Space Used MB",
(a.bytes_free)/1024/1024 "Space Free MB"
FROM V$temp_space_header a
Below is the monitoring scripts which gives us the details about
Total tablespace size, Used space, Free space, Largest Space in
tablespace,Percentage utilizes and Alarm mark (*) if it exceeds 90 %.
select tbs.tablespace_name,
tot.bytes/1024/1024 total,
tot.bytes/1024/1024-sum(nvl(fre.bytes,0))/1024/1024 used,
sum(nvl(fre.bytes,0))/1024/1024 free,
max(fre.bytes/1024/1024) Largest_Space,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),
90, '', '*'
) pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024/1024, tot.bytes
order by 6 desc;
____________________________________________________
To check the Uptime of the Database
SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time"
FROM sys.v_$instance;
____________________________________________________
To check UNDO Space Status
elect status , sum(blocks)*8192/1024/1024/1024 GB from dba_undo_extents group by status;
____________________________________________________
To find the used and free space in Temporary Tablespace
SELECT tablespace_name, SUM(bytes_used)/1024/1024 "Space Used MB", SUM(bytes_free)/1024/1024 "Space Free MB"
FROM V$temp_space_header
GROUP BY tablespace_name;
____________________________________________________
Flash Recovery
SELECT NAME, SPACE_LIMIT/1024/1024,SPACE_USED/1024/1024 FROM V$RECOVERY_FILE_DEST;
after manually deleting the archive log files
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
then check the space utilization in the FRA space.
___________________________________________________
To check the status of synchronization between Production and Standby Database
conn <username>/<password>@<TNS-Name of Production Database>
select a.name DB_NAME, b.lh LOG_HIST,c.al ARCH_LOG
from
(select name||' (PROD) -> ' name from v$database) a,
(select max(sequence#) lh from v$log_history) b,
(select max(sequence#) al from v$archived_log) c;
conn sys/<password>@<TNS-Name of DR Database> as sysdba
select a.name,a.open_mode,log_mode,to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') "DATE" from v$database a,dual;
select a.name DB_NAME, b.lh LOG_HIST,c.al ARCH_LOG
from
(select name||' (DR) -> ' name from v$database) a,
(select max(sequence#) lh from v$log_history) b,
(select max(sequence#) al from v$archived_log) c;
Note :
Sequence# from V$log_history gives us the archived log that has been applied to the database
Sequence# from V$archived_log gives us the archived log that has been registered and copied to the DR Database
____________________________________________________
To get information about the Database
Control Files
SELECT Substr(c.name,1,60) "Controlfile",
NVL(c.status,'UNKNOWN') "Status"
FROM v$controlfile c
ORDER BY 1;
Datafiles
SELECT Substr(d.name,1,60) "Datafile",
NVL(d.status,'UNKNOWN') "Status",
d.enabled "Enabled",
LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
FROM v$datafile d
ORDER BY 1;
Tempfiles
select a.TABLESPACE_NAME,
(select file_name from dba_temp_files where FILE_ID=a.file_id) filen,
(a.bytes_used)/1024/1024 "Space Used MB",
(a.bytes_free)/1024/1024 "Space Free MB"
FROM V$temp_space_header a
Log Files
select a.group#,to_char(b.first_time,'DDMMYY HH24:mm') first_time,b.status,a.member,b.bytes/1024/1024 Megs
from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1
____________________________________________________
Client IP address
select sys_context('USERENV','IP_ADDRESS') from dual;
Server IP address
select utl_inaddr.get_host_address from dual;
____________________________________________________
To Check locks on the database.
select session_id "sid",SERIAL# "Serial",
substr(object_name,1,20) "Object",
substr(os_user_name,1,10) "Terminal",
substr(oracle_username,1,10) "Locker",
nvl(lockwait,'active') "Wait",
decode(locked_mode,
2, 'row share',
3, 'row exclusive',
4, 'share',
5, 'share row exclusive',
6, 'exclusive', 'unknown') "Lockmode",
OBJECT_TYPE "Type"
FROM
SYS.V_$LOCKED_OBJECT A,
SYS.DBA_OBJECTS B,
SYS.V_$SESSION c
WHERE
A.OBJECT_ID = B.OBJECT_ID AND
C.SID = A.SESSION_ID
ORDER BY 1 ASC, 5 Desc
/
____________________________________________________
To check Memory utilization by connected sessions.
SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN module FORMAT A20
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
a.program,
Trunc(b.value/1024) AS memory_kb
FROM v$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic# = c.statistic#
AND c.name = 'PGA Memory'
AND a.program IS NOT NULL
ORDER BY b.value DESC;
____________________________________________________
To check the status of the Redo log files
col member for a30
set lines 120
col status for a10
select a.group#,to_char(b.first_time,'DDMMYY HH24:mm') first_time,b.status,a.member,b.bytes/1024/1024 Megs
from V$LOGFILE a, V$LOG b where a.group#=b.group# order by 1;
____________________________________________________
To SID of Particular resouce consuming PID
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) 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' and a.spid=<PID No> order by spid;