DBMS_ROWID包的使用（查询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')---------------------------------------------                                        73443SQL> 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')---------------------------------------------------                                                  4SQL> 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 USERSSQL> 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的类型进行修改

下面是包体定义：

create or replace package dbms_rowid is  ------------  --  OVERVIEW  --  --  This package provides procedures to create ROWIDs and to interpret  --  their contents  --  SECURITY  --  --  The execution privilege is granted to PUBLIC. Procedures in this  --  package run under the caller security.  ----------------------------  ----------------------------  --  ROWID TYPES:  --  --   RESTRICTED - Restricted ROWID  --  --   EXTENDED   - Extended ROWID  --  rowid_type_restricted constant integer := 0;  rowid_type_extended   constant integer := 1;  --  ROWID VERIFICATION RESULTS:  --  --   VALID   - Valid ROWID  --  --   INVALID - Invalid ROWID  --  rowid_is_valid   constant integer := 0;  rowid_is_invalid constant integer := 1;  --  OBJECT TYPES:  --  --   UNDEFINED - Object Number not defined (for restricted ROWIDs)  --  rowid_object_undefined constant integer := 0;  --  ROWID CONVERSION TYPES:  --  --   INTERNAL - convert to/from column of ROWID type  --  --   EXTERNAL - convert to/from string format  --  rowid_convert_internal constant integer := 0;  rowid_convert_external constant integer := 1;  --  EXCEPTIONS:  --  -- ROWID_INVALID  - invalid rowid format  --  -- ROWID_BAD_BLOCK - block is beyond end of file  --  ROWID_INVALID exception;     pragma exception_init(ROWID_INVALID, -1410);  ROWID_BAD_BLOCK exception;     pragma exception_init(ROWID_BAD_BLOCK, -28516);  --  PROCEDURES AND FUNCTIONS:  --  --  -- ROWID_CREATE constructs a ROWID from its constituents:  --  -- rowid_type - type (restricted/extended)  -- object_number - data object number (rowid_object_undefined for restricted)  -- relative_fno - relative file number  -- block_number - block number in this file  -- file_number - file number in this block  --  function rowid_create(rowid_type IN number,                        object_number IN number,                        relative_fno IN number,                        block_number IN number,                        row_number IN number)                        return rowid;  pragma RESTRICT_REFERENCES(rowid_create,WNDS,RNDS,WNPS,RNPS);  --  -- ROWID_INFO breaks ROWID into its components and returns them:  --  -- rowid_in - ROWID to be interpreted  -- rowid_type - type (restricted/extended)  -- object_number - data object number (rowid_object_undefined for restricted)  -- relative_fno - relative file number  -- block_number - block number in this file  -- file_number - file number in this block  -- ts_type_in - type of tablespace which this row belongs to  --              'BIGFILE' indicates Bigfile Tablespace  --              'SMALLFILE' indicates Smallfile (traditional pre-10i) TS.  --              NOTE: These two are the only allowed values for this param  --  procedure rowid_info( rowid_in IN rowid,                        rowid_type OUT number,                        object_number OUT number,                        relative_fno OUT number,                        block_number OUT number,                        row_number OUT number,                        ts_type_in IN varchar2 default 'SMALLFILE');  pragma RESTRICT_REFERENCES(rowid_info,WNDS,RNDS,WNPS,RNPS);  --  -- ROWID_TYPE returns the type of a ROWID (restricted/extended_nopart,..)  --  -- row_id - ROWID to be interpreted  --  function rowid_type(row_id IN rowid)                        return number;  pragma RESTRICT_REFERENCES(rowid_type,WNDS,RNDS,WNPS,RNPS);  --  -- ROWID_OBJECT extracts the data object number from a ROWID.  -- ROWID_OBJECT_UNDEFINED is returned for restricted rowids.  --  -- row_id - ROWID to be interpreted  --  function rowid_object(row_id IN rowid)                        return number;  pragma RESTRICT_REFERENCES(rowid_object,WNDS,RNDS,WNPS,RNPS);  --  -- ROWID_RELATIVE_FNO extracts the relative file number from a ROWID.  --  -- row_id - ROWID to be interpreted  -- ts_type_in - type of tablespace which this row belongs to  --  function rowid_relative_fno(row_id IN rowid,                              ts_type_in IN varchar2 default 'SMALLFILE')                        return number;  pragma RESTRICT_REFERENCES(rowid_relative_fno,WNDS,RNDS,WNPS,RNPS);  --  -- ROWID_BLOCK_NUMBER extracts the block number from a ROWID.  --  -- row_id - ROWID to be interpreted  -- ts_type_in - type of tablespace which this row belongs to  --  --  function rowid_block_number(row_id IN rowid,                              ts_type_in IN varchar2 default 'SMALLFILE')                        return number;  pragma RESTRICT_REFERENCES(rowid_block_number,WNDS,RNDS,WNPS,RNPS);  --  -- ROWID_ROW_NUMBER extracts the row number from a ROWID.  --  -- row_id - ROWID to be interpreted  --  function rowid_row_number(row_id IN rowid)                        return number;  pragma RESTRICT_REFERENCES(rowid_row_number,WNDS,RNDS,WNPS,RNPS);  --  -- ROWID_TO_ABSOLUTE_FNO extracts the relative file number from a ROWID,  -- which addresses a row in a given table  --  -- row_id - ROWID to be interpreted  --  -- schema_name - name of the schema which contains the table  --  -- object_name - table name  --  function rowid_to_absolute_fno(row_id IN rowid,                                 schema_name IN varchar2,                                 object_name IN varchar2)                        return number;  pragma RESTRICT_REFERENCES(rowid_to_absolute_fno,WNDS,WNPS,RNPS);  --  -- ROWID_TO_EXTENDED translates the restricted ROWID which addresses  -- a row in a given table to the extended format. Later, it may be removed  -- from this package into a different place  --  -- old_rowid - ROWID to be converted  --  -- schema_name - name of the schema which contains the table (OPTIONAL)  --  -- object_name - table name (OPTIONAL)  --  -- conversion_type - rowid_convert_internal/external_convert_external  --                   (whether old_rowid was stored in a column of ROWID  --                    type, or the character string)  --  function rowid_to_extended(old_rowid IN rowid,                             schema_name IN varchar2,                             object_name IN varchar2,                             conversion_type IN integer)                        return rowid;  pragma RESTRICT_REFERENCES(rowid_to_extended,WNDS,WNPS,RNPS);  --  -- ROWID_TO_RESTRICTED translates the extnded ROWID into a restricted format  --  -- old_rowid - ROWID to be converted  --  -- conversion_type - internal/external (IN)  --  -- conversion_type - rowid_convert_internal/external_convert_external  --                   (whether returned rowid will be stored in a column of  --                    ROWID type, or the character string)  --  function rowid_to_restricted(old_rowid IN rowid,                               conversion_type IN integer)                        return rowid;  pragma RESTRICT_REFERENCES(rowid_to_restricted,WNDS,RNDS,WNPS,RNPS);  --  -- ROWID_VERIFY verifies the ROWID. It returns rowid_valid or rowid_invalid  -- value depending on whether a given ROWID is valid or not.  --  -- rowid_in - ROWID to be verified  --  -- schema_name - name of the schema which contains the table  --  -- object_name - table name  --  -- conversion_type - rowid_convert_internal/external_convert_external  --                   (whether old_rowid was stored in a column of ROWID  --                    type, or the character string)  --  function rowid_verify(rowid_in IN rowid,                        schema_name IN varchar2,                        object_name IN varchar2,                        conversion_type IN integer)                        return number;  pragma RESTRICT_REFERENCES(rowid_verify,WNDS,WNPS,RNPS);end;