| 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; |
