Don’t Cramp My (SQL) Style … Musings from an Oracle DBA

This post was inspired by a Twitter thread that took place on October 31st between myself, @oraclenerd, @dtseiler (maybe I only included Don for his tongue in cheek, playground banter), and @surfsearcher :
@oraclenerd: "besides commas at the start of a line (sql, plsql), I also hate mixed case sql and plsql"

@oraclenerd: "@piontekdd come on! it's just plain ugly...besides, I've gotta have have at least a little something to bitch about now and again. :) "

@piontekdd: "@oraclenerd life is too short to get upset about personal style :)"


Index Dynamics – Part 2 (Halloween on the Block)

Add to Technorati Favorites

Ver este artículo en Español

View starting post: Index Dynamics - Part 1

As I've promised, today will share with you mid-term results for my index observations.

First we may see a graph of Used Space, as reported by column PCT_USED of table INDEX_STATS (right after an ANALYZE over each index). This percentage accounts the space allocated to the B-Tree that is used.

There is one line for every index we are considering, and note the legend on the graph showing the index name and the number of columns inside parenthesis.

What can be observed in this chart?
1) After (more...)

EclipseLink 1.0.2 Released

A new EclipseLink release is now available for download. EclipseLink 1.0.2 is a patch release on the 1.0 stream, providing bug and usability fixes.

Please come and try it.

How to Flush your database caches

Add to Technorati Favorites

Ver este articulo en Español

Flushing the SGA memory areas, Shared Pool and Buffer Cache, it's an uncommon task, however, it gets useful when you're doing some tests and want to override the memory and go direct to disk, or when you have shared pool issues (here is a workaround, however I encourage you to find the root cause).

Shared Pool flush
This is the only sentence you have to know for releases 9i and up...
alter system flush shared_pool

Buffer Cache flush
For Oracle 9i I didn't know how to do this, fortunately found it today on Rahat Agivetova's (more...)

Index dynamics

Add to Technorati Favorites

Sigue el link
Ver este articulo en Español

Today I will start with a series of 3 posts depicting the follow up of 4 index behavior on a Production database.

The purpose of this exercise is try to unveil the morphology these four indexes take during a given period of time, under what kind of load, model it in a graphical way. Since indexes are quite a black box, this exercises also proposes a complementary procedure for index quality measure.

Experiment subjects were selected using information on DBA_SEGMENTS+DBA_HIST_SEGMENTS, with focus on size and activity, indexes were choosen within the 400Mb-500Mb range thinking on (more...)

Random notes

For some time, I have been away from my blog, and sent most of my spare time on facebook, trying to figure out why I should use it. did not find the answer yet ... but I am sure there is some. otherwise, why would everyone been their, and acept my (more...)

Oracle OpenWorld Presentation Online

It seems that there has been some mixup with the Oracle OpenWorld presentations – several people have written to me complaining that the slides that could be downloaded from the OOW site were not mine. So here are the slides from my presentation “Choose Your Weapon – an Overview (more...)

Oracle Critical Patch Update – October 2008

Oracle released critical patch update for October 2008 yesterday; this is the last CPU for 2008. There have been 36 new fixes across all products including 15 new security fixes for database products. Please review the following URL to see if the product you are using requires this patch or not.

You can also refer to MetaLink Note 735216.1 for more details on database fixes.

A Map for the Post BEA Era

I stopped by Ted Farrell's keynote at Oracle Develop 2008 and heard him give an overview of Oracle's "go forward" dev tools strategy in the wake of the influx of great technology from BEA. Below is a key section of the talk:

SQL Developer Data Modeling Update

Oracle has released an 'early adopter' version of the the data modeling enhancements to SQL Developer.

See the OTN article for details.

I haven't tried it yet, it will be interesting to see just how well it works.

Oracle Metalink Notes for Oracle Apps DBAs.

Dear Friends,

Here are the list of Oracle Metalink Notes, I do refer regularly. Moreover, these are very informative. So, I thought of sharing those note ids here with you.

Note: You have to have an account in Oracle Metalink to access notes.


Note: 452120.1 - How to locate the log files and troubleshoot RapidWiz for R12
Note: 329985.1 - How to locate the Rapid Wizard Installation log files for Oracle Applications (more...)

Speak, Write, Win

If you look at the people that make it to Oracle ACE status, they aren’t just good at technology they are also comfortable speaking and writing about what they do. (I know because I’ve interviewed many of them.) My advice? Get serious about your writing skills (as in take (more...)

Virtual columns in 11g

| Oct 10, 2008
Store expressions as virtual columns in Oracle 11g. October 2008

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...)

AIOUG – TechNight in Bangalore

The All India Oracle User Group with a 4 lakh -strong developer base and a community of seven-lakh users in India is organising a second meeting in Bangalore on
October 22 to share experiences and provide insights into shape of things to come. It is the second largest technology network of professionals for Oracle after the US. Soon these TechNights will be held in other parts of India, such as Pune, Mumbai and Delhi.

Like several successful Oracle user groups around the world, AIOUG is also a not for profit organization formed by Oracle users for Oracle users. Let’s join (more...)

My Blog Feedback Page

Dear Blog Readers,

I wanted to have a feedback page as a post in my blog where my blog viewers can post their valuable comments and suggestion about my blog and articles.

In order to serve you better with required information in my blog, please leave your comments/suggestions/doubts/questions/requirements/ etc.

Link for comments here - Feedback Page !

Please check the comments submitted in the comments item below.

Thanks & Regards,
Sabdar Syed.

Undocumented Oracle Functions

Undocumented functions in Oracle are always fun, and you just may find something useful.

The caveat of course is that they are undocumented. They can change without notice between releases or patch levels, so building apps that depend on them may be unwise.

They are often quite useful from a DBA perspective when used in SQL scripts.

Here are a few that I've played with. These are all found in Oracle

These functions have one thing in common - they have a prefix of SYS_OP_

Some of these appear to be identical to documented functions.

I (more...)