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 10DBMS_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 |
---------------------------------------------------------------------------------------------
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.