dba常用检查脚本

检查警告文件，跟踪文件，备分情况

1.表空间的使用情况：

   select  a.tablespace_name as table_space, a.total_size as total_size,round(b.total_free_size,1) as  total_free_size,round((a.total_size - b.total_free_size),2) as userd_size,  to_char(100*b.total_free_size/a.total_size,'99.99')||'%' as percent_free  from (select tablespace_name,sum(bytes)/1024/1024 as total_size from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes/1024/1024) as total_free_size from dba_free_space  group by tablespace_name) b  where a.tablespace_name= b.tablespace_name;

 

2.检查有无失效对象：

   select owner object_owner,object_name,object_type,status from dba_objects where owner not in('sys','system') and status='INVALID' order by owner,object_type,object_name;

 

3.检查有无失效索引：

   select  index_name,index_type,table_name,status from dba_indexes where status='UNSABLE';

 

4. 检查是否有表空间碎片:FSFI(free space fragmentation index) 100表示没有碎片，低于30%要处理

   select tablespace_name, sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) as FSFI  from dba_free_space group by tablespace_name order by 1;

 

5.每月数据增长情况:

   select to_char(creation_time,'RRRR Month') as month, sum(bytes)/1024/1024/1024 growth_in_gb from sys.v_$datafile  where creation_time>sysdate -365 group by to_char(creation_time,'RRRR Month');

 

6.每月表空间增长情况:

    select a.ts# as tablespace_no, b.name as tablespace_name, to_char(creation_time,'RRRR Month') month,sum(a.bytes)/1024/1024/1024  growht_in_gb from sys.v_$datafile a, sys.v_$tablespace b where a.creation_time>sysdate-365 and a.ts#=b.ts# group by a.ts#,b.name,to_char(a.creation_time,'RRRR Month');

 

7.在线重做日志文件的创建:

    添加一个组

   alter database add logfile group 4('/u01/oracle/data/redo_4a.log','/u02/oracle/data/redo_4b.log') size 5M;

    添加到某个组:

    alter database add logfile member  '/u03/oracle/data/redo_4c.log' to group 3;

  系统提供: v$log       v$logfile

 

 

确认数据文件的使用量:

       select d.tablespace_name tablespace,d.file_name filename,d.bytes total_file_size,nvl((d.bytes-s.bytes),d.bytes) userd_size,trunc(((nvl((d.bytes-s.bytes),d.bytes))/d.bytes)*100) pct_used  from sys.dba_data_files d,v$datafile v,(select file_id,sum(bytes) bytes from sys.dba_free_space group by file_id) s   where (s.file_id(+)=d.file_id) and (d.file_name=v.name) union select  d.tablespace_name tablespace,d.file_name filename,d.bytes total_file_size,nvl(t.bytes_cached,0)  used_size  ,trunc((t.bytes_cached/d.bytes)*100) pct_used  from sys.dba_temp_files  d,v$temp_extent_pool t,v$tempfile v where (t.file_id(+)=d.file_id) and (d.file_id=v.file#);

 

确认数据库缓冲快取区的hit ratio：

    select 1-(phy.value/(cur.value+con.value)) cache_hit_ratio, round((1-(phy.value/(cur.value+con.value)))*100,2)  ratio   from v$sysstat cur,v$sysstat con,v$sysstat phy  where cur.name='DB BLOCK GETS' and con.name='CONSISTENT GETS'  and phy.name='PHYSICAL READS';

 

 

确认使用者联机使用PGA的大小:

   select username, name, value  from v$statname n,v$session s,v$sesstat t  where s.sid=t.sid and n.statistic#=t.statistic#   and s.type='USER'  and  s.username is not null and n.name ='SESSION PGA MEMORY'  and username not like 'SYSTEM' and t.value >30000;