注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

常在心

淡泊明志,人生自在

 
 
 

日志

 
 

健康检查  

2011-03-28 13:37:43|  分类: 常用脚本查询 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

/*  Script Version2010.08.23 By jzl */
conn / as sysdba
spool healthcheck.log
set linesize 132
set echo off
whenever sqlerror continue;


prompt
prompt =====1.Number of Datafiles:=====================================
select count(*) from dba_data_files;

prompt
prompt =====2.Number of Tablespaces:===================================
prompt
select count(*) from v$tablespace;

prompt
prompt =====3.Disk Space (of all dbfiles):=============================
select sum(bytes)/1024/1024 as "sum(bytes)" from dba_data_files;

prompt
prompt =====4.Redo Log Size:===========================================
prompt
select group#,bytes/1024/1024 from v$log;

prompt
prompt =====5.Archiving Enabled:======================================= 
Archive log list;

prompt
prompt =====6.Database version:========================================
select * from v$version;

prompt
prompt =====7.INSTALL OPTION:==========================================
Col parameter for a50
Col value for a10
Select * from v$option  where value='TRUE';

prompt
prompt =====8.All parameter:===========================================
show parameter;

prompt
prompt =====9.SGA:=====================================================
SELECT pool, round( pool_bytes / 1048576 ),round( 100 * pool_bytes / total_sga, 2 ) percent
FROM
( SELECT sum( bytes ) total_sga FROM v$sgastat ),
( SELECT nvl( pool, name ) pool, SUM( bytes ) pool_bytes
FROM v$sgastat
GROUP BY nvl( pool, name ) )
ORDER BY 3 DESC;

prompt
prompt =====10.SGA used:================================================
select pool,name,bytes from v$sgastat;

prompt
prompt =====11.Number of control files:=================================
col name for a50;
select name,status from  v$controlfile;

prompt
prompt =====12.Number of redo log members per group:====================
col member format a50
select group#,status,member from  v$logfile;

prompt
prompt =====13.temp Tablespaces management mode:========================
SELECT tablespace_name, contents, extent_management
FROM dba_tablespaces WHERE CONTENTS= 'TEMPORARY';

prompt
prompt =====14.temp tablespace is system:===============================
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE temporary_tablespace = 'SYSTEM';

prompt
prompt =====15.user default temp tablespace is system:===============================
SELECT username, default_tablespace, temporary_tablespace
FROM dba_users
WHERE temporary_tablespace = 'SYSTEM';

prompt
prompt =====16.system Tablespaces:======================================
SELECT username, default_tablespace
FROM dba_users
WHERE default_tablespace = 'SYSTEM'
AND username not in ( 'SYS', 'SYSTEM' );

prompt
prompt =====17.TABLESPACE USAGE:========================================
SELECT A.TABLESPACE_NAME,
       FILENUM,
       TOTAL "TOTAL (MB)",
       F.FREE "FREE (MB)",
       TO_CHAR(ROUND(FREE * 100 / TOTAL, 2), '990.00') "FREE%",
       TO_CHAR(ROUND((TOTAL - FREE) * 100 / TOTAL, 2), '990.00') "USED%",
       ROUND(MAXSIZES, 2) "MAX (MB)"
  FROM (SELECT TABLESPACE_NAME,
               COUNT(FILE_ID) FILENUM,
               SUM(BYTES / (1024 * 1024)) TOTAL,
               SUM(MAXBYTES) / 1024 / 1024 MAXSIZES
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024))) FREE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 WHERE A.TABLESPACE_NAME = F.TABLESPACE_NAME;
 
prompt
prompt =====18.data segment:==============================================
SELECT DISTINCT (a.tablespace_name)
FROM dba_tables a, dba_indexes b
WHERE a.owner = b.table_owner
AND a.table_name = b.table_name
AND a.tablespace_name = b.tablespace_name
AND a.tablespace_name not in ( 'SYSTEM', 'SYCRM1UX' );

prompt
prompt =====19.Outdate index:==============================================
SELECT owner || '.' || index_name AS "OWNER.INDEX_NAME", blevel
FROM dba_indexes
WHERE blevel >= 4
ORDER BY blevel DESC;

prompt
prompt =====20.BACKUP MODE:===============================================
select set_stamp,
       backup_type,
       to_char(start_time,'yyyymmdd hh24:mi:ss')
   from v$backup_set where start_time > sysdate - 5 order by start_time;

prompt
prompt =====21.Tablespaces Fragmentation:=================================
select tablespace_name,
       sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
where tablespace_name in (select tablespace_name
                            from dba_tablespaces
                            where extent_management != 'LOCAL')
group by tablespace_name
order by 2 desc;

prompt
prompt =====22.extents>1000:==============================================
select segment_name,segment_type,next_extent,pct_increase,extents
from dba_segments where extents > 1000;

prompt
prompt =====23.Invalid object:============================================
select owner,
       object_type,
       count(object_type)
    from dba_objects where status='INVALID' group by owner,object_type;


prompt
prompt =====24.Invalid constraints:=======================================
SELECT owner, constraint_name, table_name, constraint_type, status
FROM dba_constraints
WHERE status ='DISABLE' and constraint_type='P';

prompt
prompt =====25.Invalid triggers:==========================================
SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = 'DISABLED';

prompt
prompt =====26.Timing JOB status:=========================================
select job,log_user,last_date,failures
from dba_jobs
where failures>0;

prompt
prompt =====27.Rowmovement table:==========================================
select owner,table_name,tablespace_name ,chain_cnt from dba_tables where chain_cnt>0;

prompt
prompt =====28.default tablespace is system:===============================
select distinct owner,tablespace_name from dba_segments where tablespace_name='SYSTEM';

spool off;
exit

  评论这张
 
阅读(108)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017