rowid的一点总结！

一直都觉得rowid的设计非常独到，简单做了个总结！

未完。。。

[php]
未完。。。。
整理，修正，补充，完善当中。
1、rowid是什么？

每一个表中都有一列rowid(iot除外)，每一行的rowid代表了该行在数据库中存储的实际地址，可见rowid的独特及重要程度。rowid是伪列（pseudocolumn），伪劣的意思是实际上这一列本身在数据字典中并不存在，在查询结果输出时它被构造出来的。在操作上它和普通的列也有一些差别：不能通过desc以及select直接显示出来，除非显式说明,rowid不能做表的primarykey ，也不能对rowid执行dml操作。oracle的伪劣还有很多，如：rownum，ora_rowscn,versions_starttime, versions_endtime, versions_xid，versions_operation等，这些列都非常有用，这里暂时不作介绍。

简单的测试如下：

--desc无法显示

SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID

NUMBER(38)

--select 直接不能显示出来

SQL> select * from t;

ID
----------
1
2

--数据字典中并不存在

SQL> select TABLE_NAME,COLUMN_NAME from dba_tab_columns where table_name='T'
2 AND owner='SYS';

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
T ID

SQL>

不能和*直接输出，除非在*前面写上表名或者是表的别名，这其实和rowid本身关系不大，oracle就不支持直接将*和其他列（当然也包括伪劣）一起输出，sql server中可以:

SQL> select * , rowid from t;
select * , rowid from t
*
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

SQL> select t.* , rowid from t;

ID NAME ROWID
---------- ------------------ ------------------
1 AAACfGAABAAAHCaAAA
2 AAACfGAABAAAHCaAAB

SQL> select a.* , rowid from t a;

ID NAME ROWID
---------- ------------------ ------------------
1 AAACfGAABAAAHCaAAA
2 AAACfGAABAAAHCaAAB

SQL> select * ,id , rowid from t ;
select * ,id , rowid from t
*
第 1 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字

SQL> select t.* ,id , rowid from t ;

ID NAME ID ROWID
---------- ------------------ ---------- ------------------
1 1 AAACfGAABAAAHCaAAA
2 2 AAACfGAABAAAHCaAAB

SQL> select a.* ,id , rowid from t a;

ID NAME ID ROWID
---------- ------------------ ---------- ------------------
1 1 AAACfGAABAAAHCaAAA
2 2 AAACfGAABAAAHCaAAB

SQL>

--rowid不能做为表的主键

SQL> create table t4(id int);

表已创建。

SQL> alter table t4 add constraint pk_t primary key (rowid);
alter table t4 add constraint pk_t primary key (rowid)
*
第 1 行出现错误:
ORA-00904: "ROWID": 标识符无效

SQL> alter table t4 add constraint pk_t primary key (id);

表已更改。

--也不能对rowid执行dmt操作（Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.）

SQL> select id ,rowid from t4;

ID ROWID
---------- ------------------
1 AAAC89AAEAAAACAAAA
2 AAAC89AAEAAAACAAAB

SQL> update t4 set rowid=chartorowid('AAAC89AAEAAAACAAAC');
update t4 set rowid=chartorowid('AAAC89AAEAAAACAAAC')
*
第 1 行出现错误:
ORA-01747: user.table.column, table.column 或列说明无效

2、rowid的分类

1) Physical Rowid

a) extended rowid

b) restricted rowid

2) Logical Rowids

这部分内容doc（The rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Oracle provides these tables with logical row identifiers, called logical rowids. Rowids of foreign tables, such as DB2 tables accessed through a gateway, are not standard Oracle Database rowids. Oracle provides foreign tables with identifiers called foreign rowids.Oracle Database uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Both types of urowid are stored in the ROWID pseudocolumn, as are the physical rowids of heap-organized tables.Oracle creates logical rowids based on the primary key of the table. The logical rowids do not change as long as the primary key does not change. The ROWID pseudocolumn of an index-organized table has a datatype of UROWID. You can access this pseudocolumn as you would the ROWID pseudocolumn of a heap-organized table (that is, using a SELECT ... ROWID statement). If you want to store the rowids of an index-organized table, then you can define a column of type UROWID for the table and retrieve the value of the ROWID pseudocolumn into that column.）介绍的更加清楚，Logical Rowids 的数据类型是UROWID（universal rowids ），Logical Rowids 主要是用在iot类型的表上。oracle创建或者生成Logical Rowids 是基于表的primary key，如果primary key不改变则Logical Rowids 就不会发生变化。

