表空間監控（二）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/，如需轉載，請註明出處，否則將追究法律責任。

表空間監控（二）datafile size detail

相關文章