dbms_metadata.get_ddl & ORA-31603

I wanted to allow a database user/schema called TEST (other than user with SYSDBA privileges) to have privileges to run dbms_metadata.get_ddl for any object in the database. Basically for other schemas in the database. Even though I provided execute privileges on the package and even DBA privilege to the user it still gave:

16:37:39 SQL> select dbms_metadata.get_ddl('PACKAGE','PACK1','SCOTT') from dual;
ORA-31603: object "PACK1" of type PACKAGE not found in schema "SCOTT"
ORA-06512: at (more...)

UKOUG – A Deep Dive into the SQL Monitoring Report

The UK Oracle User Group Conference 2011 is just around the corner and I just realized that I haven’t yet highlighted this, other than the “I’m speaking” banner on my blog.  I’ll be speaking on one of my favorite and most used reports — the SQL Monitor Report.  Below are the session details [direct link].  Hope to see you there!

Presentation Title: A Deep Dive into the SQL Monitoring Report
Speaker: Mr Greg Rahn
Company: Oracle
Presentation abstract: The SQL Monitoring Report was introduced in Oracle Database 11g and has become the single most used diagnostic report by the Oracle (more...)

Breaking change in calling Groovy on 1.8 upgrade

I've been bitten by this a couple of times now, so for anyone else's benefit: If you have a bat file that calls a groovy program, you may notice surprising behaviour after an upgrade from 1.7.x to 1.8.x (I went from 1.7.4 to 1.8.4).

If your bat file looks something like:
..some stuff..

groovy myGroovy
copy xyz abc

... more stuff ..
Then in 1.7.4 you would have called groovy.exe, executed the program, then continued to copying the file. But in 1.8.x groovy.exe is deprecated so instead you (more...)

How to fix archive stuck error with Oracle 11gR2

Recently, I encountered an issue with Oracle R12 EBS environment unable to login to database tier with APPS schema with our Oracle 11gR2 database tier.

ORA-00257: archiver error. Connect internal only, until freed.

Sure enough, upon examination of the ADRCI alert.log entries, I found this:

* 2011-10-27 16:24:51.990 4265 krsh.c
ARC1: Error 19809 Creating archive log file to '+FRA'
*** 2011-10-27 16:24:51.990 2864 krsi.c
krsi_dst_fail: dest:1 err:19809 force:0 blast:1
DDE rules only execution for: ORA 312
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump (more...)

Who is using your Undo space? – Improved Script

Add to Technorati FavoritesVer este articulo en Español

Hi folks!
I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks

and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.

Then the script for Oracle 11g is as follows:
set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname  (more...)

Customer Service and user experience

I have been trying to setup a new account with Amazon to get into the french version of the associate program, and got an email with potential french readers. It all started well, with a nice intro:
Cher Partenaire,
Merci d'avoir pris contact avec le Programme Partenaires d'Amazon.fr.
 Then, (more...)

Some preliminaries

| Nov 25, 2011
There are a few remarks to be made on DML event triggers. Just to be sure we are all at the same level on the playing field. The first one is that since these triggers fire as part of the execution of a DML statement, the trigger code is not allowed to execute any DDL (which causes an implicit commit) or (explicit) transactional control statements, such as commit, rollback, savepoint or rollback to savepoint. This restriction is true for both statement level as well as row level DML event triggers.

If you try to do so, Oracle will throw an (more...)

Bind Variable mismatch, nulls and oci

A short post on a frustrating issue that took a while to find the solution to. The developers made a small change to a table allowing nulls in 23 columns previously configured as not null. This resulted in a large number of bind mismatches and child cursors being generated, eventually giving rise to a serious performance issue.

After much discussion of the issue test cases, being told to tune the database flushing the shared pool finally a developer opened the code and took a look at the problem and found the reason for the bind mismatch was the oci null (more...)

UKOUG 2011

I will be speaking about latches at UK Oracle User Group Conference 2011. It will be my first visit to this extraordinary conference and UK. Hopefully see you there!

