Lesson 11	Creating initial tablespaces
Objective	Create initial tablespaces for your database.

Creating initial Tablespaces

There are at least four tablespaces that every OFA-compliant database should have. Tablespaces you should have These are:

USERS: Holds user tables, indexes, and so forth.
TEMP: Used for sorting.
RBS: Holds the rollback segments.
TOOLS: Holds tables and indexes for tools such as Enterprise Manager, Discoverer 2000, and so forth.

You can create these databases from Server Manager by using the CREATE TABLESPACE command. The syntax for that command looks like this:

CREATE TABLESPACE tablespace_name
 DATAFILE ‘filename’ SIZE integer[M|K];

Tablespace guidelines

This is a simplified version of CREATE TABLESPACE, but it will work for now. Before you can create these tablespaces, you need to decide on file locations, filenames, and file sizes for each of these. The OFA guidelines call for these tablespaces to be placed on separate drives, but because this is not a production database, let's place the files in the same directory used for all the other database files.

Placing temporary and rollback tablespace files

It's especially critical to place the temporary tablespace files and the rollback tablespace files on drives of their own if that is at all possible. These files sustain a heavy I/O load, and you want to distribute a database's I/O load over as many disks as possible.
Take the temporary tablespace, for example. The primary use of the temporary tablespace is to sort data. If you are executing a large query against a table and you used an ORDER BY clause to sort that data, Oracle will be reading table data from the datafiles, and writing it to the temporary tablespace. If both your temporary tablespace and your table tablespace are on the same drive, you will lose a lot of performance as the drive has to take turns first doing one thing and then another. Rollback segments sustain a heavy load during update transactions. Every time you issue a SQL statement that changes data, the original version of that data is written out to a rollback segment. Again, for performance reasons, you don't want rollback segments on the same drives as your datafiles. That load should be spread out over as many drives as possible.
Placement of the USERS and TOOLS tablespaces may be less important. On many of the databases that I manage, these are very lightly used, if they are used at all. In that case, it doesn't bother me to put them both on the same disk.

For names and sizes, let's go with the following:

Tablespace Name  File Name   Size

 USERS     users01.dbf   5MB

 TOOLS     tools01.dbf   5MB

 TEMP     temp01.dbf   5MB

 RBS      rbs01.dbf   5MB

How you size these tablespaces depends on what tools you have installed and on the applications running against the database.

Create Initial Tablespaces - Exercise

Now, try this exercise to create tablespaces for your database.
Create Initial Tablespaces - Exercise