Saturday, January 31, 2009

Create database or who wants some DMT?

Simpler than ever

Starting from Oracle 10G, creating the database can be as simple as this:
SQL> create database;

Database created.
It's not a surprise that this feature was somewhat advertised here and there. However, what was missing in these advertisements is this:
SQL> select name, decode(bitmapped, 0, 'DMT', 'LMT')
from ts$
order by name; 2 3

------------------------------ ---
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.

I was a bit surprised watching this relic appear even when you do this in


  1. 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:
    "create database extent management local;"

  2. Gregory,

    you 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 :-)

  3. 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.

    SQL*Plus: Release - 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;

    ------------------------------ ----------

    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! :]

    Jeff Wong

  4. Jeff,

    In 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...