11 Replies Latest reply on Nov 12, 2010 9:25 AM by 762809

DATA FILES AUTO EXTEND ON/OFF

762809 Nov 12, 2010 7:42 AM

Hi :)

I have the following data_files info list in our PROD database:

select file_name,tablespace_name,bytes/1024,AUTOEXTENSIBLE,status from dba_data_files order by tablespace_name;

/uo1/oracle/oatrngdata/a_txn_data04.dbf  APPS_TS_TX_DATA                  11,888,640Kb YES AVAILABLE
/uo1/oracle/oatrngdata/a_txn_data02.dbf  APPS_TS_TX_DATA                   1,283,840Kb YES AVAILABLE
/uo1/oracle/oatrngdata/a_txn_data01.dbf  APPS_TS_TX_DATA                   1,481,600Kb YES AVAILABLE
/uo1/oracle/oatrngdata/a_txn_data03.dbf  APPS_TS_TX_DATA                   1,202,560Kb YES AVAILABLE

Note that data04.dbf is already 11G, I believe its autoextend on, but upto now I really do not know which data dictionary column tells that It was the active autoextended on data file, because I know all of them are set to autoextend on.

I want data04.dbf to stop autoextending, and shift it to data03.dbf using:

ALTER DATABASE DATAFILE '/uo1/oracle/oatrngdata/a_txn_data04.dbf' AUTOEXTEND OFF;

ALTER DATABASE DATAFILE '/uo1/oracle/oatrngdata/a_txn_data03.dbf' AUTOEXTEND ON ( I think no need to do this one because they are all set to "autoextend on".

Will this be okay? I planning that each data files will have a uniform size of 11g.
My question is, since all the data files have been set to "AUTOEXTEND ON", if I turn off the data04.dbf,
which of the 3 remaining will get activated then or will extend first?

Thanks a lot for your kind assistance...

Ms K

1. Re: DATA FILES AUTO EXTEND ON/OFF

Hussein Sawwan-Oracle Nov 12, 2010 7:44 AM (in response to 762809)

Hi,

Will this be okay? I planning that each data files will have a uniform size of 11g.
Yes, it should be OK.

How to Determine if a Datafile has AUTOEXTEND ENABLED [ID 1023280.6]
How To Prevent The SYSTEM Tablespace From Growing Excessively [ID 198477.1]
How to Take a Datafile out of AUTOEXTEND Mode [ID 115178.1]

My question is, since all the data files have been set to "AUTOEXTEND ON", if I turn off the data04.dbf,
which of the 3 remaining will get activated then?
The database will pick up the file(s), and the DBA has no control over it.

Thanks,
Hussein
Like Show 0 Likes(0)

Actions
2. Re: DATA FILES AUTO EXTEND ON/OFF

762809 Nov 12, 2010 8:01 AM (in response to Hussein Sawwan-Oracle)

Thanks hussein ;)

So Is it like this? If I have four data files in tablespace APPS_DATA, at each datafile is autoextend on with maxsize of 11G,
then if the first one reach 11g it will stop writing there and switch to the next data_file ? right?
Like Show 0 Likes(0)

Actions
3. Re: DATA FILES AUTO EXTEND ON/OFF

Helios-GunesEROL Nov 12, 2010 8:39 AM (in response to 762809)

Hi;

So Is it like this? If I have four data files in tablespace APPS_DATA, at each datafile is autoextend on with maxsize of 11G,
then if the first one reach 11g it will stop writing there and switch to the next data_file ? right?
If your datafile has 11G autoextend limit and if its full than yes it will use other datafile which is free.

Regard
Helios
1 person found this helpful
Like Show 0 Likes(0)

Actions
4. Re: DATA FILES AUTO EXTEND ON/OFF

762809 Nov 12, 2010 9:10 AM (in response to Helios-GunesEROL)

Thanks again...

One more thing...

Supposing I did not AUTOEXTEND OFF data04.dbf...so it will keep extending...
but supposing I did this:

ALTER DATABASE DATAFILE '/uo1/oracle/oatrngdata/a_txn_data03.dbf' RESIZE 11G;

From 1gb size, the data03.dbf now becomes 11G giving freespaces of 10G.

My question is, will the data04.dbf stop extending? to make use of the new 10G available freespace?
Like Show 0 Likes(0)

Actions
5. Re: DATA FILES AUTO EXTEND ON/OFF

Helios-GunesEROL Nov 12, 2010 9:22 AM (in response to 762809)

Hi;

ALTER DATABASE DATAFILE '/uo1/oracle/oatrngdata/a_txn_data03.dbf' RESIZE 11G;
From 1gb size, the data03.dbf now becomes 11G giving freespaces of 10G.
My question is, will the data04.dbf stop extending?
I belive yes, but as Hussein Sawwan's mention, datafile filled automaticly which means it can use data04.dbf instead of data03.dbf. Its better to confirm it wiht oracle support.

Regard
Helios
1 person found this helpful
Like Show 0 Likes(0)

Actions
6. Re: DATA FILES AUTO EXTEND ON/OFF

Hussein Sawwan-Oracle Nov 12, 2010 9:09 AM (in response to 762809)

Hi,

My question is, will the data04.dbf stop extending?
Not necessarily. As mentioned above the DBA has no control on what datafile will get filled by the database.

Thanks,
Hussein
1 person found this helpful
Like Show 0 Likes(0)

Actions
7. Re: DATA FILES AUTO EXTEND ON/OFF

762809 Nov 12, 2010 9:11 AM (in response to Hussein Sawwan-Oracle)

So it is a must that I have to turn in OFF :)
Like Show 0 Likes(0)

Actions
8. Re: DATA FILES AUTO EXTEND ON/OFF

Hussein Sawwan-Oracle Nov 12, 2010 9:13 AM (in response to 762809)

Hi,

So it is a must that I have to turn in OFF :)
If there is no more free space available in this specific datafile and you want it to stop growing then you have to turn autoextend off.

Thanks,
Hussein
1 person found this helpful
Like Show 0 Likes(0)

Actions
9. Re: DATA FILES AUTO EXTEND ON/OFF

762809 Nov 12, 2010 9:15 AM (in response to Hussein Sawwan-Oracle)

Yeah, I just want to test if oracle is "intelligent enough" to see that there is another big space available :)
Like Show 0 Likes(0)

Actions
10. Re: DATA FILES AUTO EXTEND ON/OFF

Hussein Sawwan-Oracle Nov 12, 2010 9:19 AM (in response to 762809)

Hi,

Yes it is (it is about tablespaces not datafiles), so if autoextend is off on one or more tablespace(s) and it is on for one datafile that belongs to that specific tablespace the database will write data to that datafile.

Thanks,
Hussein
1 person found this helpful
Like Show 0 Likes(0)

Actions
11. Re: DATA FILES AUTO EXTEND ON/OFF

762809 Nov 12, 2010 9:25 AM (in response to Hussein Sawwan-Oracle)

ok :)
Like Show 0 Likes(0)

Actions

Go to original post