Report_oracle_inspection.sql

set heading off
select ' 0-0 The User Password Expiry ' from dual;
set heading on
COL USERNAME FOR A10;
COL LIMIT FOR A10;
SELECT A.USERNAME,B.LIMIT FROM DBA_USERS A ,DBA_PROFILES B WHERE A.PROFILE=B.PROFILE AND B.RESOURCE_NAME='PASSWORD_LIFE_TIME';


set heading off
select ' 0 The Rman Status' from dual;
set heading on
COL START_TIME FOR A10;
COL END_TIME FOR A10;
COL STATUS FOR A21;
COL OBJECT_TYPE FOR A10;
COL OUTPUT_DEVICE_TYPE FOR A10;
COL OPERATION FOR A14;
select START_TIME,END_TIME,STATUS,OBJECT_TYPE,OUTPUT_DEVICE_TYPE,a.OPERATION from v$rman_status a where a.STATUS  <>'COMPLETED' order by START_TIME  desc;


set heading off
select '1 Database of the basic situation' from dual;


set heading off
select ' 1 The database version' from dual;
set heading on
select * from v$version;


set heading off
select ' 2 View the basic database information' from dual;
set heading on
set linesize 500
col host_name for a20
select dbid,name,instance_name,instance_name,version,parallel rac,host_name from v$database,v$instance;




set heading off
select ' 3 Instance Status' from dual;
set heading on
select instance_number,instance_name ,status from gv$instance;




set heading off
select ' 4 Memory conditions' from dual;
set heading on
select * from v$sgainfo;




set heading off
select ' 5 Cpu situation' from dual;
set heading on
col STAT_NAME for a20
col COMMENTS for a50
select stat_name,value,comments from v$osstat where stat_name in ('NUM_CPUS','IDLE_TIME','BUSY_TIME','USER_TIME','SYS_TIME','IOWAIT_TIME');




set heading off
select '2 Check the Oracle object status' from  dual;




set heading off
select ' 1 View the location of the parameter file' from dual;
show parameter spfile




set heading off
col NAME for a50
select ' 2 View the control file' from dual;
set heading on
select status,name from v$controlfile;




set heading off
select ' 3 View online logs' from dual;
set heading on
col MEMBER for a50
select group#,status,type,member from v$logfile;




set heading off
select ' 4 Check the log switching frequency' from dual;
set heading on
select sequence#,to_char(first_time,'yyyymmdd_hh24:mi:ss') firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from v$log_history where first_time > sysdate - 1 order by first_time ,minutes;




set heading off
select ' 5 View the data file' from dual;
set heading on
col NAME for a50
select name,status from v$datafile;




set heading off
select ' 6 View disabled Objects' from dual;
set heading on
set linesize 500
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';




set heading off
select ' 7 View rollback segment status' from dual;
set heading on
select segment_name,status from dba_rollback_segs;




set heading off
select ' 8 Check whether the constraint is disabled' from dual;
set heading on
set linesize 1000
SELECT owner, constraint_name, table_name, constraint_type, status 
     FROM dba_constraints 
    WHERE status ='DISABLE' and constraint_type='P';




set heading off
select ' 9 Check to see if triggers are disabled' from dual;
set heading on
col owner for a10
col taigger_name for a10
col table_name for a30
col table_name for a30
 SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';




set heading off
select ' 10 Job Disable' from dual;
set heading on
select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user='CAIKE';




set heading off
select ' 11 Check for invalid indexes' from dual;
set heading on
select index_name,table_name,tablespace_name,status From dba_indexes Where owner='CTAIS2' And status<>'VALID';




set heading off
select '3 Check the use of Oracle-related resources' from dual;




set heading off
select ' 1 View the table space usage' from dual;
set heading on
set linesize 100
SELECT UPPER(F.TABLESPACE_NAME) "tablespace_name",    
D.TOT_GROOTTE_MB "tablesapce_size(M)",    
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "used_tablespace_size(M)",    
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "used%",    
F.TOTAL_BYTES "free_size(M)",    
F.MAX_BYTES "max_byte(M)"   
FROM (SELECT TABLESPACE_NAME,    
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,    
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES    
FROM SYS.DBA_FREE_SPACE    
GROUP BY TABLESPACE_NAME) F,    
(SELECT DD.TABLESPACE_NAME,    
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB    
FROM SYS.DBA_DATA_FILES DD    
GROUP BY DD.TABLESPACE_NAME) D    
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME    
ORDER BY 4 DESC; 


select name, total_mb / 1024 as TOTAL_G