Oracle DBMS_ROWID
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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 DEFINER
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 LTRIC SDO_PRIDX
DBMS_PARALLEL_EXECUTE_INTERNAL LTUTIL SEM_RDFCTX
DBMS_SNAPSHOT_KKXRCA PRVT_ILM  
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;
pragma RESTRICT_REFERENCES(rowid_block_number,WNDS,RNDS,WNPS,RNPS);
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;
pragma RESTRICT_REFERENCES(rowid_create,WNDS,RNDS,WNPS,RNPS);
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');
pragma RESTRICT_REFERENCES(rowid_info,WNDS,RNDS,WNPS,RNPS);
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;
pragma RESTRICT_REFERENCES(rowid_object,WNDS,RNDS,WNPS,RNPS);
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;
pragma RESTRICT_REFERENCES(rowid_relative_fno,WNDS,RNDS,WNPS,RNPS);
SELECT tablespace_name
FROM user_all_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;
pragma RESTRICT_REFERENCES(rowid_row_number,WNDS,RNDS,WNPS,RNPS);
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;
pragma RESTRICT_REFERENCES(rowid_to_absolute_fno,WNDS,WNPS,RNPS);
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;
pragma RESTRICT_REFERENCES(rowid_to_extended,WNDS,WNPS,RNPS);
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;
pragma RESTRICT_REFERENCES(rowid_to_restricted,WNDS,RNDS,WNPS,RNPS);
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;
pragma RESTRICT_REFERENCES(rowid_type,WNDS,RNDS,WNPS,RNPS);
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;
pragma RESTRICT_REFERENCES(rowid_verify,WNDS,WNPS,RNPS);
SELECT rowid, dbms_rowid.rowid_type(rowid)
FROM bowie_stuff;
 
Demos
Block dump based on DBMS_ROWID 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%';

conn sys@pdbdev as sysdba

ALTER SYSTEM DUMP DATAFILE 16 BLOCK 4311;

-- the following is extracted from the created trace file
-- $ORACLE_BASE/oracle/diag/rdbms/orabase/orabase/trace/orabase_ora_14868.trc


Start dump data blocks tsn: 6 file#:16 minblk 4311 maxblk 4311
Block dump from cache:
Dump of buffer cache at level 4 for pdb=3 tsn=6 rdba=67113175
BH (0x7ff8c0fd8a58) file#: 16 rdba: 0x040010d7 (16/4311) class: 1 ba: 0x7ff8c0c6e000
  set: 9 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 25,19
  dbwrid: 0 obj: 79205 objn: 79205 tsn: [3/6] afn: 16 hint: f
  hash: [0x7ff82fc94a08,0x7ff82fc94a08] lru: [0x7ff8bef96b38,0x7ff88ff59f18]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x7ff8bef96b60,0x7ff88ff59f40] objaq: [0x7ff8bef96b70,0x7ff88ff59f50]
  st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' fscn: 0x97410c tch: 2
  flags: block_written_once
  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x97410d] HSUB: [1]
  Printing buffer operation history (latest change first):
  cnt: 8
  01. sid:04 L192:kcbbic2:bic:FBD 02. sid:04 L191:kcbbic2:bic:FBW
  03. sid:04 L602:bic1_int:bis:FWC 04. sid:04 L822:bic1_int:ent:rtn
  05. sid:04 L832:oswmqbg1:clr:WRT 06. sid:04 L930:kubc:sw:mq
  07. sid:04 L913:bxsv:sw:objq 08. sid:04 L608:bxsv:bis:FBW
  09. sid:04 L607:bxsv:bis:FFW 10. sid:02 L464:chg1_mn:bic:FMS
  11. sid:02 L778:chg1_mn:bis:FMS 12. sid:02 L353:gcur:set:MEXCL
  13. sid:02 L464:chg1_mn:bic:FMS 14. sid:02 L778:chg1_mn:bis:FMS
  15. sid:02 L353:gcur:set:MEXCL 16. sid:02 L464:chg1_mn:bic:FMS
Block dump from disk:
buffer tsn: 6 rdba: 0x040010d7 (16/4311)
scn: 0x97410e seq: 0x01 flg: 0x06 tail: 0x410e0601
frmt: 0x02 chkval: 0x80ec type: 0x06=trans data

-- note above that type 06 represents a data block

Block header dump: 0x040010d7
Object id on Block? Y
seg/obj: 0x13565 csc: 0x000000000097410c itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x40010c1 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.008.00000a75 0x02401396.03e0.38 --U- 3 fsc 0x0000.0097410e
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 0xb7abe064
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0xb7abe064
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

-- Here we have the row directory information. The row slot of interest is slot 1

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
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: 6 file#: 16 minblk 4311 maxblk 4311
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 line_number = 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
Built-in Functions
Built-in Packages
SYS_OP_COUNTCHG
SYS_OP_RPB
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx