Oracle - ROWID

Oracle - ROWID

Let’s play with ROWID. (ROWID pseudocolumn returns the address of the row)

select rowid, d from AJ.table1 ;

ROWID              D

------------------ --------------------

AAAZU6AAEAAAAJ9AAA D_100

AAAZU6 is the object ID

AAE is the file number

AAAAJ9 is the block number

AAA is the row number

ROWIDs are a special data type not a string. So, we need to use the ROWIDTOCHAR() function. The information in a ROWID gives Oracle everything it needs to find your row, the disk number, the cylinder, block and offset into the block.

You can get meaningful information from rowid using functions in package DBMS_ROWID.

Example:

[oracle@testing ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 27 02:44:35 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> col owner for a12

col object_name for a20

col object_type for a15

col file_name for a45

col tablespace_name for a12

set lines 200 pages 99

select rowid, d from aj.table1 ;

SQL>

ROWID              D

------------------ --------------------

AAAZU6AAEAAAAJ9AAA D_100

AAAZU6AAEAAAAJ+AAA d_101

AAAZU6AAEAAAAJ+AAB d_102

SQL> Select

dbms_rowid.rowid_object('AAAZU6AAEAAAAJ9AAA') dobjid,

dbms_rowid.rowid_relative_fno('AAAZU6AAEAAAAJ9AAA') rfno,

dbms_rowid.rowid_block_number('AAAZU6AAEAAAAJ9AAA') blockno,

dbms_rowid.rowid_row_number('AAAZU6AAEAAAAJ9AAA') rowno

from dual;  2    3    4    5    6 

    DOBJID       RFNO    BLOCKNO      ROWNO

---------- ---------- ---------- ----------

    103738          4        637          0

SQL> select do.owner,do.object_id, do.object_name, do.object_type, ddf.file_name, ddf.tablespace_name

from dba_objects do, dba_data_files ddf

where do.DATA_OBJECT_ID = (select dbms_rowid.rowid_object('AAAZU6AAEAAAAJ9AAA') from dual)

and relative_fno=(SELECT dbms_rowid.rowid_relative_fno('AAAZU6AAEAAAAJ9AAA') FROM TABLE1 where rownum=1);  2    3    4 

OWNER         OBJECT_ID OBJECT_NAME          OBJECT_TYPE     FILE_NAME                                     TABLESPACE_N

------------ ---------- -------------------- --------------- --------------------------------------------- ------------

AJ               103629 TABLE1               TABLE           /u03/app/oracle/oradata/LONDON/users01.dbf    USERS

SQL>

A rowid is assigned to a row when you insert the row and will never changed unless the row is deleted and re-inserted.

A rowid can change on following conditions:

- if you have set "enable row movement" clause on the table.

- table move using 'alter table move'

- if update of partition key causes row to move to different partition

- alter table shrink space compact

- flashback table

- export import (So you should not use rowid in your table as key value)