Re: Question on tablespace/file allocation

> I have also read that it is good practice to set up two files for each
> tablespace so that the files within each tablespace can be multiplexed
> across drives to minimize loss of data (please let me know if this is not
a
> good practice).

Probably a good idea in theory but hard to do in practice -- AFAIK Oracle fills up the "current"
extent of a table segment before allocating another one & since each extent is allocated in a single file
I don't know how you can accomplish striping this way. There is a way to pre-allocate multiple extents
to a segment -- the syntax is something like "ALTER TABLE foo ALLOCATE EXTENT SIZE ....
DATAFILE ..." but I've never bothered with it because Oracle will still fill up the current extent
before moving to the next one -- the next extent might be in a different file, but that does not give
you any "striping" on inserts -- though it could help on full table scans. Besides, most systems I've
worked on used RAID devices so that the hardware is already doing so much striping that anything
the database might add on top of that is probably useless.

> Here is my question: When I actually run the SQL scripts to create my
> tables, Oracle is putting almost all of the tables in only one of the two
> files I allocate, causing it to expand with as many as 5 extra extents,
and
> in some cases it never uses the second file at all, or maybe only one or
two
> tables will end up in the second file.

As I said it is difficult to get Oracle to do what you are attempting...

 > seem to have this same problem with my indexes, they seem more evenly
> distributed. Why would this be, and what (if anything) can I do to even
out

Index space management is a bit different from that of tables though again I think the kind of striping
you are looking for is difficult to achieve. And if you are using RAID disks it probably would not
accomplish anything anyhow. If I were you I would worry more about tuning SQL statements
if you are looking to improve performnace in ways that would be visible to end-users. Received on Tue Aug 21 2001 - 12:08:11 CDT