[20141024]不同的表rowid可以相同.txt
--前一阵子参加一个会议,在会议间隙听到别人议论"招DBA"的事,问上面的问题,竟然许多dba都回答不上来,看来现在的认证水分太大.
--"在一个数据库里面,不同的表rowid可以相同吗?"
--正好前一阵子看了cluster表的内容,知道这个答案(也许还有其他答案,我不知道,至少cluster表是其中一个正确的答案),自己也做一些
--测试.
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
create cluster cluster_dept (deptno NUMBER(2)) index;
create index i_cluster_deptno on cluster cluster_dept;
create table dept1 cluster cluster_dept(deptno) as select * from dept;
create table emp1 cluster cluster_dept(deptno) as select * from emp;
select owner, segment_name, file_id, block_id, blocks, bytes
from dba_extents
where segment_name in ('I_CLUSTER_DEPTNO','CLUSTER_DEPT')
order by segment_name, extent_id;
OWNER SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS BYTES
------ -------------------- ---------- ---------- ---------- ----------
SCOTT CLUSTER_DEPT 4 1976 8 65536
SCOTT CLUSTER_DEPT 4 544 8 65536
SCOTT I_CLUSTER_DEPTNO 4 1984 8 65536
SCOTT@test> select owner,object_name,object_id,data_object_id,object_type from dba_objects where owner=user and object_name in ('DEPT1','EMP1','CLUSTER_DEPT');
OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------ -------------------- ---------- -------------- -------------------
SCOTT CLUSTER_DEPT 291016 291016 CLUSTER
SCOTT DEPT1 291018 291016 TABLE
SCOTT EMP1 291019 291016 TABLE
--可以发现这3个对象DATA_OBJECT_ID都是一样的.DATA_OBJECT_ID=291016.
SCOTT@test> select owner,segment_name,segment_type,header_file,header_block,bytes from dba_segments where owner=user and segment_name in ('DEPT1','EMP1','CLUSTER_DEPT','I_CLUSTER_DEPTNO');
OWNER SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK BYTES
------ -------------------- ------------------ ----------- ------------ ----------
SCOTT I_CLUSTER_DEPTNO INDEX 4 1986 65536
SCOTT CLUSTER_DEPT CLUSTER 4 1978 131072
--仅仅存在CLUSTER_DEPT,I_CLUSTER_DEPTNO段.
SCOTT@test> select rowid,dept1.* from dept1;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AABHDIAAEAAAAe7AAA 20 RESEARCH DALLAS1
AABHDIAAEAAAAe8AAA 30 SALES CHICAGO
AABHDIAAEAAAAe9AAA 40 OPERATIONS BOSTON
AABHDIAAEAAAAe+AAA 50 aaa bbb
AABHDIAAEAAAAe/AAA 10 ACCOUNTING NEW YORK
AABHDIAAEAAAAIgAAA 70 aaaa BBBB
AABHDIAAEAAAAInAAA 60 cc aaa
7 rows selected.
SCOTT@test> select rowid,emp1.* from emp1;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AABHDIAAEAAAAe7AAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AABHDIAAEAAAAe7AAB 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
AABHDIAAEAAAAe7AAC 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
AABHDIAAEAAAAe7AAD 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
AABHDIAAEAAAAe7AAE 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
AABHDIAAEAAAAe8AAA 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
AABHDIAAEAAAAe8AAB 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
AABHDIAAEAAAAe8AAC 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
AABHDIAAEAAAAe8AAD 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
AABHDIAAEAAAAe8AAE 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
AABHDIAAEAAAAe8AAF 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
AABHDIAAEAAAAe/AAA 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
AABHDIAAEAAAAe/AAB 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
AABHDIAAEAAAAe/AAC 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
--可以发现rowid出现相同的情况.