Oracle Database Monitoring Scripts

 /*List of Accessed Objects */
sET PAGESIZE 60
SET LINESIZE 300
COLUMN type FORMAT a40
COLUMN sid FORMAT 9999
COLUMN object FORMAT a40
COLUMN owner FORMAT a20
SELECT a.type, Substr(a.owner,1,30) owner, a.sid,Substr(a.object,1,30) object
FROM   v$access a WHERE  a.owner NOT IN ('SYS','PUBLIC') ORDER BY 1,2,3,4;

/*CPU Usage for Active Sessions*/
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)"  FORMAT 999,999,999.0000
SELECT   s.username,  t.sid,  s.serial#, SUM(VALUE/100) as "cpu usage (seconds)"
FROM   v$session s, v$sesstat t, v$statname n WHERE   t.STATISTIC# = n.STATISTIC#
AND   NAME like '%CPU used by this session%' AND    t.SID = s.SID AND    s.status='ACTIVE'
AND    s.username is not null GROUP BY username,t.sid,s.serial#;

/*Display all logged sessions*/
 SET LINESIZE 500
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN spid FORMAT A10
COLUMN service_name FORMAT A15
COLUMN module FORMAT A35
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20
SELECT NVL(s.username, '(oracle)') AS username, s.osuser, s.sid,s.serial#, p.spid,s.lockwait,
 s.status,s.service_name,s.module,s.machine,s.program,TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM   v$session s, v$process p WHERE  s.paddr = p.addr
ORDER BY s.username, s.osuser;

/*Displays Last Analyzed Details for a given Schema,All schema owners if 'ALL' specified*/
 SET PAGESIZE 60
SET LINESIZE 300
SELECT t.owner, t.table_name AS "Table Name", t.num_rows AS "Rows", t.avg_row_len AS "Avg Row Len",
Trunc((t.blocks * p.value)/1024) AS "Size KB", to_char(t.last_analyzed,'DD/MM/YYYY HH24:MM:SS') AS "Last Analyzed" FROM   dba_tables t,v$parameter p WHERE t.owner = Decode(Upper('&&Table_Owner'), 'ALL', t.owner, Upper('&&Table_Owner')) AND   p.name = 'db_block_size' ORDER by t.owner,t.last_analyzed,t.table_name ;

/*Lists the volume of archived redo by hour for the specified day */
SET VERIFY OFF PAGESIZE 30
WITH hours AS (
 SELECT TRUNC(SYSDATE) - &1 + ((level-1)/24) AS hours
 FROM   dual  CONNECT BY level < = 24
)
SELECT h.hours AS date_hour,
 ROUND(SUM(blocks * block_size)/1024/1024/1024,2) size_gb FROM   hours h
 LEFT OUTER JOIN v$archived_log al ON h.hours = TRUNC(al.first_time, 'HH24')
GROUP BY h.hours ORDER BY h.hours;

/* Archived logs list*/
 sELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM
( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY,  Count(1)
Count#,  Min(RECID) Min#, Max(RECID) Max# FROM
v$log_history GROUP BY  To_Char(First_Time,'YYYY-MM-DD')
ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#,Count(1) Count#,
Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes FROM v$log ) B ;
 
/*Archive Generation History*/
select trunc(COMPLETION_TIME,'HH') Hour,thread# ,round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
 
/* Archivelog history*/
col "MONTH" FOR a14
col "DAY" for a28
select to_char(trunc(first_time), 'Month') Month, to_char(trunc(first_time), 'Day : DD-Mon-YYYY') Day, count(*) counts from v$log_history where trunc(first_time) > last_day(sysdate-100) +1 group by trunc(first_time);

/*Cache hit ratio*/
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio" from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads';

/* Check database locks and blockings*/
sELECT SUBSTR(TO_CHAR(session_id),1,5) "SID", SUBSTR(lock_type,1,15) "Lock Type",       SUBSTR(mode_held,1,15) "Mode Held",  SUBSTR(blocking_others,1,15) "Blocking?"  FROM dba_locks ;

/*Displays information on the current wait states for all active database sessions*/
SET LINESIZE 250
SET PAGESIZE 1000
COLUMN username FORMAT A15
COLUMN osuser FORMAT A15
COLUMN sid FORMAT 99999
COLUMN serial# FORMAT 9999999
COLUMN wait_class FORMAT A15
COLUMN state FORMAT A19
COLUMN logon_time FORMAT A20
SELECT a.username,a.osuser,a.sid,a.serial#, d.spid AS process_id, a.wait_class,a.seconds_in_wait,       a.state,a.blocking_session,a.blocking_session_status,a.module,TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time FROM   v$session a,v$process d WHERE  a.paddr  = d.addr AND    a.status = 'ACTIVE' ORDER BY 1,2;

