Oracle 12c DBMS_ROWID Demos

Oracle DBMS_ROWID Version 12.1.0.2

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	PRVT_ILM
DBMS_PARALLEL_EXECUTE_INTERNAL	LTUTIL	SDO_PRIDX

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	ROWID_RELATIVE_FNO	ROWID_TO_RESTRICTED
ROWID_CREATE	ROWID_ROW_NUMBER	ROWID_TYPE
ROWID_INFO	ROWID_TO_ABSOLUTE_FNO	ROWID_VERIFY
ROWID_OBJECT	ROWID_TO_EXTENDED	Demos

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;