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>