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