Auto Extend Off - System Impact and change procedure

[Former Member]

Hi Team,

We have done the DB Reorg and noticed that continuos freespace was not enough because of the auto allocation settings..

Could you pls let us know the system impact if we turn off the auto extend in Production system..

We are using SAPDBA , Oracle 8i ,SAP 4.6c..

Pls provide ur valuable suggestions..

If possible pls let us know the procedure to turn off the auto extend..

Regds,

Satyanarayana N.

[Former Member · Accepted solution]

Hi Satyanarayana N.

if I understand your question then you have the following situation:

your datafiles are configured with autoextend=ON

and as there is not enough freespace on your devices, you have problems when the autoextend mechanism begins to work, is that correct?

Basically the autoextend is a features that extends the datafiles automatically, if there is not enough space in the datafile anymore for the data that needs to be added. It is a feature, because it extends the datafile automatically as soon as the space in the datafile runs out - this helps you!! because there is no need for you then to monitor the filling of the datafile to manually extend it if necessary. But, to really benefit by this feature you need to make sure that there is enough free space on the devices in order that the autoextend can extend the files properly. If there is not enough space, the next attempt to autoextend a file will lead to errors like "No space on device left" (also mentioned in SAP note #546006)

If you turn autoextend off, then you will have to monitor the filling grade of your datafiles manually and extend them manually whenever there is a need for it. If you do not extend it although there is no space left in the datafile, then you will encounter error message like these (a better description for these errors can be found in SAP note #3155)

ORA-01653: unable to extend table <tab> in tablespace <tsp>

ORA-01654: unable to extend index <ind> in tablespace <tsp>

ORA-01655: unable to extend cluster <clus> in tablespace <tsp>

ORA-01658: unable to create INITIAL extent for segment in tablespace

<tsp>

ORA-01659: unable to allocate MINEXTENTS beyond <ext> in tablespace

<tsp>

So if you do not want to manually monitor the filling grade to be able to extend your datafiles manually whenever it is needed, then it is the better option to keep the configuration autoextend=ON and take care that there is enough free space on the devices where your datafiles are located.

If you still want to turn of AUTOEXTEND for a datafile, then you can first identify the datafiles that have autoextend=ON by querying:

select file_name, autoextensible from dba_data_files where autoextensible='YES';

Afterwards you can turn it off for a specific file by issuing:

alter database datafile '<path+datafile-name>' autoextend off;

regards,

beate