Phase 1 Activity

set pages 50
set lines 1000
set pages 70
set heading on
set trims on

prompt**=====================================================================================================**
prompt**    **Database Current Status**
prompt**=====================================================================================================**
set lines 300 pages 3000
select name,open_mode,database_role from v$database;

spool DB_link_details.log
prompt**=====================================================================================================**
prompt**    **DB Link Details**
prompt**=====================================================================================================**
set lines 300 pages 3000
COL OWNER FORMAT a10
COL USERNAME FORMAT A20
COL DB_LINK FORMAT A30
COL HOST FORMAT A30
SELECT * FROM DBA_DB_LINKS;
spool off;

spool FAILED_Jobs_details.log
prompt**=====================================================================================================**
prompt**    **Failed jobs details**
prompt**=====================================================================================================**
set lines 300
col job_name for a33
col owner for a13
col status for a13
col ACTUAL_START_DATE for a23
col additional_info for a60
select JOB_NAME,OWNER,STATUS,additional_info,to_char(ACTUAL_START_DATE,’dd-mm-yyyy hh24:mi:ss’) ACTUAL_START_DATE from dba_scheduler_job_run_details where STATUS=’FAILED’;
spool off;

spool multiplexing_control_files.log
prompt**=====================================================================================================**
prompt**    **Check Multiplexing control files on different mount points/File systems.**
prompt**=====================================================================================================**
set lines 300 pages 3000
col NAME for a50
select name from v$controlfile;
spool off;

spool system_default_tablespace.log
prompt**=====================================================================================================**
prompt**    **Identify users having SYSTEM as default tablespace or temporary tablespace.**
prompt**=====================================================================================================**
col profile for a15
col username for a15
col ACCOUNT_STATUS for a20
col  DEFAULT_TABLESPACE for a20
select USERNAME,to_char(CREATED,’dd-mm-yyyy hh24:mi:ss’) CREATED,PROFILE,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where username not in(
‘ANONYMOUS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘AWR_STAGE’,
‘CSMIG’,
‘CTXSYS’,
‘DBSNMP’,
‘DEMO’,
‘DIP’,
‘DMSYS’,
‘DSSYS’,
‘EXFSYS’,
‘HR’,
‘OE’,
‘SH’,
‘LBACSYS’,
‘MDSYS’,
‘ORACLE_OCM’,
‘ORDPLUGINS’,
‘ORDSYS’,
‘OUTLN’,
‘PERFSTAT’,
‘SCOTT’,
‘ADAMS’,
‘JONES’,
‘CLARK’,
‘BLAKE’,
‘SYS’,
‘SYSTEM’,
‘TRACESVR’,
‘TSMSYS’,
‘XDB’) and (temporary_tablespace=’SYSTEM’ or DEFAULT_TABLESPACE=’SYSTEM’);

select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where DEFAULT_TABLESPACE=’SYSTEM’;
select USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users where TEMPORARY_TABLESPACE=’SYSTEM’;
spool off;

spool Top_utilized_tablespace.log
prompt**=====================================================================================================**
prompt**    **Identify top utilizaed tablespace s**
prompt**=====================================================================================================**
select * From DBA_TABLESPACE_USAGE_METRICS;
spool off;

spool multiplexing_redo_log.log
prompt**=====================================================================================================**
prompt**    **Check multiplexing of Redo log at different location **
prompt**=====================================================================================================**
col member for a45
select * from v$logfile;
spool off;

spool snapshot_too_old.log
prompt**=====================================================================================================**
prompt**    **Check for snapshot too old error.**
prompt**=====================================================================================================**
set lines 300 pages 3000
col USER_ID for a18
col CLIENT_ID for a23
col MODULE_ID for a23
col PROCESS_ID for a20
col HOST_ID for a20
col HOST_ADDRESS for a23
col MESSAGE_TEXT for a80
select USER_ID,CLIENT_ID,MODULE_ID,PROCESS_ID,HOST_ID,HOST_ADDRESS,to_char(ORIGINATING_TIMESTAMP,’dd-mm-yyyy hh24:mi:ss’) ORIGINATING_TIMESTAMP,MESSAGE_TEXT from X$DBGALERTEXT where MESSAGE_TEXT like ‘%ORA-01555%’;

