How to Create Database With Dictionary Managed Tablespace

[Pinki Meggi]

Hi,

Im working with Orcale 9i, and i need to create a database with Dictionary Managed System Tablespace. DBCA creates database by default with locally managed system tablespace. how can i change it?

Thanks in advance for any help.

Best Regards

Pinki

[TwoProc]

You can have the dbca just save a script for you off all the things it's going to do, instead of having it actually run the database creation. Let it create the script, and then you can go edit that script, and then run it.

Look for the like lines "create tablespace" and then add the options you would like including "local", and I'd recommend at that time, to go ahead and use uniform allocation methods, and those to the script at that time as well.

There's some good examples to read on here:
http://kp-oracledba.com/blog/2007/03/15/how-to-create-and-alter-oracle-tablespace/

The above having examples for locally managed as well as uniform allocation with locally managed tablespaces.

HTH

[Ariel Cary]

Pinki,

You have to omit the 'EXTENT MANAGEMENT LOCAL' in the create database statement to create a dictionary managed system tablespace.

For example in Oracle 10g (I don't have 9i at hand, but I'd say syntaxwise it should be the same except for sysaux ts), assuming you've properly configured the initDBA.ora file.

$ export ORACLE_SID=DBA
$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 23 01:51:18 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1261248 bytes
Variable Size 226492736 bytes
Database Buffers 33554432 bytes
Redo Buffers 2932736 bytes
SQL>

SQL> create database DBA
2 datafile '/u02/oradata/DBA/system01.dbf' size 100M
3 undo tablespace undotbs1 datafile '/u02/oradata/DBA/undotbs1_01.dbf' size 100M
4 sysaux datafile '/u02/oradata/DBA/sysaux01.dbf' size 100M
5 /

Database created.

...
$

You'll eventually see in the alert log, your system tablespace created as:

Wed Jan 23 02:11:14 2008
Completed: create tablespace SYSTEM datafile '/u02/oradata/DBA/system01.dbf' size 100M
default storage (initial 10K next 10K) EXTENT MANAGEMENT DICTIONARY online
....

Also, I assume you understand the caveats and disadvantages of using rather old-type, non-recommended, dictionary managed tablespaces.

HTH,

-Ariel