Re: Unevent usage of datafiles within on tablespace

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:k4FL9.5037$jM5.14918_at_newsfeeds.bigpond.com...
> Hi again,
>
> This is a little demo I have that highlights the how extents are allocated
> in a multi data file tablespace. I've embedded comments to help describe
> what's going on.

Cheers.

However I have a specific tablespace with two datafiles (4 & 25) with extent distribution thus

  1 select segment_name,file_id,count(*)   2 from dba_extents
  3 where segment_name = 'AIAGLTRANSACT2'   4* GROUP BY segment_name,file_id
SQL> /

SEGMENT_NAME                        FILE_ID   COUNT(*)
-------------------------------- ---------- ----------
AIAGLTRANSACT2                            4       1070
AIAGLTRANSACT2                           25         72

Despite the file numbers the two datafiles were created at the same time (first create tablespaces then add extra datafiles to those that will need them). As I say I do have a couple of competing theories about how this happens but it is at least possible in a production database to get extent distribution that is skewed not only across datafiles but also for any given segment.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************