CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_ROWID
Version 11.1
 
General
Source {ORACLE_HOME}/rdbms/admin/dbmsrwid.sql
First Available 8.0

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_RLMGR DBMS_SNAPSHOT LTUTIL SDO_PRIDX
Exceptions
Exception Name Error Code Reason
ROWID_INVALID -01410 Invalid rowid format
ROWID_BAD_BLOCK -28516 Block is beyond end of file
Required Object Privileges Execute is granted to public
 
ROWID_BLOCK_NUMBER

This function 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
This function 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

This function 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
This function extracts the row number from the ROWID 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

This function 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
This function 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
This function 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;
 
Demo
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
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
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;
 
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [70 users online]    © 2010 psoug.org