Logical Rowids 依赖primary key，所以创建iot类型的表不指定primary key是不行的：

SQL> create table t_iot(id int) organization index;
create table t_iot(id int) organization index
*
第 1 行出现错误:
ORA-25175: 未找到任何 PRIMARY KEY 约束条件
SQL> create table t_iot(id int primary key) organization index;

表已创建。

SQL> insert into t_iot values(1);

已创建 1 行。

SQL> insert into t_iot values(2);

已创建 1 行。

SQL> select id , rowid from t_iot;

ID ROWID
---------- -----------------------------------------
1 *BABAcIoCwQL+
2 *BABAcIoCwQP+

logical rowid使用的数据类型是urowid(universal rowids ),urowid和rowdi一样本身也是一种数据类型，由于不太常用，可能不被人们说注意：

SQL> edit
已写入 file afiedt.buf

1 create table t
2 (
3 id number primary key
4 )
5* organization index
SQL> /

表已创建。
SQL> insert into t values(1);

已创建 1 行。

SQL> insert into t values(2);

已创建 1 行。

SQL> commit;

提交完成。
SQL> create table tt(id int , name urowid);

表已创建。

SQL> insert into tt select id , rowid from t;

已创建2行。

SQL> col name format a18
SQL> select * from tt;

ID NAME
---------- ------------------
1 *BAEAAEwCwQL+
2 *BAEAAEwCwQP+

另外需要注意的是不能直接在iot表上创建urowid类型的列：

SQL> create table t1(id int primary key,name urowid) organization index;
create table t1(id int primary key,name urowid) organization index
*
第 1 行出现错误:
ORA-01429: 索引表: 没有存储溢出行片段的数据段

SQL> create table t1(id int primary key) organization index;

表已创建。

SQL> alter table t1 add name urowid ;
alter table t1 add name urowid
*
第 1 行出现错误:
ORA-01429: 索引表: 没有存储溢出行片段的数据段

要想在iot上使用urowid类型，需要说明OVERFLOW字句：

SQL> create table t3(id int primary key,name urowid) organization index overflow tablespace users;

表已创建。

--可以创建rowid类型的列
SQL> create table t2(id int primary key,name rowid) organization index;

表已创建。

SQL> select * from tt;

ID NAME
---------- ------------------
1 *BAEAAEwCwQL+
2 *BAEAAEwCwQP+

SQL> insert into t2 select * from tt;
insert into t2 select * from tt
*
第 1 行出现错误:
ORA-01410: 无效的 ROWID

需要注意的是urowid类型不会和rowid类型相互转化。有关logical rowid的格式以及如何通过primary key生成的也是我的疑问。

3、extended rowid的格式

rowid是一个18位的64进制数，它的数据类型就是rowid类型的。rowid本身就是一种数据类型，由于并不常用，所以通常可能不被大家留意：

SQL> alter table t add name rowid;

表已更改。

SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME ROWID

SQL> select * from t;

ID NAME
---------- ------------------
1
2

SQL> update t set name='test';
update t set name='test'
*
第 1 行出现错误:
ORA-01410: 无效的 ROWID

SQL> update t set name='AAACdcAABAAAHCKAAB';

已更新2行。

SQL> update t set name='AAACdcAAB';
update t set name='AAACdcAAB'
*
第 1 行出现错误:
ORA-01410: 无效的 ROWID

我们发现只有满足rowod格式的数据才能存储到rowid列中。rowid的格式如下：

OOOOOO BBBBBB FFF RRR

Data object number Relative file number Row number

下面把t表的rowid拆分一下看看：

SQL> select id , rowid , substr(rowid , 1 , 6) data_object_id , substr(rowid , 7
, 3) rfile#,substr(rowid,10,6) block_id,substr(rowid , 16,3) from t;

ID ROWID DATA_OBJECT_ RFILE# BLOCK_ID SUBSTR
---------- ------------------ ------------ ------ ------------ ------
1 AAACdcAABAAAHCKAAA AAACdc AAB AAAHCK AAA
2 AAACdcAABAAAHCKAAB AAACdc AAB AAAHCK AAB

SQL>

这里显示出来的是64进制数，oracle在设计rowid时用A~Z表示0-25,用a~z表示26-51,用0~9表示52-61,用+表示62，用/表示63。知道了这个规则之后我们计算一下t表中两行数据的rowid来验证一下：

1)先看data object id是AAACdc，由于两行数据都属于t表，因此所有数据的这个值肯定都相同，转化位10进制数：

SQL> select 2*power(64,2)+29*64+28 from dual;

2*POWER(64,2)+29*64+28
----------------------
10076

查询看看和数据字典中记录的data_object_id是否相同，结果显示相同。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10076

2) 同样的方法计算一下rfile#:

AAB直接可以看出来rfile# =1

SQL> select name , rfile# from v$datafile where rfile#=1;

NAME RFILE#
------------------------------------------------------------ ----------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 1

SQL> show user
USER 为 "SYS"
由于我在建表t时使用了sys用户，因此t存放在了system 表空间中了，这当然不是个好习惯。大家在操作时最好使用其他用户而且不要把对象轻易的放在system表空间中，以免给system tbs中造成过多的碎片。

3) 再来看看block_id:AAAHCK

SQL> select 7*power(64,2)+2*64+10 from dual;

7*POWER(64,2)+2*64+10
---------------------
28810

查询一下segment header block 发现是28809，数据存在28810上应该没有问题，当然我们也可以dump 28810来验证数据是否存在。

SQL> SELECT HEADER_BLOCK FROM DBA_SEGMENTS WHERE segment_name='T' AND owner='SYS
';

HEADER_BLOCK
------------
28809

4) 最后来看一下row#:AAA AAB两行的row#分别是：0 ，1 ；0表示该行数据是其所在block上的第一行，其它的通理。

4、restricted rowid的格式

restricted rowid是在oracle 7中使用的rowid，doc上说它在绝大多数os上占用6个byte（The restricted internal ROWID is 6 bytes on most platforms），但是win下是10个byte：

SQL> select * from t1;

ID NAME
---------- ---------------------------------------
1 0000708A.0000.0001
2 0000708A.0001.0001

SQL> select dump(name) from t1;

DUMP(NAME)
--------------------------------------------------

Typ=69 Len=10: 0,0,0,0,0,64,112,138,0,0
Typ=69 Len=10: 0,0,0,0,0,64,112,138,0,1

SQL>

而且doc上说它是18位的16进制数（

The restricted ROWID pseudocolumn is returned to client applications in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.

），应该是包含了"."的，其实句点本身没有什么意义。正如上面doc所言它由datablock,row,datafile组成。

转为为10进制数：

SQL> select to_number(substr(name,1,8),'xxxxxxxx') block_id , to_number(substr(n
ame,10,4)) row# , to_number(substr(name , 15, 4)) file_id from t1;

BLOCK_ID ROW# FILE_ID
---------- ---------- ----------
28810 0 1
28810 1 1

SQL>

t1中的name存储的是t表的restricted rowid，通过这个restricted rowid拆分出来的BLOCK_ID ROW# FILE_ID和在第三部分中通过extended rowid拆分出来的完全一致。

另外restricted rowid是oracle为了版本向后兼容而保留的历史产物，这里不做太多介绍，只所以还要介绍restricted rowid是因为在介绍dbms_rowid这个package时会提到。

5、rowid package

上面通过很繁琐的计算一方面是验证一下rowid的计算，另一方面也是查询一下和数据字典中记录的信息作个对比。其实oracle还提供了一个package：dbms_rowid,利用dbms_rowid中的几个函数可以很容易的把rowid拆分成10进制数：

1）通过函数拆分rowid：

SQL> select id , rowid , dbms_rowid.rowid_relative_fno(rowid) from t;

ID ROWID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
---------- ------------------ ------------------------------------
1 AAACdcAABAAAHCKAAA 1
2 AAACdcAABAAAHCKAAB 1

SQL> select id , rowid , dbms_rowid.rowid_block_number(rowid) from t;

ID ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------ ------------------------------------
1 AAACdcAABAAAHCKAAA 28810
2 AAACdcAABAAAHCKAAB 28810

SQL> select id , rowid , dbms_rowid.rowid_row_number(rowid) from t;

ID ROWID DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
---------- ------------------ ----------------------------------
1 AAACdcAABAAAHCKAAA 0
2 AAACdcAABAAAHCKAAB 1

SQL>

dbms_row中还有几个非常有用的函数：

SQL> select dbms_rowid.rowid_to_absolute_fno(rowid , 'SYS' , 'T') from t;

DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','T')
-------------------------------------------------
1
1

1）函数rowid_to_absolute_fno可以实现rfile#(相对文件号) 到file#(绝对文件号)的转化。

SQL> select dbms_rowid.rowid_to_absolute_fno(rowid , 'SYS' , 'T') from t;

DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','T')
-------------------------------------------------
1
1

由于都是small file type的tbs以及数据文件数<1024因此file#和rfile#都是相同的。

2）rowid_to_restricted函数可以实现从extentded rowd到restricted rowid的转化：

使用这个函数时要注意一个参数（conversion_type in int），这个参数只能是0和1

其中0 表示rowid是restricted类型的，1表示是extended类型的

SQL> select dbms_rowid.rowid_to_restricted(rowid,0) from t;

DBMS_ROWID.ROWID_T
------------------
0000708A.0000.0001
0000708A.0001.0001

不过这里我发现输入1时也可以转化过来，有一点疑问。

3）rowid_to_extended可以实现从restricted rowid到extended rowid的转化。

SQL> create table t1(id int , name rowid);

表已创建。

SQL> insert into t1 select id , dbms_rowid.rowid_to_restricted(rowid , 0) from t
;

已创建2行。

SQL> select * from t1;

ID NAME
---------- ------------------------------------------------------------
1 0000708A.0000.0001
2 0000708A.0001.0001

SQL> desc t
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME ROWID

SQL> col name format a20
SQL> select id , dbms_rowid.rowid_to_extended(name,'SYS','T',1) from t1;

ID DBMS_ROWID.ROWID_T
---------- ------------------
1 AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB

SQL> select id , rowid from t;

ID ROWID
---------- ------------------
1 AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB

SQL>

这里有点绕其实就是把t中的rowid先转为restricted rowid临时存放在t1表中的name字段中，之后在转为extended rowid和t中显示的rowid比较一下看看这两个函数是否好用！结果当然是一样的，说明没有问题。

4）rowid_create可以根据输入的data_object_id,rfile#,blocked_id , row#生成rowid：

SQL> select id , rowid , dbms_rowid.rowid_create(1,10076
2 ,1,28810,decode(id,1,0,2,1)) from t;

ID ROWID DBMS_ROWID.ROWID_C
---------- ------------------ ------------------
1 AAACdcAABAAAHCKAAA AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB AAACdcAABAAAHCKAAB

SQL>

通过把上面拆分rowid生成的信息再作为参数通过rowid_create重新生成rowid和显示出来的rowid完全一样，说明没有问题。

5）函数rowid_type是用来判断rowid是restricted orextended的：

SQL> select id , dbms_rowid.rowid_type(rowid) from t;

ID DBMS_ROWID.ROWID_TYPE(ROWID)
---------- ----------------------------
1 1
2 1

SQL> select id , dbms_rowid.rowid_type(name) from t1;

ID DBMS_ROWID.ROWID_TYPE(NAME)
---------- ---------------------------
1 0
2 0

SQL> select * from t1;

ID NAME
---------- --------------------
1 0000708A.0000.0001
2 0000708A.0001.0001

SQL>

返回1表示rowid是extended，0表示rowid是restricted。

6）其实rowid提供的一个procedure可以直接把rowid拆分，当然一次只能拆分一个rowid，不过我们可以自己改造一下完全可以使其实现拆分表中各行的rowid。

SQL> declare
2 v_rowid_type int;
3 v_object_number number;
4 v_relative_fno int;
5 v_block_number number;
6 v_row_number int;
7 begin
8 dbms_rowid.rowid_info('AAACdcAABAAAHCKAAA',v_rowid_type,
9 v_object_number,v_relative_fno,v_block_number,v_row_number);
10 dbms_output.put_line(v_rowid_type||' '||v_object_number||' '||v_relative_fn
o||' '||v_block_number||' '||v_row_number);
11 end;
12 /
1 10076 1 28810 0

