ASM Useful commands

-- LUN size

SELECT
      NVL(a.name, '[CANDIDATE]')                       disk_group_name
    , b.path                                           disk_file_path
    , b.total_mb                                       total_mb
    , (b.total_mb - b.free_mb)                         used_mb
    , ROUND((1- (b.free_mb / b.total_mb))*100, 2)      pct_used
  FROM
      v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  ORDER BY
     a.name;

-- will show provisioned dasd too


col DISK_FILE_PATH format a20    
SELECT
      NVL(a.name, '[CANDIDATE]')                       disk_group_name
    , b.path                                           disk_file_path
    , b.total_mb                                       total_mb
    , (b.total_mb - b.free_mb)                         used_mb
  FROM
      v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  ORDER BY
     a.name;

-- check provisioned dasd using asmcmd

asmcmd >  lsdsk --candidate -p

   
-- ToTal Utlizatization size

 SELECT
      name                                     group_name
    , type                                     type
    , total_mb                                 total_mb
    , (total_mb - free_mb)                     used_mb
    , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
  FROM
      v$asm_diskgroup
  ORDER BY
     name;

-- find flash area usage

select database_name "Database",
         sum(space)/1024/1024 "Size in MB" FROM (
  SELECT
      CONNECT_BY_ROOT db_name as database_name, space
  FROM
      ( SELECT
            a.parent_index       pindex
          , a.name               db_name
          , a.reference_index    rindex
          , f.bytes              bytes
          , f.space              space
          , f.type               type
        FROM
            v$asm_file f RIGHT OUTER JOIN v$asm_alias a
                         USING (group_number, file_number) where GROUP_NUMBER=2
      )
  WHERE type IS NOT NULL
  START WITH (MOD(pindex, POWER(2, 24))) = 0
      CONNECT BY PRIOR rindex = pindex)
group by rollup(database_name)
order by database_name
/