Subject:            Creating the Second Tablespace
Last Revision Date: 11 September 1994
Author:    Saleem Haque                                                     



Each database is created with one tablespace, the SYSTEM tablespace.
You may create new tablespaces in order to enlarge the database or to
allocate space to certain applications. Other reasons why you may
want to add a new tablespace are:

     o  for the specific allocation of temporary or rollback segments
     o  to spread and control I/O contention
     o  to facilitate your backup/recovery scheme
     o  to control resource use on a user basis
     o  to separate table and index storage

To create a new tablespace, use the SQL statement CREATE TABLESPACE.
If this is the first tablespace to be added after SYSTEM, and you
have only the SYSTEM rollback segment, then you must also create an
additional rollback segment in the SYSTEM tablespace. This rollback
segment is necessary for the database to be able to create anything
in the new tablespace, and should be created in the SYSTEM tablespace
before creating the second tablespace.

Note that the CREATE TABLESPACE command will succeed even without
first creating the second rollback segment, but you would not be able
to create any objects in the new tablespace until another rollback
segment is created. This is why the second rollback segment must be
created in the SYSTEM tablespace.

If you want the second rollback segment to end up in the new
tablespace, proceed as follows.

1.   Create another rollback segment in the SYSTEM tablespace.

     CREATE ROLLBACK SEGMENT SYSTEM2
     TABLESPACE SYSTEM
     STORAGE (INITIAL 50K   NEXT 50K
              MINEXTENTS 2   MAXEXTENTS 99
              PCTINCREASE 0)

     It is advised that larger than the default storage parameters be
     used to create this segment, because although you might decide
     not to use this segment for everyday database operation, there
     are circumstances (described later in this document) under which
     it might be useful. Also, do not create any rollback segments as
     PUBLIC if the database will not be opened by multiple instances
     (Multi-instance support is currently not available with v6).

2.   Shutdown the database.

3.   Startup the database after adding the parameter
     rollback_segments to the init.ora file to acquire the newly
     created segment.

     ROLLBACK_SEGMENTS = (SYSTEM2)

4.   Create a second tablespace using the SQL statement CREATE
     TABLESPACE. For example

     CREATE TABLESPACE TS2
     DATAFILE 'TS2_FILE1.DBS' SIZE 20M
     DEFAULT STORAGE (INITIAL 10K   NEXT 10K
                      MINEXTENTS 1   MAXEXTENTS 99
                      PCTINCREASE 0)

5.   Create the rollback segment that you want in the newly created
     tablespace, TS2.

     CREATE ROLLBACK SEGMENT RBS1
     TABLESPACE TS2
     STORAGE (INITIAL ...   NEXT ...
              MINEXTENTS ...   MAXEXTENTS ...
              PCTINCREASE ...)

     It is advised that all rollback segments in use by a database at
     a given time be of the same size (the SYSTEM rollback segment is
     the only exception).

6.   At this point you can drop the rollback segment SYSTEM2 that was
     created in step 1 above, but we suggest that you don't. You
     should shutdown the database and start it up with the parameter
     ROLLBACK_SEGMENTS changed as follows:

     ROLLBACK_SEGMENTS = (RBS1)

Now if the DBA_ROLLBACK_SEGS view is queried, you will see that the
SYSTEM and RBS1 rollback segments are IN USE whereas the status of
the SYSTEM2 segment is AVAILABLE. Keeping a second rollback segment
available in the SYSTEM tablespace will let you use the database,
should the other tablespace TS1 (the tablespace with the rollback
segment RBS1) become unavailable and you have a third tablespace,
say TS2, with no rollback segments. Remember that although a database
with multiple tablespaces can be started with only the SYSTEM
rollback segement, only the SYSTEM tablespace is usable until another
rollback segment is acquired by the database.

Another time when the second rollback segment in the SYSTEM
tablespace can be used for maintenance purposes is when it is
desirable to drop all the other rollback segments. As in step 3
above, the database can be started with this so called maintenance
segment so only the SYSTEM2 rollback segment is acquired by the
database. All the other rollback segments can now be dropped. The
SYSTEM2 rollback segment can be returned to the AVAILABLE status
after recreating the dropped rollback segments, by shuting down the
database and starting it up with SYSTEM2 excluded from the
ROLLBACK_SEGMENTS init.ora parameter.