表空间监控（二）datafile size detail

表空间监控（二）datafile size detail

导言：监控表空间使用率、数据文件使用率脚本。

#######################################################################################
Oracle Environment=>11.2.0.4.0 rac on two nodes
OS Environment=>AIX 6.1 (64bit)
#######################################################################################

SQL> @DFsizeDetail.sql

TSpace               File                                               File        File   File File Auto   Ext by File
Name                 Name                                               Status      Size   Free Free Extend ?      Max Size
                                                                                      Mb     Mb    % ?      Mb     Mb
-------------------- -------------------------------------------------- --------- ------ ------ ---- ------ ------ --------
BMCF_CHC2            +DATA/bmdb/datafile/bhaf_cdc2_02.dbf               AVAILABLE   3200    312    1 YES    .01    32767.98
                     +DATA/bmdb/datafile/bhaf_cdc2_01.dbf               AVAILABLE  32710   1213    0 YES    .01    32767.98

bm_DATA_TBS          +DATA/bmdb/datafile/bm_data_tbs_11.dbf             AVAILABLE   3840     15    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_12.dbf             AVAILABLE   3840     19    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_10.dbf             AVAILABLE   3904     18    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_03.dbf             AVAILABLE  32704     33    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_04.dbf             AVAILABLE  32704     18    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_05.dbf             AVAILABLE  32704     37    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_06.dbf             AVAILABLE  32704     35    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_07.dbf             AVAILABLE  32704     72    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_08.dbf             AVAILABLE  32704   3421    1 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_09.dbf             AVAILABLE  32704   3505    1 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_01.dbf             AVAILABLE  32767     33    0 YES    .01    32767.98
                     +DATA/bmdb/datafile/bm_data_tbs_02.dbf             AVAILABLE  32768      7    0 YES    50     32767.98
...

### NOTE ###
TSpace Name : Tablespace name
File Status : File status
File Size Mb : Current file size in Mb
File Free Mb : Current free space is file in Mb
File Free % : Current free space in %
Auto Extend ? : Is this file configured to auto extend ?
Ext by ? Mb : How much will this file extend every time it needs to ?
File Max Size Mb : How big this file can grow ?
#############################################################################

###DFsizeDetail.sql scripts contents are as follows ### 

set linesize 150
set pagesize 50

clear break

col TSname format a20 
col TSname heading 'TSpace|Name|'
col FName format a50 
col FName heading 'File|Name|'
col FStatus format a9 
col FStatus heading 'File|Status|'
col FSizeMb format 99999 
col FSizeMb heading 'File|Size|Mb'
col FileFreeMb format 99999
col FileFreeMb heading 'File|Free|Mb'
col FileFreePrct format 999
col FileFreePrct heading 'File|Free|%'
col AutoExt format a6 
col AutoExt heading 'Auto|Extend|?'
col ExtbyMb format a6
col ExtbyMb heading 'Ext by|?|Mb'
col FMaxSizeMb format a8
col FMaxSizeMb heading 'File|Max Size|Mb'
break on TSname skip 1

SELECT
  ddf.tablespace_name as "TSname",
  ddf.file_name as "FName",
  ddf.status as "FStatus",
  ROUND(ddf.bytes/1024/1024,2) as "FSizeMb",
  ROUND(SUM(dfs.bytes)/1024/1024,2) as "FileFreeMb",
  ROUND(SUM(dfs.bytes)/SUM(ddf.bytes)*100,0) as "FileFreePrct",
  ddf.autoextensible as "AutoExt",
  CASE 
    WHEN ddf.increment_by = 0 THEN '-' ELSE TO_CHAR(ROUND((ddf.increment_by * dt.block_size)/1024/1024,2))
  END as "ExtbyMb",
  CASE 
    WHEN ddf.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND(ddf.maxbytes/1024/1024,2))
  END as "FMaxSizeMb"
FROM 
  sys.dba_data_files ddf,
  sys.dba_tablespaces dt,
  sys.dba_free_space dfs
WHERE ddf.tablespace_name = dt.tablespace_name
AND ddf.file_id = dfs.file_id(+)
GROUP BY
  ddf.tablespace_name,
  ddf.file_name,
  ddf.status,
  ddf.bytes,
  ddf.autoextensible,
  ddf.increment_by,
  dt.block_size,
  ddf.maxbytes
ORDER BY
  ddf.tablespace_name,7
;

########################################################################################
版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!【QQ交流群：53993419】
QQ：14040928
本文链接： http://blog.itpub.net/26442936/viewspace-1471615/
########################################################################################

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26442936/viewspace-1471615/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26442936/viewspace-1471615/