表空间监控（一） 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：14040928

本文链接： http://blog.itpub.net/26442936/viewspace-1471600/
########################################################################################

Oracle 11g 表空间监控（一） datafile autoextend