This procedure returns information about a
including its type, restricted or extended, and the components of
This is a procedure and it cannot be used in a SQL statement.
VARCHAR2 DEFAULT 'SMALLFILE',
get_rowinfo wrapper procedure is shown below.
create or replace procedure get_rowinfo(rid in
varchar2(9) := 'SMALLFILE';
obj_n, file_n, block_n, row_n, sm);
' || to_char(rid_t));
DBMS_OUTPUT.PUT_LINE('Data obj number:
' || to_char(obj_n));
' || to_char(file_n));
' || to_char(block_n));
' || to_char(row_n));
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;
Plugging the ROWID into the procedure yields the following.
SQL> exec get_rowinfo('AAAMfMAAEAAAAAgAAH');
Data obj number: 51148
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
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.
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