Re: Question on tablespace/file allocation

Comments embedded.
"Marcia Thomasson" <marcia_at_nospam.solcominc.com> wrote in message news:3b82911a_at_nntp01.splitrock.net...
> I am trying to create a set of tablespaces to divide my tables up into.
For
> example, a tablespace for blobs, a tablespace for infrequently modified
> lookup tables, a tablespace for history records, and a tablespace for
> everything else. Plus, each of these tablespaces also has a corresponding
> separate tablespace for indexes. (I also have RBS's and System
separated).

This is recommended practice

> 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).

This is probably recommended practice for performance reasons but I don't see how it will minimise loss of data. If you lose a datafile you will have to perform recovery hoowever many datafiles make up he tablespace.
>
> I have gone through the excercise of sizing each table and allocating the
> appropriate amount of space for it when it is created, and I have also
added
> up the total amount of space I need for each tablespace and created the
> tablespace with that amount of space in two files (with the amount divided
> by 2). I have also done this for my indexes.

Sizing the tables is a good exercise, but what do you mean by 'allocating the appropriate amount of space'. If you just mean make sure the tablespace is bigger than the total size of the objects to be stored in it then that is good. If however you mean create the table with an initial extent equal to the expected size of the data then that is bad. You should be using uniform extent sizes within a tablespace.

>
> 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. Also, interestingly enough, I
don't
> 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
> the distribution?

I would guess that the autoextend clause is why your tables all go into one datafile. If autoextend is off then of course even if objects all started in datafile 1 they would have to be allocated to datafile 2 when the first was full.

I presume that this behaviour has to do with the way Oracle finds the next free extent to allocate to an object. However I can't explain why different object types populate tablespaces in different ways.

--
Niall Litchfield
Oracle DBA
Audit Commission UK