Showing results for 
Search instead for 
Did you mean: 

How to Create Database With Dictionary Managed Tablespace

Frequent Advisor

How to Create Database With Dictionary Managed Tablespace


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

Honored Contributor

Re: How to Create Database With Dictionary Managed Tablespace

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:

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


We are the people our parents warned us about --Jimmy Buffett
Frequent Advisor

Re: How to Create Database With Dictionary Managed Tablespace


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.

$ sqlplus / as sysdba

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