monitoring scripts / Audit Scripts

column date_column new_value today_var
column database_column new_value sid_var
column file_type_column new_value type_var

select to_char(sysdate,'yyyy-mm-dd.HH24-MI-SS') date_column
from dual
/

select name||'_' database_column
from v$database
/

select '.txt' file_type_column
from dual
/

spool Health_Check_&sid_var&today_var&type_var

prompt
prompt Startup Time
prompt ------------

select to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup time"
from v$instance
/

prompt
prompt Total database size (including redo logs)
prompt -----------------------------------------

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
/

prompt
prompt Object distribution, or what's taking up all the space?
prompt ----------------------------------------------------------

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner  = seg.owner(+)
order by 3 desc ,2 desc, 1
/

prompt
prompt The ten biggest things...
prompt -------------------------

col owner format a15
col segment_name format a30
col segment_type format a15
col mb format 999,999,999
select  owner
, segment_name
, segment_type
, mb
from (
select owner
, segment_name
, segment_type
, bytes / 1024 / 1024 "MB"
from dba_segments
order by bytes desc
)
where rownum < 11
/

prompt
prompt Show all National Language Set parameters
prompt -----------------------------------------

select * from nls_database_parameters
/

prompt
prompt Currently used features
prompt -----------------------

select name
, detected_usages
from dba_feature_usage_statistics
where detected_usages > 0
/

prompt
prompt Show all connected users
prompt ------------------------

set lines 100 pages 999
col ID format a15
select username
,      sid || ',' || serial# "ID"
,      status
,      last_call_et "Last Activity"
from   v$session
where  username is not null
order by status desc
,        last_call_et desc
/
 
prompt
prompt Time since last user activity
prompt -----------------------------

set lines 100 pages 999
select username
,      floor(last_call_et / 60) "Minutes"
,      status
from   v$session
where  username is not null
order by last_call_et
/

prompt
prompt Sessions sorted by logon time
prompt -----------------------------

set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time
/

prompt
prompt Show user info including os pid
prompt -------------------------------

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid)
/

prompt
prompt All active sql
prompt --------------

