Heim >Datenbank >MySQL-Tutorial >从视图获得asm信息。
从视图获得asm信息。
- WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOriginal
- 2016-06-07 15:29:081214Durchsuche
今天是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 磁盘组的信息。见如下神图（摘自网络）：
Stellungnahme：
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel：v$开头的就一定是视图吗？Nächster Artikel：数据库设计中char和long两种类型不要选择