Oracle 10g DBA: Adding Time Information into Tablespace Names

In VLDB databases, there is a common willing in using a tablespace naming convention that includes seasonal information of data within tablespace, such as CHURN_2008M01 or SALE_2008W50. This has various benefits in terms of ILM (Information Life Cycle) management. By just looking the name of a tablespace, the DBA can have a pretty good idea about the tablespace and segments within that tablespace. Nevertheless, prior to 10g this convention can cause significant degeneration in system catalog performance if data windowing is used. In other words, if you periodically need to drop old tablespaces and add new ones, Oracle catalog starts to blow up. In this paper, you will find a tricky way of solving this problem by Oracle 10g.

Read rest of the paper…

This post consists of my article published in IOUG “Oracle Technology Best Practices” booklet delivered in Oracle Open World 2008.