set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
  for x in
   (select username||'('||sid||','||serial#||') ospid = '|| process ||
    ' program = ' || program username,
    to_char(LOGON_TIME,' Day HH24:MI') logon_time,
    to_char(sysdate,' Day HH24:MI') current_time,
    sql_address,
    sql_hash_value
   from v$session
   where status = 'ACTIVE'
   and rawtohex(sql_address) <> '00'
   and username is not null ) loop
   for y in (select sql_text
   from v$sqlarea
   where address = x.sql_address ) loop
   if ( y.sql_text not like '%listener.get_cmd%' and
    y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
    dbms_output.put_line( '--------------------' );
    dbms_output.put_line( x.username );
    dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
    dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
   end if;
  end loop;
 end loop;
end;
/
 
prompt
prompt Display any long operations
prompt ---------------------------

set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc
/

prompt
prompt List open cursors per user
prompt --------------------------

set pages 999
select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value
/


prompt
prompt Or alternatively...
prompt -------------------

set lines 100 pages 999
select count(hash_value) cursors
,      sid
,      user_name
from   v$open_cursor
group by
       sid
,      user_name
order by
       cursors
/

prompt
prompt Show non-default parameters
prompt ---------------------------

set pages 999 lines 100
col name format a30
col value format a50
select name
, value
from v$parameter
where isdefault = 'FALSE'
and value is not null
order by name
/

prompt
prompt Show DBA_JOBS
prompt -------------

set lines 100 pages 999
col schema_user format a15
col fails format 999
select job
, schema_user
, to_char(last_date, 'hh24:mi dd/mm/yy') last_run
, to_char(next_date, 'hh24:mi dd/mm/yy') next_run
, failures fails
, broken
, substr(what, 1, 15) what
from dba_jobs
order by 4
/

prompt
prompt Show DBMS_SCHEDULER_JOBS
prompt ------------------------

set lines 150
col owner format a12
col job_name format a30
col job_action format a70
select owner
, job_name
, job_action
from dba_scheduler_jobs
/

prompt
prompt Display all queues and queue tables
prompt -----------------------------------

set lines 100 pages 999
col owner format a15
select owner
, name
, queue_table
from dba_queues
/

prompt
prompt Check various stats for all queues
prompt ----------------------------------

set lines 100 pages 999
col queue format a50
select owner || '.' || name queue
, waiting
, ready
, expired
from dba_queues dq
, v$aq aq
where dq.qid = aq.qid
order by 2, 3, 4, 1
/

prompt
prompt Check DBA recycle bin
prompt ---------------------

select owner
, object_name
, original_name
from dba_recyclebin
/

prompt
prompt How full are the tablespaces?
prompt ----------------------------

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name union all
select tablespace_name || '  **TEMP**'
, sum(bytes)/1024/1024 used_mb
from dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/
 
prompt
prompt Tablespaces that are >=80% full, and how much to add to make them 80% again
prompt ---------------------------------------------------------------------------

set pages 999 lines 100
col "Tablespace" for a50
col "Size MB" for 999999999
col "%Used" for 999
col "Add (80%)" for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .8) - tsu.used_mb "Add (80%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from    dba_data_files group by tablespace_name) tsu
, (select ts.tablespace_name
,       nvl(sum(bytes)/1024/1024, 0) free_mb
from    dba_tablespaces ts, dba_free_space fs
where   ts.tablespace_name = fs.tablespace_name (+)
group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 80
order by 3,4
/

prompt
prompt User quotas on all tablespaces
prompt ------------------------------

col quota format a10
select username
,      tablespace_name
,      decode(max_bytes, -1, 'unlimited'
       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
from   dba_ts_quotas
where  tablespace_name not in ('TEMP')
/
 
prompt
prompt Show segments that are approaching max_extents
prompt ----------------------------------------------

col segment_name format a40
select owner
, segment_type
, segment_name
, max_extents - extents as "spare"
, max_extents
from dba_segments
where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/

prompt
prompt List the contents of the temporary tablespace(s)
prompt ------------------------------------------------

set pages 999 lines 100
col username format a15
col mb format 999,999
select  su.username
,       ses.sid
,       ses.serial#
,       su.tablespace
,       ceil((su.blocks * dt.block_size) / 1048576) MB
from    v$sort_usage    su
,       dba_tablespaces dt
,       v$session ses
where   su.tablespace = dt.tablespace_name
and     su.session_addr = ses.saddr
/

prompt
prompt List invalid objects
prompt --------------------

set lines 200 pages 999
col "obj" format a40
select owner || '.' || object_name "obj",
object_type
from dba_objects
where status = 'INVALID'
/

prompt
prompt Display all datafiles, tempfiles and logfiles (and their sizes)
prompt ---------------------------------------------------------------

set lines 100 pages 999
col name format a50
select name, bytes
from    (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#
union all
select name, 0
from v$controlfile) used
, (select sum(bytes) as p
from dba_free_space) free
/

prompt
prompt Anything left in backup mode?
prompt -----------------------------

set lines 100 pages 999
col name format a60
select df.name
, b.status
, to_char(time, 'hh24:mi:ss dd/mm/yyyy') time
from v$datafile df
, v$backup b
where df.file# = b.file#
and b.status = 'ACTIVE'
order by b.file#
/

prompt
prompt What's in undo?
prompt ---------------

select tablespace_name
, status
, count(*) as HOW_MANY
from dba_undo_extents
group by tablespace_name
, status
/

prompt
prompt Is anything rolling back at the moment? Look for the used_ublk value decreasing...
prompt ----------------------------------------------------------------------------------

set lines 100 pages 999
col username format a15
col command format a20
select ses.username
, substr(ses.program, 1, 19) command
, tra.used_ublk
from v$session ses
, v$transaction tra
where ses.saddr = tra.ses_addr
/

prompt
prompt Resource intensive sql - change 8192 to match block size
prompt --------------------------------------------------------

select sql_text
,      executions
,      to_char((((disk_reads+buffer_gets)/executions) * 8192)/1048576, '9,999,999,990.00')
 as total_gets_per_exec_mb
,      to_char((( disk_reads             /executions) * 8192)/1048576, '9,999,999,990.00')
 as disk_reads_per_exec_mb
,      to_char((( buffer_gets            /executions) * 8192)/1048576, '9,999,999,990.00')
 as buffer_gets_per_exec_mb
,      parsing_user_id
from   v$sqlarea
where  executions > 10
order by 6 desc
/

prompt
prompt File I/O stats
prompt -------------

set lines 80 pages 999
col fname heading "File Name" format a60
col sizemb heading "Size(Mb)" format 99,999
col phyrds heading "Reads" format 999,999,999
col readtim heading "Time" format 99.999
col phywrts heading "Writes" format 9,999,999
col writetim heading "Time" format 99.999
select lower(name) fname
,       (bytes / 1048576) sizemb
,       phyrds
, readtim
,       phywrts
, writetim
from   v$datafile df
,       v$filestat fs
where   df.file# = fs.file#
order   by 1
/

prompt
prompt Show the buffer cache advisory
prompt ------------------------------

set lines 100 pages 999
col est_mb format 99,999
col estd_physical_reads format 999,999,999,999,999
select size_for_estimate est_mb
, estd_physical_read_factor
, estd_physical_reads
from v$db_cache_advice
where name = 'DEFAULT'
order by size_for_estimate
/

prompt
prompt Top ten hottest objects by access
prompt ---------------------------------

col owner format a20 trunc
col object_name format a30
col touches format 9,999,999
select *
from (
select count(*)
, sum(tch) TOUCHES
, u.name OWNER
, o.name OBJECT_NAME
from x$bh x
, obj$ o
, user$ u
where x.obj = o.obj#
and o.owner# = u.user#
group by u.name, o.name
  order by 2 desc
)
where rownum < 11
/

prompt
prompt Waits by file
prompt -------------

col name format a60
select name
, count
from x$kcbfwait
, v$datafile
where indx + 1 = file#
order by 2
/
 
prompt
prompt Waits by segment
prompt ----------------

set lines 150
col value format 99999999
col statistic_name format a50
select object_name
, obj#
, statistic_name
, value
from v$segment_statistics
where statistic_name like '%waits%'
and value > 0
order by statistic_name
, value desc
/

prompt
prompt Count of analyzed tables broken down by schema
prompt ----------------------------------------------

set pages 999 lines 100
select a.owner
, a.total_tables tables
, nvl(b.analyzed_tables,0) analyzed
from (select owner
, count(*) total_tables
from dba_tables
group by owner) a
, (select owner
, count(last_analyzed) analyzed_tables
from dba_tables
where last_analyzed is not null
group by owner) b
where a.owner = b.owner (+)
and a.owner not in ('SYS', 'SYSTEM')
order by a.total_tables - nvl(b.analyzed_tables,0) desc
/


prompt
prompt Whats not been analyzed (at least a day ago...)
prompt ----------------------------------------------

select owner
, table_name
, last_analyzed
from dba_tables
where trunc(last_analyzed) < trunc(sysdate)
and owner not in ('SYS','SYSTEM','WMSYS','EXFSYS','CTXSYS','SYSMAN','OLAPSYS','MDSYS','ORDSYS','OUTLN','DMSYS','XDB','DBSNMP')
/

prompt
prompt Quick look at SGA
prompt -----------------

set lines 100 pages 999
col bytes format 999,999,999
compute sum of bytes on pool
break on pool skip 1
select pool
, name
, bytes
from v$sgastat
order by pool
, name
/
 
prompt
prompt PGA usage by username
prompt ---------------------

select st.sid "SID",
sn.name "TYPE",
ceil(st.value / 1024 / 1024) "MB"
from v$sesstat st,
v$statname sn
where st.statistic# = sn.statistic#
and sid in (select sid
from v$session)
and upper(sn.name) like '%PGA%'
order by st.sid,
st.value desc
/

prompt
prompt Display pool usage
prompt ------------------

select name
, sum(bytes)
from v$sgastat
where pool like 'shared pool'
group by name
order by sum(bytes)
/

spool off;
exit