Oracle’s Message to System Integrators: Go Away

As an Oracle Partner, you cannot escape the relentless push from Oracle to become “specialized” in one or more Oracle products.

If the purpose of this program had been to ensure that partners actually know what they’re talking about, I would have been all in favor. But unfortunately, the main (more...)

First pervasive-post

Today I'm writing this post from a Samsung Galaxy, my hand sized tablet, camera included, microphone and lots of fun.

I have been busy this year and that is thanks to the tremendous success of the Database Machine/Exadata.

Traveled all over the caribbean and south america, plus my first OOW experience as Oracle employee: always exciting!

Bandeja Paisa / Paisa "tray" - Medellin, Colombia

Sao Paulo Subway System

Oracle Open World 2011

So what triggers are we talking about?

| Nov 22, 2011
Here's an overview of the talk.

This post will deal with the first bullet: the scope, what triggers are we talking about? And what triggers are we not talking about. Then there will probably be a few posts on 'properties' of the triggers, most notably I will spend some time on explaining the infamous mutating table error. Next we move on to a high level classification of use-cases of triggers. And talk a bit about why some of these might be considered harmful. Finally we will explain, in detail, the one use case where triggers are the perfect means to (more...)

Integrating APEX with OAM

Thanks to the hard work from Christian Neumueller from the APEX team and Ramana Turlapati from the Access Manager team we were able to successfully integrate Application Express into Oracle Access Manager. The result is a new white paper available via the OTN APEX on the Learn More page ( (more...)

"Triggers considered harmful", considered harmful

I know I should finish posting here on TheHelsinkiDeclaration... But in the meantime I decided to start a new blog dedicated on database triggers. It will be like TheHelsinkiDeclaration a documentation of a presentation I have been giving recently entitled "Triggers considered harmful", considered harmful.

Starting this blog

| Nov 22, 2011
So I finally was able to find some time to start this blog. I mentioned before that I might do this in the asktom discussion here. This blog will be about database triggers. Oracle database triggers that is. I'm a strong advocate of database triggers. Now hold your horses... I have been using database triggers ever since Oracle7 in the early nineties brought them to us. But I've been using them for a very specific reason, and have been presenting about this ever since. I will blog about this in a very similar way as I blogged about TheHelsinkiDeclaration, (more...)

Put Intelligence on the map (part 1)

Business has been collecting and analyzing geospatial information for a long time. Being able to view information such as, volume of revenue from different regions or number of events happened in different suburbs, on a zoomable map is of great value to people who manage the business. Since the 11g release, users of OBIEE are enabled to visualize BI on digital maps. From world level to street level, users can easily overlay business information on maps. The correlation between location and business can be seen intuitively. In this blog post, I will walk you through the things behind the scene (more...)

TIOBE Programming Community Index for November 2011

| Nov 19, 2011
PL/SQL jumped to the 12nd placeTIOBE Index (november 2011).
It's was 20st in November last year.

I Can Help You Trace It

The first product I ever created after leaving Oracle Corporation in 1999 was a 3-day course about optimizing Oracle performance. The experiences of teaching this course from 2000 through 2003 (heavily revising the material each time I taught it) added up to the knowledge that Jeff Holt and I needed (more...)

Profiling trace files with preprocessor external tables in 11g

Generate and read TKProf and OraSRP reports in a single SQL*Plus window (or other IDE of choice) using preprocessor external tables. November 2011

On demand AWR Report Generator, for Many Node Cluster

update ( 10/10/2012 ): I have changed the script to make work better with hourly requests at it was causing issues. Thanks to Dominic Brooks for the trick
update ( 11/10/2012 ): Added ADDM and Global report generation

Since I got turbo boost (over 1000 hits a day-wooow) from Tanel Poder the Great, better I write something to deserve the fame. This one is a real quick one which made my life easier.

On my second week at current company, I had to cover Exadata for 2 weeks which were completely nightmare due to some problems. I had to (more...)