Set "autoextend = off" for all datafiles

[Former Member]

Hi!

My DB partittion seems to be full. Therefore I would like to set all for all the tablespaces with their datafile the autoextend on "off".

Then I will create a second DB partition where I will create additional data files.

Question:

What is the appropriate SQL-commando/skript to set up for all the datafiles the autoextend on state "off"?

With the following commando I can generate all the appropriate sql statements:

select 'alter database datafile '''||file_name||''' autoextend off;'

from dba_data_files

where AUTOEXTENSIBLE='YES';

Thank you very much!

regards

[Former Member]

I use to have the same problem but I prefer to use de BR*tools 'cause its easier and in this mode you'll have logs, and you can see the real status of your datafiles.

My 2 cents.

Regards.

[sarbajitm]

ALTER DATABASE DATAFILE filename2

AUTOEXTEND OFF;

Regards.

Sarbajit

[former_member204746]

if you would have run CHEKDB in DB13 once a day, you would have received warnings about autoexent size being too big for your partition. Are you running that job?

[Former Member]

Have you thought about BRTOOLS?

[http://help.sap.com/saphelp_nw04/helpdata/EN/f1/5e863f06593a17e10000000a114084/content.htm]

[Former Member]

First: BRTOOLS does not offer to set autoextend mode for all the datafile, but only for each data file.

Second: I would like avoid to use BRTOOLS and use SQL commando

Thanks & regards

[lbreddemann]

> First: BRTOOLS does not offer to set autoextend mode for all the datafile, but only for each data file.

Of course, because AUTOEXTEND is a per-datafile setting.

> Second: I would like avoid to use BRTOOLS and use SQL commando

So why would you do that?

By using direct sql commands you:

- have to type in all the commands yourself, which is error prone

- have no standard logging of your action - analysing errors in case something went wrong gets much harder that way

- have no chance to change your mind the last minute

Anyhow, what you can do is use brspace via commandline and specify all datafiles to be modified like this:

brspace -u / -f dfalter -a fixsize -f all_df -t <tablespace_name>

BRSPACE will check which files actually need to be changed and only apply the change to them.

hope this helps,

regards,

Lars

[Former Member]

Your choice Sir

[Former Member]

Oracle makes all (virtually all) of their commands available in a convenient location.

http://www.oracle.com/pls/db102/homepage