A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

ROWID Pseudocolumn

2 min read
For each row in the database, the ROWID pseudocolumn returns the address of the row.

Every table in an Oracle database has a pseudocolumn named ROWID.

Oracle Database rowid values contain information necessary to locate a row:

• The data object number of the object
• The data block in the data file in which the row resides
• The position of the row in the data block (first row is 0)
• The data file in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid
Rowid values have several important uses:

• They are the fastest way to access a single row.
• They can show you how the rows in a table are stored.
• They are unique identifiers for rows in a table.
You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. 

If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. 

You cannot insert, update, or delete a value of the ROWID pseudocolumn

This statement selects the address of all rows that contain data for employees in department 20:
SELECT ROWID, last_name
 FROM employees
 WHERE department_id = 20;
A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. A pseudocolumn is also similar to a SQL function without arguments.

Functions without arguments typically return the same value for every row in the result set, whereas pseudocolumns typically return a different value for each row.

Values of the ROWID pseudocolumn are strings representing the address of each row.

These strings have the data type ROWID. This pseudocolumn is not evident when listing the structure of a table by executing SELECT or DESCRIBE, nor does the pseudocolumn consume space. However, the rowid of each row can be retrieved with a SQL query using the reserved word ROWID as a column name.

The following example queries the ROWID pseudocolumn to show the rowid of the row in the employees table for employee 100:
SQL> SELECT ROWID FROM employees WHERE employee_id = 100;