PL/SQL 过程已成功完成。

SQL>

6、rfile# 和 file#的区别

rowid涉及到了rfile#,这里简要介绍一下rfile# 和 file#的区别，因为经常看到puber门发出这样的疑问：

file#是数据文件的绝对文件号，是数据文件在数据库中的唯一标示；对应着dba_data_files.file_id以及v$datafile.file#,数据库中每增加一个datafile，系统都会为其分配一个file_id,系统优先使用当前最大值以下的空闲值。

SQL> select file_name , file_id from dba_data_files;

FILE_NAME FILE_ID
------------------------------------------------------------ ----------
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 4
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 3
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF 2
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 1
E:ORACLEPRODUCT10.2.0ORADATATESTMSSM.DBF 6
E:ORACLEPRODUCT10.2.0ORADATATESTASSM.DBF 7
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS02.DBF 12

已选择7行。

SQL> alter tablespace assm add datafile 'E:ORACLEPRODUCT10.2.0ORADATATESTA
SSM01.DBF' size 3m;

表空间已更改。

SQL> select file_name , file_id from dba_data_files;

FILE_NAME FILE_ID
------------------------------------------------------------ ----------
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 4
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 3
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF 2
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 1
E:ORACLEPRODUCT10.2.0ORADATATESTASSM01.DBF 5
E:ORACLEPRODUCT10.2.0ORADATATESTMSSM.DBF 6
E:ORACLEPRODUCT10.2.0ORADATATESTASSM.DBF 7
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS02.DBF 12

已选择8行。

SQL>

这里发现新增加的file，系统为它分配的file_id=5而不是13

数据库同时能打开的最大数据文件数是受到参数db_files的限制，default值是200，超过default值以后再增加数据文件会得到错误：

declarev_string varchar2(200) ;beginfor i in 1..1024 loop
v_string:='create tablespace'||' df'||i||' datafile '||'''E:ORACLEPRODUCT
10.2.0ORADATAORCLdf'||i||'.DBF'''||' size 3m';execute immediate v_string;dbms_output.put_line(v_string) ;end loop;end;

错误如下：

............................
create tablespace df192 datafile'E:ORACLEPRODUCT10.2.0ORADATAORCLdf192.DBF' size 3mdeclare
*第 1 行出现错误:ORA-00059: 超出 DB_FILES 的最大值
ORA-06512: 在 line 6
SQL> show parameter db_files
NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_files integer 1250

SQL> select count(*) from dba_data_files;COUNT(*)
----------1209

修改之后，继续创建，目前库中数据文件达到了1209个。到底最多能有多少个数据文件？doc上说是65533，doc如下：

Database files
Maximum per tablespace
Operating system dependent; usually 1022
Maximum per database
65533

数据文件太多，不容验证。验证一下每个表空间最多能有多少个datafile：

declare 2 v_string varchar2(200) ;3 begin
4 for i in 2..1024 loop
5 v_string:='alter tablespace df1 add datafile '||'''E:ORACLEPRODUCT10.2
.0ORADATAORCLdf'||i||'.DBF'''||' size 1m';6 execute immediate v_string;7 end loop; 8 end;9 /
declare
*第 1 行出现错误:ORA-01686: 最大文件数 (1023) 对于表空间 DF1 已达到
ORA-06512: 在 line 6SQL> connect /as sysdba
已连接。
SQL> select count(*) from dba_data_files where tablespace_name='DF1';

COUNT(*)
----------
1023

COUNT(*)
----------1023doc上说每个tbs能有的数据文件依赖于os，通常是1022个，在win下看来是1023个。

另外值得一提的是db_files,oracle不建议把db_files设的太大，因为db_files越大消耗sga中的mem就越多（When starting an Oracle Database instance, the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance.
If the value of DB_FILES is too high, memory is unnecessarily consumed.）。

提到db_files，不得不说一说controlfile中的MAXDATAFILES，控制文件中的datafiles 仅仅是影响控制文件中datafile section的初始化大小！即使没有初始化，在10g也没有什么影响，为了能满足打开更多的数据文件，oracle会自动扩展controlfile中datafile section的，在10g之前，如果datafiles达到了controlfile中的MAXDATAFILES需要重新创建控制文件。否则系统也会报错：（以下错误来自于网上，机器没有低版本的oracle，没有亲自测试）

ORA-01165: MAXDATAFILES may not exceed string
Cause: MAXDATAFILES specified on the command line too large.
Action: Resubmit the command with a smaller MAXDATAFILES

不过MAXDATAFILES的值越大，控制文件也越大。MAXDATAFILES的最大值是65534

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100000
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG' SIZE 10M,9 GROUP 2 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG' SIZE 10M,10 GROUP 3 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'E:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF',14 'E:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF', 15 'E:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF',16 'E:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF',17 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST.DBF', 18 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST1.DBF', 19 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST2.DBF', 20 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST3.DBF',21 'E:ORACLEPRODUCT10.2.0ORADATAORCLDF1.DBF'22 CHARACTER SET ZHS16GBK
23 ;

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG*第 1 行出现错误:ORA-01165: MAXDATAFILES ???? 65534
SQL> edit

已写入 file afiedt.buf

1 CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 65534
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG' SIZE 10M, 9 GROUP 2 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG' SIZE 10M,10 GROUP 3 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'E:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF',14 'E:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF',15 'E:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF',16 'E:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF',17 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST.DBF',18 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST1.DBF',19 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST2.DBF',20 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST3.DBF',

21 'E:ORACLEPRODUCT10.2.0ORADATAORCLDF1.DBF'22* CHARACTER SET ZHS16GBK
23 /控制文件已创建。
SQL>

上面一下引出了很多问题，有些冗长，接着看file# 和rfile#的区别，看看doc的介绍：

Absolute Uniquely identifies a datafile in the database. This file number can be used in many SQL statements that reference datafiles in place of using the file name. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view.
Relative Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number. In a bigfile tablespace, the relative file number is always 1024 (4096 on OS/390 platform).

when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number已经很好理解了。下面创一个bigfile 类型的tbs看看：

SQL> create bigfile tablespace bigfile datafile 'E:ORACLEPRODUCT10.2.0ORADAT
ATESTbigfile.dbf' size 3m;

表空间已创建。

SQL> col name format a56
SQL> select name , file#, rfile# from v$datafile;

NAME FILE# RFILE#
-------------------------------------------------------- ---------- ----------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 1 1
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF 2 2
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 3 3
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 4 4
E:ORACLEPRODUCT10.2.0ORADATATESTASSM01.DBF 5 5
E:ORACLEPRODUCT10.2.0ORADATATESTMSSM.DBF 6 6
E:ORACLEPRODUCT10.2.0ORADATATESTASSM.DBF 7 7
E:ORACLEPRODUCT10.2.0ORADATATESTBIGFILE.DBF 8 1024
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS02.DBF 12 12

已选择9行。

SQL>

我们发现新创建的tbs bigfile其file#=8 rfile=1024和doc说的一致。

关于doc的这段说法（ The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view.
）这里需要说明一下，数据文件没有问题，他说DBA_TEMP_FILES 以及V$TEMPFILE 中的file_id以及file#是绝对文件号是有前提条件的，只能说他们是tempfile的绝对文件号，在整个数据库内部临时文件也是有绝对号的，不过在dba_temp_files和v$tempfile中都没有显示出来，如果通过一个大的排序使其使用tempseg，然后观察v$tempseg_usage 或者$sort_usage:

SQL> select tablespace,segtype ,segfile# from v$sort_usage;

TABLESPACE SEGTYPE SEGFILE#
------------------------------- --------- ----------
TEMP SORT 202

SQL>

这里看到的SEGFILE#=202才是临时文件真真在数据库中的绝对文件号，为什么是202？是因为库中还存在一个临时文件：

SQL> select file_name , tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE
-------------------------------------------------- ----------
E:ORACLEPRODUCT10.2.0ORADATATESTTMP.DBF TMP
E:ORACLEPRODUCT10.2.0ORADATATESTTEMP02.DBF TEMP

