sqlplus -S /nolog <<EOF
connect / as sysdba
set linesize 130
set pagesize 999
col BEGIN_TIME for a25
col END_TIME for a25
col HOUR for a5
col DB_LOAD for a10
SELECT 
INSTANCE_NUMBER INSTANCE,
to_char(BEGIN_TIME,'hh24') HOUR,
to_char(BEGIN_TIME,'YYYY/MM/DD HH24:MI:SS') BEGIN_TIME,
to_char(END_TIME,'YYYY/MM/DD HH24:MI:SS') END_TIME,
DB_TIME,
to_char(ROUND(DB_TIME/ROUND(TO_NUMBER(END_TIME - BEGIN_TIME) * 24 * 60),2),'fm90.99') AAS,
ROUND((DB_TIME/ROUND(TO_NUMBER(END_TIME - BEGIN_TIME) * 24 * 60))*100/22,2)||'%' DB_LOAD
  FROM ( SELECT A.INSTANCE_NUMBER,
               A.SNAP_ID,
               B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
               B.END_INTERVAL_TIME + 0 END_TIME,
               ROUND(VALUE - LAG( VALUE, 1 , '0')
                     OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB_TIME"
          FROM (SELECT B.SNAP_ID,
                       INSTANCE_NUMBER,
                       SUM(VALUE ) / 1000000 / 60 VALUE
                  FROM DBA_HIST_SYS_TIME_MODEL B
                 WHERE B.DBID = (SELECT DBID FROM V\$DATABASE)
                   AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
                 GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
               DBA_HIST_SNAPSHOT B
         WHERE A.SNAP_ID = B.SNAP_ID
           AND B.DBID = (SELECT DBID FROM V\$DATABASE)
           AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER
           AND A.INSTANCE_NUMBER = 1)
 WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') >= TO_CHAR(sysdate-1, 'YYYY-MM-DD')
 ORDER BY AAS desc;
exit
EOF

sqlplus -S /nolog <<EOF
connect / as sysdba
 select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
 "Buffer Cache Hit Ratio" 
 from v\$sysstat physical,v\$sysstat direct,v\$sysstat lobs,v\$sysstat logical
 where physical.name = 'physical reads'
 and direct.name='physical reads direct'
 and lobs.name='physical reads direct (lob)'
 and logical.name='session logical reads';
select   (1-(sum(getmisses)/sum(gets)))  "Dictionary Hit Ratio"
from  v\$rowcache;
exit
EOF

sqlplus -S /nolog  <<EOF
set lines 200
col tablespace_name for a40
set pagesize 9999
connect / as sysdba  
select tablespace_name ,pct_used "used(%)" from (
SELECT   df.tablespace_name, COUNT (*) datafile_count,
ROUND (SUM (df.BYTES) / 1048576) size_mb,
ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,
ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb,
ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree,
100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,
ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free
FROM dba_data_files df,
 (SELECT   tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_nam
e, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name ORDER BY 8)
where pct_used >= 95;
exit
EOF

sqlplus -S /nolog  <<EOF
connect / as sysdba
set pagesize  9999
col event for a30
col username for a10
col program for a20
col machine for a30
set lines 300
select 
username,
sid,
event,
sql_id,
last_call_et,
machine,program  
from v\$session 
where username is not null and username not in ('SYS','SYSTEM') 
and status='ACTIVE'  
and event not like 'SQL*Net%' and event not like '%rdbms%';
exit
EOF

sqlplus -S / as sysdba <<EOF
select count(0) "TOTAL ACTIVE SESSIONS" 
from v\$session where username is not null 
and status='ACTIVE';
exit 
EOF

sqlplus -S / as sysdba << EOF
set line 100
select trunc(FIRST_TIME) datum,
       count(*) total,
       round(10 * sum(blocks * block_size) / 1024 / 1024 ) / 10 MB
  from v\$archived_log
 group by trunc(FIRST_TIME)
 order by 1;
exit
EOF

sqlplus -S / as sysdba << EOF
select index_name from dba_ind_partitions where status!='USABLE'
union
select index_name from dba_indexes where status not in ('N/A','VALID');
exit
EOF

sqlplus -S / as sysdba <<EOF
set pagesize 9999
col owner for a10
col object_name for a30
set lines 200
select owner,object_name,object_type,status 
from dba_objects where status !='VALID';
exit
EOF

sqlplus -S / as sysdba << EOF
col owner for a20
col object_name for a30
set lines 200
select owner,object_name,status,locked_mode,session_id
from dba_objects obj,v\$locked_object l
where obj.object_id=l.object_id;
exit
EOF