当前位置:数据库 > Oracle >>

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;  

 


CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,