oracle巡检语句,oracle数据库巡检（一）基本sql语句

很多初学者在管理oracle数据库的时候面临一个问题，在没有前人留下的任何文档资料的情况下要检查这个数据库的运行状态，我也同样经历了这样的痛苦，不过好在有前人留下的一个巡检报告范本，根据网络上的资料我总结了一些基本的检查语句供自己积累和大家的参考。

--数据库巡检sql语句

--查询当前数据库大小(数据文件+临时表空间+重做日志)

select round(sum(space)) Total_DB_size_in_MB from

(

select sum(bytes)/1024/1024 space from dba_data_files

union all

select nvl(sum(bytes)/1024/1024,0) space from dba_temp_files

union all

select sum(bytes)/1024/1024 space from v$log

);

--查询表空间及数据文件使用情况

select a.tablespace_name "Tablespace",a.file_name "DataFile",a.bytes/1024/1024 "Total MBytes",a.blocks "DB Blocks",

sum(nvl(b.bytes,0))/1024/1024 "MBytes Free",

round(sum(nvl(b.bytes,0))/a.bytes*100,3) "%Free",

(a.bytes-sum(nvl(b.bytes,0)))/1024/1024 "MBytes Used",

round((a.bytes-sum(nvl(b.bytes,0)))/a.bytes*100,3) "%Used"

from dba_data_files a, dba_free_space b

where a.file_id=b.file_id

group by a.tablespace_name ,a.file_name,a.bytes,a.blocks

order by a.tablespace_name;

--查询日志组及日志的位置大小

select l.group#, lf.member as Logfile,l.status,l.bytes/1024/1024 as "Size/MB"

from v$log l,v$logfile lf

where l.group#=lf.group#

order by group#

group by group#,lf.member;

--查看所有用户基本信息

select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,CREATED from dba_users

--查看用用DBA角色的用户

select GRANTEE,GRANTED_ROLE,ADMIN_OPTION from dba_role_privs where GRANTED_ROLE='DBA' order by GRANTEE;

--查看被授予SYSDBA权限的用户

select * from V$PWFILE_USERS

select * from dba_sys_privs where GRANTEE  not in ('DBA','SYS');

--查看非系统用户权限

select * from dba_sys_privs where GRANTEE in ( select USERNAME from dba_users where account_status ='OPEN' and USERNAME not like '%SYS%') order by GRANTEE;

--查看失效对象

SELECT owner,object_type,object_name,status,LAST_DDL_TIME  FROM dba_objects WHERE status like 'INVALID';

--检查数据库安装选项

select * from v$option;

--查看oracle组件版本

select comp_id, comp_name, version,status from dba_registry;

--检查数据库参数配置

select NAME,VALUE,ISDEFAULT as "Default",ISSYS_MODIFIABLE as "Dynamic" from v$system_parameter order by name;

--检查控制文件

select * from v$controlfile;

--检查系统表空间内非系统用户的对象

select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where tablespace_name='SYSTEM' and owner not in ('SYS','SYSTEM','OUTLN');

--查询当前session数和最大的session数(自数据库运行以来)

select d.name "DATABASE",to_char(sysdate,'yyyy-MM-dd HH:MM:SS') "Check Date",l.SESSIONS_CURRENT,l.SESSIONS_HIGHWATER from v$license l ,v$database d;

--查看ASM空间使用

select name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;

--查看SGA大小

select name,display_value from v$parameter where name like 'sga%'

--查看PGA大小

select name,display_value from v$parameter where name like '%pga%'

这些只能算是一个最基本的参考语句，下一篇《oracle数据库巡检(二)全面检查》我将整理好我正在使用的一个较为完整的数据库系统的检查文档。