Data Block Dump in Oracle - part 1 - how to dump block

Dump data block provides a backdoor for understanding Oracle RDBMS implementation.

Dump data block can be accomplished by issuing a command below, given you have known DATA FILE NAME and BLOCK ADDRESS in interest.

ALTER SYSTEM DUMP DATAFILE <NUMBER DATAFILE> BLOCK <BLOCK NUMBER>;
ALTER SYSTEM DUMP DATAFILE <NUMBER DATAFILE> BLOCK MIN <FIRST NUMBER> BLOCK MAX <LAST BLOCK>;

1) If you know the object name, e.g. table/index name, you can query dba_objects/dba_segments to find the head block and start from there.

select HEADER_FILE, HEADER_BLOCK FROM DBA_SEGMENTS WHERE SEGMENT_NAME = <SEGMENT name> AND SEGMENT_TYPE = <’TABLE’/'INDEX’/…>

then dump the header to get details of the segment

ALTER SYSTEM DUMP DATAFILE <HEADER_FILE> BLOCK <HEADER_BLOCK>;

2) If you are interested in a specific row of a table, e.g. how Oracle implements certain things, you can find the rowid and then translate rowid into physical address.

SELECT ROWID, COL1,… FROM TABLE WHERE …

Using DBMS_ROWID package to get FILE NUMBER and BLOCK ADDRESS,

SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID),  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FROM DUAL;

Then ALTER SYSTEM DUMP DATAFILE ….

3) If you get a DBA (Data Block Address – here), e.g. from some trace file in udump directory,  you can get FILE NUMBER and BLOCK ADDRESS using

SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(dba_number),
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(dba_number)
FROM DUAL;

then ALTER SYSTEM DUMP DATAFILE …

Here is an example for dumping a data block.

CREATE TABLE DUMP_EXAMPLE (
COL1 VARCHAR2(2000),
COL2 NUMBER,
COL3 TIMESTAMP);

INSERT INTO DUMP_EXAMPLE VALUES(
‘ABCDEFGHIJK’,1,SYSTIMESTAMP);

INSERT INTO DUMP_EXAMPLE VALUES(
‘ABCDEFGHIJK’,2,SYSTIMESTAMP+1);

COMMIT;

SELECT SEGMENT_TYPE, FILE_ID, BLOCK_ID, BLOCKS, RELATIVE_FNO FROM DBA_EXTENTS WHERE SEGMENT_NAME = ‘DUMP_EXAMPLE’;
–TABLE 4 161 8 4

then dump the header using ALTER SYSTEM DUMP DATAFILE 4 BLOCK 161

Now, dump the real data in interest.

SELECT ROWID, COL1, COL2, COL3 FROM DUMP_EXAMPLE
– AAAtyiAAEAAAACkAAA abcdefghijk 1 06.07.2009 18:03:12.177
– AAAtyiAAEAAAACkAAB ABCDEFGHIJK 2 07.07.2009 18:03:12.000

– dump the data block
SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(‘AAAtyiAAEAAAACkAAA’) , DBMS_ROWID.ROWID_BLOCK_NUMBER(‘AAAtyiAAEAAAACkAAA’) FROM DUAL;
– 4, 164

SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(‘AAAtyiAAEAAAACkAAB’) ,  DBMS_ROWID.ROWID_BLOCK_NUMBER(‘AAAtyiAAEAAAACkAAB’) FROM DUAL;
– 4, 164
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 164;

You can find an example of dumping data blocks after knowing a Data Block Address from the post “Index Block Dump in Oracle”

About these ads