Re: Creating multiple tables within a tablespace

Although this approach is used widely by other RDBMS vendors (INGRES to name but one) under Oracle it is advisable to group tables into tablespaces.

Within an Oracle environment is not uncommon for all the application tables in a database to be stored within one, or perhaps, two tablespaces. Whether this is the right solution for you is another matter (after glancing at your expected data volumes it almost certainly is not), but having 150 tablespaces would be very difficult to justify - it would be an administrator's nightmare !

This is not only due to the fact that each tablespace must be backed-up individually, but that you are significantly increasing the risk that a tablespace will fill with data and therefore bring you system to a halt. It is wise to allow a certain of space within each tablespace for growth (no matter how accurately you have sized your database), with 150 tablespaces that will result in a lot of unused space.

The situation woud only be aggrievated by the tablespaces which you are presumably reserving for indexes.

Jeff Hunter wrote in message <34ECC4D5.2EA2B2AC_at_isrv.com>...
>Greetings,
>
>We have a database with over 150 tables. Some tables
>may have 2,000,000 rows with record lengths of
>around 400 bytes. We currently are creating
>one tablespace per table in an Oracle 7.1 database
>running on OpenVMS. We are questioning the advantages
>and disadvantages of logically grouping several tables
>into larger tablepspaces thus reducing the total number
>of tablespaces we have to maintain. What are the compelling
>advantages of having multiple tables per tablespace when
>most of our tables are of this magnitude.
>
>Kindest Regards,
>Jeff Hunter
>
>--
>
>Jeffrey Hunter Internet: jhunter_at_isrv.com
>Programmer/Analyst WWW: www.isrv.com/~jhunter
>PO BOX 832 Voice: (412) 284-7325
>Butler, PA 16003-0832 FAX: (412) 789-9601
>
>
Received on Sat Feb 21 1998 - 00:00:00 CST