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

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google