oracle数据库的运行状态,Oracle数据库在线运行情况检查

实例信息：

select instance_name,host_name,version,to_char(startup_time,'yyyy-mm-dd hh24:mi') startuptime,status,archiver from v$instance;

会话限制信息：

SELECT resource_name, current_utilization,

max_utilization,

LIMIT,

ROUND (max_utilization / LIMIT * 100) || '%' rate

FROM (SELECT resource_name,

current_utilization,

max_utilization,

TO_NUMBER (initial_allocation) LIMIT

FROM v$resource_limit

WHERE resource_name IN ('processes', 'sessions')

AND max_utilization > 0);

profile：

select profile,resource_name,resource_type,limit from dba_profiles order by profile,resource_type,resource_name;

parameter：

select name,value from v$parameter where name in('background_dump_dest','control_files','cluster_database','db_block_size','open_cursors','spfile','undo_management','undo_retention','undo_tablespace')order by name;

内存配置(Oracle9i)：

select component,current_size/1024/1024 cur_size_M from v$sga_dynamic_components

union all

select name,round(value/1024/1024) from v$parameter where name in ('sga_max_size','sga_target','pga_aggregate_target');

内存配置(Oracle10g) ：

select component,current_size/1024/1024 cur_size_M ,max_size/1024/1024

max_size_M from v$sga_dynamic_components;

内存配置(Oracle11g)：

select component,current_size/1024/1024 cur_size_M from v$memory_dynamic_components

union all

select name,round(value/1024/1024) from

v$parameter where name in ('memory_target','memory_max_target','sga_max_size','sga_target','pga_aggregate_target');

Redo log information：

select b.thread#,b.group#,a.member,b.bytes/1024/1024,b.status,b.archived from v$logfile a ,v$log b where a.GROUP#=b.GROUP# order by  b.thread#,b.group#,a.member;

日志切换频率：

select * from (

select to_char(trunc(first_time, 'HH'),'yyyy-mm-dd hh24') time, count(*)

from v$loghist

group by to_char(trunc(first_time, 'HH'),'yyyy-mm-dd hh24') order by time desc) where to_date(time,'yyyy-mm-dd hh24')>sysdate-7;

表空间使用率：

select b.tablespace_name,

b.total_size_m as "total(mb)",

nvl(a.free_size_m, 0) as "free(mb)",

b.total_size_m-nvl(a.free_size_m, 0) as "used(mb)",

decode(a.free_size_m,

'',

100,

round((b.total_size_m-a.free_size_m) / b.total_size_m * 100, 2)) as "used_rate",

c.status,c.contents,c.extent_management as "extent_mgr" ,c.segment_space_management AS "segment_mgr",

c.bigfile

from (select tablespace_name, sum(bytes) / 1024 / 1024 as free_size_m

from dba_free_space

group by tablespace_name) a,

(select tablespace_name, sum(bytes) / 1024 / 1024 as total_size_m

from dba_data_files

group by tablespace_name) b,

(select tablespace_name,status,contents, extent_management,

segment_space_management,bigfile from dba_tablespaces) c

where b.tablespace_name = a.tablespace_name(+)

and b.tablespace_name =c.tablespace_name(+)

union all

select b.TABLESPACE_NAME,

b.bytes / 1024 / 1024 as "total(mb)",

(b.bytes- nvl(a.used_bytes, 0))/1024/1024 as "free(mb)",

nvl(a.used_bytes, 0) / 1024 / 1024 as "used(mb)",

decode(a.used_bytes, null, 0, round(a.used_bytes / b.bytes * 100, 2)) as "used_rate",

c.status,c.contents,c.extent_management as "extent_mgr" ,c.segment_space_management AS "segment_mgr",

c.bigfile

from (select s.TABLESPACE_NAME,

s.total_blocks * p.value as total_bytes,

s.free_blocks * p.value as free_bytes,

s.used_blocks * p.value as used_bytes

from v$sort_segment s, v$parameter p

where p.NAME = 'db_block_size') a,

(select tablespace_name, sum(user_bytes) as bytes

from dba_temp_files

group by tablespace_name) b,

(select tablespace_name,status,contents, extent_management,

segment_space_management,bigfile from dba_tablespaces) c

where b.tablespace_name = a.TABLESPACE_NAME(+)

and b.tablespace_name = c.TABLESPACE_NAME(+)

order by 5 desc;

数据文件部署状况：

select * from (

select file_name,tablespace_name,bytes/1024/1024 mb,status,autoextensible from dba_data_files

union all

select file_name,tablespace_name,bytes/1024/1024 mb,status,autoextensible from dba_temp_files)

order by tablespace_name,file_name;

Segment information：

select a.*,b.num_rows,b.partitioned,b.last_analyzed_time from (select owner,segment_name,segment_type,Size_MB

from (select owner,

segment_name,

sum(bytes) / 1024 / 1024 as Size_MB ,

segment_type

from dba_segments

group by owner, segment_name, segment_type

having sum(bytes)/1024/1024 >100

order by 3 desc)

where rownum < 50 )a left join

(select owner,table_name,num_rows,partitioned,to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') as last_analyzed_time from dba_tables ) b

on a.owner=b.owner

and a.segment_name=b.table_name;

unusable index ：

select owner,index_name,table_name,status from  dba_indexes where status ='UNUSABLE'    order by owner,table_name,index_name;

recyclebin information(oracle10g/11g)：

select owner,count(*) from dba_recyclebin group by owner;

asm_diskgroup information：

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

asm_disk information：

select GROUP_NUMBER group#,DISK_NUMBER disk#,LABEL,STATE,FAILGROUP,name,path,TOTAL_MB,FREE_MB from v$asm_disk order by group#,disk#;

响应最慢的前50个sql：

select * from (select to_char(l.start_time,'yyyy-mm-dd hh24:mi:ss') as start_time, l.ELAPSED_SECONDS,s.SQL_ID,s. SQL_TEXT from v$sqlarea s,v$session_longops l where s.sql_id=l.sql_id and l.ELAPSED_SECONDS>5 order by l.ELAPSED_SECONDS desc) where rownum<51;

alert文件里的错误信息：

查找alert文件：

select value||'\alert_'||(select instance_name from v$instance)||'.log' from v$parameter where name='background_dump_dest';

文件系统利用率：

windows：查看数据文件所在磁盘的空间，已用空间