One simple health check for oracle with SQL

One simple health check for oracle with SQL There are some sqls which is used for check the oracle database's health condition.

[sql] ------numbers of process  [sql] select count(*) process_num from v$process;  [sql] select SESSIONS_MAX,SESSIONS_CURRENT,SESSIONS_HIGHWATER from v$license;  --parameters:  show parameter processes    select count(*) datafile_num from v$datafile;    show parameter db_files;    col comp_name for a40  col version for a16  col status for a10  set pagesize 1000  set line 100  select comp_name,version,status from dba_registry;      select count(*) datafile_num from dba_data_files;    select count(*) tablespace_num from dba_tablespaces;    select sum(bytes)/1024/1024/1024 datafile_size from v$datafile;    col name for a30  col type for a10  col value for a40  show parameter      show sga    col pool for a20  col name for a40  set line 100  set pagesize 1000  SELECT * FROM V$SGASTAT;    col value for 999999999  col name for a40  set line 100  SELECT * FROM V$PGASTAT;      col value$ for a30  col name for a30  select name,value$ from props$ where name like '%CHARACTERSET%';      ARCHIVE LOG LIST;    SELECT COUNT(*) ctl_num FROM V$CONTROLFILE;  col name for a30  set line 100  select * from v$controlfile;    set linesize 120  col first_change# for 9999999999999  col status for a10  alter session set nls_date_format='yyyymmdd hh24miss';  select * from v$log;  col member for a40  select * from v$logfile;    show parameter db_block_size    set linesize 120 pagesize 50  col file_name for a40  col ts_name for a18  col cur_mb for 99999  col max_mb for 99999  select status, file_id, file_name, tablespace_name ts_name, autoextensible,  bytes/1048576 cur_mb, maxbytes/1048576 max_mb  from dba_data_files order by file_name;      select status, file_id, file_name, tablespace_name ts_name, autoextensible,  bytes/1048576 cur_mb, maxbytes/1048576 max_mb  from dba_temp_files order by file_name;      set linesize 120 pagesize 50  col tablespace_name format a20  col contents for a9  col ext_mgmt for a10  col alloc_type for a9  col ext_kb format 999999  col ssm for a6  col total_mb format a13  col free_mb format a10  col free_pct format a7  select c.tablespace_name, c.contents, c.extent_management ext_mgmt,   c.allocation_type alloc_type, c.initial_extent/1024 ext_kb,  c.segment_space_management SSM, a.total_mb, b.free_mb,   to_char(100*b.free_mb/a.total_mb, '999.99') free_pct  from   (select tablespace_name, to_char(sum(bytes)/1048576, '99999999.99') total_mb    from dba_data_files group by tablespace_name) a,   (select tablespace_name, to_char(sum(bytes)/1048576, '999999.99') free_mb,     count(*) free_exts, to_char(max(bytes)/1048576, '999999') max_mb,     to_char(min(bytes)/1048576, '999999') min_mb    from dba_free_space group by tablespace_name) b,   dba_tablespaces c  where a.tablespace_name=b.tablespace_name(+) and c.tablespace_name=b.tablespace_name  order by free_pct, tablespace_name;      select owner,count(*) seg_num_system from dba_segments where tablespace_name='SYSTEM' group by owner;      col username for a16  col default_tablespace for a20  col temporary_tablespace for a20  select username, default_tablespace, temporary_tablespace from dba_users   where default_tablespace='SYSTEM' or temporary_tablespace='SYSTEM'; select count(*) invalid_num from dba_objects where status='INVALID'; col owner for a16  col object_name for a30  col object_type for a16  set line 100  set pagesize 10000  select owner,object_name,object_type from dba_objects where status='INVALID';    col table_name for a30  col tablespace_name for a20  set line 100  select owner, table_name, tablespace_name, chain_cnt from dba_tables where chain_cnt >0;      select OWNER||'.'||index_name as "OWNER.INDEX_NAME", blevel from dba_indexes where blevel>=4 order by 2 desc;    col username for a16  select * from v$pwfile_users;      col GRANTEE for a20  col GRANTED_ROLE for a20   Select * from dba_role_privs where granted_role='DBA';      select event,count(*) from v$session_wait group by event;