Oracle Query Table Space utilization

SELECT Tablespace_name,

Megs_alloc "Size (m)",

Megs_free "Free (m)",

Megs_used "Used (m)",

Pct_used "Usage (%)",

Max "Max (m)",

Pct_max "Usage pct of max%",

STATUS,

CONTENTS

From (with TB

As (SELECT A.tablespace_name,

ROUND (a.bytes_alloc/1024/1024) Megs_alloc,

ROUND (NVL (b.bytes_free, 0)/1024/1024) Megs_free,

ROUND (

(A.BYTES_ALLOC-NVL (b.bytes_free, 0))

/1024

/1024)

Megs_used,

ROUND (

(NVL (b.bytes_free, 0)/a.bytes_alloc) * 100)

Pct_free,

100

-ROUND (

(NVL (b.bytes_free, 0)/a.bytes_alloc) * 100)

Pct_used,

ROUND (maxbytes/1048576) MAX,

C.status,

C.contents

From (SELECT F.tablespace_name,

SUM (f.bytes) Bytes_alloc,

SUM (

DECODE (F.autoextensible,

' YES ', f.maxbytes,

' NO ', f.bytes))

MaxBytes

From Dba_data_files F

GROUP by Tablespace_name) A,

(SELECT f.tablespace_name, SUM (f.bytes) bytes_free

From Dba_free_space F

GROUP by Tablespace_name) b,

Dba_tablespaces C

WHERE A.tablespace_name = b.tablespace_name (+)

and a.tablespace_name = C.tablespace_name

UNION All

SELECT H.tablespace_name,

ROUND (SUM (h.bytes_free + h.bytes_used)/1048576)

Megs_alloc,

ROUND (

SUM (

(H.bytes_free + h.bytes_used)

-NVL (p.bytes_used, 0))

/1048576)

Megs_free,

ROUND (SUM (NVL (p.bytes_used, 0))/1048576)

Megs_used,

ROUND (

(SUM (

(H.bytes_free + h.bytes_used)

-NVL (p.bytes_used, 0))

/SUM (h.bytes_used + h.bytes_free))

* 100)

Pct_free,

100

-ROUND (

(SUM (

(H.bytes_free + h.bytes_used)

-NVL (p.bytes_used, 0))

/SUM (h.bytes_used + h.bytes_free))

* 100)

Pct_used,

ROUND (

SUM (

DECODE (F.autoextensible,

' YES ', f.maxbytes,

' NO ', f.bytes)

/1048576))

MAX,

C.status,

C.contents

From Sys.gv_$temp_space_header H,

Sys.gv_$temp_extent_pool p,

Dba_temp_files F,

Dba_tablespaces C

WHERE p.file_id (+) = h.file_id

and p.tablespace_name (+) = H.tablespace_name

and f.file_id = h.file_id

and f.tablespace_name = H.tablespace_name

and f.tablespace_name = C.tablespace_name

GROUP by H.tablespace_name, C.status, c.contents

ORDER by 6 DESC)

SELECT Tablespace_name,

Megs_alloc,

Megs_free,

Megs_used,

Pct_free,

Pct_used,

MAX,

ROUND ((Megs_used/max) * Pct_max),

STATUS,

CONTENTS

From TB where pct_used>85 and ROUND ((megs_used/max) * +) >70)

Oracle Query Table Space utilization