| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Unevent usage of datafiles within on tablespace
"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 10m5 uniform size 64;
Tablespace created.
SQL> select file_id, file_name from dba_data_files where
tablespace_name='BOWIE_
DATA';
FILE_ID
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:06:37 CST
 |
 |