Nice example, Richard.
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:k4FL9.5037$jM5.14918_at_newsfeeds.bigpond.com...
> "Cupnoodle" <noodle_at_cowmama.com> wrote in message
news:3dfe84a8_at_shknews01...
> > Richard Foote <richard.foote_at_bigpond.com> wrote:
> > I go thru DbArtisan on all the tables of that tablespace but can't see
any
> extent defined to use a particular datafiles.
> >
> > Also the datafile which got fill up is not the first one the tablespace.
> >
> > Can you tell me how can i find out more on that?
> >
> > Moreover, does a record in a table can't store accross datafile within
> same tablespace?
> >
>
> 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.
>
> SQL> create tablespace bowie_data
> 2 datafile 'c:\bowie\bowie_data01.dbf' size 10m,
> 3 'c:\bowie\bowie_data02.dbf' size 10m,
> 4 'c:\bowie\bowie_data03.dbf' size 10m
> 5 uniform size 64;
>
> Tablespace created.
>
>
> SQL> select file_id, file_name from dba_data_files where
> tablespace_name='BOWIE_
> DATA';
>
> FILE_ID
> ----------
> FILE_NAME
> --------------------------------------------------------------------------
--
> ----
>
> 16
> C:\BOWIE\BOWIE_DATA01.DBF
>
> 17
> C:\BOWIE\BOWIE_DATA02.DBF
>
> 18
> C:\BOWIE\BOWIE_DATA03.DBF
>
> OK, simply create a tablespace with 3 data files.
>
>
> SQL> create table one (x number) tablespace bowie_data;
>
> Table created.
>
> SQL> create table two (x number) tablespace bowie_data;
>
> Table created.
>
> SQL> create table three (x number) tablespace bowie_data;
>
> Table created.
>
> SQL> create table four (x number) tablespace bowie_data;
>
> Table created.
>
>
> Now I've create 4 tables in this tablespace. Let's see which data file
they
> were placed in ...
>
>
> SQL> select segment_name, file_id from dba_extents where tablespace_name =
> 'BOW
> E_DATA';
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> ONE 16
> TWO 16
> THREE 16
> FOUR 16
>
> Note that *all* tables have their first extent created in the *first* data
> file defined to the tablespace.
>
> Now lets grow these tables and see what happens next.
>
>
> SQL> alter table one allocate extent;
>
> Table altered.
>
> SQL> alter table two allocate extent;
>
> Table altered.
>
> SQL> alter table three allocate extent;
>
> Table altered.
>
> SQL> alter table four allocate extent;
>
> Table altered.
>
> I have allocated another extent to each table ...
>
> SQL> select segment_name, file_id from dba_extents where tablespace_name =
> 'BOWI
> E_DATA' order by segment_name;
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> FOUR 16
> FOUR 17
> ONE 16
> ONE 17
> THREE 16
> THREE 17
> TWO 16
> TWO 17
>
> 8 rows selected.
>
>
> ... and the second extent of each table has been created in the second
data
> file of the tablespace.
>
> If a particular table were to keep growing ...
>
>
> SQL> alter table four allocate extent;
>
> Table altered.
>
> SQL> alter table four allocate extent;
>
> Table altered.
>
> SQL> select segment_name, file_id from dba_extents where tablespace_name =
> 'BOWI
> E_DATA' order by segment_name;
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> FOUR 16
> FOUR 17
> FOUR 18
> FOUR 16
> ONE 16
> ONE 17
> THREE 16
> THREE 17
> TWO 16
> TWO 17
>
> 10 rows selected.
>
> you can see how the extents get allocated to the data files in a round
robin
> fashion. But the first extent is allocate to the first data file
(providing
> it has sufficent space) ...
>
> SQL> create table five (x number) tablespace bowie_data;
>
> Table created.
>
> SQL> select segment_name, file_id from dba_extents where tablespace_name =
> 'BOWI
> E_DATA' order by segment_name;
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> FIVE 16
> FOUR 16
> FOUR 17
> FOUR 18
> FOUR 16
> ONE 16
> ONE 17
> THREE 16
> THREE 17
> TWO 16
> TWO 17
>
> 11 rows selected.
>
> I created a new table, it's first extent is allocated to the first data
> file.
>
> Let's add a new data file. What happens now ...
>
> SQL> alter tablespace bowie_data add datafile 'c:\bowie\bowie_data04.dbf'
> size 10m;
>
> Tablespace altered.
>
> SQL> alter table four allocate extent;
>
> Table altered.
>
> SQL> select segment_name, file_id from dba_extents where tablespace_name =
> 'BO
> E_DATA' order by segment_name;
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> FIVE 16
> FOUR 16
> FOUR 17
> FOUR 18
> FOUR 17
> FOUR 16
> ONE 16
> ONE 17
> THREE 16
> THREE 17
> TWO 16
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> TWO 17
>
> 12 rows selected.
>
> I add a new extent to table four. It was up to the file 16, so file 17 is
> used ...
>
>
> SQL> alter table four allocate extent;
>
> Table altered.
>
> SQL> select segment_name, file_id from dba_extents where tablespace_name =
> 'BO
> E_DATA' order by segment_name;
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> FIVE 16
> FOUR 16
> FOUR 17
> FOUR 18
> FOUR 18
> FOUR 17
> FOUR 16
> ONE 16
> ONE 17
> THREE 16
> THREE 17
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> TWO 16
> TWO 17
>
> 13 rows selected.
>
> .... then file 18 ....
>
>
> SQL> alter table four allocate extent;
>
> Table altered.
>
> SQL> select segment_name, file_id from dba_extents where tablespace_name =
> 'BO
> E_DATA' order by segment_name;
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> FIVE 16
> FOUR 16
> FOUR 17
> FOUR 18
> FOUR 18
> FOUR 19
> FOUR 17
> FOUR 16
> ONE 16
> ONE 17
> THREE 16
>
> SEGMENT_NAME FILE_ID
> ------------ ----------
> THREE 17
> TWO 16
> TWO 17
>
> 14 rows selected.
>
> ... and now the new file 19 is used. IE, the files are still used in a
round
> robin fashion with the new file slipping in.
>
> Note how file 16 is the *most* used file and file 19 is the least. If I
were
> to allocate several new tables that were only 1 or 2 extents in size, see
> how file 16 would be the one to be most "filled".
>
> This is what I "suspect" is happening in your case.
>
> Hope it makes some kinda sense.
>
> Cheers
>
> Richard
>
> > cupnoodle (beef)
> > > Hi Niall/Cupnoodle (chicken or beef ?)
> > >
> > > I "think" you may find the reason for this is that the first extent of
> each
> > > segment is placed in the first data file. Therefore by definition the
> first
> > > datafile will fill up quickest and the degree by which it does so
> depends on
> > > how many objects have few extents. In an extreme example, if all your
> > > objects have just the one extent, then the first data file will have
all
> the
> > > extents allocated to it while the rest have zip.
> > >
> > > Cheers
> > >
> > > Richard
> > > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
> message
> > > news:3dfdb4e1$0$239$ed9e5944_at_reading.news.pipex.net...
> > >> Its not immediately obvious to me, but I do have the same behaviour
> > >> exhibited here. Once in an index tablespace and one in a tablespace
> > > reserved
> > >> for LOBs. I must admit I thought that the datafile for a next extent
> was
> > >> chosen on a 'round robin' basis but it appears that this is not
> *always*
> > > the
> > >> case, at the moment I'd suspect that this behaviour is either linked
to
> > >> particular segment types (noting that LOBs have associated indexes)
or
> > >> perhaps to exp/imp. Unless anyone else already has the answer I think
> we'd
> > >> need to devise a test.
> > >>
> > >>
> > >> --
> > >> 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
> > >>
> > >> ******************************************
> > >> "Cupnoodle" <klau_at_cowmama.com> wrote in message
> news:3dfd1d98_at_shknews01...
> > >> > All the tables in that tablespace has same extent sizes.
> > >> > Also same extent config on all datafiles.....
> > >> >
> > >> > any clue?
> > >> >
> > >> > "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in
message
> > >> > news:3dfcfb85$0$225$cc9e4d1f_at_news.dial.pipex.com...
> > >> > > different extent sizes for the objects in the tablespace?
> > >> > >
> > >> > > --
> > >> > > 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
> > >> > > ******************************************
> > >> > > "Cupnoodle" <klau_at_cowmama.com> wrote in message
> > >> > news:3dfc2d41$1_at_shknews01...
> > >> > > > I have a 20G tablespace "striped" accross 15datafiles. But in
DBA
> > >> > studio,
> > >> > > > one of the datafile filled up while others just 40% capacity
> > >> > > >
> > >> > > > how can that happen??
> > >> > > >
> > >> > > > Peter
> > >> > > >
> > >> > > >
> > >> > >
> > >> > >
> > >> >
> > >> >
> > >>
> > >>
> > >
> > >
> >
> > --
> > I'm a cup of noodle
> > ----
> > Work like you don't need the money
> > Love as though you have never been hurt
> > Dance as though no one is watching you
> > Sing as though no one can hear you
> > ----
>
>
Received on Tue Dec 17 2002 - 08:02:42 CST
