从视图获得asm信息。
程序员文章站
2022-05-07 14:43:46
...
今天是2014-03-10，堕落了半个月了，继续回到学习的脚本上来。加油！ 1、当使用asm管理database file，那么在 创建表空间的时候会自动按照默认参数位置进行文件创建，且默认大小 为100MB； 默认创建位置主要受：db_create_file_dest影响： SQL show paramete
今天是2014-03-10，堕落了半个月了，继续回到学习的脚本上来。加油！
1、当使用asm管理database file，那么在 创建表空间的时候会自动按照默认参数位置进行文件创建，且默认大小 为100MB；
默认创建位置主要受：db_create_file_dest影响：
SQL> show parameter db_create_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string +DATAGROUP1 SQL>
查看数据文件和区间对应关系：
内部视图x$kffxp介绍：
x$KFFXP column name meaning
ADD Address
indx row index
inst_id instance identifier(1 for single instance,1..n for RAC)
group_kffxp disk group number;corresponds to v$asm_diskgroup.group_number
number_kffxp file number,corresponds to v$asm_file.file_number
compound_kffxp compound index; corresponds to v$asm_file.compound_index
incarn_kffxp incarnation;corresponds to v$asm_file.incarnation
pxn_kffxp physical extent number extent number per file
xnum_kffxp extent number
lxn_kffxp logical extent number(0=primary,1=mirrored copy)
disk_kffxp disk number;corresponds to v$asm_disk.disk_number
au_kffxp offset within the device in multiples of the allocation unit size (v$asm_diskgroup.allocation_unit_size)
flags_kffxp
SIZE_KFFXP size_kffxp is used such that we account for variable sized extents.
sum(size_kffxp) provides the number of AUs that are on that disk.
eg：
set linesize 200 pagesize 200000
col path for a60
col name for a40
select dg.name,
x.NUMBER_KFFXP "FILE NUMBER",
x.XNUM_KFFXP "EXTENT NUMBER",
decode(x.LXN_KFFXP, 0, 'P', 1, 'M', 'MM') "EXTENT TYPE",
d.path
from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg
where x.GROUP_KFFXP = d.GROUP_NUMBER
and x.DISK_KFFXP = d.DISK_NUMBER
and x.GROUP_KFFXP = dg.GROUP_NUMBER
and x.NUMBER_KFFXP in
(select file_number from v$asm_alias where name like '%TENGFANG%')
order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP;
select a.name fname,
a.file_number fnum,
b.xnum_kffxp extnum,
b.lxn_kffxp lognum,
b.disk_kffxp dnum,
b.au_kffxp
from v$asm_alias a, x$kffxp b
where a.group_number = b.group_kffxp
and a.file_number = b.number_kffxp
and b.lxn_kffxp = 0
and a.name like '%TENGFANG%';
注：必须使用gi用户：
SQL> col path for a60 SQL> col name for a40 SQL> select dg.name, 2 x.NUMBER_KFFXP "FILE NUMBER", 3 x.XNUM_KFFXP "EXTENT NUMBER", 4 decode(x.LXN_KFFXP, 0, 'P', 1, 'M', 'MM') "EXTENT TYPE", 5 d.path 6 from x$kffxp x, v$asm_disk d, v$asm_diskgroup dg 7 where x.GROUP_KFFXP = d.GROUP_NUMBER 8 and x.DISK_KFFXP = d.DISK_NUMBER 9 and x.GROUP_KFFXP = dg.GROUP_NUMBER 10 and x.NUMBER_KFFXP in 11 (select file_number from v$asm_alias where name like '%TENGFANG%') 12 order by x.NUMBER_KFFXP, x.XNUM_KFFXP, x.LXN_KFFXP; NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 0 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 0 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 1 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 1 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 2 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 2 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 3 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 3 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 4 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 4 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 5 P /dev/oracleasm/disks/ASMDISK2 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 5 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 6 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 6 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 7 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 7 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 8 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 8 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 9 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 9 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 10 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 10 M /dev/oracleasm/disks/ASMDISK3 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 11 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 11 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 12 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 12 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 13 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 13 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 14 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 14 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 15 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 15 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 16 P /dev/oracleasm/disks/ASMDISK3 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 16 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 17 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 17 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 18 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 18 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 19 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 19 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 20 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 20 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 21 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 21 M /dev/oracleasm/disks/ASMDISK3 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 22 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 22 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 23 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 23 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 24 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 24 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 25 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 25 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 26 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 26 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 27 P /dev/oracleasm/disks/ASMDISK1 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 27 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 28 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 28 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 29 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 29 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 30 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 30 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 31 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 31 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 32 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 32 M /dev/oracleasm/disks/ASMDISK4 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 33 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 33 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 34 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 34 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 35 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 35 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 36 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 36 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 37 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 37 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 38 P /dev/oracleasm/disks/ASMDISK4 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 38 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 39 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 39 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 40 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 40 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 41 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 41 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 42 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 42 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 43 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 43 M /dev/oracleasm/disks/ASMDISK2 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 44 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 44 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 45 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 45 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 46 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 46 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 47 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 47 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 48 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 48 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 49 P /dev/oracleasm/disks/ASMDISK2 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 49 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 50 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 50 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 51 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 51 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 52 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 52 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 53 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 53 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 54 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 54 M /dev/oracleasm/disks/ASMDISK2 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 55 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 55 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 56 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 56 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 57 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 57 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 58 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 58 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 59 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 59 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 60 P /dev/oracleasm/disks/ASMDISK3 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 60 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 61 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 61 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 62 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 62 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 63 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 63 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 64 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 64 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 65 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 65 M /dev/oracleasm/disks/ASMDISK4 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 66 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 66 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 67 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 67 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 68 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 68 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 69 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 69 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 70 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 70 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 71 P /dev/oracleasm/disks/ASMDISK1 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 71 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 72 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 72 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 73 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 73 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 74 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 74 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 75 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 75 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 76 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 76 M /dev/oracleasm/disks/ASMDISK2 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 77 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 77 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 78 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 78 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 79 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 79 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 80 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 80 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 81 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 81 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 82 P /dev/oracleasm/disks/ASMDISK4 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 82 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 83 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 83 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 84 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 84 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 85 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 85 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 86 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 86 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 87 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 87 M /dev/oracleasm/disks/ASMDISK4 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 88 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 88 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 89 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 89 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 90 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 90 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 91 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 91 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 92 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 92 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 93 P /dev/oracleasm/disks/ASMDISK2 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 93 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 94 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 94 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 95 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 95 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 96 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 96 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 97 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 97 M /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 98 P /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 98 M /dev/oracleasm/disks/ASMDISK1 NAME FILE NUMBER EXTENT NUMBER EX PATH ---------------------------------------- ----------- ------------- -- ------------------------------------------------------------ DATAGROUP1 325 99 P /dev/oracleasm/disks/ASMDISK1 DATAGROUP1 325 99 M /dev/oracleasm/disks/ASMDISK4 DATAGROUP1 325 100 P /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 100 M /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 2147483648 P /dev/oracleasm/disks/ASMDISK2 DATAGROUP1 325 2147483648 M /dev/oracleasm/disks/ASMDISK3 DATAGROUP1 325 2147483648 MM /dev/oracleasm/disks/ASMDISK4 205 rows selected. SQL>
可以看到在normal情况下，每个区都是存在镜像的，且默认创建一个 表空间指定的文件为100Mb。
2、查看相关视图获得asm 磁盘组的信息。见如下神图（摘自网络）：