ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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. ;-)
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;
pragma RESTRICT_REFERENCES(rowid_create,WNDS,RNDS,WNPS,RNPS);
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');
pragma RESTRICT_REFERENCES(rowid_info,WNDS,RNDS,WNPS,RNPS);
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;
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;
pragma RESTRICT_REFERENCES(rowid_to_absolute_fno,WNDS,WNPS,RNPS);
SELECT dbms_rowid.rowid_to_absolute_fno(rowid, 'UWCLASS', 'BOWIE_STUFF')
FROM bowie_stuff;
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;
pragma RESTRICT_REFERENCES(rowid_to_extended,WNDS,WNPS,RNPS);
SELECT rowid, dbms_rowid.rowid_to_extended(rowid,'UWCLASS','BOWIE_STUFF',1)
FROM bowie_stuff;
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;
pragma RESTRICT_REFERENCES(rowid_verify,WNDS,WNPS,RNPS);
SELECT rowid, dbms_rowid.rowid_type(rowid)
FROM bowie_stuff;
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
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;
