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 – 10.2.0.3
Disable Optimizer Hints
SQL> set timing on
SQL> alter session set “_optimizer_ignore_hints” = true;
SQL> exec statspack.snap
PL/SQL procedure successfully (more...)
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_offset_dest INTEGER :=1;
l_offset_src INTEGER :=1;
/* Get a BFILE pointer to OS file. */
SELECT bfilename (‘TMP’,'CLIENT_CUST_BLEUPRINT.xml’)
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 10.2.0.3 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...)