For how many years have you been working
with physical servers that are starving your database of the memory
necessary to deploy important new performance features such as the Result
Cache, Memoptimize Pool, In-Memory Aggregation, In-Memory Column Store, and
Full Database Caching? Too long? Contact me to learn how to improve all
queries ... not just some queries.
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;
