Oracle DBMS_ROWID
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Create ROWIDs and obtain information about ROWIDs from PL/SQL programs and SQL statements. The package can find the data block number, the object number, and other ROWID components without writing code to interpret the base-64 character external ROWID.

In addition I would like to thank Richard Foote for every reference, on this page and others, to David Bowie who I never heard about before I met Richard. ;-)
AUTHID CURRENT_USER
Constants
Name Data Type Value
rowid_type_restricted INTEGER 0
rowid_type_extended INTEGER 1
rowid_is_valid INTEGER 0
rowid_is_invalid INTEGER 1
rowid_object_undefined INTEGER 0
rowid_convert_internal INTEGER 0
rowid_convert_external INTEGER 1
Dependencies
DBMS_COMPRESSION DBMS_SNAPSHOT SDO_PRIDX
DBMS_PARALLEL_EXECUTE_INTERNAL LTUTIL  
Documented Yes
Exceptions
Error Code Reason
ORA-01410 ROWID_INVALID: Invalid ROWID format
ORA-28516 ROWID_BAD_BLOCK: Block is beyond end of file
First Available 8.0
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsrwid.sql
Subprograms
 
ROWID_BLOCK_NUMBER
Returns the database block number for the input ROWID dbms_rowid.rowid_block_number(
row_id     IN ROWID,
ts_type_in IN VARCHAR2 DEFAULT 'SMALLFILE') 
RETURN NUMBER;
SELECT dbms_rowid.rowid_block_number(rowid)
FROM bowie_stuff;
 
ROWID_CREATE
Constructs a ROWID from its constituents dbms_rowid.rowid_create(
rowid_type    IN NUMBER,
object_number IN NUMBER,
relative_fno  IN NUMBER,
block_number  IN NUMBER,
row_number    IN NUMBER)
RETURN ROWID;
TBD
 
ROWID_INFO
Returns information about a ROWID, including its type (restricted or extended), and the components of the ROWID dbms_rowid.rowid_info (
rowid_in      IN  ROWID,
rowid_type    OUT NUMBER, 
object_number OUT NUMBER,
relative_fno  OUT NUMBER,
block_number  OUT NUMBER,
row_number    OUT NUMBER,
ts_type_in    IN  VARCHAR2 DEFAULT 'SMALLFILE');
CREATE TABLE test (
testcol VARCHAR2(20));

INSERT INTO test VALUES ('ABCDEFG');
COMMIT;

SELECT rowid
FROM test;

set serveroutput on

DECLARE
 ridtyp NUMBER;
 objnum NUMBER;
 relfno NUMBER;
 blno   NUMBER;
 rowno  NUMBER;
 rid    ROWID;
BEGIN
  SELECT rowid
  INTO rid
  FROM test;

  dbms_rowid.rowid_info(rid,ridtyp,objnum,relfno,blno,rowno,'SMALLFILE');

  dbms_output.put_line('Row Typ-' || TO_CHAR(ridtyp));
  dbms_output.put_line('Obj No-' || TO_CHAR(objnum));
  dbms_output.put_line('RFNO-' || TO_CHAR(relfno));
  dbms_output.put_line('Block No-' || TO_CHAR(blno));
  dbms_output.put_line('Row No-' || TO_CHAR(rowno));
END;
/
 
ROWID_OBJECT
Returns the data object number for an extended ROWID. The function returns zero if the input ROWID is a restricted ROWID. dbms_rowid.rowid_object(rowid_id IN ROWID) RETURN NUMBER;
SELECT object_id
FROM user_objects
WHERE object_name = 'BOWIE_STUFF';

SELECT dbms_rowid.rowid_object(rowid)
FROM bowie_stuff;
 
ROWID_RELATIVE_FNO
Returns the relative file number of the ROWID specified as the IN parameter. The file number is relative to the tablespace. dbms_rowid.rowid_relative_fno(
row_id     IN ROWID,
ts_type_in IN VARCHAR2 DEFAULT 'SMALLFILE')
RETURN NUMBER;
SELECT tablespace_name
FROM user_tables
WHERE table_name = 'BOWIE_STUFF';

