object_id and data_object_id

I have tried DBMS_REDEFINITION package to move a table from one tablespace to another tablespace to see if it changes the data_object_id.

The object_id and data_object_id both changed to object_id and data_object_id of intermediate table.

I have enabled 10053 trace to see what actually hapening.

It is using INSERT INTO .. SELECT statement to move data form original table to intermediate table.

After that looks like it is renaming the table.

scott@test> CREATE TABLE t1 (a INTEGER) tablespace tools;

Table created.

scott@test> SELECT object_name,object_id, data_object_id FROM user_objects WHERE object_name = ‘T1’;

OBJEC  OBJECT_ID DATA_OBJECT_ID
—– ———- ————–
T1      35045782       35045782

scott@test>col object_name    for a25
scott@test>col SEGMENT_NAME     for a35
scott@test>col TABLESPACE_NAME  for a25

scott@test> select segment_name,tablespace_name from user_segments where segment_name=’T1′;

SEGMENT_NAME                        TABLESPACE_NAME
———————————– ————————-
T1                                  TOOLS

scott@test> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE(uname=>’scott’,tname=>’T1′,options_flag=>dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

scott@test> CREATE TABLE scott.T2 TABLESPACE USERS AS SELECT * FROM T1 WHERE 1=2;

Table created.

scott@test> SELECT object_name,object_id, data_object_id FROM user_objects WHERE object_name = ‘T2’;

OBJECT_NAME                OBJECT_ID DATA_OBJECT_ID
————————- ———- ————–
T2                          35045783       35045783

scott@test> select segment_name,tablespace_name from user_segments where segment_name=’T2′;

SEGMENT_NAME                        TABLESPACE_NAME
———————————– ————————-
T2                                  USERS
scott@test>alter session set events ‘10053 trace name context forever, level 12’;
scott@test> EXEC DBMS_REDEFINITION.START_REDEF_TABLE(uname=>’scott’,orig_table=>’T1′,int_table=>’T2′,options_flag=>dbms_redefinition.cons_use_rowid);

PL/SQL procedure successfully completed.

scott@test> scott@test> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>’scott’, orig_table=>’T1′, int_table=>’T2′);

PL/SQL procedure successfully completed.

scott@test> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname=>’scott’, orig_table=>’T1′, int_table=>’T2′);

PL/SQL procedure successfully completed.

scott@test>alter session set events ‘10053 trace name context off’;

scott@test> SELECT object_name,object_id, data_object_id FROM user_objects WHERE object_name in (‘T1′,’T2’);

 
OBJECT_NAME                 OBJECT_ID DATA_OBJECT_ID
————————-  ———- ————–
T1                           35045783       35045783
T2                           35045782       35045782

scott@test>

scott@test> select segment_name,tablespace_name from user_segments where segment_name in (‘T1′,’T2’);
SEGMENT_NAME                        TABLESPACE_NAME
———————————– ————————-
T1                                  USERS
T2                                  TOOLS
trace entry is as follows.

sql_id=c65p9bu3a32qm.
Current SQL statement for this session:
INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND  */ INTO “SCOTT”.”T2″(M_ROW$$,”A”) SELECT “T1″.”ROWID”,”T1″.”A” FROM “SCOTT”.”T1″ “T1”
—– PL/SQL Call Stack —–
  object      line  object
  handle    number  name
c9bbb3118        50  package body SYS.DBMS_REDEFINITION
c9bbb3118      1343  package body SYS.DBMS_REDEFINITION
ae7249bc8         1  anonymous block

so in a way it changes object_id and data_object_id of an object but not similar to ‘EXCHANGE PARTITIONS’,’ALTER TABLE … MOVE’ and ‘ALTER INDEX …REBUILD’ operations.