Home > Workload Solutions > Oracle > White Papers > Oracle ASM on ScaleIO Best Practices > FILE
Table 22 includes compatibility information for the file.sql script.
Table 22. Script details—file.sql
Type of information |
Details |
Name |
file.sql |
Compatibility |
10g, 11g, 12c |
Purpose |
Shows each file known to ASM. |
Sample output:
FN DISKGROUP_NAME BLK_SZ STR_SZ SIZE FILE_TYPE FILE_NAME
--- -------------- ------ ------ ------ ---------------- -------------------------
253 CRS 512 1M 2K ASMPARAMETERFILE PHL-ORA-cluster/ASMPARAMETERFILE/REGISTRY.
255 CRS 4096 1M 260M OCRFILE PHL-ORA-cluster/OCRFILE/REGISTRY.255.77896
256 DATA 8192 1M 680M DATAFILE PHLORA/DATAFILE/SYSTEM.256.779189873
256 FRA 16384 128K 18M CONTROLFILE PHLORA/CONTROLFILE/Current.256.779190159
256 REDO 16384 128K 18M CONTROLFILE PHLORA/CONTROLFILE/Current.256.779190159
257 DATA 8192 1M 720M DATAFILE PHLORA/DATAFILE/SYSAUX.257.779189875
257 FRA 512 1M 50M ONLINELOG PHLORA/ONLINELOG/group_1.257.779190177
257 REDO 512 1M 50M ONLINELOG PHLORA/ONLINELOG/group_1.257.779190177
258 DATA 8192 1M 45M DATAFILE PHLORA/DATAFILE/UNDOTBS1.258.779189877
258 FRA 512 1M 50M ONLINELOG PHLORA/ONLINELOG/group_2.258.779190179
258 REDO 512 1M 50M ONLINELOG PHLORA/ONLINELOG/group_2.258.779190177
259 DATA 8192 1M 1G DATAFILE PHLORA/DATAFILE/USERS.259.779189877
259 FRA 512 1M 50M ONLINELOG PHLORA/ONLINELOG/group_3.259.779190507
259 REDO 512 1M 50M ONLINELOG PHLORA/ONLINELOG/group_3.259.779190505
260 DATA 8192 1M 20M TEMPFILE PHLORA/TEMPFILE/TEMP.260.779190213
260 FRA 512 1M 50M ONLINELOG PHLORA/ONLINELOG/group_4.260.779190507
260 REDO 512 1M 50M ONLINELOG PHLORA/ONLINELOG/group_4.260.779190507
261 DATA 8192 1M 100M DATAFILE PHLORA/DATAFILE/EXAMPLE.261.779190219
262 DATA 8192 1M 400M DATAFILE PHLORA/DATAFILE/UNDOTBS2.262.779190451
263 DATA 512 1M 5K PARAMETERFILE PHLORA/PARAMETERFILE/spfile.263.779190507
264 DATA 8192 1M 20M TEMPFILE PHLORA/TEMPFILE/TEMP.264.780252785
Table 23 contains the key to the output.
Table 23. Key to file.sql columns
Column |
Meaning |
GN |
Diskgroup number |
DISKGROUP_NAME |
Diskgroup name |
SEC_SZ |
Sector size in bytes |
BLK_SZ |
Block size in bytes |
AU |
Allocation unit size |
STATE |
State of the diskgroup |
PROT |
RAID Protection Level – Normal, High or External |
TOTAL |
Total size of the diskgroup |
FREE |
Free space on the diskgroup |
Script text:
set linesize 132
set pagesize 999
col gn for 99
col fn for 99999
col blk_sz for 99999
col str_sz for a6
col file_type for a16
col size for a8
col tot for a6
col free for a6
col file_name for a60
col diskgroup_name for a15
select
vaf.file_number "FN",
vad.name "DISKGROUP_NAME",
-- vaf.group_number "GN",
vaf.block_size "BLK_SZ",
decode(vaf.striped,'COARSE',stripe_size.extent,stripe_size.stripsz) "STR_SZ",
decode(floor(vaf.bytes/1099511627776),0,
decode(floor(vaf.bytes/1073741824),0,
decode(floor(vaf.bytes/1048576),0,
to_char(vaf.bytes/1024,'9999')||'K',
to_char(vaf.bytes/1048576,'9999')||'M'
),
to_char(vaf.bytes/1073741824,'9999')||'G'
),
to_char(vaf.bytes/1099511627776,'99.9')||'T'
) "SIZE",
vaf.type "FILE_TYPE",
vaa3.name||'/'||vaa2.name||'/'||vaa1.name "FILE_NAME"
from
v$asm_diskgroup vad,
v$asm_file vaf,
v$asm_alias vaa1,
v$asm_alias vaa2,
v$asm_alias vaa3,
(
select
decode(floor(y1.ksppstvl/1048576),0,
to_char(y1.ksppstvl/1024,'9999')||'K',
to_char(y1.ksppstvl/1048576,'9999')||'M'
) "STRIPSZ",
decode(floor(y2.ksppstvl/1048576),0,
to_char(y2.ksppstvl/1024,'9999')||'K',
to_char(y2.ksppstvl/1048576,'9999')||'M'
) "EXTENT",
y1.ksppstvl,
y2.ksppstvl
from
x$ksppcv y1,
x$ksppi x1,
x$ksppcv y2,
x$ksppi x2
where 1=1
and x1.indx = y1.indx
and x1.ksppinm ='_asm_stripesize'
and x2.indx = y2.indx
and x2.ksppinm ='_asm_ausize'
) stripe_size
where 1=1
and vaf.group_number = vad.group_number
and vaf.group_number = vaa1.group_number
and vaf.file_number = vaa1.file_number
and vaf.incarnation = vaa1.file_incarnation
and vaa1.parent_index = vaa2.reference_index
and vaa2.parent_index = vaa3.reference_index
order by
vaf.file_number,
vaf.group_number
/