[20130708]傳輸表空間與data_object_id.txt

[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 level<=10;
Table 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.