Top 60 Oracle Blogs

Recent comments

June 2018

Statspack installation scripts

When Diagnostic Pack is disabled, either because you don’t have Diagnostic Pack or you are in Standard Edition, I highly recommend to install Statspack. When you will need it, to investigate on an issue that occured in the past, you will be happy to have it already installed and gathering snapshots.

I order to be sure to have it installed correctly, there’s a bit more to do than just what is described in spcreate.doc and I detail that in a UKOUG Oracle Scene article Improving Statspack Experience.

For easy download, I’ve put the scripts on GitHub:

You will find the following scripts for Statspack installation:

How to do EXPORT / IMPORT between #Exasol and #Oracle

The commands IMPORT and EXPORT provide an easy way to transfer data between Exasol and other data sources like an Oracle database. You may at first get this error message: Oracle instant client not available. Please ask your administrator to install it via EXAoperation.

Here’s how to do that yourself without having to ask your admin </p />

    	  	<div class=

Managing Cost-Based Optimizer Statistics for PeopleSoft

I gave this presentation to UKOUG PeopleSoft Roadshow 2018

PeopleSoft presents some special challenges when it comes to collecting and maintaining the object statistics used by the cost-based optimizer.

Truncate upgrade

Connor McDonald produced a tweet yesterday linking to a short video he’d created about an enhancement to the truncate command in 12c. If you have referential integrity declared between a parent and child table then in 12c you can truncate the parent table and Oracle will truncate the child table for you – rather than raising an error. The feature requires the foreign key constraint to be declared “on delete cascade” – which is an option that I don’t see used very often. Unfortunately if you try to change an existing foreign key constraint to meet this requirement you’ll find that you can’t (yet) use the “alter table modify constraint” to make the necessary change. As Connor pointed out, you’ll have to drop and recreate the constraint – which leaves you open to bad data getting into the system or an outage while you do the drop and recreate.


For an Oracle DBA, we are used to join active sessions (from V$SESSION where status=’ACTIVE) with active statements (from V$SQL where users_executing>0) on the SQL_ID. V$ACTIVE_SESSION_HISTORY also displays the TOP_LEVEL_SQL_ID to get the entrypoint of the usercall if we need it. With Postgres it is a bit more difficult because it seems that PG_STAT_ACTIVITY do not show the active statement but only the top-level one. But pg_stat_statement collects statistics for the final statements.

Lighty for PostgreSQL

If you follow this blog, you should know how I like Orachrome Lighty for Oracle, for its efficiency to monitor database performance statistics. Today Orachrome released the beta version of Lighty for Postgres:
The Cloud is perfect to do short tests with more resources than my laptop, especially the predictability of performance, then I started a Bitnami Postgres Compute service on the Oracle Cloud and did some tests with pgbench and pgio.

The installation is easy:

UTL_FILE_DIR and 18c

I wrote a blog post called The Death of UTL_FILE which attracted a comment from a reader:

“There is NO chance to stay at UTL_FILE as it is DESUPPORTED starting with database Version 18c”

This is not the case, but since I wanted to clarify what has changed in 18c, it warrants this small but separate blog post. When UTL_FILE first into existence in Oracle 7, the concept of directory object did not apply to UTL_FILE. Clearly we could not just let UTL_FILE to write to any destination, otherwise a malicious person could write a little PL/SQL block like this:

Kernel panic – not syncing: Out of memory and no killable processes

This is a quick post to give a solution (maybe not the best one as this was just quick troubleshooting) if, at boot, you see something like:
Trying to allocate 1041 pages for VMLINUZ [Linux=EFI, setup=0x111f, size=0x41108d01]
and then:
Kernel panic - not syncing: Out of memory and no killable processes
Pid: 1228 comm: kworker Not tainted 3.8.13-118.17.4.el6uek.x86_64 #2

If you do not see the messages, then you may need to remove the ‘quiet’ option of kernel and replace it by ‘nosplash’ – this is done from grub.

More triggers are better

Yes, you heard me correctly. If you have got one trigger on a table, then you might be surprised to find that perhaps having a second one will be a better option. Then again, I also love the sweet scent of a clickbaity, inflammatory blog post title to draw the readers in Smile so you’ll just have to read on to see which is true.