[20130708]传输表空间与data_object_id.txt
传输表空间与scn.txt：http://space.itpub.net/267265/viewspace-750140
初学者经常搞混视图dba_objects中object_id和data_object_id，一般建立表两者是相等的，容易混淆。建立分区表的话，表仅仅有
object_id，没有data_object_id，分区表才有data_object_id。
实际上有些对象比如view，sequence等仅仅有object_id，而没有段的分配即没有data_object_id。db_link两者都没有。像表在truncate
(有数据的情况下)，move的情况下data_object_id会发生变化。
SQL> select object_id,data_object_id from dba_objects where wner=user and object_name='V_DEPT';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
269361
SQL> select object_id,data_object_id from dba_objects where object_name='SEQ1';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
275186
SQL> select object_name,object_id,data_object_id from dba_objects where object_name='TEST1.COM';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
TEST1.COM
--如果使用传输表空间，导入的是metadate数据，数据文件里面的data_object_id一定没有改变，这样有可能会出现
--data_object_id重复的情况。自己做一个测试看看。
1.测试环境：
SQL> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
CREATE TABLESPACE AAA DATAFILE
'/u01/app/oracle11g/oradata/test/aaa01.dbf' SIZE 65528K AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
SQL> create table t tablespace aaa as select rownum id,'test' name from dual connect by levelTable created.
SQL> select object_name,object_id,data_object_id from dba_objects where wner=user and object_name='T';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
T 276982 276982
2.测试是否可以传输表空间(sys用户执行)：
SQL> exec dbms_tts.transport_set_check('AAA',TRUE);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations ;
no rows selected
SQL> alter tablespace aaa read only;
Tablespace altered.
$ exp \"/ as sysdba\" tablespaces=aaa transport_tablespace=y file=aaa.dmp
Export: Release 11.2.0.3.0 - Production on Mon Jul 8 09:44:10 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace AAA ...
. exporting cluster definitions
. exporting table definitions
. . exporting table T
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
--做一个备用。
$ cp /u01/app/oracle11g/oradata/test/aaa01.dbf /tmp/aaa01.dbf
3.导入传输表空间，由于仅仅1个测试数据库。我修改表空间名字以及里面的表。执行如下：
SQL> alter tablespace aaa read write ;
Tablespace altered.
SQL> alter tablespace aaa rename to bbb;
Tablespace altered.
SQL> alter table scott.t rename to torg;
Table altered.
SQL> select object_name,object_id,data_object_id from dba_objects where wner='SCOTT' and object_name='TORG';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
TORG 276982 276982
--可以发现rename表名t，OBJECT_ID,DATA_OBJECT_ID并没有发生变化，依旧是276982。
--修改数据文件名字
SQL> alter tablespace BBB offline normal;
$ mv /u01/app/oracle11g/oradata/test/aaa01.dbf /u01/app/oracle11g/oradata/test/bbb01.dbf
SQL> alter database rename file '/u01/app/oracle11g/oradata/test/aaa01.dbf' to '/u01/app/oracle11g/oradata/test/bbb01.dbf'
SQL> alter tablespace BBB online
--这样在导入不会出现冲突情况。
$ cp /tmp/aaa01.dbf /u01/app/oracle11g/oradata/test/aaa01.dbf
$ imp \'\/ as sysdba\' tablespaces=aaa transport_tablespace=y file=aaa.dmp datafiles=/u01/app/oracle11g/oradata/test/aaa01.dbf
Import: Release 11.2.0.3.0 - Production on Mon Jul 8 10:04:34 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
. . importing table "T"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
SQL> select object_name,object_id,data_object_id from dba_objects where wner='SCOTT' and object_name in ('TORG','T');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
T 276986 276982
TORG 276982 276982
--可以发现一个情况，表T与TORG的DATA_OBJECT_ID都是276982。OBJECT_ID不同。
--从另外的侧面可以说明在一个数据库,DATA_OBJECT_ID相同，而对象可以不同。
--希望这篇文章对许多beginner更好的理解OBJECT_ID，DATA_OBJECT_ID.