ASM 정보보기 Query 정리

ASM에서 다음과 같은 view 들을 참조하여 정보를 확인할수 있다.

 

V$ASM_ALIAS
V$ASM_CLIENT
V$ASM_DISK
V$ASM_DISK_IOSTAT
V$ASM_DISK_STAT
V$ASM_DISKGROUP
V$ASM_DISKGROUP_STAT
V$ASM_FILE
V$ASM_TEMPLATE
V$ASM_USER
V$ASM_USERGROUP
V$ASM_USERGROUP_MEMBER

 

1. DISK GROUP 속성보기 (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 dg,
     V$ASM_ATTRIBUTE a WHERE dg.name = 'DATA'
     AND dg.group_number = a.group_number;

 

2. DISK GROUP 호환성 보기 (V$ASM_DISKGROUP)

 

SQL> SELECT name AS diskgroup, compatibility AS asm_compat,
     database_compatibility AS db_compat FROM V$ASM_DISKGROUP;

 

3. DISK GROUP 내 디스크 보기 (V$ASM_DISK)

 

SQL> SELECT SUBSTR(d.name,1,16) AS asmdisk, d.mount_status, d.state,
     dg.name AS diskgroup FROM V$ASM_DISKGROUP dg, V$ASM_DISK d
     WHERE dg.group_number = d.group_number;

 

4. DISK GROUP Client 보기 (V$ASM_CLIENT)

 

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 dg, V$ASM_CLIENT c 
    WHERE dg.group_number = c.group_number;

 

5. ASM FILE Access Control 정보 보기 (V$ASM_USER)

 

SQL> SELECT dg.name AS diskgroup, u.group_number, u.user_number, u.os_id, u.os_name
     FROM V$ASM_DISKGROUP dg, V$ASM_USER u
     WHERE dg.group_number = u.group_number AND dg.name = 'DATA';

 

6. ASM FILE Access Control 정보 보기 (V$ASM_USERGROUP)

 

SQL>  SELECT dg.name AS diskgroup, ug.group_number, ug.owner_number, u.os_name,
      ug.usergroup_number, ug.name FROM V$ASM_DISKGROUP 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;

 

7. ASM FILE Access Control 정보 보기 (V$ASM_USERGROUP_MEMBER)

SQL> SELECT dg.name AS diskgroup, um.group_number, um.member_number, u.os_name,
     um.usergroup_number, ug.name FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP_MEMBER um,
     V$ASM_USERGROUP ug WHERE dg.group_number = um.group_number AND
     dg.group_number = ug.group_number AND dg.group_number = u.group_number AND dg.name = 'DATA'
     AND um.member_number = u.user_number AND um.usergroup_number = ug.usergroup_number;

 

8. ASM FILE Access Control 정보 보기 (V$ASM_FILE)

 

SQL> SELECT dg.name AS diskgroup, a.name, f.permissions, f.user_number, u.os_name,
     f.usergroup_number, ug.NAME FROM V$ASM_DISKGROUP dg, V$ASM_USER u, V$ASM_USERGROUP ug,
     V$ASM_FILE f, V$ASM_ALIAS a WHERE dg.group_number = f.group_number AND
     dg.group_number = u.group_number AND dg.group_number = ug.group_number AND
     dg.name = 'FRA' AND f.usergroup_number = ug.usergroup_number AND f.user_number = u.user_number
     AND f.file_number = a.file_number;

 

9. Intelligent Data Placement 정보 (V$ASM_FILE)

 

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 dg, V$ASM_FILE f
       WHERE dg.group_number = f.group_number and dg.name = 'DATA';

 

10. Intelligent Data Placement 정보 (V$ASM_TEMPLATE)

 

SQL> SELECT dg.name AS diskgroup, t.name, t.stripe, t.redundancy, t.primary_region, t.mirror_region
       FROM V$ASM_DISKGROUP dg, V$ASM_TEMPLATE t
       WHERE dg.group_number = t.group_number and dg.name = 'DATA' ORDER BY t.name;