Oracle Blocks – Details

we can get the information about which table is present in which datafile.
For that you can seach for file_id in dba_extents using the segment name as your table name. using this file ID we can get the tablespace_name and file_name from dba_data_filesExample:

select file_id, block_id, segment_name from dba_extents
where segment_name = ‘IGI_MPP_AP_INVOICES_I1’

FILE_ID BLOCK_ID
———- ———-
SEGMENT_NAME
——————————————————————————–
29 47593
IGI_MPP_AP_INVOICES_I1
select tablespace_name, file_id, file_name from dba_data_files
where file_id = 29;
TABLESPACE_NAME FILE_ID
—————————— ———-
FILE_NAME
——————————————————————————–
APPS_TS_TX_IDX 29
/dy/oracle/product/test12/db/apps_st/data/a_txn_ind05.dbf

file_id and block number can also be obtained from dba_segments.

SQL> select SEGMENT_NAME, HEADER_FILE , HEADER_BLOCK from dba_segments
2 where SEGMENT_NAME = ‘IGI_MPP_AP_INVOICES_I1’;

SEGMENT_NAME
——————————————————————————–
HEADER_FILE HEADER_BLOCK
———– ————
IGI_MPP_AP_INVOICES_I1
29 47595
you can also get the dump of a perticular oracle block and see the content of that block.
This is basically useful to check if the data block is corrupt.
Use the following command for the same

SQL> alter system dump datafile 29 block 47595;

System altered.

Check in UDUMP area, where we can see the trace file content as given below.

Sample dump will look like the following

==================================================

Dump file /dy/oracle/product/test12/db/tech_st/10.2.0/admin/test12_ap101fam/

udump/test12_ora_24459.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /dy/oracle/product/test12/db/tech_st/10.2.0
System name: SunOS
Node name: ap101fam
Release: 5.9
Version: Generic_117171-14
Machine: sun4u
Instance name: test12
Redo thread mounted by this instance: 1
Oracle process number: 39
Unix process pid: 24459, image: oracle@ap101fam (TNS V1-V3)

*** 2007-05-26 23:31:46.263
*** SERVICE NAME:(SYS$USERS) 2007-05-26 23:31:46.247
*** SESSION ID:(356.597) 2007-05-26 23:31:46.247
Start dump data blocks tsn: 11 file#: 29 minblk 47595 maxblk 47595
buffer tsn: 11 rdba: 0x0740b9eb (29/47595)
scn: 0x0000.00127f0b seq: 0x02 flg: 0x04 tail: 0x7f0b2302
frmt: 0x02 chkval: 0x93b9 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000001064EC000 to 0x00000001064EE000
1064EC000 23020000 0740B9EB 00127F0B 00000204 [#….@……….]
1064EC010 93B90000 00000000 00000000 00000000 […………….]
1064EC020 00000000 00000001 00000010 0A9C0000 […………….]
1064EC030 00000000 00000004 00000010 0740B9ED [………….@..]
1064EC040 00000000 00000000 00000000 00000001 […………….]
1064EC050 00000000 00000000 00000000 00000000 […………….]
1064EC060 00000004 00000010 0740B9ED 00000000 [………@……]
1064EC070 00000000 00000000 00000001 0740B9E9 [………….@..]
1064EC080 0740B9E9 00000000 00000000 00000000 [.@…………..]
1064EC090 00000000 00000000 00000000 00000000 […………….]



Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 1 obj#: 102145 flag: 0x20000000
Inc # 0
Extent Map
—————————————————————–
0x0740b9e9 length: 16

Auxillary Map
——————————————————–
Extent 0 : L1 dba: 0x0740b9e9 Data dba: 0x0740b9ec
——————————————————–

Second Level Bitmap block DBAs
——————————————————–
DBA 1: 0x0740b9ea

End dump data blocks tsn: 11 file#: 29 minblk 47595 maxblk 47595

==================================================

If you see there is a obj# giving in the above dump from a block we can actually see
if this obj# is matches with out object using sys table sys.obj$.

SQL> select name from sys.obj$
2 where obj# = 102145;

NAME
——————————
IGI_MPP_AP_INVOICES_I1

So just using the dump from a datablock we can very well check which database object this data belongs to.

Advertisements