I was diving into some details of the Runtime Audit Browser for the course I just created about Administration and Operation. As I was digging into some of the trace data displayed in the RAB I noticed that I had never actually used the “ROWKEY” here to lookup the actual database rows in question.


Example, some row has had an error during processing and an error message is generated here along with it’s ROWKEY.

When I saw the ROWKEY I realized I had not idea how to use it to find the actual row. The ROWKEY appears to be the record number within the cursor, which as most readers know means that you can’t actually use it to get to the physical row. I perused the OWB public runtime views and did not find anything useful there, as the only information provided was still this ROWKEY. I did some digging and found an undocumented VIEW that helps track down and find the Oracle ROWID that can be used to find the row in question.

Login to the OWB Runtime Repository owner (or some other user that has access to these views). Track down the execution audit id, which you can use the following query with your mapping name.

Next, use the following query (or some derivative) to generate a report with the cursor keys and the actual oracle ROWIDs. Note: This uses a view that is not part of the public OWB API so you should know that this might break at any point since Oracle has not made them an actual public interface.

You now have your ROWID for the trace rows… You can query for the actual row by using ROWID in Oracle by using a query like this:

Did you notice above, that in the trace data it was only the DML cursors that had ROW_IDENTs? What a bummer! If the DML succeeded and the row was inserted then there would probably not be a need to track it down using this method. Clearly this is of limited use since OWB appears not to track the ROWID of the SELECT cursors. If anyone has had any better luck on tracking down the SELECT ROWID (or other method to identify the errored rows) I’d love to hear from you, and I’d be happy to post it here.

I put togther a three day training for a large US wireless company to help their Operations, System Administrators, and DBAs understand and administer an OWB Data Warehouse solution. These topics are covered only partially in the Oracle University course, and rightfully so. OWB is primarily a development tool and most people who use it are “developers;” some however are charged primarily with administering, testing, and managing OWB solutions. This course, offered only for private onsite trainings, is perfect for these individuals.

Here’s a PDF with the course outline, and sample slides. The training manual is hundreds of pages worth of slides, notes, and hands on exercises. If your company is interested in developing knowledge about OWB in your DBA, Operations, and System Administrators please email me to discuss bringing this course to your company.

This course is for Operators, Administrators, and Quality Assurance staff for organizations that utilize OWB. Students will learn the fundamental architecture of OWB, its repository structures, and how mappings are built (logically and physically). Students learn how to import, export, and sample metadata along with view metadata reports through the web. Students learn how to execute mappings from within OWB, and also from SQL scripts and Enterprise Manager.

Students will learn how to generate common trace files/plans for OWB mappings so they can leverage their existing knowledge and Oracle tuning skills. Students will understand the security features provided in OWB and what steps should be considered to help them secure their OWB installation. Students will also learn how to build powerful process flows, email notifications, error dispatching etc.