SET LINES 1000 FEED OFF

COL PATH      FORMAT A50
COL FAILGROUP FORMAT A20
COL LABEL     FORMAT A20

COL GROUP_NUMBER FORMAT 999 HEAD "G#"
COL DISK_NUMBER  FORMAT 999 HEAD "D#"
COL FILE_NUMBER  FORMAT 9999 HEAD "F#"

COL COMPATIBILITY    FORMAT A12
COL DATABASE_COMPATIBILITY    FORMAT A12

COL FILE_NAME    FORMAT A34
COL FILE_TYPE    FORMAT A20 
COL GROUP_NAME   FORMAT A20
COL DISK_NAME    FORMAT A20


PROMPT
PROMPT ===============> ASM DISKGROUPS
SELECT
  GROUP_NUMBER
 ,NAME GROUP_NAME
 ,STATE
 ,TYPE
 ,ROUND(TOTAL_MB/1024) TOTAL_GB
 ,ROUND(FREE_MB/1024) FREE_GB
 ,SECTOR_SIZE
 ,BLOCK_SIZE
 ,ALLOCATION_UNIT_SIZE
FROM V$ASM_DISKGROUP
ORDER BY 1
/

PROMPT
PROMPT ===============> ASM CLIENTS

SELECT 
  dg.name GROUP_NAME
 ,SUBSTR(c.instance_name,1,12) AS instance
 ,rpad(c.db_name,12,' ')  DB_CLIENT
FROM V$ASM_DISKGROUP dg
JOIN V$ASM_CLIENT c ON (dg.group_number = c.group_number)
/

PROMPT 
PROMPT ===============> ASM DISKS
SELECT
  D.GROUP_NUMBER 
 ,D.HEADER_STATUS
 ,( SELECT G.NAME FROM V$ASM_DISKGROUP G WHERE G.GROUP_NUMBER = D.GROUP_NUMBER ) GROUP_NAME
 ,D.DISK_NUMBER  
 ,D.NAME DISK_NAME
 ,D.FAILGROUP
 ,D.PATH
 ,ROUND(D.FREE_MB/1024) USABLE_GB
 ,D.MODE_STATUS
 ,D.STATE
 ,D.READS
 ,D.WRITES
 ,D.READ_TIME
 ,D.WRITE_TIME
 ,D.LABEL
 ,D.REDUNDANCY
 ,D.CREATE_DATE
 ,D.MOUNT_DATE
FROM V$ASM_DISK D
ORDER BY GROUP_NUMBER, DISK_NUMBER
/

PROMPT 
PROMPT ===============> ASM FILES
SELECT
  A.GROUP_NUMBER 
 ,( SELECT G.NAME FROM V$ASM_DISKGROUP G WHERE G.GROUP_NUMBER = A.GROUP_NUMBER ) GROUP_NAME
 ,A.FILE_NUMBER
 ,F.TYPE FILE_TYPE
 ,A.NAME FILE_NAME
 ,ROUND(F.BYTES/1024/1024) SIZE_MB
 ,ROUND(F.SPACE/1024/1024) SPACE_MB
 ,F.BLOCK_SIZE 
 ,F.BLOCKS 
 ,F.REDUNDANCY 
 ,F.STRIPED
 ,F.CREATION_DATE
 ,F.MODIFICATION_DATE
 ,A.ALIAS_INDEX
 ,A.ALIAS_INCARNATION
 ,A.SYSTEM_CREATED
 ,A.ALIAS_DIRECTORY
 ,A.FILE_INCARNATION 
FROM V$ASM_FILE F
JOIN V$ASM_ALIAS A 
ON ( A.GROUP_NUMBER = F.GROUP_NUMBER AND A.FILE_NUMBER = F.FILE_NUMBER AND A.FILE_INCARNATION = F.INCARNATION )
WHERE A.SYSTEM_CREATED = 'Y'
ORDER BY 1, 3
/

PROMPT 
PROMPT ===============> TABLESPACES

REM @DBAFREESPACE %