Slow Statspack Snapshots


For quite some time we had been experiencing slow statspack snapshots, taking about 300sec. In a worst case scenario it took 7 hours. My colleague was investigating it, it turned out that on this particular database “_optimizer_ignore_hints” was set to true. So it was ignoring all the optimization put in by Oracle in statspack snapshot code.



OS – Linux

Database –





Disable Optimizer Hints


SQL> set timing on

SQL> alter session set “_optimizer_ignore_hints” = true;

Session altered.

Elapsed: 00:00:00.03


SQL> exec statspack.snap

PL/SQL procedure successfully (more...)

Loading File into a Blob


Yesterday I received a request from a developer to load file into a BLOB. To be honest I have never loaded a file into a BLOB in past. I had some idea that I would have to use DBMS_LOB pl/sql package to achieve this.

Here are the steps to do this.

1. Create an oracle directory object.

create directory tmp as ‘/tmp’;


2. Load the file into BLOB


l_blob BLOB;

l_bfile BFILE;

l_offset_dest INTEGER :=1;

l_offset_src INTEGER :=1;


/* Get a BFILE pointer to OS file. */


INTO l_bfile

FROM dual;

/* (more...)

Slow drop user

For past 3 weeks i was covering for a project dedicated DBA who had gone on leave for 3 weeks (some people are just lucky). Its an Oracle Peoplesoft General Ledger application on Oracle database. Lot of copies of application are hosted in same database as multiple copies for different stages of software lifecyle.

I was asked by application team to refresh 6 schemas from 2 other schemas over the weekend. How hard could it be I thought? Seemed like a simple request. Just take a datapump export of source schema, drop the destination schema and (more...)

My first post

Finally i managed to start my own blog. I had been thinking about starting my own blog on my website for some time. But somehow it never took off. I am pleased that its up and running….

Watch this space for some interesting oracle reading.