oracle实验记录 （dbms_rowid使用）

SQL> insert into t1 values(1);

1 row created.

 

SQL> select file#,block# from (select dbms_rowid.rowid_relative_fno(rowid) f
,dbms_rowid.rowid_block_number(rowid) block# from t1);

     FILE#    BLOCK#
--------------------
         4       447

SQL> commit;
SQL> select object_id from dba_objects where object_name='T1';

 OBJECT_ID
----------
     54214
Commit complete.
建立一个rowid

SQL> declare
  2  a rowid;
  3  begin
  4  a:=dbms_rowid.rowid_create(1,54214,4,447,1);
  5  dbms_output.put_line(a);
  6  end;
  7  /                         (类型 0 受限 1扩展,objd,file#,block#,row)
AAANPGAAEAAAAG/AAB

SQL> select rowid from t1;

ROWID
------------------
AAANPGAAEAAAAG/AAU~~~~~~~有区别
  1  declare
  2  a rowid;
  3  begin
  4  a:=dbms_rowid.rowid_create(1,54214,4,447,20);
  5  dbms_output.put_line(a);
  6* end;
SQL> /
AAANPGAAEAAAAG/AAU~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SQL> select rowid from t1;

ROWID
------------------
AAANPGAAEAAAAG/AAK

  1  declare
  2  a number;~~~~~~~类型
  3  b number;~~~~~~~~~~~OBJD
  4  c number;~~~~~~~~~~~~~~~~~~~~FILE#
  5  d number;~~~~~~~~~~~~~~~~~~~~BLOCK#
  6  e number;~~~~~~~~~~~~~~~~~~~~数据块中行号
  7  begin
  8  dbms_rowid.rowid_info('AAANPGAAEAAAAG/AAK',a,b,c,d,e);
  9  dbms_output.put_line(a ||',' ||b ||','|| c ||','|| d ||','|| e);
 10* end;
SQL> /
1,54214,4,447,10

PL/SQL procedure successfully completed.

~~~~~~~~~~~~~
SQL> select dbms_rowid.rowid_type(rowid) from t1;

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

SQL> 看类型 0是受限,1是扩展
扩展的ROWID 格式
OOOOOO FFF BBBBBB RRR
数据对象编号相关文件编号块编号行编号       8I

• 受限的ROWID 格式
BBBBBBBB RRRR FFFF
块编号行编号文件编号  早期

~~~~~~~~~~~~

SQL> select dbms_rowid.rowid_object(rowid) from t1;

DBMS_ROWID.ROWID_OBJECT(ROWID)~~~~~~~~~~~返回objd
------------------------------
                         54214
SQL> select dbms_rowid.rowid_row_number(rowid) from t1;~~~~~~返回block中行号

DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
----------------------------------
                                10

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

DBMS_ROWID.ROWID_T
------------------
000001BF.000A.0004~~~~~~~~~扩展rowid转换为 受限rowid

SQL> select dbms_rowid.rowid_to_extended('000001BF.000A.0004','XH','T1',0) from
dual
  2  ;                               受限ROWID,SCHEMA,TABLE,TYPE

DBMS_ROWID.ROWID_T
------------------
AAANPGAAEAAAAG/AAK       受限rowid 转 扩展

~SQL> select dbms_rowid.rowid_verify('000001BF.000A.0004','XH','T1',0) from dual
  2  ;

DBMS_ROWID.ROWID_VERIFY('000001BF.000A.0004','XH','T1',0)
---------------------------------------------------------
                                                        0
检查受限能否转成扩展 0 可以1不可以

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

转载于:http://blog.itpub.net/12020513/viewspace-611512/