ASM Views & Statements

ASM Views & Statements 

• V$ASM_DISK

• V$ASM_DISKGROUP

• V$ASM_DISK_STAT

• V$ASM_DISKGROUP_STAT

• V$ASM_OPERATION

• V$ASM_CLIENT

• V$ASM_FILE

• V$ASM_ALIAS

• V$ASM_TEMPLATE

• V$ASM_VOLUME

• V$ASM_VOLUME_STAT

• V$ASM_ATTRIBUTE

• V$ASM_DISK_IOSTAT

• V$ASM_FILESYSTEM

• V$ASM_ACFSVOLUMES

• V$ASM_ACFSSNAPSHOTS

Disk Group Information

set pages 40000 lines 120

col NAME for a15

select GROUP_NUMBER DG#, name, ALLOCATION_UNIT_SIZE AU_SZ, STATE,

TYPE, TOTAL_MB, FREE_MB, OFFLINE_DISKS from v$asm_diskgroup;

ASM Disk Information

set pages 4000 lines 120

col PATH for a30

select DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,

PATH FROM V$ASM_DISK;

Combined ASM Disk and ASM Diskgroup information

col PATH for a15

col DG_NAME for a15

col DG_STATE for a10

col FAILGROUP for a10

select dg.name dg_name, dg.state dg_state, dg.type, d.disk_number dsk_no,

d.path, d.mount_status, d.FAILGROUP, d.state 

from v$asm_diskgroup dg, v$asm_disk d

where dg.group_number=d.group_number

order by dg_name, dsk_no;

Monitoring ASM disk operations

select GROUP_NUMBER, OPERATION, STATE, ACTUAL, SOFAR, EST_MINUTES 

from v$asm_operation;

Viewing Disk Group Attribute

SELECT dg.name AS diskgroup, SUBSTR(a.name,1,18) AS name, SUBSTR(a.value,1,24) AS value, read_only 

FROM V$ASM_DISKGROUP dg,V$ASM_ATTRIBUTE a 

WHERE dg.name = 'DATA'

AND dg.group_number = a.group_number;

Viewing Disk Group Clients

SELECT dg.name AS diskgroup, SUBSTR(c.instance_name,1,12) AS instance,SUBSTR(c.db_name,1,12) AS dbname, SUBSTR(c.SOFTWARE_VERSION,1,12) AS software,SUBSTR(c.COMPATIBLE_VERSION,1,12) AS compatible 

FROM V$ASM_DISKGROUP dg, V$ASM_CLIENT c  

WHERE dg.group_number = c.group_number;

Viewing Volume and ACFS Information

SELECT dg.name AS diskgroup, v.volume_name, v.volume_device, v.mountpath 

FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME v 

WHERE dg.group_number = v.group_number and dg.name = 'DATA';

SELECT dg.name AS diskgroup, v.volume_name, v.bytes_read, v.bytes_written

FROM V$ASM_DISKGROUP dg, V$ASM_VOLUME_STAT v 

WHERE dg.group_number = v.group_number and dg.name = 'DATA';

SELECT volume_name,size_mb,state,usage,volume_device,mountpath   

FROM v$asm_volume;

SELECT volume_name,reads,writes,read_errs,bytes_read,bytes_written   

FROM v$asm_volume_stat;

SELECT fs_name, vol_device, primary_vol, total_mb, free_mb 

FROM V$ASM_ACFSVOLUMES;

SELECT fs_name, available_time, block_size, state, corrupt 

FROM V$ASM_FILESYSTEM;

select substr(fs_name,1,20) FS,vol_device device,substr (snap_name,1,30) SNAP_NAME,create_time 

from v$asm_acfssnapshots;