Tablespace usage script in oracle

Select a.tablespace_name "tbsname", 100-round ((nvl (b.bytesfrozen freegrahy 0) / a.bytes_alloc) * 100recover2) "ratio"

Round (a.bytespromoallocallocker 1024gray 2) "size (M)", round (nvl (b.bytespromoallocallocker 0) / 1024plaza 1024heli2) "free (M)"

Round ((a.bytes_alloc-nvl (b.bytesfolk freegrad0)) / 1024 Universe 1024) "usedM)", to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') "get_time"

From (

Select f.tablespace_name, sum (f.bytes) bytes_alloc, sum (decode (f. Autoextensible)) 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

Where a.tablespace_name = b.tablespace_name

Order by 2

two。

SELECT D. TABLESPACENAMEMagneSpace "SUM_SPACE (M)", BLOCKS

SUM_BLOCKS,SPACE-NVL (FREE_SPACE,0) "USED_SPACE (M)"

ROUND ((1-NVL (FREE_SPACE,0) / SPACE) * 100Pol 2) "USED_RATE (%)", FREE_SPACE

"FREE_SPACE (M)"

FROM

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 / 1024), 2)

SPACE,SUM (BLOCKS) BLOCKS

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) D

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 / 1024), 2) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+)

UNION ALL-if have tempfile

SELECT D. TABLESPACENAMEMagneSpace "SUM_SPACE (M)", BLOCKS SUM_BLOCKS

USED_SPACE "USED_SPACE (M)", ROUND (NVL (USED_SPACE,0) / SPACE*100,2)

"USED_RATE (%)"

NVL (FREE_SPACE,0) "FREE_SPACE (M)"

FROM

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES) / (1024 / 1024), 2)

SPACE,SUM (BLOCKS) BLOCKS

FROM DBA_TEMP_FILES

GROUP BY TABLESPACE_NAME) D

(SELECT TABLESPACE_NAME,ROUND (SUM (BYTES_USED) / (1024 / 1024), 2)

USED_SPACE

ROUND (SUM (BYTES_FREE) / (1024 / 1024), 2) FREE_SPACE

FROM V$TEMP_SPACE_HEADER

GROUP BY TABLESPACE_NAME) F

WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+)