[20141028]不同的表rowid可以相同.txt

[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可以重复吗?这样问题就很容易知道答案,还有其他的情况,我就不知道了.

时间： 2024-10-02 02:43:12

[20141028]不同的表rowid可以相同.txt的相关文章

[20141024]不同的表rowid可以相同.txt

[20141024]不同的表rowid可以相同.txt --前一阵子参加一个会议,在会议间隙听到别人议论"招DBA"的事,问上面的问题,竟然许多dba都回答不上来,看来现在的认证水分太大. --"在一个数据库里面,不同的表rowid可以相同吗?" --正好前一阵子看了cluster表的内容,知道这个答案(也许还有其他答案,我不知道,至少cluster表是其中一个正确的答案),自己也做一些 --测试. SCOTT@test> @ver BANNER ------

[20121021]探究表的rowdependencies属性.txt

[20121021]探究表的rowdependencies属性.txt 使用bbed来探索表的rowdependencies属性. 实际上默认这个功能是不打开的,也就是采用norowdependencies方式,这样block的所有行会共享同一个SCN,如果执行DML操作, 这个SCN都是一样的在同一块中.而使用rowdependencies.每行都会有对应事务的scn.使用ora_rowscn可以查询到块中记录的SCN. SQL> select * from v$version; BANNER

[20131211]对比两个表的不同信息.txt

[20131211]对比两个表的不同信息.txt 昨天看了http://jonathanlewis.wordpress.com/2013/12/09/differences/博客,通过包dbms_rectifier_diff可以查看两个表存在那些不同. 自己做一个测试看看,重复测试. SCOTT@test> @verBANNER--------------------------------------------------------------------------------Oracl

[20130910]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED(补充).txt

[20130910]12C执行计划的TABLE ACCESS BY INDEX ROWID BATCHED(补充).txt 链接http://space.itpub.net/267265/viewspace-772371 写了12c下在范围扫描时可能出现的TABLE ACCESS BY INDEX ROWID BATCHED,这是一种新的执行方式,能够提高执行效率,特别在数据聚集很好的情况下. 既然是12c的一个特性应该有一个参数关闭这个特性.重复前面的例子: 1.建立测试环境: SCOTT@t

[20130325]表Initrans的数量.txt

[20130325]表Initrans的数量.txt http://aprakash.wordpress.com/2010/12/18/oracle-tables-initrans/ SQL> @ver BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -

[20170520]利用undo表空间保护数据.txt

[20170520]利用undo表空间保护数据.txt --//undo表空间是用来记录前映像信息,也用来保证查询时一致性的.上个星期去听一些课,提到不打开归档情况下一些维护技巧, --//就是建立多个redo日志文件,用来保存日志,至少维持3-4天甚至1个星期的日志,这样可以一定程度减少错误以及会查问题. --//另外提到一种利用undo表空间避免异常操作的恢复方法,就是再建立1个undo表空间,出现异常dml语句时切换使用新的undo表空间. --//这样可以非常从容的恢复信息.当然最好不要

[20150112]系统管理表空间的疑问.txt

[20150112]系统管理表空间的疑问.txt http://www.itpub.net/thread-1903121-1-1.html 10g下每个数据文件3-8块为位图区. 6个块＝48K(假设数据块大小8k) 48*8*1024＝393216 区 对于系统表空间管理.1区＝64K. SCOTT@test> set numw 20 SCOTT@test> select 48*8*1024*64*1024 from dual;    48*8*1024*64*1024 ----------

[20170623]传输表空间补充测试.txt

[20170623]传输表空间补充测试.txt --//昨天测试了使用dblink+传输表空间,链接如下:http://blog.itpub.net/267265/viewspace-2141115/ --//今天补充测试看看加参数SQLFILE生成的脚本是什么内容. impdp scott/book network_link=l_dbcn1 transport_tablespaces=users transport_datafiles=/home/oracle/backup/sugar01.d

[20160904]表统计信息lock.txt

[20160904]表统计信息lock.txt 晚上看链接:https://blogs.oracle.com/Database4CN/entry/%E8%AF%8A%E6%96%AD%E7%BB%9F%E8%AE%A1%E4%BF%A1%E6%81%AF%E4%B8%8D%E6%94%B6%E9%9B%86%E5%8E%9F%E5%9B%A0 提到如果导入使用ROWS=n,会导致导入的表lock,测试看看: 官方有如下解释: If ROWS=n, then statistics for all