show parameter undo;
spool off;

spool tablespace_locally_managed.log
prompt**=====================================================================================================**
prompt**    **Check that all tablespaces are locally managed**
prompt**=====================================================================================================**
select TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT,EXTENT_MANAGEMENT from dba_tablespaces;
spool off;

spool temp_tablespace.log
prompt**=====================================================================================================**
prompt**    **Check for Temp tablespace extent size.**
prompt**=====================================================================================================**
SELECT inst_id,tablespace_name ,sum(BYTES_CACHED)/1024/1024 “ALLOCATED(MB)”,sum(BYTES_USED)/1024/1024 “USED(MB)”,
sum(BYTES_CACHED-BYTES_USED)/1024/1024 “FREE(MB)”,100*sum(BYTES_USED)/sum(BYTES_CACHED) “%USED(Extent Pool)”
FROM  gv$TEMP_EXTENT_POOL group by inst_id,tablespace_name
/

col file_name for a45
col tablespace_name for a15
select file_id,file_name,tablespace_name,status,bytes/1024/1024/1024 GB,autoextensible from dba_temp_files;
spool off;

spool archive_mode.log
prompt**=====================================================================================================**
prompt**    **Check archive log mode for production databases.**
prompt**=====================================================================================================**
archive log list

spool off;

spool archive_generation_for_month.log
prompt**=====================================================================================================**
prompt**    **archive_generation_for_month.**
prompt**=====================================================================================================**
SELECT SUM_ARCH.DAY,
SUM_ARCH.GENERATED_MB,
SUM_ARCH_DEL.DELETED_MB,
SUM_ARCH.GENERATED_MB – SUM_ARCH_DEL.DELETED_MB “REMAINING_MB”
FROM (  SELECT TO_CHAR (COMPLETION_TIME, ‘DD/MM/YYYY’) DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
GENERATED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = ‘YES’
GROUP BY TO_CHAR (COMPLETION_TIME, ‘DD/MM/YYYY’)) SUM_ARCH,
(  SELECT TO_CHAR (COMPLETION_TIME, ‘DD/MM/YYYY’) DAY,
SUM (ROUND ( (blocks * block_size) / (1024 * 1024), 2))
DELETED_MB
FROM V$ARCHIVED_LOG
WHERE ARCHIVED = ‘YES’ AND DELETED = ‘YES’
GROUP BY TO_CHAR (COMPLETION_TIME, ‘DD/MM/YYYY’)) SUM_ARCH_DEL
WHERE SUM_ARCH.DAY = SUM_ARCH_DEL.DAY(+)
ORDER BY TO_DATE (DAY, ‘DD/MM/YYYY’);

spool off;

spool datafile_status.log
prompt**=====================================================================================================**
PROMPT**    **Check all Datafile status**
prompt**=====================================================================================================**
col NAME for a60
SELECT name,
FILE#,
STATUS,
CHECKPOINT_CHANGE# “CHECKPOINT”
FROM   V$DATAFILE;
spool off;

spool database_component.log
prompt**=====================================================================================================**
prompt**    **Check Database component status**
prompt**=====================================================================================================**
set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,STATUS from dba_registry;
spool off;

spool dba_directories.log
prompt**=====================================================================================================**
prompt**    **Check dba directories**
prompt**=====================================================================================================**
col owner for a10
col directory_name for a40
col directory_path for a60
select owner,directory_name,directory_path from dba_directories;
spool off;

spool recycle_bin.log
prompt**=====================================================================================================**
prompt**    **Check Database recyclebin status**
prompt**=====================================================================================================**
SELECT Value FROM V$parameter WHERE Name = ‘recyclebin’;
spool off;

spool RMAN_backup_config.log
prompt**=====================================================================================================**
prompt**    **Identify DBs for which RMAN backup is not configured**
prompt**=====================================================================================================**
col GB for9,999
col START_TIME for a20
col end_TIME for a20
col LEVEL for 99
col operation for a10
col status for a10
select stamp,ROW_LEVEL “LEVEL”,OPERATION,status,(MBYTES_PROCESSED/1024) “GB”,
to_char(START_TIME,’DD-MON-YYYY HH24:MI:SS’) “START_TIME”, to_char(end_TIME,’DD-MON-YYYY HH24:MI:SS’) “END_TIME”, object_type from v$RMAN_STATUS where OPERATION=’BACKUP’  and start_time  > (sysdate – 10)
order by stamp  ;
spool off;

