| General |
| Note: The execution privilege is granted
to PUBLIC. Procedures in this package run under the caller security |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsutil.sql |
| First Available |
8.0 |
| Constants |
| Name |
Data Type |
Value |
|
rowid_convert_external |
INTEGER |
1 |
|
rowid_convert_internal |
INTEGER |
0 |
|
rowid_is_invalid |
INTEGER |
1 |
|
rowid_is_valid |
INTEGER |
0 |
|
rowid_object_undefined |
INTEGER |
0 |
|
rowid_type_extended |
INTEGER |
1 |
| rowid_type_restricted |
INTEGER |
0 |
|
| Dependencies |
| DBMS_SNAPSHOT |
LTUTIL |
SDO_PRIDX |
|
| Exceptions |
| Exception Name |
Error Code |
Reason |
| ROWID_BAD_BLOCK |
-28516 |
Block is beyond end of file |
| ROWID_INVALID |
-01410 |
Invalid rowid format |
|
| Required Object Privileges |
GRANT execute ON dbms_rowid TO <schema_name>; |
| |
| ROWID_BLOCK_NUMBER |
|
This function returns the database block number for the input ROWID. |
DBMS_ROWID.ROWID_BLOCK_NUMBER (row_id IN ROWID)
RETURN NUMBER; |
SELECT dbms_rowid.rowid_block_number(rowid)
FROM bowie_stuff; |
| |
| ROWID_CREATE |
| This function lets you create a ROWID,
given the component parts as parameters. This is useful for testing ROWID operations,
because only the Oracle Server can create a valid ROWID that points to data in a database. |
| |
| ROWID_INFO |
This procedure returns information about a
ROWID, including its type (restricted or extended), and the components of the ROWID |
DBMS_ROWID.ROWID_INFO (
ts_type_in IN VARCHAR2,
row_number OUT NUMBER
block_number OUT NUMBER,
relative_fno OUT NUMBER,
object_number OUT NUMBER,
rowid_type OUT NUMBER,
rowid_in IN ROWID); |
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 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(
rowid_in IN ROWID, TS_TYPE_IN IN VARCHAR2) RETURN NUMBER; |
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); |
SELECT
dbms_rowid.rowid_to_absolute_fno(rowid, 'ABC', '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. |
| |
| ROWID_TO_RESTRICTED |
| This function converts an extended ROWID
into restricted ROWID format. |
| |
| ROWID_TYPE |
|
This function returns 0 if the ROWID is a restricted ROWID, and 1 if it is extended. |
| |
| ROWID_VERIFY |
| This function verifies the ROWID. It
returns 0 if the input restricted ROWID can be converted to extended format, given the
input schema name and table name, and it returns 1 if the conversion is not possible. |
| |
| 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 |