Who's online

There are currently 0 users and 27 guests online.

Recent comments


Monitoring Changes to Table Data

March 22, 2012 Suppose that you receive a request stating that a particular table in one of your databases must be monitored for changes.  One such table might be the one that lists the ERP system’s suppliers and their addresses – you would not want someone to be able to set up a legitimate supplier, and later [...]


The column is there for a long time – even 9i documentation have it. I’ve never thought about it until today when I caought something extraordinary on instance.

Starting with 10g oracle introduced SQL_ID for simplicity; it is used in combination with CHILD_NUMBER to locate a particular row in the V$SQL. The documentation is clear (bold is mine):

V$SQL lists statistics on shared SQL areas without the GROUP BY clause and contains one row for each child of the original SQL text entered

SQL Quiz – How To Multiply across Rows

A colleague came to me a couple of days ago with a SQL problem. He had something like this:


NAME          INPUT
------------- -----
GROUP_1       5
GROUP_2       3
GROUP_3       4
GROUP_4       7
GROUP_5       3

What he wanted to do was multiply all the inputs across the groups, to get the total number of possible permutations. ie 5*3*4*7*3. The product of all the INPUTS. This has to be in straight SQL. Easy! You just need to… Ahhh… Hmmm…

No, I just could not think of a way to do it that my colleague could use.

Dropped Tables, Hiding Extents and Slow DBA_FREE_SPACE Queries

My last post was on slow dictionary queries caused by having many, many thousands of extents in your database. This post is about a special case of this problem, which I encountered recently. It was on an Exadata box – Exadata is not particularly relevant to the issue, but I’m curious to see if mentioning Exadata will make this post more popular :-)

I was querying the used and free space on a very small database on the client’s X2-2 1/4 rack Exadata machine. The query was really slow, taking about 30 seconds. This is a FAST box, what is going on?

I quickly realised that the problem was specific to one tablespace:

Oracle Query Optimizer Vanishing Acts

February 3, 2012 A couple of days ago I noticed an interesting thread in the Usenet group that described a problem of vanishing tables.  The title of the thread certainly caught my attention, and I was a bit disappointed when I found that the there was little to no magic involved in the vanishing act.  The situation reported [...]

Friday Philosophy – The Answer To Everything

For those of us acquainted with the philosophical works of Douglas Adams we know that the the answer to everything is 42.

mdw1123> select all knowledge from everything
  2  /


This above is a real SQL statement (version, just in case you wanted to know :-) ).

Non-Specific Index Hints

January 24, 2012 (Modified January 25, 2012) As I read the “Troubleshooting Oracle Performance” book for the second time a couple of months ago, I made note on page 385 that it was possible to specify table columns in an index hint, rather than specifying specific index names (or just specifying the table name).  This might be [...]

Dropped Partitions do not go in the Recycle Bin

If you alter table TEST drop partition Q1, does it go in the recycle bin?

That is the question I was asked today. “Of course it….” Pause. More pause.

No, I did not know. I knew I’d seen partitions in the recyclebin on Oracle but I thought there was no option to state PURGE when you dropped a partition.

So, a quick test was needed.

First I tried a drop of a partition I knew I could live without {NB this is on version, I am 99.8% sure this is the same on 10}

Finding the Next Primary Key Value, a Pre-fixed Solution

January 20, 2012 A request for assistance came in from an ERP mailing list.  The original poster (OP) is running an unspecified version of Oracle Database 9i, and is in need of a solution to generate new part numbers with prefixed characters that describe the type of part, followed by a sequential number that is [...]

What Number Immediately Follows 3,999? Oracle Database Refuses to Answer

January 16, 2012 I put together a test case to demonstrate how the physical reads autotrace statistic could exceed the consistent gets autotrace statistic if a single-pass or multi-pass workarea execution were performed during the execution of the SQL statement.  If you are interested, you can see the test case in this recent OTN thread.  [...]