Starting from Oracle 10G, creating the database can be as simple as this:
SQL> create database;It's not a surprise that this feature was somewhat advertised here and there. However, what was missing in these advertisements is this:
Database created.
SQL> select name, decode(bitmapped, 0, 'DMT', 'LMT')For you see, create database will make your SYSTEM tablespace to be dictionary managed by default. I don't really know if there are any reasons for this and since everything else will default to LMT, this should not be a big deal for most of you anyway. Just don't forget that SYSTEM hosts objects like AUD$ (audit log), FGA_LOG$ (fine-grained audit log) or NCOMP_DLL$ (natively compiled objects) which could grow to a fairly large number of extents.
from ts$
order by name; 2 3
NAME DEC
------------------------------ ---
SYSAUX LMT
SYSTEM DMT
SYS_UNDOTS LMT
I was a bit surprised watching this relic appear even when you do this in 11.1.0.7...
Posts
I'm sure you'll agree with me if I say it's a feature :-). That's to allow dba to work longer and type:
ReplyDelete"create database extent management local;"
Gregory,
ReplyDeleteyou know what's the bold difference between cabs in Ottawa and Toronto?
When they don't know how to drive to a destination...
Ottawa's cab driver says "I'll get you somewhere around and we'll figure out from there..."
Toronto's cab driver pulls out a map before he even starts moving...
That's right, Ottawa bills you by a mile while Toronto has a fixed price.
Hopefully, not all DBAs are billing by the hour to consider this to be a feature :-)
Hi Alex - I think you're alluding to the real answer, which is that DMT SYSTEM tablespace is needed in order to create other DMT tablespaces. Conversely a LMT SYSTEM tablespace only allows LMT tablespaces to be created.
ReplyDeleteSQL*Plus: Release 9.2.0.7.0 - Production on Sun Feb 8 12:53:40 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
...
SQL> select tablespace_name,extent_management from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM LOCAL
...
SQL> create tablespace jefftmp datafile '/appl/oracle/oradata/xxxx/jefftmp01.dbf' size 10m extent management dictionary;
create tablespace jefftmp datafile '/appl/oracle/oradata/E2esvP/jefftmp01.dbf' size 10m extent management dictionary
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace
..Not that there's any real good reason to continue using DMT in this day and age!
Keep the interesting posts coming! :]
cheers-
Jeff Wong
Jeff,
ReplyDeleteIn my opinion they would be much better with letting LMT to be a default, whoever wants to use DMT could specify it manually (but they did it the other way around).
Just think how many people would want to use DMT in 11G...