Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Tablespace design question, pls help..

Re: Tablespace design question, pls help..

From: David Fitzjarrell <>
Date: 6 Jul 2004 15:03:36 -0700
Message-ID: <> (Krist) wrote in message news:<>...
> Hi All,
> I am migrating data from SQL Server 2000 into Oracle 9i on AIX (using
> OMWB). I have checked that SQL data size is 17 GB.

I take it this means you have a database file in SQL Server that is 17 Gig in size.

> The question is about Tablespace design. For best performance, how
> should I create the tablespace :
> 10 DataFile @ 2 GB
> 20 DataFile @ 1 GB
> or any better options ?
> Thank you for your suggestion,
> xtanto

You do not understand how Oracle is configured, nor the difference between an Oracle database and a SQL Server database. Let me explain the difference:

A 'database' in SQL Server is a collection of related data. As such, "create database beverage" creates a common location for beverage data.

A database in Oracle is the instance (memory structures necessary for access to the datafiles) and all files (datafiles, controlfiles, redo logs, initialization files) associated with that instance. Oracle divides collections of related data into 'schemas'; a schema is 'owned' by a user. As an example:

create user beverage identified by ******* default tablespace bev temporary tablespace temp quota unlimited on bev;

grant connect, create session to bev;

The Oracle user 'bev' would function like your beverage database in SQL Server. Any tables, indexes, stored procedures, packages, functions, views, etc. that you would need for your beverage database would be created under the 'bev' user account. By default, you would access such tables through the 'bev' account, although there are options available to allow other user accounts to access (and, possibly, modify) the data 'bev' would 'own'. Also, this account does not need to be restricted to a single tablespace. The simple fact that 'bev' created the tables/indexes/objects is sufficient to associate them with the beverage application.

That being said, you really need to find out:

Knowing this you'll find, hopefully, a far more efficient way to create your Oracle database, and you'll stop thinking about creating ONE user tablespace and start thinking about creating several, to ease the data management issues.

There are other concerns as well. You'll have redo logs in Oracle, and those need to be separate from datafiles used for tablespaces. You'll have an UNDO tablespace, which should also be separate from the tablespaces used for application data. You'll have controlfiles (you should have 3) that should be located on different physical disks to prevent losing your database should one become corrupted due to a disk failure or a bad disk block. And, all of the above should be separate from each other (redo logs are not on the same disk as controlfiles, controlfiles not on the same disk as the UNDO tablespace).

I know this is far more than you originally asked, however your original question clearly indicated your misconceptions about Oracle and its configuration.

If you have any further questions email me.

David Fitzjarrell Received on Tue Jul 06 2004 - 17:03:36 CDT

Original text of this message