One 易做图 health check for oracle with sql
One 易做图 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;