He is referring to the fact that the SYS table TS$ is not "deleted from" over time. When you drop a tablespace - it remains in TS$ (but not in the dictionary views)

ops$tkyte%ORA11GR1> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
USERS
EXAMPLE
ENCRYPTED
CLEAR
T1
T2
BIG_TABLE
TOOLS
DEMO
MSSM
ASSM
UNDOTBS

15 rows selected.

ops$tkyte%ORA11GR1> select name from sys.ts$;

NAME
------------------------------
ASSM
BIG_TABLE
CLEAR
DEMO
ENCRYPTED
EXAMPLE
MSSM
RO
RW
SYSAUX
SYSTEM
T1
T2
TDE_TEST
TEMP
TEST
TOOLS
UNDOTBS
UNDOTBS1
UNDOTBS2
USERS

21 rows selected.

Now, in most normal 'use cases', this is just fine, this table TS$ never gets really huge.

But if you were to do something like "every day, create a new tablespace named after tomorrow's date - like TS_20090612 - and create a new partition PART_20090612 in it for a large partitioned table, that is destined to hold the data for 12-jun-2009. Then, find the oldest partition (say PART_20080611 - last year) and drop that partition and drop that tablespace.


The tablespace row in TS$ would stick around - you would have hundreds, then thousands, then 10's of thousands of entries over time. The background processes of Oracle will come in from time to time and query this table - employing a full scan against it.

If you have thousands of entries in there relating to tablespaces that no longer exists, the full scan will consume measurable resources.

If you 'reuse' tablespace names - this will not happen.