Oracle DBMS_ROWID
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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 LTUTIL SDO_PRIDX
DBMS_PARALLEL_EXECUTE_INTERNAL PRVT_ILM SEM_RDFCTX
DBMS_SNAPSHOT_KKXRCA    
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
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
Packages
SYS_OP_COUNTCHG
SYS_OP_RPB
What's New In 12cR1
What's New In 12cR2

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