Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
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;
