Oracle rowid Datatype

In this Oracle tutorial, we will learn about the rowid datatype in the oracle database. Also, we will illustrate how to use it to create rowid columns for a table in oracle.

Oracle rowid Datatype

The ROWID datatype in Oracle 21c stores the unique address of a row in a database table. It is 18 characters long and stored in hexadecimal format. ROWID is the fastest and most efficient way to access a specific row in a table.

A ROWID is a combination of the file number, block number, and row number, which together identify the location of a row in the database. The file number and block number identify the database block that contains the row, and the row number identifies the row within that block.

Also, check: Oracle Create Sequence Tutorial

Advantages of rowid datatype

• Unique identifier: ROWID is a unique identifier for each row in a database table. This makes it easy to identify and retrieve specific rows from a table.
• Performance: ROWID can quickly and efficiently retrieve rows from a database table. This is because ROWID stores the row’s physical location in the database, allowing the database to directly access the desired row without having to perform a full table scan.
• Security: ROWID can prevent unauthorized access to sensitive data by limiting access to specific rows in a table.
• Data Auditing: ROWID can be used to track changes to the data in a database table. This is useful for auditing purposes, as it provides a history of the changes made to the data.
• Data Migration: ROWID can migrate data from one database table to another. It provides a unique identifier for each row, allowing the data to be moved without losing the relationship between the rows.

Read: How to add a column to a table in Oracle

Syntax

The syntax for declaring a column of rowid data type in Oracle 21c is as follows:

column_name ROWID;

Here, column_name is the column’s name and ROWID is the data type.

Also, check: How to backup a table in Oracle

Example

Here’s an example of how you can create a table with a rowid column in Oracle 21c.

CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(50),
  emp_salary NUMBER
);

We will insert data into a table with a rowid column using the INSERT statement.

INSERT INTO employees (emp_id, emp_name, emp_salary)
VALUES (1, 'John Doe', 50000);
SELECT ROWID, emp_id, emp_name, emp_salary
FROM employees
WHERE emp_id = 3421;

Example:

Now we will use the rowid datatype in a SQL developer tool.

INSERT INTO students (student_id, student_name, student_marks)
VALUES (6732, 'Micheal', 382);
INSERT INTO students (student_id, student_name, student_marks)
VALUES (3421, 'John', 356);

SELECT ROWID, student_id, student_name, student_marks
FROM students
WHERE student_id = 3421;

Features of Rowid Datatype

A ROWID is a combination of the file number, block number, and row number, which together identify the location of a row in the database. The file number and block number identify the database block that contains the row, and the row number identifies the row within that block.

When a database record in a table is inserted into the database, it is assigned a unique ROWID value. The ROWID value remains the same for the record’s lifetime unless the record is deleted and re-inserted into the database, in which case a new ROWID value is assigned.

The ROWID data type can be used in SQL statements to access database records efficiently. For example, instead of using a complex WHERE clause to identify a record, the ROWID value can be used to directly access the record in the database.

Conclusion

In this tutorial, we have understood how to define and use the blob Data type in Oracle 21c Database and covered a few sample examples related to it.

Also, take a look at some more Oracle tutorials.

• Char Datatype in Oracle Database
• Oracle Varchar2 Datatype
• Oracle Binary_double Datatype
• Dec Datatype in Oracle Database