DATABASE MONITORING

CHECK  BUFFER CACHE HIT RATIO

SELECT ROUND((1-(phy.VALUE / (cur.VALUE + con.VALUE)))*100,2) "Cache Hit 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 DATAFILE SIZE

SELECT dp.tablespace_name

, df.file_name

, dp.block_size

, round(sum(df.maxbytes) /(1024*1024*1024),0) "Max_Size_GB"

, round(sum(df.bytes) /(1024*1024*1024),0) "Used_Size_GB"

FROM dba_tablespaces dp, dba_data_files df

WHERE dp.tablespace_name=df.tablespace_name

GROUP BY dp.tablespace_name, dp.block_size , df.file_name,df.maxbytes

ORDER BY dp.tablespace_name, df.file_name;

######################################################################

CHECK DATABASE SIZE 

SQL> select sum(bytes)/(1024*1024*1024) DB_SIZE_IN_GB from dba_data_files;

DB_SIZE_IN_GB

-------------

   5.93847656

////////////////////////////////////////

col "Database Size" format a20

col "Free space" format a20

col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"

, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -

round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"

, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"

from (select bytes

from v$datafile

union all

select bytes

from v$tempfile

union all

select bytes

from v$log) used

, (select sum(bytes) as p

from dba_free_space) free

group by free.p;

###################################################################

TOP  QUERIES IN LAST 5 MIN

SELECT * FROM

(select parsing_schema_name as users, sql_id, sql_text

,executions, disk_reads,buffer_gets

,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) "CPU Time"

,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) "Elapsed Time"

FROM v$sqlarea

WHERE LAST_ACTIVE_TIME BETWEEN (sysdate - 5/1440) AND sysdate

AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')

ORDER BY "CPU Time" DESC) x

WHERE rownum <=5; 

###############################################################3

TOP WAITING SESSION

SELECT sess.sid, sess.serial#, sess.username, sess.machine, SUM(ash.wait_time + ash.time_waited) wait_time

FROM v$active_session_history ash, v$session sess

WHERE ash.session_id = sess.sid

AND ash.sample_time BETWEEN (sysdate - 15/1440) AND sysdate

GROUP BY sess.sid, sess.serial#, sess.username, sess.machine

ORDER BY 4 DESC;

#####################################################################

KILL SESSION 

alter system kill session 'sid,serial#' ;

USING V$SESSION TO GET SID,SERIAL#

############################################################

TO CHECK HOW MANY TABLE IN USER 'HR'

select count(*) from dba_objects do where do.owner = 'HR'

and lower(object_type)=lower('Table');

####################################################################3