db_size.sql

-- ************************************************************************************************************
-- * Filename           : db_size.sql
-- * Author             : Saravanakumar K
-- * Description        : This script with show all database physical file info  
-- * Usage              : @db_size.sql
-- * Version            : v1
-- *************************************************************************************************************
set lines 200 pages 999
COL "Database Total Size" FORMAT a20
COL "Free space" FORMAT a20
COL "Used space" FORMAT a20

SELECT
    name.name,round(SUM(used.bytes) / 1024 / 1024 / 1024)
    || ' GB' "Database Total Size",
    round(SUM(used.bytes) / 1024 / 1024 / 1024) - round(free.p / 1024 / 1024 / 1024)
    || ' GB' "Used space",
    round(free.p / 1024 / 1024 / 1024)
    || ' GB' "Free space"
FROM
    (
        SELECT
            bytes
        FROM
            v$datafile
        UNION ALL
        SELECT
            bytes
        FROM
            v$tempfile
        UNION ALL
        SELECT
            bytes
        FROM
            v$log
    ) used,
    (
        SELECT
            SUM(bytes) AS p
        FROM
            dba_free_space
    ) free,
    (
    select name from v$database) name
GROUP BY
    free.p,name.name
/