DBMS_ROWID包的使用

dbms_rowid包可以通过表中各条记录的唯一rowid号，来查看这条记录的信息，例如所属object、block等等。这个包很简单，但是对于确定当前行的信息值还是比较有用的。而dbms_rowid包的function可以在SQL中直接使用，下面先找一个rowid出来。

SQL> select rowid,object_name,object_id from t1 where rownum = 1; 

ROWID           　　   OBJECT_NAME           OBJECT_ID 

------------------ 　　-------------------- ----------

AAAR7jAAEAAAAC7AAA　 ICOL$                        20

 

1、dbms_rowid.rowid_create             --输入相应信息后自己创建一个ROWID并返回，主要用于测试比对

2、dbms_rowid.rowid_info               --返回ROWID确定的各种信息

3、dbms_rowid.rowid_type               --返回ROWID类型(restricted or extended)

rowid_type_restricted constant integer := 0;
rowid_type_extended   constant integer := 1;

SQL> select dbms_rowid.rowid_type('AAAR7jAAEAAAAC7AAA') from dual;

DBMS_ROWID.ROWID_TYPE('AAAR7JAAEAAAAC7AAA') 
------------------------------------------- 
                                          1

4、dbms_rowid.rowid_object             --返回该ROWID对应的OBJECT的OBJ#

SQL> select dbms_rowid.rowid_object('AAAR7jAAEAAAAC7AAA') from dual; 


DBMS_ROWID.ROWID_OBJECT('AAAR7JAAEAAAAC7AAA') 

---------------------------------------------

                                        73443 
SQL> select owner,object_name from all_objects where data_object_id = 73443; 

OWNER                          OBJECT_NAME 

------------------------------ --------------------
U1                             T1 

5、dbms_rowid.rowid_relative_fno       --返回该ROWID对应的对应文件号

SQL> select dbms_rowid.rowid_relative_fno('AAAR7jAAEAAAAC7AAA') from dual; 

DBMS_ROWID.ROWID_RELATIVE_FNO('AAAR7JAAEAAAAC7AAA') 

---------------------------------------------------

                                                  4 

SQL> select file_name,file_id,tablespace_name from dba_data_files where file_id = 4; 

 
FILE_NAME                                   FILE_ID TABLESPACE_NAME 
---------------------------------------- ---------- ------------------------------

+DG/orcl/datafile/users.259.773991911             4 USERS  

SQL> select owner,table_name,tablespace_name from dba_tables where table_name = 'T1';  

OWNER                          TABLE_NAME                     TABLESPACE_NAME 

------------------------------ ------------------------------ ------------------------------
U1                             T1                             USERS 

6、dbms_rowid.rowid_block_number       --返回该ROWID所在的BLOCK号

SQL> select dbms_rowid.rowid_block_number('AAAR7jAAEAAAAC7AAA') from dual; 

DBMS_ROWID.ROWID_BLOCK_NUMBER('AAAR7JAAEAAAAC7AAA') 

---------------------------------------------------
                                                187 

7、dbms_rowid.rowid_row_number         --返回该行数据在BLOCK中的相对位置

SQL> select dbms_rowid.rowid_row_number('AAAR7jAAEAAAAC7AAA') from dual; 

DBMS_ROWID.ROWID_ROW_NUMBER('AAAR7JAAEAAAAC7AAA') 

-------------------------------------------------
                                                0 

8、dbms_rowid.rowid_to_absolute_fno    --返回相关的完全数据文件号

SQL> select dbms_rowid.rowid_to_absolute_fno('AAAR7jAAEAAAAC7AAA','U1','T1') from dual; 

DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO('AAAR7JAAEAAAAC7AAA','U1','T1') 

----------------------------------------------------------------
                                                               4 

9、dbms_rowid.rowid_to_extended        --将restricted类型的ROWID修改为extended
10、dbms_rowid.rowid_to_restricted      --将extended类型的ROWID修改为restricted
11、dbms_rowid.rowid_verify             --查看是否可以对ROWID的类型进行修改