Systems Engineering and RDBMS

DBMS_ROWID

Posted by decipherinfosys on August 20, 2008

DBMS_ROWID is one of the supplied PL/SQL packages, which can be used to manipulate and retrieve rowid information. Rowid is one of the most famous pseudo columns along with the rownum. Rowid uniquely identifies the row in the table. Rowid can be used as data type as well. We will cover rowid/urowid and its usage in different blog post, but in this blog post, we will talk about DBMS_ROWID package and some of its useful subprograms. Using these subprograms, we can retrieve object number, file number, block number and row number information we are interested in. Let us first create table and populate it with some data.

CREATE TABLE TEST
(
 ID            NUMBER(9),
 OBJ_TYPE   VARCHAR(5),
 OBJ_NAME     VARCHAR(30),
 CREATE_DATE  DATE
);

INSERT INTO TEST(id,obj_Type,obj_name,create_Date)
SELECT object_id,object_Type,object_name,sysdate
  FROM user_objects
 WHERE object_Type = ‘TABLE’
   AND ROWNUM <= 5;

Commit;

Now let us retrieve rowid for the inserted rows.

SELECT ROWID, id,
       dbms_rowid.rowid_type(ROWID) rowid_type,
       dbms_rowid.rowid_object(ROWID) object_no
  FROM test;

ROWID                      ID ROWID_TYPE  OBJECT_NO
------------------ ---------- ---------- ----------
AABHPOABWAAAxUAAAA     242439          1     291790
AABHPOABWAAAxUAAAB     242441          1     291790
AABHPOABWAAAxUAAAC     242443          1     291790
AABHPOABWAAAxUAAAD     242445          1     291790
AABHPOABWAAAxUAAAE     242447          1     291790

 

This is not usual ROWID format. We know that ROWID has block.row.file format (BBBBBBB.RRRR.FFFFF).  This is extended ROWID format. In above sql, we have used rowid_type function to know the ROWID type. They are of two types. 1 indicates extended rowid (Oracle 8i and higher) and 0 indicated restricted rowid (Oracle 7 and less). One can convert extended rowids to restricted rowid (block.row.file) format.

Let us execute following sql to obtain restricted and extended rowids both.
SELECT id, dbms_rowid.rowid_to_restricted(ROWID,0) RESTRICTED ,
       dbms_rowid.rowid_to_extended
    ((dbms_rowid.rowid_to_restricted(ROWID,0)),
     ‘SCOTT’,’TEST’,1) extended
  FROM test;

        ID RESTRICTED         EXTENDED
---------- ------------------ ------------------
    242439 00031500.0000.0056 AABHPOABWAAAxUAAAA
    242441 00031500.0001.0056 AABHPOABWAAAxUAAAB
    242443 00031500.0002.0056 AABHPOABWAAAxUAAAC
    242445 00031500.0003.0056 AABHPOABWAAAxUAAAD
    242447 00031500.0004.0056 AABHPOABWAAAxUAAAE

 

Other useful function is dbms_rowid.rowid_verify, which verifies the validity of restricted rowid to make sure that it can be converted to extended rowid. It returns 0, if conversion is possible otherwise returns 1.

Three other important functions are to obtain the block number, row number and absolute file number from the rowid. Following is the sql followed by data.

SELECT dbms_rowid.rowid_block_number(ROWID) Block_No,
       dbms_rowid.rowid_row_number(ROWID) Row_No,
       dbms_rowid.rowid_to_absolute_fno(ROWID,’SCOTT’,’TEST’) Abs_File_No
  FROM TEST;

  BLOCK_NO     ROW_NO ABS_FILE_NO
---------- ---------- -----------
    201984          0          86
    201984          1          86
    201984          2          86
    201984          3          86
    201984          4          86

 

Similarly we can get the relative file number as well using dbms_rowid.rowid_relative_fno function. It takes two arguments rowid and tablespace_type it belongs to (bigfile or smallfile).  ROWID_INFO is the only procedure of dbms_rowid package, which gives all of the above information in single call.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: