Home » RDBMS Server » Server Administration » autoextend on tablespace with multiple datafiles (Oracle 10.2.0.2)
autoextend on tablespace with multiple datafiles [message #304202] Tue, 04 March 2008 09:01 Go to next message
godinqc
Messages: 3
Registered: March 2008
Location: Canada
Junior Member
We have a tablespace with mulitple datafiles
ex: tbs_01.dbf and tbs_02.dbf
.
We would like to enable autoextend, I assume that we should only enable autoextend on the latest datafile ex: tbs_02.dbf
Since tbs_01.dbf is not really used to store new data anymore
Thanks
Re: autoextend on tablespace with multiple datafiles [message #304204 is a reply to message #304202] Tue, 04 March 2008 09:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Since tbs_01.dbf is not really used to store new data anymore
We do not get to decide that Smile.
Oracle will use any datafile within the tablespace.
>>We would like to enable autoextend,
May I know why?
Re: autoextend on tablespace with multiple datafiles [message #304210 is a reply to message #304202] Tue, 04 March 2008 09:49 Go to previous messageGo to next message
godinqc
Messages: 3
Registered: March 2008
Location: Canada
Junior Member
Here's the situation
We have 2 tablespaces that have 2 datafiles each.
One tablespace contains the data the the other the indexes
ex:
tablespace1 contains the data and has 2 datafiles
tbsD_01.dbf and tbsD_02.dbf
tablespace2 contains the index and has 2 datafiles
tbsX_01.dbf and tbsX_02.dbf
.
tbsD_01.dbf has already hit the O/S limit of 32G, so this is why we created a second datafile tbsD_02.dbf
tbsX_01.dbf is close to hitting the 32G limit, so we also created a second datafile tbsX_02.dbf, before hitting the O/S limit
.
We have a hugh batch job that will be running soon and we want to make sure it does not fail because of freespace so this is why we want to enable the autoextend. I was under the impression that the first datafile is not used to store new data, this is why we were only going to enable autoextend on the second datafile only.
.
So are so saying that we should enable the autoextend on both datafiles for each tablespaces ?
Has mentionned tbsD_01.dbf has already reached it's O/S limit so it cannot extend, what we are trying to avoid is that oracle tries to autoextend tbsX_01.dbf and might fail before it might reach it's O/S limitation by then. If this occurs will it automatically switch to the second datafile tbsX_02.dbf and autoextend it ?
.
Thanks





Re: autoextend on tablespace with multiple datafiles [message #304215 is a reply to message #304210] Tue, 04 March 2008 09:59 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Since first datafile is already near max capacity, not worth extending it. Oracle will try use any space available within all the datafiles. We can only assign the tablespace, not a datafile within the tablespace to be used.
>>we want to make sure it does not fail because of freespace
I assume you are not in a position to estimate the space requirements for the job. If so, what if the second datafile will also fill up (even after autoextend on?).
Re: autoextend on tablespace with multiple datafiles [message #304221 is a reply to message #304202] Tue, 04 March 2008 10:15 Go to previous messageGo to next message
godinqc
Messages: 3
Registered: March 2008
Location: Canada
Junior Member
.
>>I assume you are not in a position to estimate the space >>requirements for the job. If so, what if the second datafile >>will also fill up (even after autoextend on?).

The second datafile is currently only 2G, it can grow up to 32G,
our job will only take a couple of gigs, so we are fine with the autoextend.
.
So just to confirm, there's no point in enabling autoextend on both the first and second datafile, only the second datafile should be enough.
.
If we put autoextend on the first and second datafile, is there a possiblity that Oracle tries to autoextend the first datafile and fail because of the O/S limit , as the first datafile is about 31G and cannot grow more than 32G.
This is why I was going to put the autoextend on the second datafile only as the second datafile has plenty of room to grow
.
Thanks



Re: autoextend on tablespace with multiple datafiles [message #304228 is a reply to message #304221] Tue, 04 March 2008 10:28 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
This is why I was going to put the autoextend on the second datafile only as the second datafile has plenty of room to grow

I would do the same. Also, you can set a MAXSIZE upto which the AUTOEXTEND can grow.
Previous Topic: object necessary for warmstarting database cannot be altered
Next Topic: [urgent] ASK: How to increase Free_MB in my physical standby database?
Goto Forum:
  


Current Time: Sat Dec 03 22:08:52 CST 2016

Total time taken to generate the page: 0.05675 seconds