-- Thanks for the question regarding "Tablespace", version 8.1.7

You Asked

Hello Sir

Thanks for this useful site, I learned lots of think.

While reading Joanathan Lewis book "Pratical oracle 8i" he mentioned about "resuing the tablespace name, since pmon wake up and scan the tablespace for reclaiming the space"

could you please flash more light on this.

many thanks in advance

and we said...

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.

Reviews

June 12, 2009 - 10am Central time zone Bookmark | Bottom | Top Reviewer: A reader  Thanks Tom for your reply. Tablespaces in 8i   June 12, 2009 - 5pm Central time zone Bookmark | Bottom | Top Reviewer: Jonathan Lewis from UK Tom's answer says all that needs to be said. But if you want a little more detail, you could always enabled event 10046 on smon (on a test system only, of course) for 48 hours and see what you get. The particular query that made me raise this point was the following one, which executes every five minutes: select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0 Regards Jonathan Lewis http://jonathanlewis.wordpress.com http://www.jlcomp.demon.co.uk TS$   June 13, 2009 - 5pm Central time zone Bookmark | Bottom | Top Reviewer: A reader  We had run into this issue on a lot of 9i and 10.2.0.3 databases (some of them TB in size) We created/dropped a lot of partitions and this resulted in the TS$ growing to a huge size. Even a select query on the tablespaces took a lot of time to fetch the data. We followed up with Oracle Support and got a patch which will sort of "manage" the TS$ table. It has been included from 10.2.0.4 onwards. Patch number   June 14, 2009 - 10am Central time zone Bookmark | Bottom | Top Reviewer: jonathan Lewis from UK Thanks for the note about a patch - can you give us a patch reference ? Thanks Jonathan Lewis http://jonathanlewis.wordpress.com http://www.jlcomp.demon.co.uk Followup   June 15, 2009 - 12pm Central time zone: I think they might mean bug 5861536 - an enhancement in 11.2

---

Write a Review