One simple health check for oracle with sql

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

 

------numbers of process

select count(*) process_num from v$process;

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;