SELECT file_id
FROM dba_data_files
WHERE tablespace_name = 'UWDATA';

SELECT dbms_rowid.rowid_relative_fno(rowid)
FROM bowie_stuff;
 
ROWID_ROW_NUMBER
Extracts the row number from the ROW_ID IN parameter dbms_rowid.rowid_row_number(row_id IN ROWID) RETURN NUMBER;
SELECT rowid, dbms_rowid.rowid_row_number(rowid)
FROM bowie_stuff;
 
ROWID_TO_ABSOLUTE_FNO
Returns the datafile number providing there are  less than 1022 datafiles dbms_rowid.rowid_to_absolute_fno(
row_id      IN ROWID,
schema_name IN VARCHAR2,
object_name IN VARCHAR2)
RETURN NUMBER;
SELECT dbms_rowid.rowid_to_absolute_fno(rowid, 'UWCLASS', 'BOWIE_STUFF')
FROM bowie_stuff;
 
ROWID_TO_EXTENDED
Translates a restricted ROWID that addresses a row in a schema and table that you specify to the extended ROWID format dbms_rowid.rowid_to_extended(
old_rowid       IN ROWID,
schema_name     IN VARCHAR2,
object_name     IN VARCHAR2,
conversion_type IN INTEGER)
RETURN ROWID;
SELECT rowid, dbms_rowid.rowid_to_extended(rowid,'UWCLASS','BOWIE_STUFF',1)
FROM bowie_stuff;
 
ROWID_TO_RESTRICTED
Converts an extended ROWID into restricted ROWID format dbms_rowid.rowid_to_restricted(
old_rowid       IN ROWID,
conversion_type IN INTEGER)
RETURN ROWID;
SELECT rowid, dbms_rowid.rowid_to_restricted(rowid, 0)
FROM bowie_stuff;
 
ROWID_TYPE
Returns 0 if the ROWID is a restricted ROWID, and 1 if it is extended dbms_rowid.rowid_type(row_id IN ROWID) RETURN NUMBER;
SELECT rowid, dbms_rowid.rowid_type(rowid)
FROM bowie_stuff;
 
ROWID_VERIFY
Verifies the ROWID. Returns 0 if the input restricted ROWID can be converted to  extended format returns 1 if the conversion is not possible dbms_rowid.rowid_type(
rowid_in        IN ROWID,
schema_name     IN VARCHAR2,
object_name     IN VARCHAR2,
conversion_type IN INTEGER)
RETURN NUMBER;
SELECT rowid, dbms_rowid.rowid_type(rowid)
FROM bowie_stuff;
 
Demos
A quick question for you, I have a couple of rows in a table which are giving the old 'integer overflow' error from time to time. I suspect the problem is data but I'm not sure. I was wondering how I can get from a ROWID to a file and block number ready for a dump. Can it be done?

Demo provided by Richard Foote
conn uwclass/uwclass@pdbdev

CREATE TABLE bowie_stuff (
album  VARCHAR2(30),
year   NUMBER,
rating VARCHAR2(30));

INSERT INTO bowie_stuff VALUES ('Man Who Sold The World', 1970, 'Bloody Good!!');

INSERT INTO bowie_stuff VALUES ('Diamond Dogs', 1974 , 'Brilliant');

INSERT INTO bowie_stuff VALUES ('Outside', 1995, 'Underrated Masterpiece');
COMMIT;

SELECT *
FROM bowie_stuff;

SELECT album,
dbms_rowid.rowid_to_absolute_fno
(rowid, 'UWCLASS', 'BOWIE_STUFF') ABSOLUTE_FNO,
dbms_rowid.rowid_block_number(rowid) BLOCKNO, dbms_rowid.rowid_row_number(rowid) ROWNUMBER
FROM bowie_stuff
WHERE album LIKE '%Dogs%';

ALTER SYSTEM DUMP DATAFILE 6 BLOCK 21229;

Start dump data blocks tsn: 7 file#: 6 minblk 21229 maxblk 21229
buffer tsn: 7 rdba: 0x018052ed (6/21229)
scn: 0x0000.0028b451 seq: 0x05 flg: 0x02 tail: 0xb4510605
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

** note above that type 06 represents a data block so it's looking good !!

Block header dump: 0x018052ed
Object id on Block? Y
seg/obj: 0xce53 csc: 0x00.28b44e itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x18052e9 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.026.00000116 0x008009a6.00bf.05 --U- 3 fsc 0x0000.0028b451
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000

** Above is the transaction slot entries. Only the one concurrent transaction on this block so far ...

data_block_dump,data header at 0xc0e1264
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x0c0e1264
bdba: 0x018052ed
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f28
avsp=0x1f10
tosp=0x1f10
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f6c
0x14:pri[1] offs=0x1f4e
0x16:pri[2] offs=0x1f28

** Note here we have the row directory information. Remember the row slot of interest is slot 1, so offset address 0x1f6d is for us. I always find counts starting at 0 a pain but I guess it's nice and efficient.

block_row_dump:
tab 0, row 0, @0x1f6c
tl: 44 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [22]
4d 61 6e 20 57 68 6f 20 53 6f 6c 64 20 54 68 65 20 57 6f 72 6c 64
col 1: [ 3] c2 14 47
col 2: [13] 42 6c 6f 6f 64 79 20 47 6f 6f 64 21 21

** and below is the row (@0x1f6d) that we're after !! As you can see, translation from b64 is a useful skill ;)

tab 0, row 1, @0x1f4e
tl: 30 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [12] 44 69 61 6d 6f 6e 64 20 44 6f 67 73
col 1: [ 3] c2 14 4b
col 2: [ 9] 42 72 69 6c 6c 69 61 6e 74
tab 0, row 2, @0x1f28
tl: 38 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 7] 4f 75 74 73 69 64 65
col 1: [ 3] c2 14 60
col 2: [22]
55 6e 64 65 72 72 61 74 65 64 20 4d 61 73 74 65 72 70 69 65 63 65
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 21229 maxblk 21229
Demo by Howard Rogers altered for a demo table.

Rows returned within a single block are not in consecutive order
conn uwclass/uwclass@pdbdev

SELECT *
FROM (
  SELECT fno, bno, rno, program_id, dr, 
  LAG(dr) OVER (PARTITION BY fno, bno ORDER BY rno) prev_dr
  FROM (
    SELECT fno, bno, rno, program_id,
    DENSE_RANK() OVER (PARTITION BY fno, bno ORDER BY program_id) dr
    FROM (
      SELECT dbms_rowid.rowid_relative_fno(rowid) fno,
      dbms_rowid.rowid_block_number(rowid) bno,
      dbms_rowid.rowid_row_number(rowid) rno, program_id
      FROM airplanes)))
WHERE dr != prev_dr
AND dr != prev_dr+1;

CREATE TABLE airbak AS
SELECT *
FROM airplanes
WHERE program_id = 737
AND lineno = 30;

DELETE FROM airplanes
WHERE program_id = 737
AND line_number = 30;

INSERT INTO airplanes
SELECT * FROM airbak;

SELECT *
FROM (
  SELECT fno, bno, rno, program_id, dr, 
  LAG(dr) OVER (PARTITION BY fno, bno ORDER BY rno) prev_dr
  FROM (
    SELECT fno, bno, rno, program_id,
    DENSE_RANK() OVER (PARTITION BY fno, bno ORDER BY program_id) dr
    FROM (
      SELECT dbms_rowid.rowid_relative_fno(rowid) fno,
             dbms_rowid.rowid_block_number(rowid) bno,
             dbms_rowid.rowid_row_number(rowid) rno, program_id
      FROM airplanes)))
WHERE dr != prev_dr
AND dr != prev_dr+1;

Related Topics
Packages
SYS_OP_COUNTCHG
SYS_OP_RPB

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved