Datafiles and Table Spaces Auto Extensible - Unlimited

[Former Member]

Hi

Basically, we are using the method of Datafiles/Table Spaces as Auto Extension OFF. But some reason since BRTOOLS have been upgraded (6.40 (51)) all the datafiles/Table spaces have been changed to Auto Extension ON.

DB02 - its status

Sample

Max. autoextensible/kb Unlimited

Tablespace Size (kb) Free (kb) Used (%) Tab/ind Extents AutoExt (kb)

PSAPBTABD 213,603,352 45,614,848 78 4,113 15,003 Unlimited

PSAPBTABI 103,788,448 43,566,976 58 5,672 19,686 Unlimited

PSAPCLUD 38,092,744 8,863,680 76 36 980 Unlimited

PSAPCLUI 3,096,576 1,372,480 55 36 386 Unlimited

PSAPDARTD 2,047,992 1,893,376 7 14 164 Unlimited

PSAPDARTI 1,536,000 1,344,704 12 14 163 Unlimited

PSAPDDICD 1,008,616 377,088 62 288 743 Unlimited

PSAPDDICI 559,088 175,104 68 339 831 Unlimited

DBA_DATA_FILES

FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS AUTOEXTENSIBLE

/oracle/XXX/sapdata5/btabd_1/btabd.data1 136 PSAPBTABD 5360312320 654335 YES

/oracle/XXX/sapdata5/btabd_2/btabd.data2 137 PSAPBTABD 4911521792 599551 YES

/oracle/XXX/sapdata7/btabd_3/btabd.data3 138 PSAPBTABD 6031409152 736256 YES

/oracle/XXX/sapdata5/btabd_4/btabd.data4 139 PSAPBTABD 4395622400 536575 YES

/oracle/XXX/sapdata6/btabd_5/btabd.data5 140 PSAPBTABD 3414163456 416768 YES

/oracle/XXX/sapdata3/btabd_6/btabd.data6 141 PSAPBTABD 3338665984 407552 YES

/oracle/XXX/sapdata4/btabd_7/btabd.data7 142 PSAPBTABD 3254779904 397312 YES

-

---

Like wise we have 265 datafies for all table spaces.

Could pelase let me know how can we change all the datafiles/tables spaces to auto extension off? And is there any procedure or SQL statement through which we can change them to auto off?

Thanks in advance

[Former Member · Accepted solution]

Dear Kasinath,

You can do that using Brtools and is quite simple.

using Brtools go to SPACE MANAGEMENT- > Alter data file- Alter datafile action->autoext

Press continue

Select option "Turn off Autoextend"

Then it wil list the datafiles once listed you can select all the datafiles by giving the range (example 1-80)

and autoextend will be turned off for all the datafiles that you selected.

Gerard

[Former Member · Accepted solution]

Hi Gerard

I did it successfully........your process was correct......basically I was giving range selecting point 5 "Data file names (file) .......... []"

It was not askjng the range after selecting "Turn off Autoextend"; that was why it was just taking 1 datafile when I did before you give me the solution.

Thanks for your solution.

[stefan_koehler]

Hello Kasinath,

> Could pelase let me know how can we change all the datafiles/tables spaces to auto extension off?

You will need a SQL script for that ... here we go: http://www.oriolecorp.com/scripts/autoextend_off.sql

This script will set all your permanent data files to autoextend off .. if you also want to set your temporary files to autoextend off .. you need to add an additional query on DBA_TEMP_FILES.

Regards

Stefan

[Former Member]

Hi Stefan

Thanks for the scirpt provided.

Before doing it in our prod system I wanted to test it in our sand box where in all the datafiles are auto extend off.

Here my question is I would need to auto extend on in our sandbox. Is there any other script for auto extend on or if we can change the same script what lines to be changed and what should be in those lines?

Can you please let me know?

Thank-you

Hi Gerard

Already I did this process but some reason it was just taking system datafile though I had specified the number range as you mentioned but not other datafiles.

Edited by: Kasinath Parepally on Jan 7, 2010 4:24 PM

[Former Member]

Kasinath ,

I will take all the datafile but in the screen you will see only the first datafile , but look for the number next to "command"

it will give you the number of commands that are going to be executed.

Just proceed and it will turn off autoextend on all.

example

[alter database datafile '/oracle/RBQ/sapdata1/saux_1/saux.data1' autoextend off;... (4 commands)

Here in this case i have given a range of 1-4 ( see above it says "4 commands"

Gerard