[orapsms@m11smsd1:/smspora/script/util] cat healthcheck.sql
set time on
set echo on
set pagesize 100
set linesize 220
set trimspool on
column tablespacename format a20

BREAK ON REPORT
COMPUTE SUM LABEL TOTAL OF Total_Size ON REPORT
COMPUTE SUM  OF Real_Size ON REPORT
COMPUTE SUM  OF Used_Size ON REPORT
COMPUTE SUM  OF Free_Size ON REPORT

--1.Current Session Count
select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') "Time",
count(*) "Total Sessions",
count(decode(status, 'ACTIVE',1) ) "Active Sessions"
from v$session
/

--2.Invalid Object Count
select /*+ ORDERED_PREDICATES */
object_type "Object Type",
count(*) "Invalid Count"
from dba_objects
where status = 'INVALID'
group by object_type
/

--3.Resource Limit
select RESOURCE_NAME "Resource Name",
CURRENT_UTILIZATION "Current Util",
MAX_UTILIZATION "Max Util",
INITIAL_ALLOCATION "Initial Alloc",
LIMIT_VALUE "Limit"
from v$resource_limit
where LIMIT_VALUE not in (' UNLIMITED')
/

--4. Redo Log Switch Count(Daily)
select thread#, to_char(first_time, 'yyyy/mm/dd') "Time", count(*) "Count"
from v$loghist
where first_time > sysdate -30
group by thread#, to_char(first_time, 'yyyy/mm/dd')
order by 2 desc
/

--5.Literal SQL
col "SQL" format a40
select substr(sql_text, 1, 40) "SQL",
count(*) "Count",
sum(executions) "TotExecs",
sum(sharable_mem) "Mem",
to_char(to_date(min(first_load_time), 'yyyy-mm-dd/hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss') "Start Time",
to_char(to_date(max(first_load_time), 'yyyy-mm-dd/hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss') "End TIme",
max(hash_value) "Hashval"
from v$sql
where executions < 5
group by substr(sql_text, 1, 40)
having count(*) > 30
order by 2 desc
/

--6.JOB status
select count(case when BROKEN='Y' then 1 end) brokenJob,
       count(case when this_date is not null then 1 end) runningJob
  from dba_jobs
 group by log_user
/

--7. Tablespace check
SELECT to_char(sysdate,'YYYY/MM/DD') "sysdate",
       t.tn TableSpaceName,
       round(t.sizes / 1024,3) Total_Size,
       round(u.sizes / 1024,3) Used_Size,
       round((t.sizes - u.sizes)/1024, 3) Free_Size,
       round(u.sizes / t.sizes * 100,0) Used_Pct,
       round((t.sizes - u.sizes)/t.sizes*100,0) FreeRatio
  FROM ( SELECT tablespace_name tn,
                sum( decode(autoextensible,'NO',bytes, 'YES', decode( sign(maxbytes-bytes), 1, maxbytes, bytes) ) ) /1024/1024 Sizes
           FROM dba_data_files
          GROUP BY tablespace_name) t,
       ( SELECT tablespace_name tn,
                sum(bytes)/1024/1024 sizes
           FROM dba_segments
          GROUP BY tablespace_name) u
 WHERE t.tn = u.tn
 ORDER BY FreeRatio
/

+ Recent posts