SQL>

而临时文件TMP.DBF 的绝对文件号是201。oracle是如何绝对临时文件在库中的编号的，其实是受到参数db_files的限制：

SQL> show parameter db_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 200
SQL>

从这个参数指定的为数据库预留的数据文件编号开始为临时文件指定临时文件在库中的真真绝对号。

7、object_id 和data_object_id的区别

经常看到pub上有人发出这样的疑问：两者的区别，其实容易理解：

object_id 是对象的物理编号，只要这个对象创建系统就为它分配了一个对象编号，而且只要对象在数据库中存在，这个编号就不会变化，是对象在数据库中的唯一标示。

data_object_id是对象的逻辑编号，准确的说是segment的编号，由于segment存在表空间中，因此也可以说data_object_id是在表空间里的对象编号，或者说是segment的编号，如果segment发生变化，data_object_id是会改变的，诸如我们熟悉的move , truncate操作都会是data_object_id发生变化。简单的测试一下：

--数据库中确实存在data_object_id相同的很多对象，因为data_object_id是tbs内部的标示，因此在库中存在相同的不足为奇

SQL> select data_object_id , count(*) from dba_objects
2 group by data_object_id
3 having count(*)>1;

DATA_OBJECT_ID COUNT(*)
-------------- ----------
7612
29 3
6 3
573 2
2 17
181 7
251 2
163 3
8 3
241 3
10 3

已选择11行。

--再来看看tbs中是否存在相同的data_object_id ：

--再来看看库中是否存在相同的object_id:

SQL> select object_id , count(*) from dba_objects
2 where object_id is not null
3 group by object_id
4 having count(*)>1;

未选定行

SQL>

验证一下data_object_id的变化：

SQL> select id , rowid from t;

ID ROWID
---------- ------------------
1 AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10076

SQL> ALTER table t move;

表已更改。

--这里需要注意，move后面不写tablespace以及tbs名字似乎不能完全保证segment发生移动

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10176

SQL> ALTER table t move tablespace users;

表已更改。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10177

SQL> ALTER table t move tablespace system;

表已更改。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10178

SQL> ALTER table t move tablespace system;

表已更改。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10179

SQL> truncate table t;

表被截断。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10182

SQL>

直观感觉DATA_OBJECT_ID应该是个序列。

8、rowid的应用

1）删除重复数据

表中出现重复数据要想删除不是一件容易的事情，通常人们能想到的做法是给这个表加上一列，然后把流水号更新上来，我之前在sybase，sql server中就经常这样干，表很大的话有时候操作起来风险很大，很不方面，但是在oracle数据中通过rowid很方便的可以实现重复数据的删除：

SQL> select t.* , rowid from t;

ID NAME ROWID
---------- ------------------ ------------------
1 AAACfGAABAAAHCaAAA
2 AAACfGAABAAAHCaAAB
1 AAACfGAABAAAHCaAAC
2 AAACfGAABAAAHCaAAD
SQL> delete from t where rowid in (select max(rowid) from t group by id);

已删除2行。

SQL> select * from t;

ID NAME
---------- ------------------
1
2

2）rowid在index中的存储

由于通过rowid可以准确定位每一行在datafile中的确切位置，因此通过rowid查找数据是最有效、最直接的方式。oracle内部把它和index key一起存储在index 的leaf node上：

SQL> create table t4(id int);

表已创建。

SQL> insert into t4 values(1);

已创建 1 行。

SQL> insert into t4 values(2);

已创建 1 行。

SQL> commit;

提交完成。

SQL> create index idx_t4 on t4(id);

索引已创建。
SQL> analyze table t4 compute statistics for all indexes;

表已分析。

SQL> select blevel , leaf_blocks from dba_indexes where index_name='IDX_T4';

BLEVEL LEAF_BLOCKS
---------- -----------
0 1

SQL> select HEADER_FILE,HEADER_BLOCK,BLOCKS from dba_segments where segment_name
='IDX_T4';

HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
4 11283 8

SQL> select file_id , block_id from dba_extents where segment_name='IDX_T4';

FILE_ID BLOCK_ID
---------- ----------
4 11281

