Autoextensible on

[Former Member]

Hi,

In a tablespace we have some of the datafiles as autoextensible and some of them as off.Can we change all of the data files to be autoextensible off.Can we do it online.Thanks.

[lbreddemann · Accepted solution]

> In a tablespace we have some of the datafiles as autoextensible and some of them as off.Can we change all of the data files to be autoextensible off.Can we do it online.Thanks.

Sure.

And you may also use the documentation or the search function here in SDN to find out how.

Another easy option would be to just display the BRSPACE short help

brspace -h
BR1001I BRSPACE 7.10 (25)
General syntax:
        brspace [<main_options>] -f|-function <function> [<function_options>]
Main options:
        [-c|-confirm [force]]
                unattended mode - no confirmation required
                [no confirmations at all]
                default: attended mode
[...]
Program functions:
        dbstart   - start up database
[...]
        dfalter   - alter data file
        dfmove    - move data file
        tbreorg   - reorganize tables
[...]

Ok, so there's something like a dfalter function...

Further down the list we even find a detailed description of the functionality...

[...]
Options for 'dfalter' function:
        [-a|-action online|offline|autoext|fixsize|resize|rename|drop]
                alter data file action:
                online  - set data file online
                offline - set data file offline
                autoext - maintain autoextend
                fixsize - switch off autoextend
                resize  - resize data file
                rename  - rename data file
                drop    - drop empty data file
                no default
        [-c|-force]
                force offline mode
                default: don't set offline if SAP user connected
        [-f|-file all_df|<file>|<file_id>|<file_id1>-<file_id2>|<file_list>]
                file name
                all_df - all eligible data files
                no default
        [-i|-incrsize <size>]
[...]

Wow - there even is a function for switch off autoextend.

And even better: you can select just the eligible datafiles for that!

Now, with just a very short attention span we can build a nice little BRSPACE command to do the job:

brspace -u / -c -f dfalter -a fixsize -f all_df

Looks like magic, doesn't it?

regards,

Lars

[Former Member · Accepted solution]

Lars,

What can i say its simply too good.One more question if we have to change the table space to auto extend off so does that mean we have to change it for data files?Apologies if i have asked a basic question.

[lbreddemann · Accepted solution]

>

> Lars,
> What can i say its simply too good.One more question if we have to change the table space to auto extend off so does that mean we have to change it for data files?Apologies if i have asked a basic question.

Yes, precisely!

AUTOEXTEND ON or OFF is a setting on datafile level!

It's never set on the tablespace.

What irritates users sometimes is the way this informaiton is displayed in DB02/DBACockpit and the BRTOOLS.

There you'll always see that a tablespace is set to autoextend on, when at least one of the datafiles belonging to the tablespace is setup as autoextensible.

regards,

Lars

[Former Member]

Hi Ambarish,

You can do it from Oracle Level with

Alter database datafile /oracle/GC2/sapdata3/users_1/users.data1' autoextend off

Or you can use BRTOOLS where you need to login with ORASID (Oracle) and brtools > space management > alter datafile and then choose the Filename e.g. (users.data1) and Then choose an action, that is 'fixsize'. Autext' is for switching on and 'fixsize' is for switching off.

The summary log space<DBSID>.log displays the return code.

The structure change log struc<DBSID>.log logs all structure changes

More Help on BRTOOLS http://help.sap.com/saphelp_erp2004/helpdata/en/f1/5e863f06593a17e10000000a114084/content.htm

Hope it will help you to achieve !

Edited by: prateek_y on Jun 9, 2010 2:59 PM

[former_member227600]

yes we can do this using brtools. you have to just change the properties of data files using brtools. select the re-size option for data file which one you want to do auto extend off & change minor size increase/decrees , chose auto extend off option.