Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 31, 2009

Installing Oracle Block Browser and Editor tool (bbed)

By Steve Callan

What if you could directly read and manipulate data at the block level? Oracle provides such a tool to do exactly that, but you have to build it yourself. The Block Browser and Editor tool, or bbed for short, is your ticket into the contents of data blocks within an Oracle database. What can you do with bbed? You can:

  • Change data
  • Recover damaged or deleted data
  • Alter a file header
  • Corrupt and uncorrupt a block

The ability to change data is what it sounds like. You can change any data anywhere. What does this imply? It also means you can change passwords – any password. For example, you can change the password for sys.

Deleted a row and need to recover it? What happens to data when you or a user deletes it? Specifically, does the data really go away, or does something else take place? The answer is that something else takes place. Oracle marks the row (or rows) as deleted and makes the space available for use in the future. Using files in DOS, as an example, when a file was deleted, the first character of the file name was changed and the file became hidden to normal “dir” listings. Recovery tools could be used to show deleted files; you’re only real work was to figure out what the missing first character was. Recovering data in Oracle using bbed is roughly the same thing – you just have to find where the deleted row lives and reset some flags to make the row active again (assuming the row has not been overwritten yet).

On a larger scale, the same type of recovery can be done using data files. By setting values inside the file header (the file header block), you can make an older file become part of the current database.

The bbed utility also gives you the power to corrupt and uncorrupt a block (reset the corrupt block marker). Use of bbed for this purpose, although interesting, is not practical in that there are better (i.e., more established and approved) ways of repairing corrupt blocks. However, if you want to corrupt a block and test out your RMAN skills, this would be a fairly quick way to set up that lab environment.

All of the above can be done without having access to a database in terms of being logged in or having an active instance running (except for the RMAN recovery). In other words, if someone has access to bbed and access to your datafiles, that person has access to everything in your database. Everything. If that doesn’t convince you to safeguard your Oracle datafiles from unauthorized users, what will?

Installing bbed

Where and how do you get bbed? In UNIX, Oracle gives you the pieces needed to create the tool. You don’t get bbed as a live or active executable as like what you get with exp or sqlplus. In older versions of Oracle on Windows, the executable was installed ready for use, but this is no longer the case. It didn’t even have to be the RDBMS installation to get BBED.EXE (how it is named on Windows). Using an Oracle8i client installation, BBED.EXE is installed in $ORACLE_HOME/bin by default.

In a 32-bit installation on UNIX (refers to all *NIX variants), look for two object files in $ORACLE_HOME/rdbms/lib: sbbdpt.o and ssbbded.o. In a 64-bit installation, the files will be in the lib32 directory.

[oracle] ls -la *bb*.o
-rw-r--r--    1 oracle   dba          1160 Nov 18  2003 sbbdpt.o
-rw-r--r--    1 oracle   dba           848 Nov 18  2003 ssbbded.o

To create or make the executable, use the make command as shown.

[oracle] make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

Sample output from the make command is shown below.

Linking BBED utility (bbed)

rm -f /u001/app/oracle/ora904/rdbms/lib/bbed

gcc -o /u001/app/oracle/ora904/rdbms/lib/bbed -L/u001/app/oracle/ora904/rdbms/lib/
-L/u001/app/oracle/ora904/lib/  /u001/app/oracle/ora904/lib/s0main.o
/u001/app/oracle/ora904/rdbms/lib/sbbdpt.o `cat
/u001/app/oracle/ora904/lib/ldflags`    -lnsslb9 -lncrypt9 -lnsgr9 -lnzjs9 -
<some lines removed>

