What is the difference between the objecct_id and data_object_id?
Object_id - is the logical iddata_object_id is the segment id
The object_id is the nuber in the object number in the data dictionary. Data_object_id is the number of the real segment that can change over time. this number will be change during the index rebuild, aleter table move, truncat ..
MNISCAK@SODS1 SQL> @o i
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- -------------------- --------------------
MNISCAK I INDEX VALID 283185 283185 06-AUG-2015 12:05:19 06-AUG-2015 12:05:19
1 row selected.
_USER'@'_CONNECT_IDENTIFIER SQL> alter index i rebuild;
Index altered.
MNISCAK@SODS1 SQL> @o i
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- -------------------- --------------------
MNISCAK I INDEX VALID 283185 283186 06-AUG-2015 12:05:19 06-AUG-2015 12:05:37
1 row selected.
Truncate table also changes the data_object_id.. it actualy reinitializes the segment header of the table.
MNISCAK@SODS1 SQL> @o t_lots_segments
no rows selected
_USER'@'_CONNECT_IDENTIFIER SQL> @o t_lots_extents
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- -------------------- --------------------
MNISCAK T_LOTS_EXTENTS TABLE VALID 283183 283183 06-AUG-2015 08:32:20 06-AUG-2015 12:14:08
MNISCAK T_LOTS_EXTENTS_2 TABLE VALID 283184 283184 06-AUG-2015 09:08:54 06-AUG-2015 09:08:54
_USER'@'_CONNECT_IDENTIFIER SQL> truncate table T_LOTS_EXTENTS_2
2 ;
Table truncated.
MNISCAK@SODS1 SQL> @o t_lots_extents
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- -------------------- --------------------
MNISCAK T_LOTS_EXTENTS TABLE VALID 283183 283183 06-AUG-2015 08:32:20 06-AUG-2015 12:14:08
MNISCAK T_LOTS_EXTENTS_2 TABLE VALID 283184 283187 06-AUG-2015 09:08:54 06-AUG-2015 12:14:46
Object no longer exists error:
MNISCAK@SODS1 SQL> create table t as select * from dba_users;
Table created.
MNISCAK@SODS1 SQL> @gts t
Gather Table Statistics for table t...
PL/SQL procedure successfully completed.
MNISCAK@SODS1 SQL> variable c refcursor
MNISCAK@SODS1 SQL> exec open :c for select * from t;
PL/SQL procedure successfully completed.
The refcursor will do the parse, execute and has fetched the first row when opened.
MNISCAK@SODS1 SQL> drop table t;
Table dropped.
Now print the refursor:
MNISCAK@SODS1 SQL> print c
It returns the rows normaly, because the delete commands only removes the table from the dictionary. The segments header remains untouched. the data_object_id si not changed in the segment header.
Now lets check the truncate command:
MNISCAK@SODS1 SQL> create table t as select * from dba_users;
Table created.
MNISCAK@SODS1 SQL> exec open :c for select * from t;
PL/SQL procedure successfully completed.
MNISCAK@SODS1 SQL> truncate table t;
Table truncated.
MNISCAK@SODS1 SQL> print c
ERROR:
ORA-08103: object no longer exists
Here the command failes, because the truncate table resets the segment header. Oracle revisits the segment header whenever the next fetch occures or when he needs the info about the next extent in the extent map.
Another example:
MNISCAK@SODS1 SQL> create table t as select * from dba_objects;
Table created.
Now set the arraysize to 10 so each fetch will return 10 rows
MNISCAK@SODS1 SQL> set arraysize 10 pages 10 pause on
MNISCAK@SODS1 SQL> select object_id from t;
OBJECT_ID
----------
20
46
28
15
29
3
25
In the other session we drop the table and try to reuse the blocks:
SYS@SODS1 SQL> drop table mniscak.t;
Table dropped.
SYS@SODS1 SQL> create table mniscak.big as select * from dba_source;
Table created.
After we hit the enter in the first session again, we got the error:
ERROR:
ORA-08103: object no longer exists
This is because the blocks were allready overwriten by the data in the create table command, so the data_object_id was changed.
The object_id is the nuber in the object number in the data dictionary. Data_object_id is the number of the real segment that can change over time. this number will be change during the index rebuild, aleter table move, truncat ..
MNISCAK@SODS1 SQL> @o i
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- -------------------- --------------------
MNISCAK I INDEX VALID 283185 283185 06-AUG-2015 12:05:19 06-AUG-2015 12:05:19
1 row selected.
_USER'@'_CONNECT_IDENTIFIER SQL> alter index i rebuild;
Index altered.
MNISCAK@SODS1 SQL> @o i
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- -------------------- --------------------
MNISCAK I INDEX VALID 283185 283186 06-AUG-2015 12:05:19 06-AUG-2015 12:05:37
1 row selected.
Truncate table also changes the data_object_id.. it actualy reinitializes the segment header of the table.
MNISCAK@SODS1 SQL> @o t_lots_segments
no rows selected
_USER'@'_CONNECT_IDENTIFIER SQL> @o t_lots_extents
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- -------------------- --------------------
MNISCAK T_LOTS_EXTENTS TABLE VALID 283183 283183 06-AUG-2015 08:32:20 06-AUG-2015 12:14:08
MNISCAK T_LOTS_EXTENTS_2 TABLE VALID 283184 283184 06-AUG-2015 09:08:54 06-AUG-2015 09:08:54
_USER'@'_CONNECT_IDENTIFIER SQL> truncate table T_LOTS_EXTENTS_2
2 ;
Table truncated.
MNISCAK@SODS1 SQL> @o t_lots_extents
owner object_name object_type status OID D_OID CREATED LAST_DDL_TIME
------------------------- ------------------------------ ------------------ --------- ---------- ---------- -------------------- --------------------
MNISCAK T_LOTS_EXTENTS TABLE VALID 283183 283183 06-AUG-2015 08:32:20 06-AUG-2015 12:14:08
MNISCAK T_LOTS_EXTENTS_2 TABLE VALID 283184 283187 06-AUG-2015 09:08:54 06-AUG-2015 12:14:46
Object no longer exists error:
MNISCAK@SODS1 SQL> create table t as select * from dba_users;
Table created.
MNISCAK@SODS1 SQL> @gts t
Gather Table Statistics for table t...
PL/SQL procedure successfully completed.
MNISCAK@SODS1 SQL> variable c refcursor
MNISCAK@SODS1 SQL> exec open :c for select * from t;
PL/SQL procedure successfully completed.
The refcursor will do the parse, execute and has fetched the first row when opened.
MNISCAK@SODS1 SQL> drop table t;
Table dropped.
Now print the refursor:
MNISCAK@SODS1 SQL> print c
It returns the rows normaly, because the delete commands only removes the table from the dictionary. The segments header remains untouched. the data_object_id si not changed in the segment header.
Now lets check the truncate command:
MNISCAK@SODS1 SQL> create table t as select * from dba_users;
Table created.
MNISCAK@SODS1 SQL> exec open :c for select * from t;
PL/SQL procedure successfully completed.
MNISCAK@SODS1 SQL> truncate table t;
Table truncated.
MNISCAK@SODS1 SQL> print c
ERROR:
ORA-08103: object no longer exists
Here the command failes, because the truncate table resets the segment header. Oracle revisits the segment header whenever the next fetch occures or when he needs the info about the next extent in the extent map.
Another example:
MNISCAK@SODS1 SQL> create table t as select * from dba_objects;
Table created.
Now set the arraysize to 10 so each fetch will return 10 rows
MNISCAK@SODS1 SQL> set arraysize 10 pages 10 pause on
MNISCAK@SODS1 SQL> select object_id from t;
OBJECT_ID
----------
20
46
28
15
29
3
25
In the other session we drop the table and try to reuse the blocks:
SYS@SODS1 SQL> drop table mniscak.t;
Table dropped.
SYS@SODS1 SQL> create table mniscak.big as select * from dba_source;
Table created.
After we hit the enter in the first session again, we got the error:
ERROR:
ORA-08103: object no longer exists
This is because the blocks were allready overwriten by the data in the create table command, so the data_object_id was changed.