Database Reference
In-Depth Information
5 1 263 183508992 1048576 124 PRIMARY SDA11 /dev/oracleasm/disks/SDA11
6 1 263 183508992 1048576 127 PRIMARY SDA6 /dev/oracleasm/disks/SDA6
7 1 263 183508992 1048576 124 PRIMARY SDA9 /dev/oracleasm/disks/SDA9
8 1 263 183508992 1048576 126 PRIMARY SDA10 /dev/oracleasm/disks/SDA10
9 1 263 183508992 1048576 126 PRIMARY SDA5 /dev/oracleasm/disks/SDA5
10 1 263 183508992 1048576 125 PRIMARY SDA11 /dev/oracleasm/disks/SDA11
…
The next step consists of locating the correct database block among all those ASM file
extents. The package
DBMS_ROWID
may be used to retrieve the block within a segment, where
a certain row resides. The block number returned by
DBMS_ROWID.ROWID_BLOCK_NUMBER
must
subsequently be mapped to the correct extent within the ASM file. We will use the row in table
LOCATIONS
, which contains the name of the city of angels, as an example.
SQL> SELECT f.file_name, e.relative_fno AS rel_fno,
e.extent_id, e.block_id AS "1st BLOCK", e.blocks,
dbms_rowid.rowid_block_number(l.ROWID) AS block_id
FROM locations l, dba_extents e, dba_data_files f
WHERE l.city='Los Angeles'
AND f.relative_fno=e.relative_fno
AND e.relative_fno=dbms_rowid.rowid_relative_fno(l.ROWID)
AND dbms_rowid.rowid_block_number(l.ROWID)
BETWEEN e.block_id AND e.block_id + e.blocks -1;
FILE_NAME REL_FNO EXTENT_ID 1st BLOCK BLOCKS BLOCK_ID
------------------------------------ ------- --------- --------- ------ --------
+DG/ten/datafile/users.263.628550085 4 0 21793 8 21798
The row where
LOCATIONS.CITY='Los Angeles'
is in file 4, block 21798 (column
BLOCK_ID
in
the query result). Block 21798 is in extent 0 of the segment, which starts at block 21793. Extent 0 has
a total of 8 blocks. The name of file 4 indicates that it is an ASM file. Next, we need to locate
the ASM allocation unit that contains this database block. The
BLOCK_ID
is the offset from the
beginning of the database file. The following query takes into consideration that the database
block size is 8192 bytes and the ASM allocation unit size is 1048576 bytes. Offsets in a segment
are measured in database blocks (
DB_BLOCK_SIZE
or the tablespace's block size), whereas offsets in
ASM disks are measured in allocation units. The extent in the ASM file, which contains the
block, is thus:
21798
1048576
8192
floor
-------------------------------
=
170
The following query returns the ASM disk and the sought after allocation unit within
the disk:
SQL> SELECT x.xnum_kffxp AS extent, a.group_number AS grp#, a.file_number AS file#,
f.bytes, allocation_unit_size AS au_size, au_kffxp AS au#,
decode(x.lxn_kffxp, 0, 'PRIMARY', 1, 'MIRROR') AS type, d.failgroup, d.path
FROM v$asm_alias a, v$asm_file f, x$kffxp x, v$asm_disk d, v$asm_diskgroup dg
WHERE lower(a.name)=lower('USERS.263.628550085')
AND a.group_number=f.group_number
AND a.file_number=f.file_number
