BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

Oracle ROWID_INFO Procedure

Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.