Understanding ROWID In Oracle Database

db_rowid

Every record has a unique ROWID (pseudocolumn) within a database representing the physical location on disk where the record lives.

The Format of the ROWID is HEXADECIMAL.

Sample ROWID – AAAXFxAAHAAAaztAAO

  • AAAXFx – Data Object Number
  • AAH – Relative File Number
  • AAAazt – Block Number
  • AAO – Row Number
SQL> SELECT ROWID,E.* FROM EMP E;

ROWID                   EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
------------------ ---------- ---------- --------- ---------- --------- ---------- ---------- ----------
AAAXFxAAHAAAaztAAO       7839 KING       PRESIDENT            17-NOV-81       5000                    10
AAAXFxAAHAAAaztAAP       7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
AAAXFxAAHAAAaztAAQ       7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
AAAXFxAAHAAAaztAAR       7566 JONES      MANAGER         7839 02-APR-81       2975                    20
AAAXFxAAHAAAaztAAS       7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
AAAXFxAAHAAAaztAAT       7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
AAAXFxAAHAAAaztAAU       7369 SMITH      CLERK           7902 17-DEC-80        800                    20
AAAXFxAAHAAAaztAAV       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
AAAXFxAAHAAAaztAAW       7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
AAAXFxAAHAAAaztAAX       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
AAAXFxAAHAAAaztAAY       7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
AAAXFxAAHAAAaztAAZ       7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
AAAXFxAAHAAAaztAAa       7900 JAMES      CLERK           7698 03-DEC-81        950                    30
AAAXFxAAHAAAaztAAb       7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

ROWIDTOCHAR

In Oracle Database, the ROWIDTOCHAR function converts a ROWID value to VARCHAR2 data type.

CHARTOROWID

In Oracle Database, the CHARTOROWID function converts a string value to ROWID data type.

SQL> SELECT ROWID,ROWIDTOCHAR(ROWID),EMPNO,ENAME,DEPTNO FROM EMP;

ROWID              ROWIDTOCHAR(ROWID)      EMPNO ENAME          DEPTNO
------------------ ------------------ ---------- ---------- ----------
AAAXFxAAHAAAaztAAO AAAXFxAAHAAAaztAAO       7839 KING               10
AAAXFxAAHAAAaztAAP AAAXFxAAHAAAaztAAP       7698 BLAKE              30
AAAXFxAAHAAAaztAAQ AAAXFxAAHAAAaztAAQ       7782 CLARK              10
AAAXFxAAHAAAaztAAR AAAXFxAAHAAAaztAAR       7566 JONES              20
AAAXFxAAHAAAaztAAS AAAXFxAAHAAAaztAAS       7788 SCOTT              20
AAAXFxAAHAAAaztAAT AAAXFxAAHAAAaztAAT       7902 FORD               20
AAAXFxAAHAAAaztAAU AAAXFxAAHAAAaztAAU       7369 SMITH              20
AAAXFxAAHAAAaztAAV AAAXFxAAHAAAaztAAV       7499 ALLEN              30
AAAXFxAAHAAAaztAAW AAAXFxAAHAAAaztAAW       7521 WARD               30
AAAXFxAAHAAAaztAAX AAAXFxAAHAAAaztAAX       7654 MARTIN             30
AAAXFxAAHAAAaztAAY AAAXFxAAHAAAaztAAY       7844 TURNER             30
AAAXFxAAHAAAaztAAZ AAAXFxAAHAAAaztAAZ       7876 ADAMS              20
AAAXFxAAHAAAaztAAa AAAXFxAAHAAAaztAAa       7900 JAMES              30
AAAXFxAAHAAAaztAAb AAAXFxAAHAAAaztAAb       7934 MILLER             10

14 rows selected.

SQL> SELECT ROWID,EMPNO,ENAME,DEPTNO FROM EMP WHERE ROWID = CHARTOROWID('AAAXFxAAHAAAaztAAO');

ROWID                   EMPNO ENAME          DEPTNO
------------------ ---------- ---------- ----------
AAAXFxAAHAAAaztAAO       7839 KING               10

DBMS_ROWID

The DBMS_ROWID package lets you create ROWIDs and obtain information about ROWIDs from PL/SQL programs and SQL statements.

You can find the data block number, the object number, and other ROWID components without writing code to interpret the base-64 character external ROWID.

SQL> SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) "DATA OBJECT NUMBER",
  2         DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) "RELATIVE FILE NUMBER",
  3         DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) "BLOCK NUMBER",
  4         DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) "ROW NUMBER"
  5  FROM DUAL;

DATA OBJECT NUMBER RELATIVE FILE NUMBER BLOCK NUMBER ROW NUMBER
------------------ -------------------- ------------ ----------
               143                    1         1425          0

SQL> SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) "DATA OBJECT NUMBER",
  2         DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) "RELATIVE FILE NUMBER",
  3         DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) "BLOCK NUMBER",
  4         DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) "ROW NUMBER"
  5  FROM EMP
  6  ORDER BY "ROW NUMBER";

DATA OBJECT NUMBER RELATIVE FILE NUMBER BLOCK NUMBER ROW NUMBER
------------------ -------------------- ------------ ----------
             94577                    7       109805         14
             94577                    7       109805         15
             94577                    7       109805         16
             94577                    7       109805         17
             94577                    7       109805         18
             94577                    7       109805         19
             94577                    7       109805         20
             94577                    7       109805         21
             94577                    7       109805         22
             94577                    7       109805         23
             94577                    7       109805         24
             94577                    7       109805         25
             94577                    7       109805         26
             94577                    7       109805         27

14 rows selected.
SQL> SET AUTOTRACE ON EXPLAIN

SQL> CREATE INDEX emp_indx1 ON emp(ename);

Index created.

SQL> ANALYZE INDEX emp_indx1 VALIDATE STRUCTURE;

Index analyzed.

SQL> EXPLAIN PLAN FOR SELECT * FROM emp WHERE ename = 'KING';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2637531874

-------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP       |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME"='KING')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMP"."EMPNO"[NUMBER,22], "ENAME"[VARCHAR2,10], "EMP"."JOB"[VARCHAR2,9],
       "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
       "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]
   2 - "EMP".ROWID[ROWID,10], "ENAME"[VARCHAR2,10]

28 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |  8168 | 16336 |    29   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

One thought on “Understanding LOCKING In Database”

  1. SQL UPDATE hr.employees SET salary=salary; 107 rows updated. SQL SELECT XID, STATUS FROM V$TRANSACTION; XID STATUS —————- —————- 0800090033000000 ACTIVE SQL ROLLBACK; Rollback complete. SQL SELECT XID FROM V$TRANSACTION; no rows selected SQL UPDATE hr.employees SET last_name=last_name; 107 rows updated. SQL SELECT XID, STATUS FROM V$TRANSACTION; XID STATUS —————- —————- 0900050033000000 ACTIVE Oracle Database supports statement-level atomicity, which means that a SQL statement is an atomic unit of work and either completely succeeds or completely fails.

Leave a Reply

Your email address will not be published. Required fields are marked *