今天是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_filename type value------------------------------------ ----------- ------------------------------db_create_file_dest string +datagroup1sql>
查看数据文件和区间对应关系：
内部视图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 a60sql> col name for a40sql> 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/asmdisk3datagroup1 325 0 m /dev/oracleasm/disks/asmdisk1datagroup1 325 1 p /dev/oracleasm/disks/asmdisk2datagroup1 325 1 m /dev/oracleasm/disks/asmdisk1datagroup1 325 2 p /dev/oracleasm/disks/asmdisk4datagroup1 325 2 m /dev/oracleasm/disks/asmdisk1datagroup1 325 3 p /dev/oracleasm/disks/asmdisk1datagroup1 325 3 m /dev/oracleasm/disks/asmdisk4datagroup1 325 4 p /dev/oracleasm/disks/asmdisk3datagroup1 325 4 m /dev/oracleasm/disks/asmdisk2datagroup1 325 5 p /dev/oracleasm/disks/asmdisk2name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 5 m /dev/oracleasm/disks/asmdisk4datagroup1 325 6 p /dev/oracleasm/disks/asmdisk4datagroup1 325 6 m /dev/oracleasm/disks/asmdisk2datagroup1 325 7 p /dev/oracleasm/disks/asmdisk1datagroup1 325 7 m /dev/oracleasm/disks/asmdisk2datagroup1 325 8 p /dev/oracleasm/disks/asmdisk3datagroup1 325 8 m /dev/oracleasm/disks/asmdisk4datagroup1 325 9 p /dev/oracleasm/disks/asmdisk2datagroup1 325 9 m /dev/oracleasm/disks/asmdisk3datagroup1 325 10 p /dev/oracleasm/disks/asmdisk4datagroup1 325 10 m /dev/oracleasm/disks/asmdisk3name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 11 p /dev/oracleasm/disks/asmdisk1datagroup1 325 11 m /dev/oracleasm/disks/asmdisk3datagroup1 325 12 p /dev/oracleasm/disks/asmdisk3datagroup1 325 12 m /dev/oracleasm/disks/asmdisk1datagroup1 325 13 p /dev/oracleasm/disks/asmdisk2datagroup1 325 13 m /dev/oracleasm/disks/asmdisk1datagroup1 325 14 p /dev/oracleasm/disks/asmdisk4datagroup1 325 14 m /dev/oracleasm/disks/asmdisk1datagroup1 325 15 p /dev/oracleasm/disks/asmdisk1datagroup1 325 15 m /dev/oracleasm/disks/asmdisk4datagroup1 325 16 p /dev/oracleasm/disks/asmdisk3name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 16 m /dev/oracleasm/disks/asmdisk2datagroup1 325 17 p /dev/oracleasm/disks/asmdisk2datagroup1 325 17 m /dev/oracleasm/disks/asmdisk4datagroup1 325 18 p /dev/oracleasm/disks/asmdisk4datagroup1 325 18 m /dev/oracleasm/disks/asmdisk2datagroup1 325 19 p /dev/oracleasm/disks/asmdisk1datagroup1 325 19 m /dev/oracleasm/disks/asmdisk2datagroup1 325 20 p /dev/oracleasm/disks/asmdisk3datagroup1 325 20 m /dev/oracleasm/disks/asmdisk4datagroup1 325 21 p /dev/oracleasm/disks/asmdisk2datagroup1 325 21 m /dev/oracleasm/disks/asmdisk3name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 22 p /dev/oracleasm/disks/asmdisk4datagroup1 325 22 m /dev/oracleasm/disks/asmdisk3datagroup1 325 23 p /dev/oracleasm/disks/asmdisk1datagroup1 325 23 m /dev/oracleasm/disks/asmdisk3datagroup1 325 24 p /dev/oracleasm/disks/asmdisk3datagroup1 325 24 m /dev/oracleasm/disks/asmdisk1datagroup1 325 25 p /dev/oracleasm/disks/asmdisk2datagroup1 325 25 m /dev/oracleasm/disks/asmdisk1datagroup1 325 26 p /dev/oracleasm/disks/asmdisk4datagroup1 325 26 m /dev/oracleasm/disks/asmdisk1datagroup1 325 27 p /dev/oracleasm/disks/asmdisk1name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 27 m /dev/oracleasm/disks/asmdisk4datagroup1 325 28 p /dev/oracleasm/disks/asmdisk3datagroup1 325 28 m /dev/oracleasm/disks/asmdisk2datagroup1 325 29 p /dev/oracleasm/disks/asmdisk2datagroup1 325 29 m /dev/oracleasm/disks/asmdisk4datagroup1 325 30 p /dev/oracleasm/disks/asmdisk4datagroup1 325 30 m /dev/oracleasm/disks/asmdisk2datagroup1 325 31 p /dev/oracleasm/disks/asmdisk1datagroup1 325 31 m /dev/oracleasm/disks/asmdisk2datagroup1 325 32 p /dev/oracleasm/disks/asmdisk3datagroup1 325 32 m /dev/oracleasm/disks/asmdisk4name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 33 p /dev/oracleasm/disks/asmdisk2datagroup1 325 33 m /dev/oracleasm/disks/asmdisk3datagroup1 325 34 p /dev/oracleasm/disks/asmdisk4datagroup1 325 34 m /dev/oracleasm/disks/asmdisk3datagroup1 325 35 p /dev/oracleasm/disks/asmdisk1datagroup1 325 35 m /dev/oracleasm/disks/asmdisk3datagroup1 325 36 p /dev/oracleasm/disks/asmdisk3datagroup1 325 36 m /dev/oracleasm/disks/asmdisk1datagroup1 325 37 p /dev/oracleasm/disks/asmdisk2datagroup1 325 37 m /dev/oracleasm/disks/asmdisk1datagroup1 325 38 p /dev/oracleasm/disks/asmdisk4name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 38 m /dev/oracleasm/disks/asmdisk1datagroup1 325 39 p /dev/oracleasm/disks/asmdisk1datagroup1 325 39 m /dev/oracleasm/disks/asmdisk4datagroup1 325 40 p /dev/oracleasm/disks/asmdisk3datagroup1 325 40 m /dev/oracleasm/disks/asmdisk2datagroup1 325 41 p /dev/oracleasm/disks/asmdisk2datagroup1 325 41 m /dev/oracleasm/disks/asmdisk4datagroup1 325 42 p /dev/oracleasm/disks/asmdisk4datagroup1 325 42 m /dev/oracleasm/disks/asmdisk2datagroup1 325 43 p /dev/oracleasm/disks/asmdisk1datagroup1 325 43 m /dev/oracleasm/disks/asmdisk2name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 44 p /dev/oracleasm/disks/asmdisk3datagroup1 325 44 m /dev/oracleasm/disks/asmdisk4datagroup1 325 45 p /dev/oracleasm/disks/asmdisk2datagroup1 325 45 m /dev/oracleasm/disks/asmdisk3datagroup1 325 46 p /dev/oracleasm/disks/asmdisk4datagroup1 325 46 m /dev/oracleasm/disks/asmdisk3datagroup1 325 47 p /dev/oracleasm/disks/asmdisk1datagroup1 325 47 m /dev/oracleasm/disks/asmdisk3datagroup1 325 48 p /dev/oracleasm/disks/asmdisk3datagroup1 325 48 m /dev/oracleasm/disks/asmdisk1datagroup1 325 49 p /dev/oracleasm/disks/asmdisk2name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 49 m /dev/oracleasm/disks/asmdisk1datagroup1 325 50 p /dev/oracleasm/disks/asmdisk4datagroup1 325 50 m /dev/oracleasm/disks/asmdisk1datagroup1 325 51 p /dev/oracleasm/disks/asmdisk1datagroup1 325 51 m /dev/oracleasm/disks/asmdisk4datagroup1 325 52 p /dev/oracleasm/disks/asmdisk3datagroup1 325 52 m /dev/oracleasm/disks/asmdisk2datagroup1 325 53 p /dev/oracleasm/disks/asmdisk2datagroup1 325 53 m /dev/oracleasm/disks/asmdisk4datagroup1 325 54 p /dev/oracleasm/disks/asmdisk4datagroup1 325 54 m /dev/oracleasm/disks/asmdisk2name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 55 p /dev/oracleasm/disks/asmdisk1datagroup1 325 55 m /dev/oracleasm/disks/asmdisk2datagroup1 325 56 p /dev/oracleasm/disks/asmdisk3datagroup1 325 56 m /dev/oracleasm/disks/asmdisk4datagroup1 325 57 p /dev/oracleasm/disks/asmdisk2datagroup1 325 57 m /dev/oracleasm/disks/asmdisk3datagroup1 325 58 p /dev/oracleasm/disks/asmdisk4datagroup1 325 58 m /dev/oracleasm/disks/asmdisk3datagroup1 325 59 p /dev/oracleasm/disks/asmdisk1datagroup1 325 59 m /dev/oracleasm/disks/asmdisk3datagroup1 325 60 p /dev/oracleasm/disks/asmdisk3name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 60 m /dev/oracleasm/disks/asmdisk1datagroup1 325 61 p /dev/oracleasm/disks/asmdisk2datagroup1 325 61 m /dev/oracleasm/disks/asmdisk1datagroup1 325 62 p /dev/oracleasm/disks/asmdisk4datagroup1 325 62 m /dev/oracleasm/disks/asmdisk1datagroup1 325 63 p /dev/oracleasm/disks/asmdisk1datagroup1 325 63 m /dev/oracleasm/disks/asmdisk4datagroup1 325 64 p /dev/oracleasm/disks/asmdisk3datagroup1 325 64 m /dev/oracleasm/disks/asmdisk2datagroup1 325 65 p /dev/oracleasm/disks/asmdisk2datagroup1 325 65 m /dev/oracleasm/disks/asmdisk4name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 66 p /dev/oracleasm/disks/asmdisk4datagroup1 325 66 m /dev/oracleasm/disks/asmdisk2datagroup1 325 67 p /dev/oracleasm/disks/asmdisk1datagroup1 325 67 m /dev/oracleasm/disks/asmdisk2datagroup1 325 68 p /dev/oracleasm/disks/asmdisk3datagroup1 325 68 m /dev/oracleasm/disks/asmdisk4datagroup1 325 69 p /dev/oracleasm/disks/asmdisk2datagroup1 325 69 m /dev/oracleasm/disks/asmdisk3datagroup1 325 70 p /dev/oracleasm/disks/asmdisk4datagroup1 325 70 m /dev/oracleasm/disks/asmdisk3datagroup1 325 71 p /dev/oracleasm/disks/asmdisk1name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 71 m /dev/oracleasm/disks/asmdisk3datagroup1 325 72 p /dev/oracleasm/disks/asmdisk3datagroup1 325 72 m /dev/oracleasm/disks/asmdisk1datagroup1 325 73 p /dev/oracleasm/disks/asmdisk2datagroup1 325 73 m /dev/oracleasm/disks/asmdisk1datagroup1 325 74 p /dev/oracleasm/disks/asmdisk4datagroup1 325 74 m /dev/oracleasm/disks/asmdisk1datagroup1 325 75 p /dev/oracleasm/disks/asmdisk1datagroup1 325 75 m /dev/oracleasm/disks/asmdisk4datagroup1 325 76 p /dev/oracleasm/disks/asmdisk3datagroup1 325 76 m /dev/oracleasm/disks/asmdisk2name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 77 p /dev/oracleasm/disks/asmdisk2datagroup1 325 77 m /dev/oracleasm/disks/asmdisk4datagroup1 325 78 p /dev/oracleasm/disks/asmdisk4datagroup1 325 78 m /dev/oracleasm/disks/asmdisk2datagroup1 325 79 p /dev/oracleasm/disks/asmdisk1datagroup1 325 79 m /dev/oracleasm/disks/asmdisk2datagroup1 325 80 p /dev/oracleasm/disks/asmdisk3datagroup1 325 80 m /dev/oracleasm/disks/asmdisk4datagroup1 325 81 p /dev/oracleasm/disks/asmdisk2datagroup1 325 81 m /dev/oracleasm/disks/asmdisk3datagroup1 325 82 p /dev/oracleasm/disks/asmdisk4name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 82 m /dev/oracleasm/disks/asmdisk3datagroup1 325 83 p /dev/oracleasm/disks/asmdisk1datagroup1 325 83 m /dev/oracleasm/disks/asmdisk3datagroup1 325 84 p /dev/oracleasm/disks/asmdisk3datagroup1 325 84 m /dev/oracleasm/disks/asmdisk1datagroup1 325 85 p /dev/oracleasm/disks/asmdisk2datagroup1 325 85 m /dev/oracleasm/disks/asmdisk1datagroup1 325 86 p /dev/oracleasm/disks/asmdisk4datagroup1 325 86 m /dev/oracleasm/disks/asmdisk1datagroup1 325 87 p /dev/oracleasm/disks/asmdisk1datagroup1 325 87 m /dev/oracleasm/disks/asmdisk4name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 88 p /dev/oracleasm/disks/asmdisk3datagroup1 325 88 m /dev/oracleasm/disks/asmdisk2datagroup1 325 89 p /dev/oracleasm/disks/asmdisk2datagroup1 325 89 m /dev/oracleasm/disks/asmdisk4datagroup1 325 90 p /dev/oracleasm/disks/asmdisk4datagroup1 325 90 m /dev/oracleasm/disks/asmdisk2datagroup1 325 91 p /dev/oracleasm/disks/asmdisk1datagroup1 325 91 m /dev/oracleasm/disks/asmdisk2datagroup1 325 92 p /dev/oracleasm/disks/asmdisk3datagroup1 325 92 m /dev/oracleasm/disks/asmdisk4datagroup1 325 93 p /dev/oracleasm/disks/asmdisk2name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 93 m /dev/oracleasm/disks/asmdisk3datagroup1 325 94 p /dev/oracleasm/disks/asmdisk4datagroup1 325 94 m /dev/oracleasm/disks/asmdisk3datagroup1 325 95 p /dev/oracleasm/disks/asmdisk1datagroup1 325 95 m /dev/oracleasm/disks/asmdisk3datagroup1 325 96 p /dev/oracleasm/disks/asmdisk3datagroup1 325 96 m /dev/oracleasm/disks/asmdisk1datagroup1 325 97 p /dev/oracleasm/disks/asmdisk2datagroup1 325 97 m /dev/oracleasm/disks/asmdisk1datagroup1 325 98 p /dev/oracleasm/disks/asmdisk4datagroup1 325 98 m /dev/oracleasm/disks/asmdisk1name file number extent number ex path---------------------------------------- ----------- ------------- -- ------------------------------------------------------------datagroup1 325 99 p /dev/oracleasm/disks/asmdisk1datagroup1 325 99 m /dev/oracleasm/disks/asmdisk4datagroup1 325 100 p /dev/oracleasm/disks/asmdisk3datagroup1 325 100 m /dev/oracleasm/disks/asmdisk2datagroup1 325 2147483648 p /dev/oracleasm/disks/asmdisk2datagroup1 325 2147483648 m /dev/oracleasm/disks/asmdisk3datagroup1 325 2147483648 mm /dev/oracleasm/disks/asmdisk4205 rows selected.sql>
可以看到在normal情况下，每个区都是存在镜像的，且默认创建一个 表空间指定的文件为100mb。
2、查看相关视图获得asm 磁盘组的信息。见如下神图（摘自网络）：