asm.sql

-- ************************************************************************************************************
-- * Filename           : asm.sql
-- * Author             : Saravanakumar K
-- * Description        : This script with show space utilization
-- * Usage              : @asm.sql
-- * Version            : v1
-- *************************************************************************************************************
SELECT
    name,
    round(total_mb/1024) total_gb,
    round(used_mb/1024,2) used_gb,	
    round(free_mb/1024,2) free_gb,
    round(free_mb * 100 / total_mb,2) free_pct,
    round(used_mb * 100 / total_mb,2) used_pct,
    '['
    || rpad(
        nvl(
            rpad(
                '#', round(round(used_mb * 100 / total_mb) / 10), '#'
            ), '_'
        ), 10, '_'
    )
    || ']'
    || lpad(
        to_char(
            round(used_mb * 100 / total_mb)
        ), 3, ' '
    )
    || '%'                          "USED (%)"
FROM
    (
        SELECT
            name,
            round((total_mb - required_mirror_free_mb) /(decode(
                type, 'HIGH', 3, 'NORMAL', 2, 1
            )))                   total_mb,
            ( usable_file_mb )    free_mb,
            round(((total_mb - required_mirror_free_mb) /(decode(
                type, 'HIGH', 3, 'NORMAL', 2, 1
            ))) - usable_file_mb) used_mb
        FROM
            v$asm_diskgroup
    );
clear col