Skip to content

ASM Dictionary Views in Oracle 19c

    ASM Dictionary Views for Viewing disk group attributes with V$ASM_ATTRIBUTE

    Use of the V$ASM_ATTRIBUTE and V$ASM_DISKGROUP views. The COMPATIBLE.ASM value must be set to 11.1 or higher for the disk group to display in the V$ASM_ATTRIBUTE view output. Attributes that are designated as read-only (Y) can only be set during disk group creation.

    SELECT SUBSTR(dg.name,1,12) AS diskgroup, SUBSTR(a.name,1,24) 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
         AND a.name NOT LIKE '%template%';
    
    DISKGROUP    NAME                     VALUE                    READ_ON
    ------------ ------------------------ ------------------------ -------
    DATA         idp.type                 dynamic                  N
    DATA         idp.boundary             auto                     N
    DATA         vam_migration_done       false                    Y
    DATA         scrub_metadata.enabled   TRUE                     N
    DATA         scrub_async_limit        1                        N
    DATA         content_hardcheck.enable FALSE                    N
    DATA         access_control.umask     066                      N
    DATA         access_control.enabled   FALSE                    N
    DATA         cell.sparse_dg           allnonsparse             N
    DATA         cell.smart_scan_capable  FALSE                    N
    DATA         compatible.advm          19.0.0.0.0               N
    DATA         compatible.rdbms         19.0.0.0.0               N
    DATA         compatible.asm           19.0.0.0.0               N
    DATA         appliance._partnering_ty GENERIC                  Y
    DATA         au_size                  1048576                  Y
    DATA         content.check            FALSE                    N
    DATA         content.type             data                     N
    DATA         logical_sector_size      512                      N
    DATA         sector_size              512                      N
    DATA         ate_conversion_done      true                     Y
    DATA         preferred_read.enabled   FALSE                    N
    DATA         thin_provisioned         FALSE                    N
    DATA         failgroup_repair_time    24.0h                    N
    DATA         phys_meta_replicated     true                     Y
    DATA         disk_repair_time         12.0h                    N
    

    Viewing the compatibility of a disk group with V$ASM_DISKGROUP

    This is an example of displaying the compatibility for a disk group with the V$ASM_DISKGROUP view.

    SELECT name AS diskgroup, substr(compatibility,1,12) AS asm_compat,
         substr(database_compatibility,1,12) AS db_compat FROM V$ASM_DISKGROUP;
    
    DISKGROUP                      ASM_COMPAT   DB_COMPAT
    ------------------------------ ------------ ------------
    DATA                           19.0.0.0.0   19.0.0.0.0
    FRA                            19.0.0.0.0   19.0.0.0.0
    

    Viewing disks in disk groups with V$ASM_DISK

    Use of the V$ASM_DISK and V$ASM_DISKGROUP views run on an Oracle ASM instance. The example displays the disks associated with a disk group, plus the mount status, state, and failure group of the disks.

    SELECT SUBSTR(dg.name,1,16) AS diskgroup, SUBSTR(d.name,1,16) AS asmdisk,
         d.mount_status, d.state, SUBSTR(d.failgroup,1,16) AS failgroup 
         FROM V$ASM_DISKGROUP dg, V$ASM_DISK d WHERE dg.group_number = d.group_number;
    
    DISKGROUP        ASMDISK          MOUNT_S STATE    FAILGROUP
    ---------------- ---------------- ------- -------- ----------------
    DATA             DATA_0008        CACHED  NORMAL   DATA_0008
    DATA             DATA_0000        CACHED  NORMAL   DATA_0000
    DATA             DATA_0004        CACHED  NORMAL   DATA_0004
    DATA             DATA_0015        CACHED  NORMAL   DATA_0015
    DATA             DATA_0003        CACHED  NORMAL   DATA_0003
    DATA             DATA_0012        CACHED  NORMAL   DATA_0012
    DATA             DATA_0017        CACHED  NORMAL   DATA_0017
    DATA             DATA_0013        CACHED  NORMAL   DATA_0013
    DATA             DATA_0007        CACHED  NORMAL   DATA_0007
    DATA             DATA_0014        CACHED  NORMAL   DATA_0014
    DATA             DATA_0009        CACHED  NORMAL   DATA_0009
    DATA             DATA_0001        CACHED  NORMAL   DATA_0001
    DATA             DATA_0016        CACHED  NORMAL   DATA_0016
    DATA             DATA_0011        CACHED  NORMAL   DATA_0011
    DATA             DATA_0005        CACHED  NORMAL   DATA_0005
    DATA             DATA_0010        CACHED  NORMAL   DATA_0010
    DATA             DATA_0002        CACHED  NORMAL   DATA_0002
    DATA             DATA_0006        CACHED  NORMAL   DATA_0006
    FRA              FRA_0012         CACHED  NORMAL   FRA_0012
    FRA              FRA_0013         CACHED  NORMAL   FRA_0013
    FRA              FRA_0007         CACHED  NORMAL   FRA_0007
    FRA              FRA_0006         CACHED  NORMAL   FRA_0006
    FRA              FRA_0010         CACHED  NORMAL   FRA_0010
    FRA              FRA_0000         CACHED  NORMAL   FRA_0000
    FRA              FRA_0003         CACHED  NORMAL   FRA_0003
    ...
    

    Viewing disks in disk groups with V$ASM_DISK_STAT

    Use of the V$ASM_DISK_STAT and V$ASM_DISKGROUP_STAT views run on an Oracle ASM instance. The example displays the disks associated with a specific disk group along with the mount status, state, and various read and write statistics.

    SELECT SUBSTR(dgs.name,1,10) AS diskgroup, SUBSTR(ds.name,1,10) AS asmdisk, 
           ds.mount_status, ds.state, ds.reads, ds.writes, ds.read_time, ds.write_time, 
           bytes_read, bytes_written 
           FROM V$ASM_DISKGROUP_STAT dgs, V$ASM_DISK_STAT ds 
           WHERE dgs.group_number = ds.group_number AND dgs.name = 'DATA';
    
    DISKGROUP  ASMDISK    MOUNT_S STATE    READS WRITES READ_TIME WRITE_TIME BYTES_READ BYTES_WRITTEN
    ---------- ---------- ------- ------- ------ ------ --------- ---------- ---------- -------------
    DATA       DATA_0000  CACHED  NORMAL     841  10407  1.212218   3.511977   23818240     178369024
    DATA       DATA_0008  CACHED  NORMAL   26065   1319  1.592524    .297728  436203520      38358528
    DATA       DATA_0010  CACHED  NORMAL     561    868   .794849    .337575   18631680      22584320
    DATA       DATA_0004  CACHED  NORMAL     695  10512  1.282711   3.351801   23240704     177246208
    DATA       DATA_0006  CACHED  NORMAL     484   1642  1.506733     .45724   19857408      30191616
    DATA       DATA_0016  CACHED  NORMAL     583   1028  2.283268    .263629   21012480      17682432
    DATA       DATA_0007  CACHED  NORMAL     724   2316  1.259379    .546318   26017792      42283008
    DATA       DATA_0009  CACHED  NORMAL     537    757  1.146663    .241434   19893248      20633088
    DATA       DATA_0014  CACHED  NORMAL    1049   1464  7.346259    .677313   25378816      27578368
    DATA       DATA_0017  CACHED  NORMAL    1440   1326  1.132886   2.541013   25899008      26537984
    DATA       DATA_0013  CACHED  NORMAL     714   1391  1.527926    .371432   18169856      22814720
    DATA       DATA_0001  CACHED  NORMAL     713    807   .790505    .219565   20406272      28561408
    DATA       DATA_0012  CACHED  NORMAL     617   1206  1.016893    3.60425   24477696      25391104
    DATA       DATA_0003  CACHED  NORMAL   15567  11500  5.642053   3.328861  266956800     183625728
    DATA       DATA_0015  CACHED  NORMAL     642   1357  2.545441    .403455   22179840      24973312
    DATA       DATA_0011  CACHED  NORMAL    7585   1685  1.121678    .359123  135217152      37572608
    DATA       DATA_0005  CACHED  NORMAL     513   1431  1.007476     .47202   26427392      21344256
    DATA       DATA_0002  CACHED  NORMAL   11368   2196  1.209433    .861601  199213056      32090624
    

    Viewing disk group clients with V$ASM_CLIENT

    Use of the V$ASM_CLIENT and V$ASM_DISKGROUP views on an Oracle ASM instance. The example displays disk groups with information about the connected database client instances.

    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;
     
    DISKGROUP                      INSTANCE     DBNAME   SOFTWARE     COMPATIBLE
    ------------------------------ ------------ -------- ------------ ------------
    DATA                           +ASM         +ASM     19.0.0.0.0   19.0.0.0.0
    DATA                           orcl         orcl     19.0.0.0.0   19.0.0.0.0
    DATA                           +ASM         asmvol   19.0.0.0.0   19.0.0.0.0
    FRA                            orcl         orcl     19.0.0.0.0   19.0.0.0.0
    ...

    Viewing Oracle ASM File Access Control Information

    View information about Oracle ASM File Access Control in the columns of the V$ASM_USERV$ASM_USERGROUPV$ASM_USERGROUP_MEMBER, and V$ASM_FILE views.

    Viewing Oracle ASM File Access Control information with V$ASM_USER

    This example shows information about Oracle ASM File Access Control users displayed in the V$ASM_USER views.

    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';
    
    DISKGROUP       GROUP_NUMBER USER_NUMBER OS_ID OS_NAME
    --------------- ------------ ----------- ----- -------
    DATA                       1           1 1001  oracle1
    DATA                       1           2 1002  oracle2
    DATA                       1           3 1003  grid
    

    Viewing File Access Control information with V$ASM_USERGROUP

    This example shows information about Oracle ASM File Access Control user groups displayed in the V$ASM_USERGROUP views.

    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;
    
    DISKGROUP         GROUP_NUMBER OWNER_NUMBER OS_NAME         USERGROUP_NUMBER NAME
    ----------------- ------------ ------------ --------------- ---------------- --------
    DATA                         1            3 grid                           1 asm_data
    

    Viewing File Access Control information with V$ASM_USERGROUP_MEMBER

    This example shows information about Oracle ASM File Access Control user groups and members displayed in the V$ASM_USERGROUP_MEMBER ASM Dictionary Views.

    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;
    
    DISKGROUP       GROUP_NUMBER MEMBER_NUMBER OS_NAME            USERGROUP_NUMBER NAME
    --------------- ------------ ------------- ------------------ ---------------- --------
    DATA                       1             1 oracle1                           1 asm_data
    DATA                       1             2 oracle2                           1 asm_data
    

    Viewing Oracle ASM File Access Control information with V$ASM_FILE

    This example shows information about Oracle ASM File Access Control file permissions displayed in the V$ASM_FILE ASM Dictionary Views.

    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.name = 'FRA' AND dg.group_number = u.group_number AND 
           u.group_number = ug.group_number AND ug.group_number = f.group_number AND 
           f.group_number = a.group_number AND 
           f.usergroup_number = ug.usergroup_number AND f.user_number = u.user_number AND 
           f.file_number = a.file_number;
    
    DISKGROUP NAME                   PERMISSIONS USER_NUMBER OS_NAME USERGROUP_NUMBER NAME
    --------- ---------------------- ----------- ----------- ------- ---------------- -------
    DATA      USERS.259.685366091    rw-r-----             3 grid                   1 asm_fra 
    DATA      TEMP.264.685366227     rw-r-----             3 grid                   1 asm_fra 
    ...

    For More Information check Oracle Help Center:

    ASM Dictionary Views