lcommon9 -lgeneric9  -ltrace9 -lnls9  -lcore9 -lnls9 -lcore9 -lnls9 -lxml9 -lcore9
-lunls9 -lclient9  -lvsn9 -lwtc9 -lcommon9 -lgeneric9 -lnls9  -lcore9 -lnls9
-lcore9 -lnls9 -lxml9 -lcore9 -lunls9   `cat
-Wl,-rpath,/u001/app/oracle/ora904/lib:/lib:/usr/lib -lm    `cat
/u001/app/oracle/ora904/lib/sysliblist` -ldl -lm

Sample output from a installation is shown below. The reason for showing it is to illustrate the difference between Oracle versions (9 and 10) in the flags. Put another way, there is no guarantee that you can take bbed from one version and use it on another version (you’re welcome to try, of course).

[oracle@oralinux lib]$ make -f ins_rdbms.mk $ORACLE_HOME/rdbms/lib/bbed

Linking BBED utility (bbed)
rm -f /opt/oracle/product/10.2.0/db_1/rdbms/lib/bbed
gcc -o /opt/oracle/product/10.2.0/db_1/rdbms/lib/bbed -
L/opt/oracle/product/10.2.0/db_1/rdbms/lib/ -
L/opt/oracle/product/10.2.0/db_1/lib/ -
L/opt/oracle/product/10.2.0/db_1/lib/stubs/ -L/usr/lib -lirc  
/opt/oracle/product/10.2.0/db_1/rdbms/lib/sbbdpt.o `cat 
/opt/oracle/product/10.2.0/db_1/lib/ldflags`    -lnsslb10 -lncrypt10 -lnsgr10 
<some lines removed>
-lclient10 -lnnetd10  -lvsn10 -lcommon10 -lgeneric10 -lsnls10 -lnls10  -
lcore10 -lsnls10 -lnls10 -lcore10 -lsnls10 -lnls10 -lxml10 -lcore10 -lunls10 
-lsnls10 -lnls10 -lcore10 -lnls10   `cat 
/opt/oracle/product/10.2.0/db_1/lib/sysliblist` -Wl,-
rpath,/opt/oracle/product/10.2.0/db_1/lib -lm    `cat 
/opt/oracle/product/10.2.0/db_1/lib/sysliblist` -ldl -lm   -

To confirm the creation, see if the bbed executable was created. In this example, the make command was executed in the rdbms/lib directory. You can place bbed anywhere you’d like. Also, change the permissions if needed.

-rwxr-xr-x   1 oracle   dba   434057 Aug 25 16:26 bbed

To confirm that the utility actually runs, invoke it. This example uses the 10g version, which shows release, and so does the 9.0.4 version. Aside from the change in the copyright, the release does not appear to have changed in quite some time.

[oracle@oralinux lib]$ ./bbed

BBED: Release - Limited Production on Wed Aug 27 16:17:06 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************


The “Big” Secret Behind bbed

Note that you will be prompted for a password. Virtually all of the references to bbed via a search on the Internet mention that if you are motivated enough to be using bbed in the first place, you are clever enough to determine the password on your own. Whatever. The password is blockedit. You’ll see it as BLOCKEDIT in a hex dump file of bbed. Use xxd in /usr/bin to create a dump of bbed, and then look for “BBED>” in the file. A few lines up is BLOCKEDIT.


First and foremost is this: bbed is an undocumented and unsupported (from a customer’s perspective) utility. Unless being directed to use this tool by Oracle Support, you are on your own. Do not use bbed on a production database unless you know what you are doing. Do not use bbed on any database that you cannot afford to lose. Take a backup of any database on which you are going to use this tool.

If you need to recover data and find yourself completely stymied by every other effort made so far, this is your last resort. There may be bigger and better tools out there, but the here and now tool is bbed. Should you find yourself needing to use this tool to save/rescue/recover a production database, it would be in your best interest to first take a cold backup and then take a copy of that backup as your test bed. In other words, do your work on files separate from the actual files. If you are trying to restore data, transfer it from a “rescue” instance back into the production instance.

Oracle documentation for bbed (to include looking for it on MetaLink) is almost nowhere to be found in the public domain. MetaLink note 62015.1 contains (assuming it still exists within OSS) a note that “BBED is a SUPPORT ONLY tool and should NOT be discussed with customers.” (See http://www.freelists.org/archives/oracle-l/04-2004/msg01068.html)

Nonetheless, we can glean information about this tool (and others as well) from the message library that accompanies Oracle software. $ORACLE_HOME/rdbms/mesg contains a file named bbedus.msg. You can cat or vi the file and peruse its contents to obtain an idea of how the tool works. Within the message library (towards the end) is a listing of valid positional parameters, one of which being HELP. Windows installations of Oracle still contain the message library even though BBED.EXE is no longer included.

Helpful Tools

Before you start working directly with bbed, it’s helpful to know your way around data blocks in general, including how to get internal block information by row within a table. That, and other pieces of information commonly needed include the absolute file number, the full path and name of datafiles, datafile size in blocks, data block address, block number, block size, and the block type.

You’ll need a reporting tool to output information about a block. There is more than one way to get this information, but the easiest is based on using the supplied PL/SQL built-in named DBMS_ROWID. This package (with ten functions and one procedure) has been available since at least Oracle8i days, but use of it may be new to you (how often have you dug into the internals of a data block?). Information from several functions is combined in the one procedure, which makes use of OUT parameters. Create your own wrapper procedure around DBMS_ROWID.ROWID_INFO to make it reusable. Let’s look at what the procedure contains (Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2)).

ROWID_INFO Procedure

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.


   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;
  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));

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.

In Closing

Getting bbed installed is fairly simple, and being able to use the tool once the ultra-secret password is known, opens the door to many possibilities. Just being able to see how data is stored within a file is worthwhile in and of itself, as is how Oracle tags or marks data blocks with their current status. In a future article, we’ll use a bare bones database and look at the contents of a datafile.

» See All Articles by Columnist Steve Callan

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM