oracle rowid 研究

简介:

SQL> create table tab01(id integer,val varchar(4));

Table created.

SQL> insert into tab01 values(1,'0001');

1 row created.

SQL> insert into tab01 values(2,'0002');       

1 row created.

SQL> insert into tab01 values(3,'0003');

1 row created.

SQL> insert into tab01 values(4,'0004');

1 row created.

SQL> select rowid, tab01.* from tab01;

ROWID                      ID VAL
------------------ ---------- ----
AAAVVLAAEAAAACvAAA          1 0001
AAAVVLAAEAAAACvAAB          2 0002
AAAVVLAAEAAAACvAAC          3 0003
AAAVVLAAEAAAACvAAD          4 0004

SQL> 
******************************************************

[oracle@localhost ~]$ cat test.sql
select dbms_rowid.rowid_object(rowid) object_id,
       dbms_rowid.rowid_relative_fno(rowid) file_id,
       dbms_rowid.rowid_block_number(rowid) block_id,
       dbms_rowid.rowid_row_number(rowid) num ,id,val from tab01;
[oracle@localhost ~]$ 

SQL> @test.sql;

 OBJECT_ID    FILE_ID   BLOCK_ID        NUM         ID VAL
---------- ---------- ---------- ---------- ---------- ----
     87371          4        175          0          1 0001
     87371          4        175          1          2 0002
     87371          4        175          2          3 0003
     87371          4        175          3          4 0004

SQL> 

[oracle@localhost ~]$ cat test02.sql
select rowid, 
     substr(rowid,1,6) "object", 
     substr(rowid,7,3) "file",
     substr(rowid,10,6) "block",
     substr(rowid,16,3) "row",  
     id,val from tab01;

[oracle@localhost ~]$ 

SQL> @test02.sql;

ROWID              object fil block  row         ID VAL
------------------ ------ --- ------ --- ---------- ----
AAAVVLAAEAAAACvAAA AAAVVL AAE AAAACv AAA          1 0001
AAAVVLAAEAAAACvAAB AAAVVL AAE AAAACv AAB          2 0002
AAAVVLAAEAAAACvAAC AAAVVL AAE AAAACv AAC          3 0003
AAAVVLAAEAAAACvAAD AAAVVL AAE AAAACv AAD          4 0004

SQL>

******************************************************

 

如果,把数据库进行冷备份,然后恢复会如何?

 

SQL> @test02.sql;

ROWID              object fil block  row         ID VAL
------------------ ------ --- ------ --- ---------- ----
AAAVVLAAEAAAACvAAA AAAVVL AAE AAAACv AAA          1 0001
AAAVVLAAEAAAACvAAB AAAVVL AAE AAAACv AAB          2 0002
AAAVVLAAEAAAACvAAC AAAVVL AAE AAAACv AAC          3 0003
AAAVVLAAEAAAACvAAD AAAVVL AAE AAAACv AAD          4 0004

SQL> 

SQL> @test.sql;

 OBJECT_ID    FILE_ID   BLOCK_ID        NUM         ID VAL
---------- ---------- ---------- ---------- ---------- ----
     87371          4        175          0          1 0001
     87371          4        175          1          2 0002
     87371          4        175          2          3 0003
     87371          4        175          3          4 0004

SQL>

完全没有变化。

 

现在重新作表,看导出导入到其他机器上状况会如何:

[oracle@o_target ~]$ sqlplus gao/gao

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 4 09:42:57 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table gaotab(id integer,val varchar(3));

Table created.

SQL> insert into gaotab values(4,'004');

1 row created.

SQL> insert into gaotab values(1,'001');

1 row created.

SQL> insert into gaotab values(3,'003');

1 row created.

SQL> insert into gaotab values(2,'002');

1 row created.

SQL> commit;

Commit complete.

SQL> 







本文转自健哥的数据花园博客园博客,原文链接:http://www.cnblogs.com/gaojian/p/3643747.html,如需转载请自行联系原作者

目录
相关文章
|
8月前
|
分布式计算 Oracle 关系型数据库
在Flink中使用CDC同步Oracle中的RowID
在Flink中使用CDC同步Oracle中的RowID
408 1
|
SQL Oracle 关系型数据库
Oracle 等待事件研究:SQL*Net break/reset to client
SQL*Net break/reset to client事件是一个容易被误解的事件,这个事件看起来和网络有关,但实际上大多数情况下这个事件与网络无关。
372 0
Oracle 等待事件研究:SQL*Net break/reset to client
|
Oracle 关系型数据库 索引
|
Oracle 关系型数据库 数据库
|
Oracle 关系型数据库 数据库
|
SQL Oracle 关系型数据库

推荐镜像

更多