How to use Oracle rowID

How to use Oracle rowID

1. Create a temporary table
CREATE TABLE Test_rowid (ID number, row_id rowid);
2. Insert a row of records
INSERT into TEST_ROWID values (1,null);
3. Modify the record you just inserted
Update Test_rowid Set row_id = rowid where id = 1;
4. View rowID
Select rowid,row_id from Test_rowid;
The result returned is:
rowID row_id
AAAO0DAAJAAAAMYAAA AAAO0DAAJAAAAMYAAA

Oracle's physical expansion ROWID has 18 bits, each with 64-bit encoding, represented by A~z, A~z, 0~9, +,/total 64 characters respectively. A means that 0,b 1, ... Z indicates that the 26,......Z represents the 51,0 representation of the 52,......,9, representing the 61,+ representing the 63 25,a.

The specific division of ROWID can be divided into 4 parts.

(1). Oooooo: The first 6 digits represent data OBJECT number, which matches the data_object_id in dba_objects after the conversion digits, to determine the table information.

As the data OBJECT number in the example above is aaao0d, the conversion digit is 14x64x64 +52x64 + 3.
Enter the following query:
Select owner, object_name from dba_objects where data_object_id = 14*64*64 + 52*64 + 3;
Return:
OWNER object_name
WG Test_rowid
(2) FFF: The 7th to 9th digit indicates the data file number relative to the table space.
The example above is AAJ, which represents the data file 9.
Enter the following query:
(3). BBBBBB: the 10th to 15th digit indicates that this record is in the first block in the data file.

The example above is Aaaamy, where the conversion number is 12x64+24, which means that the No. 792 block in the data file is recorded.

(4). RRR: The last 3 digits indicate that this record is the first of several records in block.

The example above is AAA, which represents the No. 0 record (always counting from 0).

Four. Reference materials

1.oracle rowID
Analysis of storage format of 2.Oracle basic data Types (IV.)--rowid type (i)
3.oracle data file Why exists rfile# and file#

Sql> CREATE TABLE EMP (EMPNO number (4) is not NULL,
2 ename VARCHAR2 (10),
3 JOB VARCHAR2 (9),
4 MGR Number (4),
5 HireDate DATE,
6 SAL Number (7, 2),
7 COMM Number (7, 2),
8 DEPTNO Number (2));

Table created.

Sql>
Sql> INSERT into EMP VALUES (7369, ' SMITH ', ' clerk ', 7902, to_date (' 17-dec-1980 ', ' dd-mon-yyyy '), 20;

1 row created.

Sql> INSERT into EMP VALUES (7499, ' ALLEN ', ' salesman ', 7698, to_date (' 20-feb-1981 ', ' dd-mon-yyyy '), 1600, 300, 30);

1 row created.

Sql> INSERT into EMP VALUES (7521, ' WARD ', ' salesman ', 7698, to_date (' 22-feb-1981 ', ' dd-mon-yyyy '), 1250, 500, 30);

1 row created.

Sql> INSERT into EMP VALUES (7566, ' JONES ', ' MANAGER ', 7839, to_date (' 2-apr-1981 ', ' dd-mon-yyyy '), 2975, NULL, 20);

1 row created.

Sql> INSERT into EMP VALUES (7654, ' MARTIN ', ' salesman ', 7698,to_date (' 28-sep-1981 ', ' dd-mon-yyyy '), 1250, 1400, 30);

1 row created.

Sql> INSERT into EMP VALUES (7698, ' BLAKE ', ' MANAGER ', 7839,to_date (' 1-may-1981 ', ' dd-mon-yyyy '), 2850, NULL, 30);

1 row created.

Sql> INSERT into EMP VALUES (7782, ' CLARK ', ' MANAGER ', 7839,to_date (' 9-jun-1981 ', ' dd-mon-yyyy '), 2450, NULL, 10);

1 row created.

Sql> INSERT into EMP VALUES (7788, ' SCOTT ', ' ANALYST ', 7566,to_date (' 09-dec-1982 ', ' dd-mon-yyyy '), 3000, NULL, 20);

1 row created.

Sql> INSERT into EMP VALUES (7839, ' KING ', ' PRESIDENT ', null,to_date (' 17-nov-1981 ', ' dd-mon-yyyy '), 5000, NULL, 10);

1 row created.

Sql> INSERT into EMP VALUES (7844, ' TURNER ', ' salesman ', 7698,to_date (' 8-sep-1981 ', ' dd-mon-yyyy '), 1500, 0, 30);

1 row created.

Sql> INSERT into EMP VALUES (7876, ' ADAMS ', ' Clerk ', 7788,to_date (' 12-jan-1983 ', ' dd-mon-yyyy '), 1100, NULL, 20);

1 row created.

Sql> INSERT into EMP VALUES (7900, ' JAMES ', ' Clerk ', 7698,to_date (' 3-dec-1981 ', ' dd-mon-yyyy '), 950, NULL, 30);

1 row created.

Sql> INSERT into EMP VALUES (7902, ' FORD ', ' ANALYST ', 7566,to_date (' 3-dec-1981 ', ' dd-mon-yyyy '), 3000, NULL, 20);

1 row created.

Sql> INSERT into EMP VALUES (7934, ' MILLER ', ' Clerk ', 7782,to_date (' 23-jan-1982 ', ' dd-mon-yyyy '), 1300, NULL, 10);

1 row created.

Sql>
Sql> CREATE TABLE DEPT (DEPTNO number (2), Dname VARCHAR2 (), LOC VARCHAR2 (13));

Table created.

Sql>
Sql> INSERT into DEPT VALUES (ten, ' ACCOUNTING ', ' NEW YORK ');

1 row created.

Sql> INSERT into DEPT VALUES (, ' The ', ' DALLAS ');

1 row created.

Sql> INSERT into DEPT VALUES (' SALES ', ' CHICAGO ');

1 row created.

Sql> INSERT into DEPT VALUES (, ' OPERATIONS ', ' BOSTON ');

1 row created.

Sql>
Sql> Select rowID from emp
2 intersect
3 Select rowID from dept;

Instance

Sql> SELECT * from Employee
2/

ID first_name last_name start_dat end_date SALARY City DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
Jason Martin 25-jul-96 25-jul-06 1234.56 Toronto Programmer
Alison Mathews 21-mar-76 21-feb-86 6661.78 Vancouver Tester
James Smith 12-dec-78 15-mar-90 6544.78 Vancouver Tester
Celia Rice 24-oct-82 21-apr-99 2344.78 Vancouver Manager
Robert Black 15-jan-84 08-aug-98 2334.78 Vancouver Tester
Linda Green 30-jul-87 04-jan-96 4322.78 New York Tester
David Larry 31-dec-90 12-feb-98 7897.78 New York Manager
James Cat 17-sep-96 15-apr-02 1232.78 Vancouver Tester

8 rows selected.

Sql>
Sql>
Sql> SELECT ROWID, ID from employee
2/

ROWID ID
------------------ ----
aaafx7aabaaakjcaaa 01
Aaafx7aabaaakjcaab 02
AAAFX7AABAAAKJCAAC 03
Aaafx7aabaaakjcaad 04
Aaafx7aabaaakjcaae 05
Aaafx7aabaaakjcaaf 06
Aaafx7aabaaakjcaag 07
Aaafx7aabaaakjcaah 08