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.

Introduction to Oracle rowid Datatype

The ROWID datatype in Oracle 21c is used to store the unique address of a row in a database table. It is stored in hexadecimal format and is 18 characters long. 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 be used to retrieve rows from a database table quickly and efficiently. This is because ROWID stores the physical location of the row in the database, allowing the database to directly access the desired row without having to perform a full table scan.
• Security: ROWID can be used to 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 be used to migrate data from one database table to another. This is because ROWID 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

Oracle rowid datatype 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 name of the column and ROWID is the data type.

Also, check: How to backup a table in Oracle

Oracle rowid datatype 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
);

Now 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.

Each database record in a table is assigned a unique ROWID value when it is inserted into the database. The ROWID value remains the same for the lifetime of the record 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 more 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

So, in this tutorial, we understood how to define and use the blob Data type in Oracle 21c Database. And we have also 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