DBMS_ROWID for Oracle ROWID Pseudo column Format. Structure, Purpose, Features

advertisements

_____________________________________________________________________________________________________________________

ROWID:

• It is a unique address identifier or pointer for each row to locate the row resides in the disk
• The details in the ROWID gives Oracle everything it needs to find your row, disk number, block and offset to the block
• ROWID is an 18 digit unique number and that internally represented as a base-64 number.
• Oracle internally manages the ROWID details
• You can see the ROWID values by selecting the ROWID pseudo column in select statement
• Describe table will not display the ROWID

Information contained in ROWID

• Object number of the object
• Data block in the datafile in which the row resides
• The position of the row in the data block(First row is 0)
• The datafile in which the row resides(fist file is 1) and this number is relative to the tablespaces
• Values of the ROWID pseudocolumn have the datatype ROWID or UROWID

Important use of ROWID

• ROWID is the fastest way to access a row in a table
• It shows how the rows in a table stored
• They are the unique identifiers for the rows

Some Facts of ROWID

• ROWID cannot be used as primary key
• If you delete and insert the same in a table, the ROWID may change. If you delete a row, then oracle may reassign its ROWID to a new row inserted later.
• ROWID (any pseudo column) values are not stored anywhere in the database
• You cannot insert, delete, update the ROWID column values

Structure of ROWID

First 6 characters refers to the data object number

Next 3 characters represent relative file number

Next 6 characters represent the block number

Last 3 characters refers to the row number

SQL> select rowid, DEPTNO, DNAME, LOC from dept;

ROWID                  DEPTNO DNAME          LOC

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

AABfsIAAEAAAC70AAA         10 ACCOUNTING     NEW YORK

AABfsIAAEAAAC70AAB         20 RESEARCH       DALLAS

AABfsIAAEAAAC70AAC         30 SALES          CHICAGO

AABfsIAAEAAAC70AAD         40 OPERATIONS     BOSTON

AABfsI : Object Number

AAE    : Relative file number

AAAC70 : Block number

AAD    : row number for the department id 40

DBMS_ROWID Package

DEPT is our example table and the rows in the table are shown below with rowid

SQL> select rowid, DEPTNO, DNAME, LOC from dept;

ROWID                  DEPTNO DNAME          LOC

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

AABfsIAAEAAAC70AAA         10 ACCOUNTING     NEW YORK

AABfsIAAEAAAC70AAB         20 RESEARCH       DALLAS

AABfsIAAEAAAC70AAC         30 SALES          CHICAGO

AABfsIAAEAAAC70AAD         40 OPERATIONS     BOSTON

Query to find out the file number & file name using DBMS_ROWID

SQL> SELECT dbms_rowid.rowid_to_absolute_fno('AABfsIAAEAAAC70AAD', 'SCOTT', 'DEPT') FILEID FROM DUAL;

    FILEID

----------

         4

SQL> SELECT FILE_NAME FROM DBA_DATA_FILES WHERE FILE_ID=4;

FILE_NAME

-------------------------------------------------------------/data/oracle/oradata/prod9/users01.dbf

OR

SQL>  select file_name

  2  from dba_data_files

  3  WHERE FILE_ID=dbms_rowid.rowid_to_absolute_fno('AABfsIAAEAAAC70AAD', 'SCOTT', 'DEPT');

FILE_NAME

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

/data/oracle/oradata/prod9/users01.dbf

Query to find out the block number

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

DBMS_ROWID.ROWID_BLOCK_NUMBER('AABFSIAAEAAAC70AAD')

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

                                              12020

Query to find out the row number

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

ROW_NUMBER

----------

         3

_____________________________________________________________________________________________________________________

Website Stats