This procedure returns information about a
ROWID,
including its type, restricted or extended, and the components of
the
ROWID.
This is a procedure and it cannot be used in a SQL statement.
Syntax
DBMS_ROWID.ROWID_INFO (
rowid_in
IN
ROWID,
ts_type_in
IN
VARCHAR2 DEFAULT 'SMALLFILE',
rowid_type
OUT NUMBER,
object_number
OUT NUMBER,
relative_fno
OUT NUMBER,
block_number
OUT NUMBER,
row_number
OUT NUMBER);
A sample
get_rowinfo wrapper procedure is shown below.
create or replace procedure get_rowinfo(rid in
rowid) as
sm
varchar2(9) := 'SMALLFILE';
rid_t
number;
obj_n
number;
file_n
number;
block_n number;
row_n
number;
begin
DBMS_ROWID.ROWID_INFO(rid, rid_t,
obj_n, file_n, block_n, row_n, sm);
DBMS_OUTPUT.PUT_LINE('Type:
' || to_char(rid_t));
DBMS_OUTPUT.PUT_LINE('Data obj number:
' || to_char(obj_n));
DBMS_OUTPUT.PUT_LINE('Relative fno:
' || to_char(file_n));
DBMS_OUTPUT.PUT_LINE('Block number:
' || to_char(block_n));
DBMS_OUTPUT.PUT_LINE('Row number:
' || to_char(row_n));
end;
/
Note how placeholder variables are used for the OUT parameters and can
be directly referenced. The parameter list in the code also shows the
file type (SMALLFILE) being passed in last in the list. The
documentation shows this as the second parameter, and that is
incorrect. A describe command issued against the package shows this to
be the case as well.
Start with a boiled down test database. The example throughout the
rest of this chapter is based on a database named ORCL2 (use dbca to
create a general purpose database). Pick a sample schema such as SCOTT
to practice on. This simplifies what one has to find. The schema
contains four tables (EMP, DEPT, BONUS, and SALGRADE) and two indexes,
all contained in the USERS tablespace. Also, there is only one
database file to contend with (users01). the DBA does not care about
the other two segment names (PK_EMP and PK_DEPT) because they
represent indexes, and really, why would he want to use bbed on an
index? Remember, use of bbed is a last resort measure and there are so
many other ways of fixing or rescuing indexes that are much safer.
As an example, get the ROWID for Scott, the analyst (EMPNO=7788) from
the EMP table. If not obvious, the ROWID value that will be shown is
likely to be different.
SQL> select rowid from emp where empno=7788;
ROWID
------------------
AAAMfMAAEAAAAAgAAH
Plugging the ROWID into the procedure yields the following.
SQL> exec get_rowinfo('AAAMfMAAEAAAAAgAAH');
Type:
1
Data obj number: 51148
Relative fno:
4
Block number:
32
Row number:
7
PL/SQL procedure successfully completed.
Understanding the block number and row number within the block is
pretty straightforward. So is the relative file number of 4 and this
is easily confirmed via the following query.
SQL> select file#||' '||name||' '||bytes from
v$datafile;
FILE#||''||NAME||''||BYTES
--------------------------------------------------------
1 /opt/app/oracle/oradata/ORCL2/system01.dbf
503316480
2 /opt/app/oracle/oradata/ORCL2/undotbs01.dbf
36700160
3 /opt/app/oracle/oradata/ORCL2/sysaux01.dbf
272629760
4 /opt/app/oracle/oradata/ORCL2/users01.dbf
5242880
5 /opt/app/oracle/oradata/ORCL2/example01.dbf
104857600
This means that 1 = DATA and 2 = INDEX, so seeing the “1” is
confirmation one is working with the correct type. The data object
number is for information and is not essential, but can also be used
for confirmation. Once one knows the block and file number, one can
tie them together for the Data Block Address (dba), of which the dba
value will be used quite extensively in all future bbed commands.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
