| autoextend on tablespace with multiple datafiles [message #304202] |
Tue, 04 March 2008 09:01  |
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 #304210 is a reply to message #304202] |
Tue, 04 March 2008 09:49  |
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 #304221 is a reply to message #304202] |
Tue, 04 March 2008 10:15 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
.