Oracle Query to check tablespace usage

Oracle Query to check tablespace usage & free details with their percentage 

1.Query:-Return Tablespace usage and free details excluding TEMP Tablespace

break on report

compute sum of FREE_SUM  FREE_MAX on report

/* FREE SPACE */

compute sum of col2 col3 col4  on report

--ttitle 'Free Space by Tablespace'

set lines 200

set pages 2000

set head on

set feed off

col col1    format a25                heading "Tablespace"

col col2    format 999,999,999,999    heading "Bytes(MB)"

col col3    format 999,999,999,999    heading "Used(MB)"

col col4    format 999,999,999,999    heading "Free(MB)"

col col6    format 999,999,999,999    heading "Free MAX(MB)"

col col5    format 999.9              heading "Free(%)"

select

    b.tablespace_name col1,

       b.bytes / 1024 / 1024 col2,

    ((b.bytes - sum(nvl(a.bytes,0)))) / 1024 / 1024 col3,

    (sum(nvl(a.bytes,0))) / 1024 / 1024 col4,

    (sum(nvl(a.bytes,0)) / (b.bytes)) * 100 col5,

    max(a.bytes)/1024 / 1024 col6, count(*)

from sys.dba_free_space a,

     (select  tablespace_name, sum(bytes) bytes

      from sys.dba_data_files

      group by tablespace_name ) b

where a.tablespace_name(+) = b.tablespace_name

group by b.tablespace_name, b.bytes

order by 5 desc;

OUTPUT:-

Tablespace   Bytes(MB)         Used(MB)         Free(MB) Free(%)     Free MAX(MB)   COUNT(*)

-----------  --------- ---------------- ---------------- ------- ---------------- ----------

DYM             16,484                0           16,484   100.0            3,968          7

UNDOTBS1         5,280               33            5,247    99.4            3,907         19
USERS           14,084            1,710           12,374    87.9            3,968         10
SYSTEM           1,370            1,358               12      .9                9          6
SYSAUX             620              616                4      .7                4          2
            ---------- ---------------- ----------------

sum             37,838            3,717           34,121

2.Query:-Return Tablespace usage and free details including TEMP Tablespace.

col tablespace_name format a25 heading 'TableSpace|Name'
col AvailSpace format 9999990.90 heading 'Total Size|(in Mb)'
col FreeSpace format 9999990.90 heading 'FreeSpace |(in Mb)'
col UsedSpace format 9999990.90 heading 'UsedSpace |(in Mb)'
SELECT

        dts.tablespace_name,

        (ddf.bytes / 1024 / 1024) "AvailSpace",
        (ddf.bytes - (dfs.bytes))/1024/1024 "UsedSpace",

        (dfs.bytes / 1024/1024 ) "FreeSpace",

        TO_CHAR(((ddf.bytes - (dfs.bytes)) / ddf.bytes * 100),'990.00') "Used %"

        FROM

        sys.dba_tablespaces dts,

        (select tablespace_name, sum(bytes) bytes

                from dba_data_files group by tablespace_name) ddf,

                        (select tablespace_name, sum(bytes) bytes

                                from dba_free_space group by tablespace_name) dfs

                                WHERE

                                        dts.tablespace_name = ddf.tablespace_name

                                AND dts.tablespace_name = dfs.tablespace_name

                                AND NOT (dts.extent_management like 'LOCAL'

                                AND dts.contents like 'TEMPORARY')

                                UNION ALL

                                SELECT dts.tablespace_name,

                                        (dtf.bytes / 1024 / 1024) "AvailSpace",

                                        (t.bytes)/1024/1024 "UsedSpace",

                                        (dtf.bytes - (t.bytes))/1024/1024 "FreeSpace",

                                        TO_CHAR((t.bytes / dtf.bytes * 100), '990.00') "Used %"

                                FROM

                                        sys.dba_tablespaces dts,

                                        (select tablespace_name, sum(bytes) bytes

                                        from dba_temp_files group by tablespace_name) dtf,

                                                (select tablespace_name, sum(bytes_used) bytes

                                                from v$temp_space_header group by tablespace_name) t

        WHERE

        dts.tablespace_name = dtf.tablespace_name

        AND dts.tablespace_name = t.tablespace_name

        AND dts.extent_management like 'LOCAL'

        AND dts.contents like 'TEMPORARY'

/

OUTPUT:-

TableSpace                 Total Size  UsedSpace   FreeSpace

Name                          (in Mb)     (in Mb)     (in Mb) Used %

------------------------- ----------- ----------- ----------- -------
UNDOTBS1                      5280.00       33.38     5246.63    0.63

SYSAUX                         620.00      615.63        4.38   99.29

DYM                          16484.00        0.19    16483.81    0.00

USERS                        14083.75     1710.00    12373.75   12.14

SYSTEM                        1370.00     1357.75       12.25   99.11

TEMP                            45.00       45.00        0.00  100.00