Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South
and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't
make it this year, check out TidalScale at www.tidalscale.com. Be
sure to click on the Solutions link and look through the Oracle resources.
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;
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;
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;
