DBMS_ROWID包的使用（查询rowid资料）！

最新推荐文章于 2025-06-21 00:00:00 发布

原创最新推荐文章于 2025-06-21 00:00:00 发布 · 6k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#object #integer #schema #function #file

DBMS包专栏收录该内容

14 篇文章

订阅专栏

本文详细介绍了DBMS_ROWID包的功能，包括如何通过ROWID查看表中记录的信息，如所属对象、块等，并提供了创建ROWID、解析ROWID、验证ROWID等操作的SQL函数实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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的类型进行修改

下面是包体定义：

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;