I was recently working on some data block corruptions and I needed to dump some data blocks to verify its contents. I had to brush off a paper I wrote a long time ago which showed how to do this. What follows is a portion of that paper:
To dump a block belonging to a table, you’ll need to know the file number and block number of that block. If you already know the file number and block, then you are all set. If you do not know the file number and block, you can query DBA_EXTENTS for that information. Now that we know which file and blocks hold our table, let’s dump a sample block of the table. This is done as follows:
ORA9I SQL> alter system dump datafile 3 block 10;
System altered.
You can dump a range of blocks with the following command:
ORA9I SQL> alter system dump datafile 3 block min 10 block max 12;
System altered.
Let’s now look at the contents of dumping one block.
Start dump data blocks tsn: 3 file#: 3 minblk 10 maxblk 10
buffer tsn: 3 rdba: 0x00c0000a (3/10)
scn: 0x0000.00046911 seq: 0x02 flg: 0x04 tail: 0x69110602
frmt: 0x02 chkval: 0x579d type: 0x06=trans data
Block header dump: 0x00c0000a
Object id on Block? Y
seg/obj: 0x6d9c csc: 0x00.46911 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0005.02f.0000010c uba: 0x00806f10.00ca.28 C--- 0 scn 0x0000.00046900
0x02 xid: 0x0003.01c.00000101 uba: 0x00800033.0099.04 C--- 0 scn 0x0000.00046906
This is the beginning of the data block dump. The first line tells us that we are dumping file#3, starting at block# 10 (minblk), and finishing with block# 10 (maxblk). Had we dumped more than one data block, these values would represent a range. The relative data block address (rdba) is 0x00c0000a. For more information on the rdba, refer to a later section in this paper. At the end of this line, we can see in parentheses that the rdba corresponds to file# 3, block# 10 (3/10).
The third line describes the SCN of the data block. In our case, the SCN is 0x0000.00046911. The tail of the data block is composed of the last two bytes of the SCN (6911) appended with the type (06) and the sequence (02). If the decomposition of the tail does not match these three values, then the system knows that the block is inconsistent and needs to be recovered. While this tail value shows up at the beginning of the block dump, it is physically stored at the end of the data block.
The block type shows up on the fourth line. Some of the valid types correspond to the following table:
Type Meaning
0x02 undo block
0x06 table or index data block
0x0e undo segment header
0x10 data segment header block
0x17 bitmapped data segment header
The “Object id on Block?” line tells us whether or not this object is in SYS.OBJ$. Since Oracle 6, this should always be “Y”. If you look at the next line, the seg/obj value tells us the segment’s object id (in hex). In our example, this is 0x6d9c. Hex ‘6D9C’ is ‘28060’ in decimal. We can verify that this is our table with the following query:
ORA9I SQL> select owner,object_name from dba_objects
2 where object_id=28060;
OWNER OBJECT_NAME
---------- ------------------------------
PEASLAND EMP
As we had hoped, this is our table.
The csc value is the Cleanout System Change number. This value tells us when block cleanout was performed on this block. Hopefully, it matches the SCN of the data block. The itc value is the Interested Transaction List Count. In our case, there are two transactions interested in this block. Those interested transactions appear at the end of our example. We can see the transaction id (Xid) of those two transactions. Those transaction ids correspond to rollback segments that are used to process our transactions.
In my eBook, there are lots of natural male enhancement accessible available it becomes essential to recognize if they very work. viagra sale uk After oral consumption of viagra online no rx these medicines, the enzyme called phosphodiesterase type five (PDE5) is secreted less. Revealing a company that can provide you such facilities is easier but choosing the best one amongst the available ones is not easier as for this reason you can see many cialis prescription http://raindogscine.com/?attachment_id=69 fanatical ads for different herbal medicines. Although these symptoms are temporary, it vanishes within few hours of buy cialis from canada buying here sleep and stay up late in the night, or suffer from insomnia, then you may face lack of energy all day long.
The flag (flg) is either “-” or “O”, used to indicate if this block is on a freelist. If the block is on a freelist, the flag will be “0”. If it is not on a freelist, then the flag will be “-”. Our block in question is on the freelist.
Well, that was quite a lot of information and we haven’t really looked at too much of the dump. Let’s look at the next section of the data block dump.
data_block_dump
===============
tsiz: 0x1fa0
hsiz: 0x2e
pbl: 0x024d015c
bdba: 0x00c0000a
flag=-------------
ntab=1
nrow=14
frre=9
fsbo=0x2e
fseo=0x1b18
avsp=0x1d8a
tosp=0x1d8a
0xe:pti[0] nrow=14 offs=0
0x12:pri[0] offs=0x1c30
0x14:pri[1] offs=0x1f4f
0x16:pri[2] offs=0x1f24
0x18:pri[3] offs=0x1efb
0x1a:pri[4] offs=0x1ece
0x1c:pri[5] offs=0x1ea5
0x1e:pri[6] offs=0x1e7c
0x20:pri[7] offs=0x1e54
0x22:pri[8] offs=0x1e2e
0x24:pri[9] sfll=13
0x26:pri[10] offs=0x1ca4
0x28:pri[11] offs=0x1cf1
0x2a:pri[12] offs=0x1b18
0x2c:pri[13] sfll=-1
The tsiz value shows us the amount of available room in the block for data. Here, we get ‘1fa0’ which translates to 8,096 bytes of useable room. The rest of our 8,192 byte block is used for overhead such as the block header.
The ntab value shows us how many tables are stored in this block. Unless this block belongs to a cluster, this value will be ‘1’. The nrow value tells us how many rows of data are stored in this block. Our data block has 14 rows of data.
Starting at address ‘0xe’, we get a directory to each row. We can see that the first row (index entry zero) starts at offset address to the block ‘0x1c30’. Each of the blocks rows follows from here. This way, a row can be found really quickly. Remember that a ROWID is basically a pointer to a unique row. In Oracle 8+, the ROWID is of the form O.F.B.R (or objectno,relativefno,blockno,rowno). So when the system quickly points to a particular block in a particular file, the row number points to a slot in this directory. The directory then points to a specific location in the block. This is the start of that row.
Now that we have a roadmap to our data block, let’s look at the remainder of the trace file to see the actual rows of data in the block.
block_row_dump:
tab 0, row 0, @0x1c30
tl: 39 fb: --H-FL-- lb: 0x0 cc: 8
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 3] c2 09 19
col 6: *NULL*
col 7: [ 2] c1 15
The actual row data starts with the phrase “block_row_dump:”. Then a row of data is given. I’ve only shown one row of data here, as the rest is similar. We can see that this row belongs to table ‘0’ (tab) of our cluster. Since there is no cluster in our example, we do not have more than one table so this value will be zero. We can also see that this is row ‘0’ and the address of that row is given. This address should correspond to our roadmap noted above.
The ‘tl’ value gives us the total number of bytes for this row, including any overhead. We can see that this row occupies 39 bytes. The ‘cc’ value gives us a column count. We have eight columns in this row. This can easily be verified by doing a DESCRIBE on the table and counting the columns, or by querying USER_TAB_COLUMNS.
The ‘fb’ value gives us flags about the row. ‘H’ means that we have the head of the row. ‘F’ means that we have the first piece of the row. ‘L’ means we also have the last piece of the row. Since this is the first and last piece of the row, the row is not chained. Since this is also the head of the row, the row has not been migrated.
The rest of the information for the row is the data for each column. For instance, in column 1, we have the following ASCII character codes, “53 4d 49 54 48”. A quick look at an ASCII conversion chart will tell us that these characters are “SMITH”. If you are familiar with the sample EMP table, you will know that SMITH is one of our employees. Notice that column 6 is NULL. Column 4 is the HIREDATE column. This is a DATE datatype. From this block, you can easily verify that the DATE datatype requires seven bytes of storage. Column 0 contains a number. The three bytes here are the representation of that number.
