REM ASM views:

REM VIEW |ASM INSTANCE |DB INSTANCE

REM ----------------------------------------------------------------------------------------------------------

REM V$ASM_DISKGROUP |Describes a disk group (number, name, size |Contains one row for every open ASM

REM |related info, state, and redundancy type) |disk in the DB instance.

REM V$ASM_CLIENT |Identifies databases using disk groups |Contains no rows.

REM |managed by the ASM instance. |

REM V$ASM_DISK |Contains one row for every disk discovered |Contains rows only for disks in the

REM |by the ASM instance, including disks that |disk groups in use by that DB instance.

REM |are not part of any disk group. |

REM V$ASM_FILE |Contains one row for every ASM file in every |Contains rows only for files that are

REM |disk group mounted by the ASM instance. |currently open in the DB instance.

REM V$ASM_TEMPLATE |Contains one row for every template present in |Contains no rows.

REM |every disk group mounted by the ASM instance. |

REM V$ASM_ALIAS |Contains one row for every alias present in |Contains no rows.

REM |every disk group mounted by the ASM instance. |

REM v$ASM_OPERATION |Contains one row for every active ASM long |Contains no rows.

REM |running operation executing in the ASM instance. |

set wrap off

set lines 155 pages 9999

col "Group Name" for a6 Head "Group|Name"

col "Disk Name" for a10

col "State" for a10

col "Type" for a10 Head "Diskgroup|Redundancy"

col "Total GB" for 9,990 Head "Total|GB"

col "Free GB" for 9,990 Head "Free|GB"

col "Imbalance" for 99.9 Head "Percent|Imbalance"

col "Variance" for 99.9 Head "Percent|Disk Size|Variance"

col "MinFree" for 99.9 Head "Minimum|Percent|Free"

col "MaxFree" for 99.9 Head "Maximum|Percent|Free"

col "DiskCnt" for 9999 Head "Disk|Count"

prompt

prompt ASM Disk Groups

prompt ===============

SELECT g.group_number "Group"

, g.name "Group Name"

, g.state "State"

, g.type "Type"

, g.total_mb/1024 "Total GB"

, g.free_mb/1024 "Free GB"

, 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"

, 100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"

, 100*(min(d.free_mb/d.total_mb)) "MinFree"

, 100*(max(d.free_mb/d.total_mb)) "MaxFree"

, count(*) "DiskCnt"

FROM v$asm_disk d, v$asm_diskgroup g

WHERE d.group_number = g.group_number and

d.group_number <> 0 and

d.state = 'NORMAL' and

d.mount_status = 'CACHED'

GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb

ORDER BY 1;

prompt ASM Disks In Use

prompt ================

col "Group" for 999

col "Disk" for 999

col "Header" for a9

col "Mode" for a8

col "State" for a8

col "Created" for a10 Head "Added To|Diskgroup"

col "Path" for a19

col "SecsPerRead" for 9.000 Head "Seconds|PerRead"

col "SecsPerWrite" for 9.000 Head "Seconds|PerWrite"

select group_number "Group"

, disk_number "Disk"

, header_status "Header"

, mode_status "Mode"

, state "State"

, create_date "Created"

, total_mb/1024 "Total GB"

, free_mb/1024 "Free GB"

, name "Disk Name"

, path "Path"

, read_time/reads "SecsPerRead"

, write_time/writes "SecsPerWrite"

from v$asm_disk_stat

where header_status not in ('FORMER','CANDIDATE')

order by group_number

, disk_number

Prompt File Types in Diskgroups

Prompt ========================

col "File Type" for a16

col "Block Size" for a5 Head "Block|Size"

col "Gb" for 9990.00

col "Files" for 99990

break on "Group Name" skip 1 nodup

select g.name "Group Name"

, f.TYPE "File Type"

, f.BLOCK_SIZE/1024||'k' "Block Size"

, f.STRIPED

, count(*) "Files"

, round(sum(f.BYTES)/(1024*1024*1024),2) "Gb"

from v$asm_file f,v$asm_diskgroup g

where f.group_number=g.group_number

group by g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED

order by 1,2;

clear break

prompt Instances currently accessing these diskgroups

prompt ==============================================

col "Instance" form a8

select c.group_number "Group"

, g.name "Group Name"

, c.instance_name "Instance"

from v$asm_client c

, v$asm_diskgroup g

where g.group_number=c.group_number

prompt Free ASM disks and their paths

prompt ==============================

col "Disk Size" form a9

select header_status "Header"

, mode_status "Mode"

, path "Path"

, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"

from v$asm_disk

where header_status in ('FORMER','CANDIDATE')

order by path

prompt Current ASM disk operations

prompt ===========================

select *

from v$asm_operation

/