ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130708]传输表空间与data_object_id.txt

[20130708]传输表空间与data_object_id.txt

原创 Linux操作系统 作者:lfree 时间:2013-07-08 10:14:00 0 删除 编辑
[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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-765713/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2594
  • 访问量
    6370052