ORACLE 查看ASM磁盘组信息常用SQL
-- 查看磁盘路径
SQL> select name, path, group_number from v$asm_disk_stat;
-- 查看磁盘组信息
SQL> select state,name,type,total_mb, free_mb from v$asm_diskgroup_stat ;
-- 查看磁盘组操作
SQL> select * from v$asm_operation;
-- 查看磁盘组属性（查询V$ASM_ATTRIBUTE视图查看磁盘组属性）
SQL> 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_STAT dg,V$ASM_ATTRIBUTE a
WHERE dg.name = 'OCR_VOT' AND dg.group_number = a.group_number;
-- 查看磁盘组兼容属性
SQL> SELECT name,COMPATIBILITY,DATABASE_COMPATIBILITY FROM V$ASM_DISKGROUP_STAT;
-- 查看磁盘状态
SQL> SELECT SUBSTR(d.name,1,16) AS asmdisk,d.mount_status,HEADER_STATUS, d.state,dg.name AS diskgroup FROM V$ASM_DISKGROUP_STAT dg, V$ASM_DISK_STAT d WHERE dg.group_number = d.group_number;
-- 查看磁盘客户端信息
SQL> 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_STAT dg, V$ASM_CLIENT c
WHERE dg.group_number = c.group_number;
-- 查看磁盘访问控制信息
SQL> SELECT dg.name AS diskgroup, u.group_number,u.user_number, u.os_id, u.os_name
FROM V$ASM_DISKGROUP_STAT dg, V$ASM_USER u
WHERE dg.group_number = u.group_number AND dg.name ='DATA';
-- 查看磁盘访问控制组信息
SQL> SELECT dg.name AS diskgroup, ug.group_number,ug.owner_number, u.os_name,
ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP_STAT dg, V$ASM_USER u, V$ASM_USERGROUP ug
WHERE dg.group_number = ug.group_number AND dg.name ='DATA'
AND ug.owner_number = u.user_number;
-- 查看数据分布信息
SQL> SELECT dg.name AS diskgroup, f.file_number, f.primary_region,f.mirror_region, f.hot_reads,f.hot_writes, f.cold_reads, f.cold_writes
FROM V$ASM_DISKGROUP_STAT dg, V$ASM_FILE f
WHERE dg.group_number = f.group_number and dg.name ='DATA';