spool archive_log_generation.log
prompt**=====================================================================================================**
prompt**    **Identify the DBs with high archive log generation to tune archive log backup frequency **
prompt**=====================================================================================================**
SET PAGESIZE 6000
SET LINESIZE 300
SET VERIFY OFF
break on report
compute sum of TOTAL_ARCHIVES on report
compute sum of TotalArchive_sIZE_in_MB on report
col arch_date for a15
SELECT thread#,TO_CHAR(completion_time,’DD-MM-YYYY’) ARCH_DATE,
count(sequence#) TOTAL_ARCHIVES,
round(sum(blocks*block_size)/1024/1024) “TotalArchive_sIZE_in_MB”
from gv$archived_log where dest_id=1 and trunc(completion_time)>=trunc(sysdate-15)
group by thread#,TO_CHAR(completion_TIME,’DD-MM-YYYY’) order by 1;
spool off;

spool dba_role.log
prompt**=====================================================================================================**
prompt**    **Identify non DBA users with DBA role**
prompt**=====================================================================================================**
select GRANTEE,GRANTED_ROLE,ADMIN_OPTION,DEFAULT_ROLE from dba_role_privs where GRANTED_ROLE in(‘DBA’) and ADMIN_OPTION=’YES’ order by GRANTEE;
spool off;

spool elevated_pvilages.log
prompt**=====================================================================================================**
prompt**    **Identify  non DBA/System users having  elevated privilages, e..g user having access to V$ views, or any privileges granted to it, or privilege with admin option**
prompt**=====================================================================================================**
select * from dba_sys_privs where PRIVILEGE like ‘%ANY%’ and GRANTEE not in(
‘ANONYMOUS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘AWR_STAGE’,
‘CSMIG’,
‘CTXSYS’,
‘DBSNMP’,
‘DEMO’,
‘DIP’,
‘DMSYS’,
‘DSSYS’,
‘EXFSYS’,
‘HR’,
‘OE’,
‘SH’,
‘LBACSYS’,
‘MDSYS’,
‘ORACLE_OCM’,
‘ORDPLUGINS’,
‘ORDSYS’,
‘OUTLN’,
‘PERFSTAT’,
‘SCOTT’,
‘ADAMS’,
‘JONES’,
‘CLARK’,
‘BLAKE’,
‘SYS’,
‘SYSTEM’,
‘TRACESVR’,
‘TSMSYS’,
‘IMP_FULL_DATABASE’,
‘EXP_FULL_DATABASE’,
‘DBA’,
‘DATAPUMP_IMP_FULL_DATABASE’,
‘AQ_ADMINISTRATOR_ROLE’,
‘JAVADEBUGPRIV’,
‘SCHEDULER_ADMIN’,
‘SYSMAN’,
‘XDB’) order by 1,2;
spool off;

spool default_user_account_status.log
PROMPT**=====================================================================================================**
prompt**    **Identify Oracle default users with account status as .OPEN.**
prompt**=====================================================================================================**

select username,account_status from dba_users where username in (
‘ANONYMOUS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘AWR_STAGE’,
‘CSMIG’,
‘CTXSYS’,
‘DBSNMP’,
‘DEMO’,
‘DIP’,
‘DMSYS’,
‘DSSYS’,
‘EXFSYS’,
‘HR’,
‘OE’,
‘SH’,
‘LBACSYS’,
‘MDSYS’,
‘ORACLE_OCM’,
‘ORDPLUGINS’,
‘ORDSYS’,
‘OUTLN’,
‘PERFSTAT’,
‘SCOTT’,
‘ADAMS’,
‘JONES’,
‘CLARK’,
‘BLAKE’,
‘SYS’,
‘SYSTEM’,
‘TRACESVR’,
‘TSMSYS’,
‘XDB’) order by 2,1;

spool off;

spool users_privilages_access_metadata.log
prompt**=====================================================================================================**
prompt**    **Identify Users with privilege to access Metadata**
prompt**=====================================================================================================**
select * from dba_role_privs where GRANTED_ROLE=’SELECT_CATALOG_ROLE’ and GRANTEE not in(
‘ANONYMOUS’,
‘AURORA$ORB$UNAUTHENTICATED’,
‘AWR_STAGE’,
‘CSMIG’,
‘CTXSYS’,
‘DBSNMP’,
‘DEMO’,
‘DIP’,
‘DMSYS’,
‘DSSYS’,
‘EXFSYS’,
‘HR’,
‘OE’,
‘SH’,
‘LBACSYS’,
‘MDSYS’,
‘ORACLE_OCM’,
‘ORDPLUGINS’,
‘ORDSYS’,
‘OUTLN’,
‘PERFSTAT’,
‘SCOTT’,
‘ADAMS’,
‘JONES’,
‘CLARK’,
‘BLAKE’,
‘SYS’,
‘SYSTEM’,
‘TRACESVR’,
‘TSMSYS’,
‘IMP_FULL_DATABASE’,
‘EXP_FULL_DATABASE’,
‘DBA’,
‘XDB’) order by 1;
spool off;

spool audit.log
prompt**=====================================================================================================**
prompt**    **Identify db audit**
prompt**=====================================================================================================**
show parameter audit
spool off;

spool user_profile.log
prompt**=====================================================================================================**
prompt**    **Oracle user and profiles **
prompt**=====================================================================================================**
set lines 300
col username for a20
col profile for a20
select username,profile from dba_users;
spool off;

spool supplied_packages.log
prompt**=====================================================================================================**
prompt**    **Oracle supplied packages**
prompt**=====================================================================================================**
col object_name for a45
SELECT DISTINCT Owner, Object_Type, Object_Name,STATUS FROM DBA_Objects_AE
WHERE Owner IN (
‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’,
‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’,
‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’,
‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’,
‘WMSYS’, ‘OLAPDBA’, ‘OLAPSVR’, ‘OLAP_USER’,
‘OLAPSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’,
‘SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’)
AND Object_Type IN (‘PACKAGE’)
ORDER BY Owner, Object_Type, Object_Name;

spool off;

spool sga.log
prompt**=====================================================================================================**
prompt**    **Check if  allocated SGA is adequate **
prompt**=====================================================================================================**
show parameter sga

select round((sum(decode(name, ‘free memory’, bytes, 0)) / sum(bytes))* 100,2) “SGA Free Memory” from v$sgastat;
spool off;

spool pga.log
prompt**=====================================================================================================**
prompt**    **Identify DBs performing heavy sorting, hash joining and configure PGA Separately**
prompt**=====================================================================================================**
show parameter PGA

SELECT (1 – (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 Dictionary_Cache_Hit_Ratio FROM  v$rowcache;

SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100 Library_Cache_Hit_Ratio FROM   v$librarycache;

SELECT (1 – (Sum(misses) / Sum(gets))) * 100 Latch_Hit_Ratio FROM   v$latch;

select (disk.value/mem.value) * 100 Disk_Sort_Ratio FROM  v$sysstat disk,v$sysstat mem WHERE  disk.name = ‘sorts (disk)’ AND    mem.name  = ‘sorts (memory)’;
spool off;

spool index.log
prompt**=====================================================================================================**
prompt**    **Check any unusable status of existing Indexes**
prompt**=====================================================================================================**

select owner,index_name from dba_indexes WHERE STATUS = ‘UNUSABLE’
union all
select INDEX_OWNER, index_name from dba_ind_partitions WHERE STATUS = ‘UNUSABLE’
union all
select INDEX_OWNER, index_name from dba_ind_subpartitions WHERE STATUS = ‘UNUSABLE’;
spool off;

spool fragmention.log
prompt**=====================================================================================================**
prompt**    **Identify Tables with fragmentation**
prompt**=====================================================================================================**
set lines 300 pages 3000
col TABLESPACE_NAME for a25
col TABLE_NAME for a25
break on report
compute sum of FRAGMENTED_SPACE on report;
select
owner,table_name,tablespace_name,
blocks,
num_rows,
avg_row_len,round(((blocks*8/1024)),2)  “TOTAL_SIZE”,
round((num_rows*avg_row_len/1024/1024),2) “ACTUAL_SIZE”,
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) “FRAGMENTED_SPACE”
from dba_tables
where owner <> ‘SYS’
and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 500
order by FRAGMENTED_SPACE desc;

select OWNER,TABLE_NAME,SEGMENT_NAME from dba_lobs where table_name in (select table_name from dba_tables where owner <> ‘SYS’ and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 100);
spool off;

spool row_chaining.log
prompt**=====================================================================================================**
prompt**    **Row chaining and recommend**
prompt**=====================================================================================================**
set pages 9999;
column c1 heading “Owner”   format a9;
column c2 heading “Table”   format a12;
column c3 heading “PCTFREE” format 99;
column c4 heading “PCTUSED” format 99;
column c5 heading “avg row” format 99,999;
column c6 heading “Rows”    format 999,999,999;
column c7 heading “Chains”  format 999,999,999;
column c8 heading “Pct”     format .99;
set heading off;
select ‘Tables with migrated/chained rows and no RAW columns.’ from dual;
set heading on;
select
owner              c1,
table_name         c2,
pct_free           c3,
pct_used           c4,
avg_row_len        c5,
num_rows           c6,
chain_cnt          c7,
chain_cnt/num_rows c8
from dba_tables
where
owner not in (‘SYS’,’SYSTEM’)
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in (‘RAW’,’LONG RAW’,’CLOB’,’BLOB’,’NCLOB’)
)
and
chain_cnt > 0
order by chain_cnt desc
;
spool off;

spool tablespace_fragmentation.log
prompt**=====================================================================================================**
prompt**    **Tablespace Level  Fragmentation**
prompt**=====================================================================================================**
SELECT
tablespace_name,
count(*) free_chunks,
decode(round((max(bytes) / 1024000),2),
null,0,
round((max(bytes) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
FROM
sys.dba_free_space
group by
tablespace_name
order by 2 desc, 1;
spool off;

spool stale_stas.log
prompt**=====================================================================================================**
prompt**    **Identify objects with stale statistics **
prompt**=====================================================================================================**

col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED,STALE_STATS from dba_TAB_STATISTICS where STALE_STATS=’YES’ and OWNER not in(‘SYS’,’SYSTEM’) and TABLE_NAME not like ‘%BIN$%’;
spool off;

spool invalid_object.log
prompt**=====================================================================================================**
prompt**    **Identify INVALID objects**
prompt**=====================================================================================================**

set lines 300
col CREATED for a28
col LAST_DDL_TIME for a28
col object_name for a40
col object_type for a18
col owner for a16
col status for a19
select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS,to_char(CREATED,’dd-mm-yyyy hh24:mi:ss’) CREATED,to_char(LAST_DDL_TIME,‘dd-mm-yyyy hh24:mi:ss’) LAST_DDL_TIME from dba_objects where status=’INVALID’ and OWNER not in (‘SYS’,’SYSTEM’);
spool off;

spool deadlock.log
prompt**=====================================================================================================**
prompt**    **Check the alertlog for Frequently Occurring Deadlocks.**
prompt**=====================================================================================================**

set lines 300 pages 3000
col USER_ID for a18
col CLIENT_ID for a23
col MODULE_ID for a23
col PROCESS_ID for a20
col HOST_ID for a20
col HOST_ADDRESS for a23
col MESSAGE_TEXT for a80
select USER_ID,CLIENT_ID,MODULE_ID,PROCESS_ID,HOST_ID,HOST_ADDRESS,to_char(ORIGINATING_TIMESTAMP,’dd-mm-yyyy hh24:mi:ss’) ORIGINATING_TIMESTAMP,MESSAGE_TEXT from X$DBGALERTEXT where MESSAGE_TEXT like ‘%ORA-00060%’;
spool off;

spool standby_sync.log
prompt**=====================================================================================================**
prompt**    **Check Primary and standby sync status .**
prompt**=====================================================================================================**

set lines 300
col name for a10
col status for a10
select a.name,a.status,to_char(sysdate,’DD-MON-YYYY HH24:MI:SS’) time,a.thread#,(select max(sequence#) from v$archived_log where archived=’YES’ and thread#=a.thread#) archived,max(a.sequence#) applied,(select max(sequence#) from v$archived_log where archived=’YES’ and thread#=a.thread#)-max(a.sequence#) gap from v$archived_log a where a.applied=’YES’  group by a.thread#,a.name,a.status;

spool off;

=================================================================

set pagesize 1100
set markup html on spool on
spool Dailycheck.html
set feedback off
set pages 50
set lines 1000
set pages 70
set heading on
PROMPT================================================================
prompt
PROMPT                        

PROMPT                    Daily Checkup Report
prompt =================================================================================================================================================
PROMPT  REPORT DATE
select to_char(sysdate,’DD-MON-YYYY:HH:MI:SS’) “Report Run Date”  from dual;
—
prompt DATABASE NAME
PROMPT ==============
select instance_name,host_name,version,status from gv$instance ,v$database a;
—

PROMPT DATABASE INFO
PROMPT =============
select NAME,
CREATED,
LOG_MODE,
CHECKPOINT_CHANGE#,
ARCHIVE_CHANGE#
from v$database;

select status from v$instance;

prompt TABLESPACE INFO
prompt ===================
column tablespace_name for a30
column INITIAL_SIZE    for 999999999.99
column tbfree    for 99999.99
column Largest   for 99999.99
column ratio     for  9999.99
column FREE_SPACE  for 99999.99
SELECT
fs.tablespace_name name,
df.totalspace mbytes,
(df.totalspace – fs.freespace) used,
fs.freespace free,
100 * (fs.freespace / df.totalspace) pct_free
FROM
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) TotalSpace
FROM
dba_data_files
GROUP BY
tablespace_name
) df,
(SELECT
tablespace_name,
ROUND(SUM(bytes) / 1048576) FreeSpace
FROM
dba_free_space
GROUP BY
tablespace_name
) fs
WHERE
df.tablespace_name = fs.tablespace_name(+);
PROMPT   SESSION INFORMATION
PROMPT ========================
select status,count(status) Count from v$session group by status;

PRoMPT HIT RATIOS
PROMPT =============
PROMPT SORT STATISTICS  THIS SHOULD BE MORE THAN 95 %
SELECT (1-d.VALUE/m.value)*100 “SORT RATIO ” FROM V$SYSsTAT d,v$sysstat m
WHERE d.name =’sorts (disk)’ and m.name=’sorts (memory)’;

PROMPT DICTIONARY HIT RATIO
PROMPT Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora
PROMPT ======================
select (1-(sum(getmisses)/sum(gets)))*100 “OVER ALL DICT HIT RATIO” from v$rowcache;

PROMPT  THE OVER ALL HITRATIO OF THE LIBRARY CACHE
select sum(gethitratio)/count(*) *100 ” LIBRARY CACHE HIT RATIO ” from v$librarycache;

PROMPT BUFFER HIT RATIO
PROMPT =================
SELECT (1-PHY.VALUE/(cur.value+con.value))*100 ” BUFFER CACHE HIT RATIO ” from v$sysstat phy,v$sysstat con,v$sysstat cur
where cur.name=’db block gets’
and con.name=’consistent gets’
and phy.name=’physical reads’;

PROMPT  INDEX LOOK UP RATIO
PROMPT =====================
SELECT (1-l.VALUE/(l.value+s.value))*100 “INDEX LOOK UP RATIO ” FROM V$SYSSTAT l ,v$sysstat s
WHERE s.name =’table scans (short tables)’ and l.name= ‘table scans (long tables)’;

—
PROMPT DATABASE SIZE
PROMPT ================
PROMPT TOTAL SIZE OF A DATABASE
select sum(bytes)/1024/1024/1024 “Physical Database Size” ,’ GB ‘
from dba_data_files ;

PROMPT ACTUAL SIZE OF DATABASE
select sum(bytes)/1024/1024/1024 “Actual Database Size”, ‘ GB ‘
from dba_segments ;

PROMPT   INVALID OBJECTS
PROMPT =====================
Select count(*) “INVALID OBJECTS”,OWNER,object_type  from all_objects where status=’INVALID’ group by owner,object_type order by 2;

—-
PROMPT  REDO LOGS AND ARCHIVE STATUS
PROMPT ==================================
COLUMN member_name HEADING ‘Member_Name’;
COL MEMBER FOR A40;
SELECT vlf.member “member_name”,
vl.group# “Group”,
vl.status “Status”,
vl.archived “Archived”,
vl.bytes / 1024 “Size (K)”,
vl.sequence# “Sequence”
FROM v$logfile vlf,
v$log vl
WHERE vlf.group# = vl.group#
ORDER BY 1, vl.group#, vlf.member;

—
PROMPT  Session I/O By User
PROMPT ==================================

select nvl(ses.USERNAME,’ORACLE PROC’) username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME;

—
prompt SEGMENTS HAVING LESS THAN 45 FREE EXTENTS
PROMPT ========================================
SELECT owner, segment_name, segment_type, extents, max_extents, next_extent,
initial_extent
FROM dba_segments
WHERE max_extents – extents < 45;

—
PROMPT LOCK INFORMATION
PROMPT ===================
select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID oracle_id,
decode(TYPE,
‘MR’, ‘Media Recovery’,
‘RT’, ‘Redo Thread’,
‘UN’, ‘User Name’,
‘TX’, ‘Transaction’,
‘TM’, ‘DML’,
‘UL’, ‘PL/SQL User Lock’,
‘DX’, ‘Distributed Xaction’,
‘CF’, ‘Control File’,
‘IS’, ‘Instance State’,
‘FS’, ‘File Set’,
‘IR’, ‘Instance Recovery’,
‘ST’, ‘Disk Space Transaction’,
‘TS’, ‘Temp Segment’,
‘IV’, ‘Library Cache Invalidation’,
‘LS’, ‘Log Start or Switch’,
‘RW’, ‘Row Wait’,
‘SQ’, ‘Sequence Number’,
‘TE’, ‘Extend Table’,
‘TT’, ‘Temp Table’, type) lock_type,
decode(LMODE,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’, lmode) lock_held,
decode(REQUEST,
0, ‘None’,
1, ‘Null’,
2, ‘Row-S (SS)’,
3, ‘Row-X (SX)’,
4, ‘Share’,
5, ‘S/Row-X (SSX)’,
6, ‘Exclusive’, request) lock_requested,
decode(BLOCK,
0, ‘Not Blocking’,
1, ‘Blocking’,
2, ‘Global’, block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND     l.SID = lo.SESSION_ID;

—–

PROMPT HIGH RESOURCE CONSUMING SQL
PROMPT ===========================
select sql_text,
username,
disk_reads_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
(select sql_text ,
b.username ,
round((a.disk_reads/decode(a.executions,0,1,
a.executions)),2)
disk_reads_per_exec,
a.disk_reads ,
a.buffer_gets ,
a.parse_calls ,
a.sorts ,
a.executions ,
a.rows_processed ,
100 – round(100 *
a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
a.first_load_time ,
sharable_mem ,
persistent_mem ,
runtime_mem,
cpu_time,
elapsed_time,
address,
hash_value
from
sys.v_$sqlarea a,
sys.all_users b
where
a.parsing_user_id=b.user_id and
b.username not in (‘sys’,’system’)
order by 3 desc)
where rownum < 21;

PROMPT Scheduled Job Status
PROMPT ====================

set pagesize 1000
SELECT log_id, job_name, status,
EXTRACT(HOUR FROM RUN_DURATION)||’:’||EXTRACT(MINUTE FROM RUN_DURATION)||’:’||EXTRACT(SECOND FROM RUN_DURATION) RUN_DURATION,
to_char(log_date,’DD-MON-YYYY HH24:MI’) log_date FROM dba_scheduler_job_run_details
WHERE owner=’STAGING’ AND TO_CHAR(LOG_DATE,’DD-MON-YYY’) = TO_CHAR(SYSDATE, ‘DD-MON-YYY’);

spool off

exit