Oracle Scratchpad

October 22, 2010


Filed under: Troubleshooting — Jonathan Lewis @ 6:45 pm BST Oct 22,2010

A little while ago I noticed a couple of page views that had come from the AskTom website – and I’m always interested to see what question has prompted a link and visit to my blog. In this case it was a question that raised a piece of (nearly ancient) history. In a question dated July 2009 someone was asking about a comment I had made in “Practical Oracle 8i”. (Despite the book being over 10 years old I’m still getting royalty cheques on it – small ones, but they keep on coming – and it’s still a book worth reading).

The question was about the following text:

Avoid naming tablespaces according to time-periods; this is particularly relevant to large, time-based data warehouses where very old data is ultimately dropped off the back end of the database.There is an unfortunate quirk of tablespace naming – Oracle never forgets a tablespace – in other words it never deletes it from the data dictionary table TS$ (I think this is some sort of hangover from Trusted Oracle where tablespaces have security labels associated with them). Unfortunately, one of the permanently running Oracle processes called SMON (the System monitor) scans this list of tablespaces every 3 minutes looking for tablespaces that might have some free space to coalesce; the size and cost of this scan will grow indefinitely unless you adopt a strategy of recycling tablespace names. It is actually surprising how many databases suffer a massive I/O load on the SYSTEM tablespace because of SMON.

The problem will be reduced somewhat by the introduction of locally managed tablespaces, since the scanning problem is actually exacerbated by the number of free extents that gets generated in poorly managed systems in a way that can’t happen with locally managed tablespaces; nevertheless you need to make sure that the list of tablespace names does not get out of hand.

You’ll be pleased to hear that this is no longer a problem. If you have access to Metalink (MOS), you can find information about (unpublished) bug 5861536: “Slow DDL due to Tablespace lookup with large number of tablespaces” dated 24th Sept 2008; and see in note 401436.1 ( Patch Set – List of bug fixes by Problem Type) that this problem has now been addressed.


  1. Amazing! “Only” 10 years later…
    (actually a bit less, but still amazing)

    Comment by Noons — October 23, 2010 @ 12:15 am BST Oct 23,2010 | Reply

  2. Sounds like it might be time for an update of your book Jonathan to take account of all the chnages over the past 10 years (and keep the royalty cheques flowing!).

    Comment by John Seaman — October 25, 2010 @ 3:39 am BST Oct 25,2010 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Powered by