Re: schemas & tablespaces

From: Daniel A. Morgan <dmorgan_at_exesolutions.com>
Date: Tue, 16 Jan 2001 22:14:15 -0800
Message-ID: <3A653837.A135D36@exesolutions.com>

> I'm coming from a MS SQL Server background and want to know what the
> best method for creating new schemas is, as far as tablespaces are
> concerned. Is it better to create a new tablespace for each new
> schema? I'm equating schemas with SQL Server databases. Each schema
> will be application specific. If not, what is the recommended approach.

Oracle is definitely NOT SQL Server. And there is no relationship between users (schemas) and tablespaces other than that which you wish to create for purposes of back-up and recovery for to optimize data access. I have put multiple applications into a single tablespace but never would I ever put an entire application into a single tablespace.

The best practice for a single application is as follows. And I said best ... not mandatory.

Put the SYSTEM tablespace on a physical drive of its own. Put the ROLLBACK tablespace on a physical drive of its own. Put the TEMP tablespace on a physical drive of its own.

Build a tablespace for small table extents (e.g.. 64K - 512K)
Build a tablespace for medium table extents (e.g.. 1M - 50M)
Build a tablespace for large table extents (e.g.. 100M - 1G)

Do the same as the above three lines for indexes to decrease tablespace fragmentation
If you have tables that are used for validation purposes only put them into one or more separate tablespaces that are set READ-ONLY.

Now if you have a separate application you wish to put onto the same server make sure it has its own schema name and the choice as to whether to create new tablespaces, or not, is related more to available room and I/O than anything else.

Daniel A. Morgan Received on Wed Jan 17 2001 - 00:14:15 CST