Top 60 Oracle Blogs

Recent comments

November 2012


Here’s a funny little glitch – typical of the sort of oddity that creeps into the data dictionary from time to time – cut-n-pasted from

SQL> select count(*) from user_indexes;


1 row selected.

SQL> select count(*) from user_objects where object_type = 'INDEX';


1 row selected.

We seem to have 157 indexes that aren’t indexes – what could they be. Perhaps a small enhancement to our query on user_indexes (the one that has the larger result) will help:

Oracle Exadata Database Machine vs. IBM Power Systems

Just a quick note to point out that the official white paper version of the TCO study I talked about in a previous post (Exadata vs. IBM P-Series) is now available on the Oracle web site here:

Cost Comparison – Oracle Exadata Database Machine vs. IBM Power Systems

The most interesting part of the study (in my opinion) is the quotes from the participants that are using Exadata. These quotes provide some insight into how people feel about the platform after having systems in production for a while (note that all the interviews were done prior to the release of the X3-2). I should also point out that these customers are not typical Oracle reference customers. They were interviewed by a third party and promised anonymity as part of the study.

PeopleTools 8.52 Application Engine sets MODULE and ACTION

I have written and spoken often about the huge importance of instrumentation in an application. PeopleSoft introduced internal instrumentation to PeopleTools 8.44 for its own Performance Monitor that works on any platform. Since PeopleTools 7.53, when running on an Oracle database, PeopleTools also calls the Oracle supplied package dbms_application_info package to set the module and action for the session. The values set were not particularly useful, so I wrote a PL/SQL package (psftapi) and trigger to set the module and action attributes when a process started.

Why can’t I resize my datafile


We’ve all done that common administrative task of:

- find the HWM in a datafile

- resize the datafile down to that mark.

But sometimes, you might get what appears to be a problem:

Here’s a tablespace I created a while back…

SQL> create tablespace DEMO
  2  datafile ‘C:\ORACLE\ORADATA\DB112\DATAFILE\DEMO.DBF’ size 100m
  3  extent management local uniform size 1m;

Tablespace created.

After a while I wanted to reclaim that 100 megabytes back, so I looked at the high water mark in DBA_EXTENTS

SQL> select max(block_id+blocks)*8192/1024/1024 high_mb
  2  from dba_extents
  3  where tablespace_name = ‘DEMO’;


So, if the high water mark is 2meg, all I need so now is resize the file….

Friday Philosophy – The Importance of Context

A couple of weeks ago I was making my way through the office. As I came towards the end of the large, open-plan room I became aware that there was someone following behind me so, on passing through the door I held it briefly for the person behind me {there was no where else they could be going}, turned left and through the next door – and again held it and this time looked behind me to see if the person was still going the same way as I. The lady behind gave me the strangest look.

The strange look was reasonable – the door I’d just held for her was the one into the gentleman’s bathroom. *sigh*

Thanksgiving Thoughts

Today is Thanksgiving Day in the US. For those who are not familiar with American holidays and traditions, Thanksgiving is a really tribute to cooperation and camaraderie. There are many theories and folklore surrounding the concept; but I think one trumps over the rest. More than four scores ago when the pilgrims from England set foot in North America and decided to call it their home, they didn't encounter a Welcome to America sign at JFK airport. Harsh New England weather was just one of the many sobering reminders of the fate of things to come for the visitors.

Row Level Security 3 – In Pictures!

<..Part one intro and examples
<….Part two Permissions

I’ve noticed that there has not been a lot of traffic on this series on Row Level Security (data masking) so far – maybe due to how I am presenting the material? So here is a summary to date in picture/diagram format:

Using OSX, Keynote and your iPhone as a clicker

A substantial part of the people I encounter present using OSX on a Macbook. I am not sure how much of these people use Apple’s Keynote for presenting, but I like Keynote very much for various reasons, like a cleaner interface. This blogpost is about some handy tips and tricks I learned using a few years of presenting around the world. If you don’t use OSX, this blogpost is probably not for you.

Plan Order

The previous post reminded me of another (fairly special) case where the order of operations in an execution plan seems to be wrong according to the “traditional” strategy for reading execution plans. Here’s a simple select statement with its execution plan to demonstrate the point: