Tablespaces

This could nearly be one for the “ancient history” series, because it starts with a quote from “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 script 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).

The notable  thing about this exchange, though, 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 your database holds (or has held at some time in the past) those of you with Metalink/MOS accounts might want to look at document ID 5861536.8.

Footnote

The sql.bsq script had got so large by 12c that Oracle Corp. split it into many different *.bsq parts, leaving sql.bsq itself holding nothing but calls to each of the difference parts in turn.