oracle實驗記錄 （dbms_rowid使用）

fufuh2o發表於2009-08-06
Oracle

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章