表空间监控(一) datafile autoextend
导言:监控数据文件自增的表空间脚本。
#######################################################################################
Oracle Environment=>11.2.0.4.0 rac on two nodes
OS Environment=>AIX 6.1 (64bit)
#######################################################################################
SQL> @DFfreePrct.sql
TSpace TSpace Auto TSpace TSpace TSpace TSpace TSpace TSpace TSpace TSpace TSpace TSpace
Name Status DATAFILE Extend Size Used Free Used Free Segmt Extent Maxed MaxSiz Maxed
Used File Space Space Space Space Space Space Used Mb Free
Space ? Mb Mb % % Mb Mb Space Space
% % %
------------------ --------- ---------- ------ --------- --------- --------- ------ ------ --------- --------- ------ ------ ------
HP_DATA_TBS ONLINE 76 Yes 306046 298833 7213 98 2 298822 298821 76 393216 24
HPU_DATA_TBS ONLINE 73 Yes 321599 288444 33155 90 10 288432 288432 73 393216 27
DHP_DATA_TBS ONLINE 63 Yes 42544 41492 1053 98 2 41490 41490 63 65536 37
BMCD_BBD2 ONLINE 52 Yes 35910 34385 1525 96 4 34455 34383 52 65536 48
...
### NOTE ###
TSpace Name : Tablespace name
TSpace Status : Tablespace status
TSpace Size : TableSpace Total allocated size
TSpace Used Space Mb : Tablespace currently used space
TSpace Free Space Mb : Tablespace remaining free space
TSpace Used Space % : Tablespace currently used space in percentage
TSpace Free Space % : Tablespace remaining free space in percentage
TSpace Segmt Space Mb : Tablespace space used by all segments
TSpace Extent Space Mb : Tablespace space used by all extents
Auto Extend File ? : Does the Tablespace use a file that is auto-extendable ?
TSpace MaxSize mb : Tablespace Total allocate-able size, taking into account the possible extension of auto-extendable files. This supposes that the files have enough disk space available to auto-extend.
TSpace Maxed Free Space : Tablespace remaining free space, taking into account the possible extension of auto-extendable files. This supposes that the files have enough disk space available to auto-extend.
##########################################################################################################################################################
##DFfreePrct.sql scripts contents are as follows ##
set lines 150
set pages 10000
clear break
col TSname heading 'TSpace|Name|||'
col TSname format a18
col TSstatus heading 'TSpace|Status|||'
col TSstatus format a9
col TSSizeMb heading 'TSpace|Size|||'
col TSSizeMb format 99999999
col TSUsedMb heading 'TSpace|Used|Space|Mb|'
col TSUsedMb format 99999999
col TSFreeMb heading 'TSpace|Free|Space|Mb|'
col TSFreeMb format 99999999
col TSUsedPrct heading 'TSpace|Used|Space|%|'
col TSUsedPrct format 99999
col TSFreePrct heading 'TSpace|Free|Space|%|'
col TSFreePrct format 99999
col TSSegUsedMb heading 'TSpace|Segmt|Space|Mb|'
col TSSegUsedMb format 99999999
col TSExtUsedMb heading 'TSpace|Extent|Space|Mb|'
col TSExtUsedMb format 99999999
col AutoExtFile heading 'Auto|Extend|File|?|'
col AutoExtFile format a6
col TSMaxSizeMb heading 'TSpace|MaxSize|Mb||'
col TSMaxSizeMb format a6
col TSMaxUsedPrct heading 'TSpace|Maxed|Used|Space|%'
col TSMaxUsedPrct format a6
col TSMaxFreePrct heading 'TSpace|Maxed|Free|Space|%'
col TSMaxFreePrct format a6
col DFfreePrct heading 'DATAFILE|Used|Space|%'
col TSMaxFreePrct format a6
WITH
ts_total_space AS (SELECT
tablespace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks,
SUM(maxbytes) as maxbytes
FROM
dba_data_files
GROUP BY
tablespace_name),
ts_free_space AS (SELECT
ddf.tablespace_name,
NVL(SUM(dfs.bytes),0) as bytes,
NVL(SUM(dfs.blocks),0) as blocks
FROM
dba_data_files ddf,
dba_free_space dfs
WHERE
ddf.file_id = dfs.file_id(+)
GROUP BY
ddf.tablespace_name),
ts_total_segments AS (SELECT
tablespace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks
FROM
dba_segments
GROUP BY
tablespace_name),
ts_total_extents AS (SELECT
tablespace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks
FROM
dba_extents
GROUP BY
tablespace_name),
tts_total_space AS (SELECT
tablespace_name,
SUM(bytes) as bytes,
SUM(blocks) as blocks,
SUM(maxbytes) as maxbytes
FROM
dba_data_files where maxbytes>0
GROUP BY
tablespace_name)
SELECT
dt.tablespace_name as "TSname",
dt.status as "TSstatus",
ROUND((ttsp.bytes-tfs.bytes)/tttsp.maxbytes*100,0) as "DFfreePrct",
CASE
WHEN ttsp.maxbytes = 0 THEN 'No' ELSE 'Yes'
END as "AutoExtFile",
ROUND(ttsp.bytes/1024/1024,0) as "TSSizeMb",
ROUND((ttsp.bytes-tfs.bytes)/1024/1024,0) as "TSUsedMb",
ROUND(tfs.bytes/1024/1024,0) as "TSFreeMb",
ROUND((ttsp.bytes-tfs.bytes)/ttsp.bytes*100,0) as "TSUsedPrct",
ROUND(tfs.bytes/ttsp.bytes*100,0) as "TSFreePrct",
ROUND(ttse.bytes/1024/1024,0) as "TSSegUsedMb",
ROUND(tte.bytes/1024/1024,0) as "TSExtUsedMb",
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.bytes-tfs.bytes)/ttsp.maxbytes*100,0))
END as "TSMaxUsedPrct",
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND(ttsp.maxbytes/1024/1024,0))
END as "TSMaxSizeMb" ,
CASE
WHEN ttsp.maxbytes = 0 THEN '-' ELSE TO_CHAR(ROUND((ttsp.maxbytes-(ttsp.bytes-tfs.bytes))/ttsp.maxbytes*100,0))
END as "TSMaxFreePrct"
FROM
dba_tablespaces dt,
ts_total_space ttsp,
tts_total_space tttsp,
ts_free_space tfs,
ts_total_segments ttse,
ts_total_extents tte
WHERE dt.tablespace_name = ttsp.tablespace_name(+)
AND dt.tablespace_name = tfs.tablespace_name(+)
AND dt.tablespace_name = ttse.tablespace_name(+)
AND dt.tablespace_name = tte.tablespace_name(+)
and dt.tablespace_name = tttsp.tablespace_name(+)
and ROUND((ttsp.bytes-tfs.bytes)/tttsp.maxbytes*100,0)>50
ORDER BY 10 desc
;
########################################################################################
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!【QQ交流群:53993419】
QQ:14040928
本文链接: http://blog.itpub.net/26442936/viewspace-1471600/
########################################################################################
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-1471600/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26442936/viewspace-1471600/