根据对oracle逻辑结构的认识通过上面查询得知 11283 是segment header block， 11282是二级bitmap block ， 11281是一级bitmap block（因为使用的是assm），所以断定11284 是leaf block，dump一下看看：

SQL> alter system dump datafile 4 block 11284;

系统已更改。

主要dump内容如下：

--================================================

Start dump data blocks tsn: 4 file#: 4 minblk 11284 maxblk 11284
buffer tsn: 4 rdba: 0x01002c14 (4/11284)
scn: 0x0000.001010ca seq: 0x01 flg: 0x04 tail: 0x10ca0601
frmt: 0x02 chkval: 0xec2d type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x086A2200 to 0x086A4200
........................
Block header dump: 0x01002c14
Object id on Block? Y
seg/obj: 0x2f40 csc: 0x00.1010c9 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1002c11 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001010c9

Leaf block dump
===============
header address 141173348=0x86a2264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 8012=0x1f4c
kdxcoavs 7972
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 00 2c 10 00 00
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 2c 10 00 01
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 11284 maxblk 11284

--================================================

通过这一段：

col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 00 2c 10 00 00
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 2c 10 00 01

清楚的知道leaf node不仅存储了index key值，而且存储了index key所在的行的rowid部分信息，存储在index leaf node上的rowid占用了6byte，把16进制数转化为二进制数和rowid对比一下看看：

16进制：01 00 2c 10 00 00

2进制：0000 0001 00 00 0000 0010 1100 0001 0000 0000 0000 0000 0000

前10位表示rfile＃：0000 0001 00 转为10进制是4

接下来的22位表示block_number:00 0000 0010 1100 0001 0000

SQL> select 8*1024+2*1024+1024+16 from dual;

8*1024+2*1024+1024+16
---------------------
11280

最后16位表示row_number：0000 0000 0000 0000 显然是10进制的0

再把rowid拆分一下做对比：

SQL> select dbms_rowid.rowid_relative_fno(rowid) rfile#, dbms_rowid.rowid_block_
number(rowid) block_number ,dbms_rowid.rowid_row_number(rowid) row_number from t
4;

RFILE# BLOCK_NUMBER ROW_NUMBER
---------- ------------ ----------
4 11280 0
4 11280 1

SQL>

我们此时清楚的知道在index leaf node上存储的rowid没有data_object_id，事实上也确实不需要存储data_object_id，因为一个index是属于那个表，在数据字典中是有记录的，就像书的页码上不需要指定书的名字是一回事。有关index和rowid就介绍这么多，关于分区index和rowid的关系大家也可以利用同样的办法分析。
9、rowid的存储以及对oracle的限制

上面提到rowid采用的是64位编码，但是内部事实上是需要10个字节即80位来存储rowid的，其中data_object_id占用32 位，rfile#占用10 位，block_number占用22，row#占用16 位。由此，我们可以得出：

32bit的object number，每个数据库最多有4G个对象

10bit的rfile#，由于rfile#占用了10位，因此每个表空间最多有1022个文件（2个文件预留）

22bit的block number，每个文件最多有4M个BLOCK

16bit的row number，每个BLOCK最多有64K个ROWS

简单的测试如下：

SQL> select id , rowid from t;

ID ROWID
---------- ------------------
1 AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB

SQL> select vsize(rowid) from t;

VSIZE(ROWID)
------------
10
10

可见实际存储rowid却是使用了10byte

10、rowid和字符类型相互转化

oracle提供了2个函数可以实现rowid和字符类型相互转化：

SQL> select id, rowid , rowidtochar(rowid) , chartorowid(rowidtochar(rowid)) fro
m t;

ID ROWID ROWIDTOCHAR(ROWID) CHARTOROWID(ROWIDT
---------- ------------------ ------------------ ------------------
1 AAACfGAABAAAHCaAAA AAACfGAABAAAHCaAAA AAACfGAABAAAHCaAAA
2 AAACfGAABAAAHCaAAB AAACfGAABAAAHCaAAB AAACfGAABAAAHCaAAB

SQL>

在最开始我的测试例子中其中有一个是把t表的rowid转换位restricted rowid后更新到了t1表的name字段里了其实是oracle内部发生了隐式转换。
.

[/php]

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

转载于:http://blog.itpub.net/19602/viewspace-61760/