What is rowid in Oralce?

ROWID in oracle is a pseudo column which returns the actual physical address of a record in a table. It contains the object_id, file_id, block_id, and row number that uniquely identifies a row in the table.

No two records in database can have same rowid.

Format is object.file.block.row

Data object id is an identification number assigned to every database segment.

file id is a hexadecimal string identifying the database file containing the row. The first datafile has the number 1.

block id is a hexadecimal string identifying the data block of the datafile containing the row.

row number is a four-digit hexadecimal string identifying the row in the data block. The first row of the block has a digit of 0.

You can fetch rowid for a record of a table by selecting ROWID as column list from the table like :

SQL>select Rowid,a.* from emp_sal a;

SQL>

ROWID ID NAME SALARY DEPT

AAARBGAAEAAAJjdAAA 1 Salim 29999 IT

AAARBGAAEAAAJjdAAB 2 kamlesh 5000 Admin

AAARBGAAEAAAJjdAAC 3 Alok 30000 HR

AAARBGAAEAAAJjdAAD 4 Suresh 70000 IT

AAARBGAAEAAAJjdAAE 5 Vikash 40000 HR

AAARBGAAEAAAJjdAAF 6 Shyam 50000 IT

AAARBGAAEAAAJjdAAG 7 Vijay 120000 Admin

Here one thing is very common that rowid for each record have length of 18 character. Let me explain the format for value “AAARBGAAEAAAJjdAAG”

The first 6 characters (AAARBG) represent the Data Object Id. Identifies the segment, where the database object is stored.

The next 3 characters (AAE) represent the data file, which contain the record.

The next 6 characters (AAAJjd) represent the datablock in the datafile containing the record.

The last 3 characters(AAG) represent the row number in the block.

If you have the ROWID of the record then you can select the record based on the ROWID like:

SQL>SELECT * FROM Emp_Sal WHERE ROWID = 'AAARBGAAEAAAJjdAAN';

**Remember you always need to enclose the ROWID in single quotes.

**ROWID is also available as a datatype in oracle. You can create table with a column having datatype as ROWID, which can contain the rowid of that record. Oracle validate the data to be stored in rowid column should be a ROWID of any record, but it's not necessary that rowid column of a record need to have the physical rowid of that cloumn only.

Below sql statement can be used to create a table with a column having datatype ROWID.

CREATE TABLE test (ID NUMBER, NAME VARCHAR2(100), RECID ROWID);

Since the rowid will be available once you have inserted a row in table, So at the time of record insertation ROWID for that record will not be available, later on you can update that like :

SQL>insert into test(id,name) values(1,'kamlesh');

SQL>1 rows inserted.

SQL>UPDATE test T SET RECID = T.ROWID

SQL>1 rows updated.

** Oracle indexes store ROWID information for fast data retrival.

The most frequently asked question regarding ROWID are :

1. Can ROWID be changed for a reocrd in database?

ROWID in database can be changed in many cases when a record is physically moved.

For example we are performing

ALTER TABLE Table_Name MOVE;

FLASHBACK TABLE Table_Name;

Export and Import tables.

Spliting a partition or combining two partitions.

2. Can ROWID be used for primary key? If not, Why?

ROWID can't be used as primary key column even if we have a column to contain the ROWID of each record because ROWID is not available at the time of record insertation and initially it will be NULL where as a primary key can't have NULL values.

3. If I delete a record from a table and insert the same againg. Will the rowid be same?

It's not predictable, in most of the cases the rowid changed.