in Oracle

Obj# and DataObj# in 12c AWR

The 12c AWR Report section on Segment Statistics now reports both Obj# (OBJECT_ID) and DataObj# (DATA_OBJECT_ID). This is useful information when you have (table) objects undergoing TRUNCATEs during the workload.
A TRUNCATE (or a MOVE {or REBUILD for an Index}) causes a reallocation of the data segment and change of the DATA_OBJECT_ID.

Thus :

SQL> show user
USER is "HEMANT"
SQL> create table hkc_t_1 (id_col number);

Table created.

SQL> select object_id, data_object_id
2 from user_objects
3 where object_name = 'HKC_T_1'
4 and object_type = 'TABLE'
5 /

OBJECT_ID DATA_OBJECT_ID
---------- --------------
94422 94422

SQL> insert into hkc_t_1 values (1);

1 row created.

SQL> truncate table hkc_t_1;

Table truncated.

SQL> select object_id, data_object_id
2 from user_objects
3 where object_name = 'HKC_T_1'
4 and object_type = 'TABLE'
5 /

OBJECT_ID DATA_OBJECT_ID
---------- --------------
94422 94423

SQL>

The 12c AWR report shows both the Obj# and DataObj#..

In my lab, I created a workload where two tables were TRUNCATED 100 times in a loop that would insert fresh rows after each TRUNCATE. (Those familiar with Peoplesoft Batch Jobs would know this behaviour)

Some extracts from the AWR show :

Segments by Physical Writes               DB/Inst: NONCDB/NONCDB  Snaps: 53-54
-> Total Physical Writes: 340,305
-> Captured Segments account for 0.7% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

Tablespace
Owner Name
-------------------- ----------
Subobject Obj. Physical
Object Name Name Type Obj# Dataobj# Writes %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT HEMANT
SOURCE_TB_1 TABLE 94220 94220 1,575 .46
HEMANT USERS
LIST_TB_2 TABLE 94219 94231 263 .08
HEMANT USERS
WORKLOAD_LOG TABLE 94221 94221 71 .02
SYS SYSTEM
COL$ TABLE 21 2 46 .01
SYS SYSTEM
SEG$ TABLE 14 8 45 .01
------------------------------------------------------

Segments by Physical Write Requests DB/Inst: NONCDB/NONCDB Snaps: 53-54
-> Total Physical Write Requests: 175,206
-> Captured Segments account for 22.3% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

Tablespace
Owner Name
-------------------- ----------
Subobject Obj. Phys Write
Object Name Name Type Obj# Dataobj# Requests %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT USERS
LIST_TB_1 TABLE 94218 94370 1,086 .62
HEMANT USERS
LIST_TB_1 TABLE 94218 94234 983 .56
HEMANT USERS
LIST_TB_1 TABLE 94218 94228 981 .56
HEMANT USERS
LIST_TB_1 TABLE 94218 94232 971 .55
HEMANT USERS
LIST_TB_1 TABLE 94218 94218 964 .55
------------------------------------------------------

Segments by Table Scans DB/Inst: NONCDB/NONCDB Snaps: 53-54
-> Total Table Scans: 243
-> Captured Segments account for 18.9% of Total
-> When ** MISSING ** occurs, some of the object attributes may not be available

Tablespace
Owner Name
-------------------- ----------
Subobject Obj. Table
Object Name Name Type Obj# Dataobj# Scans %Total
-------------------- ---------- ----- ---------- ---------- ------------ -------
HEMANT USERS
LIST_TB_1 TABLE 94218 94240 1 .41
HEMANT USERS
LIST_TB_1 TABLE 94218 94248 1 .41
HEMANT USERS
LIST_TB_1 TABLE 94218 94388 1 .41
HEMANT USERS
LIST_TB_1 TABLE 94218 94224 1 .41
HEMANT USERS
LIST_TB_1 TABLE 94218 94234 1 .41
------------------------------------------------------

Segments by DB Blocks Changes DB/Inst: NONCDB/NONCDB Snaps: 53-54
-> % of Capture shows % of DB Block Changes for each top segment compared
-> with total DB Block Changes for all segments captured by the Snapshot
-> When ** MISSING ** occurs, some of the object attributes may not be available

Tablespace
Owner Name
-------------------- ----------
Subobject Obj. DB Block % of
Object Name Name Type Obj# Dataobj# Changes Capture
-------------------- ---------- ----- ---------- ---------- ------------ -------
** MISSING ** TEMP
** MISSING: -4001635 MISSING ** UNDEF 4.2550E+09 4218752 10,032 2.89
HEMANT USERS
LIST_TB_2 TABLE 94219 94235 7,616 2.20
HEMANT USERS
LIST_TB_2 TABLE 94219 94231 7,488 2.16
HEMANT USERS
LIST_TB_2 TABLE 94219 94403 7,392 2.13
HEMANT USERS
LIST_TB_1 TABLE 94218 94314 7,360 2.12
------------------------------------------------------

These are the two target tables (with the current DATA_OBJECT_ID after 100 TRUNCATEs each) :

SQL> select object_name, object_id, data_object_id
2 from user_objects
3 where object_name like 'LIST_TB_%'
4 and object_type = 'TABLE'
5 order by 1
6 /

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
LIST_TB_1 94218 94418
LIST_TB_2 94219 94419

SQL>

The fact that the AWR report shows a different Dataobj# from the Obj# indicates that a TRUNCATE may have occurred sometime in the past (The TRUNCATE may have occurred before the beignning of the AWR report window !). Multiple entries for the same table indicate that multiple TRUNCATES occurred within the AWR report window.
Note : Although both tables underwent 100 TRUNCATEs in the workload, AWR reports only 5 occurrences of activity.
.
.
.