Oracle Scratchpad

June 19, 2009


Filed under: Infrastructure,Troubleshooting — Jonathan Lewis @ 7:08 pm BST Jun 19,2009

This could nearly be one for the “ancient history” series, because it starts with a quote from that marvellous book “Practical Oracle 8i”, where I wrote in Chapter 8:

Tablespace names are never removed from the data dictionary (cluster SYS.C_TS#), and by default each tablespace entry occupies a minimum of one block in this cluster, even if it is a locally managed tablespace rather than a dictionary-managed tablespace. 

Since SMON scans TS$ every five minutes you need to avoid letting this table get too large.Your options are (1) hack the SQL.BSQ table that creates the data dictionary to reduce the cluster size in the C_TS# cluster – but make sure you get official approval from Oracle Support first, and (2) recycle tablespace names as much as possible and avoid using ‘time-related’ tablespaces names, especially if you are taking advantage of transportable tablespaces .

A few days ago, I got an email from Tom Kyte, because someone had written to AskTom asking him to throw more light on this comment.

Tom explained the comment, and I added a little extra note (the query is one used by smon to identify free space that can be coalesced in dictionary-managed tablespaces). But the notable  thing about this exchange was that someone then wrote in to say that they had experienced in 9i and 10g a variant of the problem that I had predicted - and Oracle has now got a patch for the problem in with a possible enhancement in 11.2.

For some interesting background reading on how the number of tablespaces you have – or have had at some time – in your database, those of you with Metalink accounts might want to look at document ID 5861536.8, and Bug 5855429.

1 Comment »

  1. [...] Jonathan Lewis – Tablespaces [...]

    Pingback by Blogroll Report 12/06/09 – 19/06/09 « Coskan’s Approach to Oracle — June 23, 2009 @ 11:24 am BST Jun 23,2009 | 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 3,528 other followers