Default Tablespaces

[malru]

In oracle 9i,I created a database using Oracle database configuration assistant,some of the default tablespaces created for this database are

select tablespace_name from dba_tablespaces

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
TOOLS
USERS
XDB

But when i created the database manually using the following script,

create database NEWDB
controlfile reuse
logfile
GROUP 1 ( 'C:\oracle\oradata\newdb\log1a.rdo') SIZE 5M reuse,
GROUP 2 ( 'C:\oracle\oradata\newdb\log2a.rdo') SIZE 5M reuse,
GROUP 3 ( 'C:\oracle\oradata\newdb\log3a.rdo') SIZE 5M reuse
datafile 'C:\oracle\oradata\newdb\data01.dbf' SIZE 20M reuse
undo tablespace UNDO
datafile 'C:\oracle\oradata\newdb\undo01.dbf' SIZE 15M reuse
default temporary tablespace TEMP
tempfile 'C:\oracle\oradata\newdb\temp01.dbf ' SIZE 10M reuse
extent management local uniform size 64k
;

When i issue the same query,

select tablespace_name from dba_tablespaces

TABLESPACE_NAME
------------------------------
UNDO
TEMP


1) Why it is so?

2) Can anyone explain what are the default tablespaces get created with during database creation?

[hrishy]

Hi

create database NEWDB
controlfile reuse
logfile
GROUP 1 ( 'C:\oracle\oradata\newdb\log1a.rdo') SIZE 5M reuse,
GROUP 2 ( 'C:\oracle\oradata\newdb\log2a.rdo') SIZE 5M reuse,
GROUP 3 ( 'C:\oracle\oradata\newdb\log3a.rdo') SIZE 5M reuse
datafile 'C:\oracle\oradata\newdb\data01.dbf' SIZE 20M reuse
undo tablespace UNDO
datafile 'C:\oracle\oradata\newdb\undo01.dbf' SIZE 15M reuse
default temporary tablespace TEMP
tempfile 'C:\oracle\oradata\newdb\temp01.dbf ' SIZE 10M reuse
extent management local uniform size 64k
;

Did this code sucessfully execute ?

The defaulkt tabelspaces that needs to be created usually are
system temp and undo

If you use the database configuratioin assistant it creates a lot of smaple schemas and those sample schemas are having there own tablespaces something like explae schemas are created under EXAMPLE tablespace

regards
Hrishy

[JMac]

As Hrishy says - look at your script. You only create two TBSs. So you only get 2.

The DBCA creates a lot of other 'default' TBSs automatically, which is why its good for quick and dirty DB creations (for developers to play with, say), but for anything for Production you're better off creating a manual script. Then you know exactly what you're getting.

[DaPi]

Near the end of the DBCA "dialogue" you can choose to store the code that will be used to cretae the DB. Look at this to understand what DBCA does or proposes to do. All will then be obvious.