Friday, 15 November 2013

Oracle databse: What is rowid

Every row in every table has a physical address. The address of a row is determined from a combination of
the following:
• Datafile number
• Block number
• Location of the row within the block
• Object number

You can display the address of a row in a table by querying the ROWID pseudo-column—for example:
SQL> select rowid, emp_id from emp;
Here’s some sample output:
------------------ ----------

The ROWID pseudo-column value isn’t physically stored in the database. Oracle calculates its value when you query it. The ROWID contents are displayed as base-64 values that can contain the characters A–Z, a–z, 0–9, +, and /. You can translate the ROWID value into meaningful information via the DBMS_ROWID package. For example, to display the file number, block number, and row number in which a row is stored, issue this statement:

,dbms_rowid.rowid_relative_fno(rowid) file_num
,dbms_rowid.rowid_block_number(rowid) block_num
,dbms_rowid.rowid_row_number(rowid) row_num
from emp;

Here’s some sample output:
---------- ---------- ---------- ----------
2960       4        144 1     26
2961       4        144 1     27
You can use the ROWID value in the SELECT and WHERE clauses of a SQL statement. In most cases, the
ROWID uniquely identifies a row. However, it’s possible to have rows in different tables that are stored in
the same cluster and so contain rows with the same ROWID.

No comments:

Post a Comment

Number of Visitors