oracle数据库监控方案 _oracle

一、概述监控Oracle数据库的数据增长是数据库管理员（DBA）和数据管理团队必须进行的重要任务之一。通过监控数据增长，可以确保数据库的运行顺畅、性能高效，并能够及时采取必要的措施来处理数据增长引起的问题。
监控数据文件的增长是非常重要的，因为数据文件是存储数据库中所有数据的主要组成部分。DBA可以通过跟踪数据文件的大小和增长速度来了解数据增长的趋势，并根据需要调整数据库的存储配置。
【oracle数据库监控方案】
二、解决方案2.1 监控数据库的增长SET LINESIZE 200SET PAGESIZE 200COL "Database Size" FORMAT a13COL "Used Space" FORMAT a11COL "Used in %" FORMAT a11COL "Free in %" FORMAT a11COL "Database Name" FORMAT a13COL "Free Space" FORMAT a12COL "Growth DAY" FORMAT a11COL "Growth WEEK" FORMAT a12COL "Growth DAY in %" FORMAT a16COL "Growth WEEK in %" FORMAT a16SELECT(select min(creation_time) from v$datafile) "Create Time",(select name from v$database) "Database Name",ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %"FROM(SELECT BYTES FROM V$DATAFILEUNION ALLSELECT BYTES FROM V$TEMPFILEUNION ALLSELECT BYTES FROM V$LOG) USED,(SELECT SUM(BYTES) AS P FROM DBA_FREE_SPACE) FREEGROUP BY FREE.P;

文章插图

2.2 监控数据库每月的增长情况

select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB"from sys.v_$datafilewhere to_char(creation_time,'RRRR')='2014'group by to_char(creation_time, 'MM-RRRR')order byto_char(creation_time, 'MM-RRRR');