Re: Tablespace best practices

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Wed, 29 Dec 2004 01:05:42 +1100
Message-ID: <41d16837$0$20821$afc38c87@news.optusnet.com.au>

Howard J. Rogers wrote:
[snip]

>> Sadly, I think ASSM is in fact the default if you have a LMT system
>> tablespace.
>>
>> hth
>> connor

>
>
>
> No it's not.
>
> Oracle 9i:
>
> SQL> select extent_management from user_tablespaces
> 2 where tablespace_name='SYSTEM';
>
> EXTENT_MAN
> ----------
> LOCAL
>
> SQL> create tablespace X datafile 'x.dbf' size 5m;
>
> Tablespace created.
>
> SQL> select segment_space_management from
> 2 user_tablespaces where tablespace_name='X';
>
> SEGMEN
> ------
> MANUAL
>
> Perhaps you could return the favour and test 10g?
>
> Regards
> HJR
OK, so I couldn't wait...

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> select extent_management from user_tablespaces

   2 where tablespace_name='SYSTEM';

EXTENT_MAN

---

LOCAL SQL> create tablespace X10 datafile 'x10.dbf' size 5m;

Tablespace created.

SQL> select segment_space_management from

   2 user_tablespaces where tablespace_name='X10';

SEGMEN

---

MANUAL So the fact is, it's still not the default. Just as it shouldn't be!

However, I think your error originally arose from the fact that DBCA will merrily create all its sample tablespaces as ASSM when SYSTEM is LMT... in other words, DBCA has its *own* set of defaults, as it always has had. And quite often those defaults are not the same defaults as the *database* itself has (as they have quite often been in the past: 3 log groups, not two. Multiplexed, not solo. 3 control files, not one. 8K block sizes on Linux. SYSTEM as LMT. Use of spfile. Etc. Etc. Etc.). Which I have always thought to be extremely poor practice on Oracle's part, and a source of much confusion and bewilderment to many.

Regards
HJR Received on Tue Dec 28 2004 - 08:05:42 CST