db_size_details.sql

-- ************************************************************************************************************
-- * Filename           : db_size_details.sql
-- * Author             : Saravanakumar K
-- * Description        : This script with show all database physical size and average growth in Day and Week  
-- * Usage              : @db_size_details.sql
-- * Version            : v1
-- *************************************************************************************************************
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a15
COL "Growth WEEK" FORMAT a15
COL "Growth DAY in %" FORMAT a18
COL "Growth WEEK in %" FORMAT a20

SELECT
    (
        SELECT
            MIN(creation_time)
        FROM
            v$datafile
    ) "Create Time",
    (
        SELECT
            name
        FROM
            v$database
    ) "Database Name",
    substr(round((SUM(used.bytes) / 1024 / 1024), 2)
           || ' MB', 1, 13) "Database Size",
    substr(round((SUM(used.bytes) / 1024 / 1024) - round(free.p / 1024 / 1024), 2)
           || ' MB', 1, 13) "Used Space",
    substr(round(((SUM(used.bytes) / 1024 / 1024) -(free.p / 1024 / 1024)) / round(SUM(used.bytes) / 1024 / 1024, 2) * 100, 2), 1
    , 13) "Used in %",
    substr(round((free.p / 1024 / 1024), 2)
           || ' MB', 1, 13) "Free Space",
    substr(round(((SUM(used.bytes) / 1024 / 1024) -((SUM(used.bytes) / 1024 / 1024) - round(free.p / 1024 / 1024))) / round(SUM(used
    .bytes) / 1024 / 1024, 2) * 100, 2), 1, 13) "Free in %",
    substr(round(((SUM(used.bytes) / 1024 / 1024) -(free.p / 1024 / 1024)) /(
        SELECT
            sysdate - MIN(creation_time)
        FROM
            v$datafile
    ), 2)
           || ' MB', 1, 13) "Growth DAY",
    substr(round(((SUM(used.bytes) / 1024 / 1024) -(free.p / 1024 / 1024)) /(
        SELECT
            sysdate - MIN(creation_time)
        FROM
            v$datafile
    ) / round((SUM(used.bytes) / 1024 / 1024), 2) * 100, 3), 1, 20) "Growth DAY in %",
    substr(round(((SUM(used.bytes) / 1024 / 1024) -(free.p / 1024 / 1024)) /(
        SELECT
            sysdate - MIN(creation_time)
        FROM
            v$datafile
    ) * 7, 2)
           || ' MB', 1, 15) "Growth WEEK",
    substr(round((((SUM(used.bytes) / 1024 / 1024) -(free.p / 1024 / 1024)) /(
        SELECT
            sysdate - MIN(creation_time)
        FROM
            v$datafile
    ) / round((SUM(used.bytes) / 1024 / 1024), 2) * 100) * 7, 3), 1, 20) "Growth WEEK in %"
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
GROUP BY
    free.p;