Re: use of tablespaces in oracle

In article <DCy5z4.1r2_at_HAN.UnisysGSG.COM>, Mike Prompovitch <prompom_at_mcln.unisysgsg.com> writes:
> I have a question on the use of tablespaces in Oracle. We are developing
> some new applications in Oracle, and being new to the product have gone
> to Oracle training. Unfortunatly, we have gone at different times and
> recived contradictory advice from the instructors. In this case one
> instructor said to create a separate tablespace for each table in the
> database. The other said that tablespaces are logical areas and that the
> tables should be grouped in tablespaces based on how they are used. This
> instructor also said that indexes should be placed in a single tablespace
> to reduce the administration effort due to the high degree of
> fragmentation caused by indexes.
>
> I would appreciate any advice from experienced Oracle administrators.
> Thanks
>
> prompom_at_mcln.unisysgsg.com
>
>

-- 

The second instructor is right.

You should have at least the following tablespaces:
  SYSTEM (if possible only to be used for data dictionary entries)
  USERS (for user tables)
  INDEX (for user indices)
  RBS (for rollback segements to prevent fragmentation)
  TEMP (for temporary data, also to prevent fragmentation)

If you have seveleral independant or nearly independant schemata maybe used for
different applications you should try to create different tablespaces for
their user data. For example APPLICATION1_USERS, APPLICATION2_USERS etc.
You can also do this for indices but it's not so important. 

The advantage of this is that you can now set an application dependent
tablespace offline, backup the files belonging to it and then set the
tablespace online again. Other applications within the same database will not
be hindered through the backup then.

Be carefull with the rollback tablespace and the rollback segments. The storage
parameters have to ensure that in case of an insert the largest table within
the database fits in there.

Regards
Yvonne
---------------------------------------------------------------------------
Yvonne Baumert       LL     TTTTTTTTTT  UU    UU   The statements made 
LTU GmbH             LL     TT  TT  TT  UU    UU   represent my own opinion 
Airport, Hangar 8    LL         TT      UU    UU   and do not necessarily 
D-40474 Düsseldorf   LL         TT      UU    UU   correspond with my 
Germany              LL         TT      UU    UU   employers opinion.
y_baumert_at_decus.ch   LLLLLL    TTTT      UUUUUU     

PSI%(0262)45211013068::baumert  Some countries don't need the leading 0!!
---------------------------------------------------------------------------