Re: use of tablespaces in oracle

Mike Prompovitch <prompom_at_mcln.unisysgsg.com> wrote:
>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.

My experience is that tables and indexes should be placed in separate tablespaces (tbs). This can make it easier to distribute the "table" tbs datafiles onto separate disks from where the "index" tbs datafiles are located. (e.g. don't put the datafiles for your index onto the same drive as the datafiles for the associated table)

As far as grouping tables into a single tbs, I've created a tbs for each application. But, I don't know if that is the "best" method, it just works for me. I would suggest that you create a tbs for static-type tables and a separate tbs for the dynamic tables (i.e. if you have tables that have a lot of growth, or get dropped and recreated, or get truncated, place them in a separate tbs from those tables that have a slow growth). This makes it easier for me to manage extent growth.

Also, as system is the default tablespace when a database is created, before installing tools (e.g. sqlforms, srw), create a "tools" tbs and alter user system to use the "tools" tbs as its default tablespace. This will prevent the tools tables from frag'n the system tbs.

>
>I would appreciate any advice from experienced Oracle administrators.
>Thanks
>
>prompom_at_mcln.unisysgsg.com
>
>
Received on Tue Aug 08 1995 - 00:00:00 CEST