Data Block Dump in Oracle – part 3 – object_id, data_object_id, block clean up

Data_object_id in the block dump files is different from object_id which can be found in dba_objects. But sometime, they are the same in value. Why?

Block header dump:  0x0041966a
Object id on Block? Y
seg/obj: 0x2dca9  csc: 0×00.16b6bab1  itc: 3  flg: -  typ: 1 – DATA
fsl: 0  fnx: 0×0 ver: 0×01

Every object in Oracle database has an unqiue object_id. If the object is a data object, table/index/lob/their partition/cluster, it will have a data_object_id that is not zero.

When you create a new object, Oracle will assign object_id with the maximum value of two queries.  If it is a data_object, like a table, Oracle will assign the same maximum value.

select max(object_id)+1 from dba_objects
select max(hwmincr)+1 from seg$

Therefore, you can will find object_id having the same value as data_object_id. But data_object_id can be different later, for example, after move or truncate table.

In the example below, moving a table to a different tablespace causes a new data_object_id assigned to the table. Usually, this new value is the max(hwmincr)+1 from seg$ at that point of time.

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

SELECT OBJECT_ID, DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = ‘DUMP_EXAMPLE’;
– 70962 70962

ALTER TABLE DUMP_EXAMPLE MOVE TABLESPACE USERS;

SELECT OBJECT_ID, DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = ‘DUMP_EXAMPLE’;
– 70962 70963

Here is a question regarding block clean up. Would Oracle erases the data in the old location after moves it to a new tablespace? The answer is Not immediately. Here is an example.

I create a table, dump its data block; then I move the table to another tablespace, and then dump data block at the new location as well as the data block at the old location.

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

SELECT OBJECT_ID, DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = ‘DUMP_EXAMPLE2′;
–70969 70969

INSERT INTO DUMP_EXAMPLE2 VALUES(‘ABCDEFGHIJK’,1,SYSTIMESTAMP);
INSERT INTO DUMP_EXAMPLE2 VALUES(‘ABCDEFGHIJK’,2,SYSTIMESTAMP+1);
INSERT INTO DUMP_EXAMPLE2 VALUES(‘ABCDEFGHIJK’,3,SYSTIMESTAMP+2);
COMMIT;

SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID), DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) FROM DUMP_EXAMPLE2;
1 86610
1 86610
1 86610

ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86610;

See dump trace file here.
——————————————————————————–
buffer tsn: 0 rdba: 0×00415252 (1/86610)
scn: 0×0000.000ef52f seq: 0×07 flg: 0×02 tail: 0xf52f0607
frmt: 0×02 chkval: 0×0000 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x1C4E6000 to 0x1C4E8000
1C4E6000 0000A206 00415252 000EF52F 02070000  [....RRA./.......]
1C4E6010 00000000 00000001 00011539 000EF528  [........9...(...]
1C4E6020 00000000 00030002 00000000 00050007  [................]
1C4E6030 00000284 00C0058A 00200175 00002003  [........u. .. ..]
1C4E6040 000EF52F 00000000 00000000 00000000  [/...............]
1C4E6050 00000000 00000000 00000000 00030100  [................]
1C4E6060 0018FFFF 1F361F4E 00001F36 1F820003  [....N.6.6.......]
1C4E6070 1F4E1F68 00000000 00000000 00000000  [h.N.............]
1C4E6080 00000000 00000000 00000000 00000000  [................]
Repeat 250 times
1C4E7030 00000000 00010000 00010001 00000001  [................]
1C4E7040 00000000 00000001 00415252 00415252  [........RRA.RRA.]
1C4E7050 00000000 00000000 00000000 00000000  [................]
Repeat 244 times
1C4E7FA0 00000000 00000000 012C0000 42410B03  [..........,...AB]
1C4E7FB0 46454443 4A494847 04C1024B 076D7807  [CDEFGHIJK....xm.]
1C4E7FC0 0B13100B 0B03012C 44434241 48474645  [....,...ABCDEFGH]
1C4E7FD0 024B4A49 780703C1 100A076D 012C0B13  [IJK....xm.....,.]
1C4E7FE0 42410B03 46454443 4A494847 02C1024B  [..ABCDEFGHIJK...]
1C4E7FF0 076D780B 0B131009 C0DB6C29 F52F0607  [.xm.....)l..../.]
Block header dump:  0×00415252
Object id on Block? Y
seg/obj: 0×11539 csc: 0×00.ef528  itc: 2  flg: O  typ: 1 – DATA
fsl: 0  fnx: 0×0 ver: 0×01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0007.005.00000284  0x00c0058a.0175.20  –U-    3  fsc 0×0000.000ef52f
0×02   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
——————————————————————————–

ALTER TABLE DUMP_EXAMPLE2 MOVE TABLESPACE USERS;

You can see that a new data_object_id is assigned, while object_id stays the same. The new data_object_id matches to the new location recorded as sys.seg$

SELECT OBJECT_ID, DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = ‘DUMP_EXAMPLE2′;
– 70969 70970

SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) ,  DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
FROM DUMP_EXAMPLE2;
4 420
4 420
4 420

ALTER SYSTEM DUMP DATAFILE 1 BLOCK 86610;

Dump the block at the old location.
——————————————————————————–
buffer tsn: 0 rdba: 0×00415252 (1/86610)
scn: 0×0000.000ef52f seq: 0×07 flg: 0×06 tail: 0xf52f0607
frmt: 0×02 chkval: 0xbad1 type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0C558200 to 0x0C55A200
C558200 0000A206 00415252 000EF52F 06070000  [....RRA./.......]
C558210 0000BAD1 00000001 00011539 000EF528  [........9...(...]
C558220 00000000 00030002 00000000 00050007  [................]
C558230 00000284 00C0058A 00200175 00002003  [........u. .. ..]
C558240 000EF52F 00000000 00000000 00000000  [/...............]
C558250 00000000 00000000 00000000 00030100  [................]
C558260 0018FFFF 1F361F4E 00001F36 1F820003  [....N.6.6.......]
C558270 1F4E1F68 00000000 00000000 00000000  [h.N.............]
C558280 00000000 00000000 00000000 00000000  [................]
Repeat 250 times
C559230 00000000 00010000 00010001 00000001  [................]
C559240 00000000 00000001 00415252 00415252  [........RRA.RRA.]
C559250 00000000 00000000 00000000 00000000  [................]
Repeat 244 times
C55A1A0 00000000 00000000 012C0000 42410B03  [..........,...AB]
C55A1B0 46454443 4A494847 04C1024B 076D7807  [CDEFGHIJK....xm.]
C55A1C0 0B13100B 0B03012C 44434241 48474645  [....,...ABCDEFGH]
C55A1D0 024B4A49 780703C1 100A076D 012C0B13  [IJK....xm.....,.]
C55A1E0 42410B03 46454443 4A494847 02C1024B  [..ABCDEFGHIJK...]
C55A1F0 076D780B 0B131009 C0DB6C29 F52F0607  [.xm.....)l..../.]
Block header dump:  0×00415252
Object id on Block? Y
seg/obj: 0×11539 csc: 0×00.ef528  itc: 2  flg: O  typ: 1 – DATA
fsl: 0  fnx: 0×0 ver: 0×01

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0×01   0×0007.005.00000284  0x00c0058a.0175.20  –U-    3  fsc 0×0000.000ef52f
0×02   0×0000.000.00000000  0×00000000.0000.00  —-    0  fsc 0×0000.00000000
——————————————————————————–

ALTER SYSTEM DUMP DATAFILE 4 BLOCK 420;

——————————————————————————–
buffer tsn: 4 rdba: 0x010001a4 (4/420)
scn: 0×0000.000ef540 seq: 0×02 flg: 0×04 tail: 0xf5400602
frmt: 0×02 chkval: 0x3a1f type: 0×06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0C558200 to 0x0C55A200
C558200 0000A206 010001A4 000EF540 04020000  [........@.......]
C558210 00003A1F 00000001 0001153A 000EF53F  [.:......:...?...]
C558220 00000000 00320003 010001A1 00130001  [......2.........]
C558230 00000294 00000000 00000000 00000000  [................]
C558240 00000000 00000000 00000000 00000000  [................]
Repeat 2 times
C558270 00000000 00000000 00000000 00030100  [................]
C558280 0018FFFF 1F161F2E 00001F16 1F620003  [..............b.]
C558290 1F2E1F48 00000000 00000000 00000000  [H...............]
C5582A0 00000000 00000000 00000000 00000000  [................]
Repeat 495 times
C55A1A0 00000000 00000000 002C0000 42410B03  [..........,...AB]
C55A1B0 46454443 4A494847 04C1024B 076D7807  [CDEFGHIJK....xm.]
C55A1C0 0B13100B 0B03002C 44434241 48474645  [....,...ABCDEFGH]
C55A1D0 024B4A49 780703C1 100A076D 002C0B13  [IJK....xm.....,.]
C55A1E0 42410B03 46454443 4A494847 02C1024B  [..ABCDEFGHIJK...]
C55A1F0 076D780B 0B131009 C0DB6C29 F5400602  [.xm.....)l....@.]
Block header dump:  0x010001a4
Object id on Block? Y
seg/obj: 0x1153a csc: 0×00.ef53f  itc: 3  flg: E  typ: 1 – DATA
brn: 0  bdba: 0x10001a1 ver: 0×01 opc: 0
inc: 0  exflg: 0
——————————————————————————–

As you can see, when you move a table, the content of blocks in the table is copied to the new location, with a new data_object_id flag. Oracle updates the data directionary seg$ so that the object is associated to the new location. The content at the old location is NOT erased immediately. This mechanism is known as “delayed block clean up“.

Delayed block clean up also applies to “truncate”. When you truncate a table, Oracle just allocates new extents and points this new location. The old data is left intact at the old location. That is one of reasons that truncate is fast. It also indicates a way to peek into history data even the tables is already truncated.

About these ads