[20141028]不同的表rowid可以相同.txt
--前一阵子参加一个会议,在会议间隙听到别人议论"招DBA"的事,问上面的问题,竟然许多dba都回答不上来,看来现在的认证水分太大.
--"在一个数据库里面,不同的表rowid可以相同吗?"
--正好前一阵子看了cluster表的内容,知道这个答案(也许还有其他答案,我不知道,至少cluster表是其中一个正确的答案),自己也做一些
--测试.相关链接如下：
http://blog.itpub.net/267265/viewspace-1308948/
--还有其它情况吗？突然想起来实际上传输表空间也会导致这种情况的出现，以前写过一篇：
--[20130708]传输表空间与data_object_id.txt,链接如下，为了加强记忆，自己再重做一次,这次使用expdp/impdp做看看(上次的操作有
--点繁琐)。
http://blog.itpub.net/267265/viewspace-765713/
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;
SCOTT@test> create table t tablespace aaa as select rownum id,'test' name from dual connect by levelTable created.
SCOTT@test> select object_name,object_id,data_object_id from dba_objects where owner=user and object_name='T';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
T 292276 292276
2.测试是否可以传输表空间(sys用户执行)：
SYS@test> exec dbms_tts.transport_set_check('AAA',TRUE);
PL/SQL procedure successfully completed.
SYS@test> select * from transport_set_violations ;
no rows selected
SYS@test> alter tablespace aaa read only;
Tablespace altered.
$ expdp \"/ as sysdba\" tablespaces=aaa transport_tablespace=y directory=DATA_PUMP_DIR file=aaa.dmp
Export: Release 11.2.0.3.0 - Production on Tue Oct 28 10:27:50 2014
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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=aaa.dmp" Location: Command Line, Replaced with: "dumpfile=aaa.dmp"
Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, Replaced with: "transport_tablespaces=aaa"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" tablespaces=aaa directory=DATA_PUMP_DIR dumpfile=aaa.dmp reuse_dumpfiles=true
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle11g/admin/test/dpdump/aaa.dmp
******************************************************************************
Datafiles required for transportable tablespace AAA:
/u01/app/oracle11g/oradata/test/aaa01.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:29:21
--做一个备用。
$ cp /u01/app/oracle11g/oradata/test/aaa01.dbf /u01/app/oracle11g/oradata/test/bbb01.dbf
3.导入传输表空间，由于仅仅1个测试数据库。我修改表空间名字以及里面的表。执行如下：
SYS@test> alter tablespace aaa read write ;
Tablespace altered.
SYS@test> alter table scott.t rename to torg;
Table altered.
SYS@test> select object_name,object_id,data_object_id from dba_objects where owner='SCOTT' and object_name='TORG';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
TORG 292276 292276
--可以发现rename表名t后,OBJECT_ID,DATA_OBJECT_ID并没有发生变化，依旧是292276。
$ impdp \'\/ as sysdba\' transport_tablespace=y directory=DATA_PUMP_DIR file=aaa.dmp transport_datafiles=/u01/app/oracle11g/oradata/test/bbb01.dbf remap_tablespace=aaa:bbb
Import: Release 11.2.0.3.0 - Production on Tue Oct 28 10:37:40 2014
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
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "file=aaa.dmp" Location: Command Line, Replaced with: "dumpfile=aaa.dmp"
Legacy Mode Parameter: "transport_tablespace=TRUE" Location: Command Line, ignored.
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=aaa.dmp transport_datafiles=/u01/app/oracle11g/oradata/test/bbb01.dbf remap_tablespace=aaa:bbb
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:37:46
SYS@test> select object_name,object_id,data_object_id from dba_objects where owner='SCOTT' and object_name in ('TORG','T');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
-------------------- ---------- --------------
T 292341 292276
TORG 292276 292276
--可以发现一个情况，表T与TORG的DATA_OBJECT_ID都是292276。OBJECT_ID不同。
--从另外的侧面可以说明在一个数据库,DATA_OBJECT_ID相同，而对象可以不同。
SYS@test> select rowid,t.* from scott.t ;
ROWID ID NAME
------------------ ---------- --------------------
AABHW0AAMAAAACDAAA 1 test
AABHW0AAMAAAACDAAB 2 test
AABHW0AAMAAAACDAAC 3 test
AABHW0AAMAAAACDAAD 4 test
AABHW0AAMAAAACDAAE 5 test
AABHW0AAMAAAACDAAF 6 test
AABHW0AAMAAAACDAAG 7 test
AABHW0AAMAAAACDAAH 8 test
AABHW0AAMAAAACDAAI 9 test
AABHW0AAMAAAACDAAJ 10 test
10 rows selected.
SYS@test> select rowid,torg.* from scott.torg;
ROWID ID NAME
------------------ ---------- --------------------
AABHW0AAMAAAACDAAA 1 test
AABHW0AAMAAAACDAAB 2 test
AABHW0AAMAAAACDAAC 3 test
AABHW0AAMAAAACDAAD 4 test
AABHW0AAMAAAACDAAE 5 test
AABHW0AAMAAAACDAAF 6 test
AABHW0AAMAAAACDAAG 7 test
AABHW0AAMAAAACDAAH 8 test
AABHW0AAMAAAACDAAI 9 test
AABHW0AAMAAAACDAAJ 10 test
10 rows selected.
--对比发现两者rowid都一样.
--也希望这篇文章对许多beginner更好的理解OBJECT_ID，DATA_OBJECT_ID.
4.做进一步的研究:
SCOTT@test> column name format a50
SCOTT@test> set numw 16
SCOTT@test> select file#,status,fuzzy,tablespace_name,ts#,rfile#,checkpoint_change#,checkpoint_count,name from v$datafile_header ;
FILE# STATUS FUZ TABLESPACE_NAME TS# RFILE# CHECKPOINT_CHANGE# CHECKPOINT_COUNT NAME
---------------- ------- --- ------------------------------ ---------------- ---------------- ------------------ ---------------- --------------------------------------------------
1 ONLINE YES SYSTEM 0 1 11736877945 856621646 /u01/app/oracle11g/oradata/test/system01.dbf
2 ONLINE YES SYSAUX 1 2 11736877945 856621646 /u01/app/oracle11g/oradata/test/sysaux01.dbf
3 ONLINE YES UNDOTBS1 2 3 11736877945 856621646 /u01/app/oracle11g/oradata/test/undotbs01.dbf
4 ONLINE YES USERS 4 4 11736877945 856621652 /u01/app/oracle11g/oradata/test/users01.dbf
5 ONLINE YES EXAMPLE 6 5 11736877945 856621649 /u01/app/oracle11g/oradata/test/example01.dbf
6 ONLINE YES RMAN 7 6 11736877945 856621644 /u01/app/oracle11g/oradata/test/rman01.dbf
7 ONLINE YES TOOLS 8 7 11736877945 856621644 /u01/app/oracle11g/oradata/test/tools01.dbf
8 ONLINE YES TEST 9 8 11736877945 856621646 /u01/app/oracle11g/oradata/test/test01.dbf
9 ONLINE YES TESTMSSM 1061 9 11736877945 856621644 /u01/app/oracle11g/oradata/test/testmssm01.dbf
10 ONLINE YES UNDOTBS2 5 10 11736877945 856621644 /u01/app/oracle11g/oradata/test/undotbs02.dbf
11 ONLINE YES TEST16K 1063 11 11736877945 107 /u01/app/oracle11g/oradata/test/test16k01.dbf
12 ONLINE YES AAA 1055 12 11736879807 5 /u01/app/oracle11g/oradata/test/aaa01.dbf
13 ONLINE NO AAA 1055 12 11736878978 3 /u01/app/oracle11g/oradata/test/bbb01.dbf
13 rows selected.
--可以发现TABLESPACE_NAME还是AAA,TS#=1055,相对文件号RFILE#没有变化都是12.实际上现在表空间BBB还是read only方式.
SCOTT@test> select file#,rfile#,enabled,checkpoint_change#,name from v$datafile ;
FILE# RFILE# ENABLED CHECKPOINT_CHANGE# NAME
---------------- ---------------- ---------- ------------------ --------------------------------------------------
1 1 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/system01.dbf
2 2 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/sysaux01.dbf
3 3 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/undotbs01.dbf
4 4 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/users01.dbf
5 5 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/example01.dbf
6 6 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/rman01.dbf
7 7 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/tools01.dbf
8 8 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/test01.dbf
9 9 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/testmssm01.dbf
10 10 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/undotbs02.dbf
11 11 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/test16k01.dbf
12 12 READ WRITE 11736879807 /u01/app/oracle11g/oradata/test/aaa01.dbf
13 12 READ ONLY 11736878978 /u01/app/oracle11g/oradata/test/bbb01.dbf
13 rows selected.
SCOTT@test> alter tablespace bbb read write ;
Tablespace altered.
SCOTT@test> select file#,status,fuzzy,tablespace_name,ts#,rfile#,checkpoint_change#,checkpoint_count,name from v$datafile_header ;
FILE# STATUS FUZ TABLESPACE_NAME TS# RFILE# CHECKPOINT_CHANGE# CHECKPOINT_COUNT NAME
---------------- ------- --- ------------------------------ ---------------- ---------------- ------------------ ---------------- --------------------------------------------------
1 ONLINE YES SYSTEM 0 1 11736877945 856621646 /u01/app/oracle11g/oradata/test/system01.dbf
2 ONLINE YES SYSAUX 1 2 11736877945 856621646 /u01/app/oracle11g/oradata/test/sysaux01.dbf
3 ONLINE YES UNDOTBS1 2 3 11736877945 856621646 /u01/app/oracle11g/oradata/test/undotbs01.dbf
4 ONLINE YES USERS 4 4 11736877945 856621652 /u01/app/oracle11g/oradata/test/users01.dbf
5 ONLINE YES EXAMPLE 6 5 11736877945 856621649 /u01/app/oracle11g/oradata/test/example01.dbf
6 ONLINE YES RMAN 7 6 11736877945 856621644 /u01/app/oracle11g/oradata/test/rman01.dbf
7 ONLINE YES TOOLS 8 7 11736877945 856621644 /u01/app/oracle11g/oradata/test/tools01.dbf
8 ONLINE YES TEST 9 8 11736877945 856621646 /u01/app/oracle11g/oradata/test/test01.dbf
9 ONLINE YES TESTMSSM 1061 9 11736877945 856621644 /u01/app/oracle11g/oradata/test/testmssm01.dbf
10 ONLINE YES UNDOTBS2 5 10 11736877945 856621644 /u01/app/oracle11g/oradata/test/undotbs02.dbf
11 ONLINE YES TEST16K 1063 11 11736877945 107 /u01/app/oracle11g/oradata/test/test16k01.dbf
12 ONLINE YES AAA 1055 12 11736879807 5 /u01/app/oracle11g/oradata/test/aaa01.dbf
13 ONLINE YES BBB 1064 12 11736885229 3 /u01/app/oracle11g/oradata/test/bbb01.dbf
13 rows selected.
SCOTT@test> select file#,rfile#,enabled,checkpoint_change#,name from v$datafile ;
FILE# RFILE# ENABLED CHECKPOINT_CHANGE# NAME
---------------- ---------------- ---------- ------------------ --------------------------------------------------
1 1 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/system01.dbf
2 2 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/sysaux01.dbf
3 3 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/undotbs01.dbf
4 4 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/users01.dbf
5 5 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/example01.dbf
6 6 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/rman01.dbf
7 7 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/tools01.dbf
8 8 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/test01.dbf
9 9 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/testmssm01.dbf
10 10 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/undotbs02.dbf
11 11 READ WRITE 11736877945 /u01/app/oracle11g/oradata/test/test16k01.dbf
12 12 READ WRITE 11736879807 /u01/app/oracle11g/oradata/test/aaa01.dbf
13 12 READ WRITE 11736885229 /u01/app/oracle11g/oradata/test/bbb01.dbf
13 rows selected.
--打开读写后,修改为正确TABLESPACE_NAME信息.TS#=1064.
--总结：
--现在想想我估计这个才是当时别人需要的答案，因为传输表空间这种技术在一些系统要经常使用。
--还说明一点，凡是要认真思考，谋定而动，也许解决问题的思路是多种多样的，不要局限在特定的范围里面。
--从两个角度思考:1. 1个数据块可以放两个表吗? 2.不同的数据文件里面DATA_OBJECT_ID,RFILE可以重复吗?这样问题就很容易知道答案,还有其他的情况,我就不知道了.