/*Displays the recovery status of each datafile  */ 
SET LINESIZE 500
SET PAGESIZE 500
SET FEEDBACK OFF
col Datafile for a44
SELECT Substr(a.name,1,60) "Datafile", b.status "Status" FROM   v$datafile a,v$backup b WHERE  a.file# = b.file#;
SET PAGESIZE 14
SET FEEDBACK ON

/*Displays datafiles information   */
SET LINESIZE 200
COL FILE_NAME FOR a48
SELECT file_id, file_name,ROUND(bytes/1024/1024/1024) AS size_gb,ROUND(maxbytes/1024/1024/1024) AS max_size_gb,autoextensible,increment_by,status FROM   dba_data_files ORDER BY file_name;

/*Displays general information about the database*/
SET PAGESIZE 1000
SET LINESIZE 100
SET FEEDBACK OFF
SELECT * FROM   v$database;
SELECT * FROM   v$instance;
SELECT * FROM   v$version;
SELECT a.name,a.value FROM   v$sga a;
SELECT Substr(c.name,1,60) "Controlfile",NVL(c.status,'UNKNOWN') "Status" FROM   v$controlfile c ORDER BY 1;
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;
SELECT l.group# "Group",Substr(l.member,1,60) "Logfile",NVL(l.status,'UNKNOWN') "Status" FROM   v$logfile l ORDER BY 1,2;
PROMPT
SET PAGESIZE 14
SET FEEDBACK ON

/*Database Object Counts*/
prompt
col owner for a18
select DECODE(GROUPING(a.owner), 1, 'All Owners',
a.owner) AS "Owner",
count(case when a.object_type = 'TABLE' then 1 else null end) "Tables",
count(case when a.object_type = 'INDEX' then 1 else null end) "Indexes",
count(case when a.object_type = 'PACKAGE' then 1 else null end) "Packages",
count(case when a.object_type = 'SEQUENCE' then 1 else null end) "Sequences",
count(case when a.object_type = 'TRIGGER' then 1 else null end) "Triggers",
count(case when a.object_type not in ('PACKAGE','TABLE','INDEX','SEQUENCE','TRIGGER') then 1 else null end) "Other",count(case when 1 = 1 then 1 else null end) "Total" from dba_objects a group by rollup(a.owner) ;

/*Database size*/
prompt
with dbsize as
(select ' '||tablespace_name tablespace_name,sum(bytes)/(1024*1024) size_mb from dba_data_files group by tablespace_name
union all
select ' '||tablespace_name,sum(bytes)/(1024*1024) size_mb from dba_temp_files group by tablespace_name
union all
select 'LOGFILES',sum(bytes)/(1024*1024) size_mb from v$log
)
select * from dbsize
union all
select 'Total',sum(size_mb) from dbsize order by 1;

/*check ITL waits*/
Set pages 1000
col owner format a15 trunc
col object_name format a30 word_wrap
col value format 999,999,999 heading "NBR. ITL WAITS"
select owner,object_name||' '||subobject_name object_name, value from v$segment_statistics where statistic_name = 'ITL waits' and value > 0 order by 3,1,2;

/* check log sizes*/
SELECT A.*,Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb FROM
( SELECT To_Char(First_Time,'YYYY-MM-DD') DAY,  Count(1)
Count#,  Min(RECID) Min#, Max(RECID) Max# FROM v$log_history GROUP BY  To_Char(First_Time,'YYYY-MM-DD') ORDER BY 1 DESC) A,(SELECT Avg(BYTES) AVG#,Count(1) Count#, Max(BYTES) Max_Bytes,Min(BYTES) Min_Bytes FROM v$log ) B ; 

/*Provides information about memory resize operations*/
SET LINESIZE 200
COLUMN parameter FORMAT A25
SELECT start_time,end_time,component,oper_type,oper_mode,parameter,ROUND(initial_size/1024/1204) AS initial_size_mb,
ROUND(target_size/1024/1204) AS target_size_mb,ROUND(final_size/1024/1204) AS final_size_mb,status
FROM   v$memory_resize_ops ORDER BY start_time;

/*memory allocation to all db sessions*/
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A20
COLUMN module FORMAT A50
COLUMN program FORMAT A50
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 = 'session pga memory' AND    a.program IS NOT NULL ORDER BY b.value DESC;