Re: Unevent usage of datafiles within on tablespace

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3dff4cbb$0$248
> 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.

I've got a nasty feeling that I've missed something bad here (Like file 25 actually being created far later than file 4 in the above eg I'll double check later). anyway for what its worth my two theories were

1. Index segments behave differently. and
2. Export/import especially with compress=y and locally managed tablespaces might screw things up because imp would say right create me one large extent (compress=y) as the initial extent (so picking datafile 1) and the use of LMT's would give a load of extents (but all in file 1).

Both of these appear to be bogus ideas

SQL> create tablespace tables
  2 datafile 'c:\oracle\oradata\home9i\tables.dbf' size 1049m;

Tablespace created.

(I need a table)

SQL> create tablespace indx2
  2 datafile 'c:\oracle\oradata\home9i\index01.dbf' size 10m,   3 'c:\oracle\oradata\home9i\index02.dbf' size 10m,   4 'c:\oracle\oradata\home9i\index03.dbf' size 10m   5 extent management local uniform size 64k;

Tablespace created.

(this is what I care about)

SQL> create table tab1(col1 char(2000))   2 tablespace tables;

Table created.

SQL> create index i_tab1 on tab1(col1) tablespace indx2;

Index created.

(single index in tablespace indx2)

SQL> begin
  2 for i in 1..1000 loop
  3 insert into tab1 values (to_char(i));   4 commit;
  5 end loop;
  6 end;
  7 /

PL/SQL procedure successfully completed.

(one table and therefore index populated)

SQL> select file_id,count(*)
  2 from dba_extents
  3 where tablespace_name='INDX2'
  4 group by file_id;

   FILE_ID COUNT(*)
---------- ----------

        11         15
        12         16
        13         15

even spread theory 1 dies.

SQL> export table here compress=y
SP2-0734: unknown command beginning "export tab..." - rest of line ignored. SQL> drop table tab1;

Table dropped.

SQL> import table here
SP2-0734: unknown command beginning "import tab..." - rest of line ignored. SQL> select file_id,count(*)
  2 from dba_extents
  3 where tablespace_name='INDX2'
  4 group by file_id;

   FILE_ID COUNT(*)
---------- ----------

        11         14
        12         14
        13         14

even spread theory 2 dies.

--
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
******************************************