PSAPSR3 autoextend

[Former Member]

HI Gurus

can any one guide me how to make table AUTOEXTEND ON. For my system tablespacePSAPSR3 autoextend is showing off at GUI level(db02), Where from brtools it is showing for PSAPSR3 autoextend is ON, still space left at file system level...but it is giving dump like

Database error text........: "ORA-01688: unable to extend table

SAPSR3./BIC/B0000095000 partition /BIC/B00000950000000000002 by 8192 in

tablespace PSAPSR3"

Internal call code.........: "[RSQL/INSR//BIC/B0000095000 ]"

Please check the entries in the system log (Transaction SM21).

But why it is showing differnet entries at brtools(psapsr3 autoextend :ON and at GUI level:DB02(psapsr3 autoextend : OFF)

How can i change autoextend ON for PSAPSR3 at db02 or using tcode

Thanks in advance.

[lbreddemann · Accepted solution]

Hi Balaji,

the big misunderstanding (that of course happens to many others as well) is that the AUTOEXTEND functionality is not a tablespace setting but a datafile setting.

As DB02 shows you the information just tablespace wise it will report AUTOEXTEND ON as soon as at least one of the datafiles has AUTOEXTEND ON.

Anyhow: it can happen that Oracle chooses one of the datafiles that are not autoextensible and tries to write data into it. If then the freespace is not sufficient, Oracle won't move to another file and try to store the data there. Instead it will stop that transaction and you see the error e.g. your ORA-1688.

Therefore, make sure that either none of the datafiles is autoextensible or all of them.

You can easily check this setting via BRTOOLS:

(2 - Space management -> 7 - Additional space functions -> 1 = Show tablespaces)

-

---

List of database tablespaces Pos. Tablespace Type Status ExtMan. SegMan. Backup Files/AuExt. Total<KB> Used<%> Free<KB> ExtSize<KB> FreeExt. Largest<KB> 1 - PSAPBUT DATA ONLINE LOCAL AUTO NO 6/5 18075712 99.92 13696 819200 10 819200+:4096:1984:1024:1024 2 - PSAPBUT620 DATA ONLINE LOCAL AUTO NO 4/4 8704000 93.62 555520 819200 88 819200+:24576:16384:11200:10240 ---/ ... / --- -

---

As you can see PSAPBUT has 6 datafiles but only 5 of them are set to be autoextensible.

DB02 will anyhow report that PSAPBUT is autoextensible.

To change this setting BRTOOLS are just fine.

The following is an example on my testmachine 'BUT'.

BR0656I Choice menu 5 - please make a selection -

---

Database space management 1 = Extend tablespace 2 - Create tablespace 3 - Drop tablespace 4 - Alter tablespace 5 - Alter data file 6 - Move data file 7 + Additional space functions 8 - Reset program status Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your choice: 5 BR0280I BRTOOLS time stamp: 2008-04-16 17.14.09 BR0663I Your choice: '5' BR0280I BRTOOLS time stamp: 2008-04-16 17.14.09 BR0657I Input menu 85 - please check/enter input values -

---

BRSPACE options for alter data file 1 - BRSPACE profile (profile) ....... <initBUT.sap> 2 - Database user/password (user) ... </> 3 ~ Alter data file action (action) . <> 4 ~ Tablespace names (tablespace) ... <> 5 ~ Data file names (file) .......... <> 6 - Confirmation mode (confirm) ..... <yes> 7 - Scrolling line count (scroll) ... <20> 8 - Message language (language) ..... <E> 9 - BRSPACE command line (command) .. <-p initBUT.sap -s 20 -l E -f dfalter> Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your choice: 4 BR0280I BRTOOLS time stamp: 2008-04-16 17.14.11 BR0663I Your choice: '4' BR0280I BRTOOLS time stamp: 2008-04-16 17.14.11 BR0681I Enter string value for "tablespace" <>: PSAPBUT BR0280I BRTOOLS time stamp: 2008-04-16 17.14.13 BR0683I New value for "tablespace": 'PSAPBUT' BR0280I BRTOOLS time stamp: 2008-04-16 17.14.13 BR0657I Input menu 85 - please check/enter input values -

---

BRSPACE options for alter data file 1 - BRSPACE profile (profile) ....... <initBUT.sap> 2 - Database user/password (user) ... </> 3 ~ Alter data file action (action) . <> 4 ~ Tablespace names (tablespace) ... <PSAPBUT> 5 ~ Data file names (file) .......... <> 6 - Confirmation mode (confirm) ..... <yes> 7 - Scrolling line count (scroll) ... <20> 8 - Message language (language) ..... <E> 9 - BRSPACE command line (command) .. <-p initBUT.sap -s 20 -l E -f dfalter -t PSAPBUT> Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your choice: c BR0280I BRTOOLS time stamp: 2008-04-16 17.14.16 BR0663I Your choice: 'c' BR0259I Program execution will be continued... BR0291I BRSPACE will be started with options '-p initBUT.sap -s 20 -l E -f dfalter -t PSAPBUT' BR0280I BRTOOLS time stamp: 2008-04-16 17.14.16 BR0670I Enter 'c<ont>' to continue, 'b<ack>' to go back, 's<top>' to abort: c BR0280I BRTOOLS time stamp: 2008-04-16 17.14.17 BR0257I Your reply: 'c' BR0259I Program execution will be continued... ############################################################################### BR1001I BRSPACE 6.40 (45) BR1002I Start of BRSPACE processing: sdxsgncr.dfa 2008-04-16 17.14.17 BR0484I BRSPACE log file: E:\oracle\BUT\sapreorg\sdxsgncr.dfa BR0280I BRSPACE time stamp: 2008-04-16 17.14.19 BR1009I Name of database instance: BUT BR1010I BRSPACE action ID: sdxsgncr BR1011I BRSPACE function ID: dfa BR1012I BRSPACE function: dfalter BR0280I BRSPACE time stamp: 2008-04-16 17.14.19 BR0656I Choice menu 314 - please make a selection -

---

Alter data file main menu 1 - Set data file online 2 - Set data file offline 3 - Turn on and maintain autoextend 4 - Turn off autoextend 5 - Resize data file 6 - Show data files 7 - Show tablespaces 8 - Show disk volumes 9 * Exit program 10 - Reset program status Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your choice: 3 BR0280I BRSPACE time stamp: 2008-04-16 17.14.25 BR0663I Your choice: '3' BR0280I BRSPACE time stamp: 2008-04-16 17.14.28 BR0659I List menu 315 + please select one or more entries -

---

List of data files for alter Pos. Tablespace Status Type Size<KB> AuExt. File 1 - PSAPBUT ONLINE FILE 3051520 YES E:\ORACLE\BUT\SAPDATA1\BUT_1\BUT.DATA1 2 - PSAPBUT ONLINE FILE 3051520 YES E:\ORACLE\BUT\SAPDATA1\BUT_2\BUT.DATA2 3 - PSAPBUT ONLINE FILE 3031040 YES E:\ORACLE\BUT\SAPDATA1\BUT_3\BUT.DATA3 4 - PSAPBUT ONLINE FILE 2756672 YES E:\ORACLE\BUT\SAPDATA4\BUT_4\BUT.DATA4 5 - PSAPBUT ONLINE FILE 3092480 YES E:\ORACLE\BUT\SAPDATA4\BUT_5\BUT.DATA5 6 - PSAPBUT ONLINE FILE 3092480 NO E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6 Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your selection: 6 BR0280I BRSPACE time stamp: 2008-04-16 17.14.33 BR0663I Your selection: '6' BR0280I BRSPACE time stamp: 2008-04-16 17.14.33 BR0657I Input menu 316 - please check/enter input values -

---

Options for alter of data file E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6 1 * Current data file status (status) ....... <FIXSIZE> 2 * Current data file size in MB (currsize) . <3020> 3 * Alter data file action (action) ......... <autoext> 4 # Force offline mode (force) .............. <no> 5 ? Maximum file size in MB (maxsize) ....... <> 6 ? File increment size in MB (incrsize) .... <> 7 # New data file size in MB (size) ......... <> 8 - SQL command (command) ................... <alter database datafile 'E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6' autoextend on next M maxsize M> Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your choice: 5 BR0280I BRSPACE time stamp: 2008-04-16 17.14.38 BR0663I Your choice: '5' BR0280I BRSPACE time stamp: 2008-04-16 17.14.38 BR0682I Enter integer value for "maxsize" (0-32767) <>: 3500 BR0280I BRSPACE time stamp: 2008-04-16 17.14.48 BR0683I New value for "maxsize": '3500' BR0280I BRSPACE time stamp: 2008-04-16 17.14.48 BR0657I Input menu 316 - please check/enter input values -

---

Options for alter of data file E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6 1 * Current data file status (status) ....... <FIXSIZE> 2 * Current data file size in MB (currsize) . <3020> 3 * Alter data file action (action) ......... <autoext> 4 # Force offline mode (force) .............. <no> 5 - Maximum file size in MB (maxsize) ....... <3500> 6 ? File increment size in MB (incrsize) .... <> 7 # New data file size in MB (size) ......... <> 8 - SQL command (command) ................... <alter database datafile 'E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6' autoextend on next M maxsize 3500M> Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your choice: 6 BR0280I BRSPACE time stamp: 2008-04-16 17.14.55 BR0663I Your choice: '6' BR0280I BRSPACE time stamp: 2008-04-16 17.14.55 BR0682I Enter integer value for "incrsize" (1-32767) <>: 1 BR0280I BRSPACE time stamp: 2008-04-16 17.15.01 BR0683I New value for "incrsize": '1' BR0280I BRSPACE time stamp: 2008-04-16 17.15.01 BR0657I Input menu 316 - please check/enter input values -

---

Options for alter of data file E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6 1 * Current data file status (status) ....... <FIXSIZE> 2 * Current data file size in MB (currsize) . <3020> 3 * Alter data file action (action) ......... <autoext> 4 # Force offline mode (force) .............. <no> 5 - Maximum file size in MB (maxsize) ....... <3500> 6 - File increment size in MB (incrsize) .... <1> 7 # New data file size in MB (size) ......... <> 8 - SQL command (command) ................... <alter database datafile 'E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6' autoextend on next 1M maxsize 3500M> Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your choice: 6 BR0280I BRSPACE time stamp: 2008-04-16 17.15.04 BR0663I Your choice: '6' BR0280I BRSPACE time stamp: 2008-04-16 17.15.04 BR0682I Enter integer value for "incrsize" (1-32767) <1>: 50 BR0280I BRSPACE time stamp: 2008-04-16 17.15.05 BR0683I New value for "incrsize": '50' BR0280I BRSPACE time stamp: 2008-04-16 17.15.05 BR0657I Input menu 316 - please check/enter input values -

---

Options for alter of data file E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6 1 * Current data file status (status) ....... <FIXSIZE> 2 * Current data file size in MB (currsize) . <3020> 3 * Alter data file action (action) ......... <autoext> 4 # Force offline mode (force) .............. <no> 5 - Maximum file size in MB (maxsize) ....... <3500> 6 - File increment size in MB (incrsize) .... <50> 7 # New data file size in MB (size) ......... <> 8 - SQL command (command) ................... <alter database datafile 'E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6' autoextend on next 50M maxsize 3500M> Standard keys: c - cont, b - back, s - stop, r - refr, h - help -

---

BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2008-04-16 17.15.07 BR0663I Your choice: 'c' BR0259I Program execution will be continued... BR0280I BRSPACE time stamp: 2008-04-16 17.15.08 BR0370I Directory E:\oracle\BUT\sapreorg\sdxsgncr created BR0280I BRSPACE time stamp: 2008-04-16 17.15.10 BR0319I Control file copy created: E:\oracle\BUT\sapreorg\sdxsgncr\cntrlBUT.old 6955008 BR0280I BRSPACE time stamp: 2008-04-16 17.15.10 BR1016I SQL statement 'alter database datafile 'E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6' autoextend n next 50M maxsize 3500M' executed successfully BR1078I Data file E:\ORACLE\BUT\SAPDATA4\BUT_6\BUT.DATA6 altered successfully into status 'autoext BR0280I BRSPACE time stamp: 2008-04-16 17.15.10 BR0340I Switching to next online redo log file for database instance BUT ... BR0321I Switch to next online redo log file for database instance BUT successful BR0280I BRSPACE time stamp: 2008-04-16 17.15.13 BR0319I Control file copy created: E:\oracle\BUT\sapreorg\sdxsgncr\cntrlBUT.new 6955008 BR0280I BRSPACE time stamp: 2008-04-16 17.15.13 BR0256I Enter 'c<ont>' to continue, 's<top>' to cancel BRSPACE:

As you see, I just chose to alter a datafile from the tablespace PSAPBUT. Then I got a list of the datafiles and could easily manage the autoextend setting via the menu without having to deal with SQL commands.

Hope that helps,

Lars

[stefan_koehler · Accepted solution]

Hello Lars,

> Anyhow: it can happen that Oracle chooses one of the datafiles that are not autoextensible and tries to write data into it. If then the freespace is not sufficient, Oracle won't move to another file and try to store the data there. Instead it will stop that transaction and you see the error e.g. your ORA-1688.

That is interesting - in which cases can this happen?

In the most database we already have LMT and ASSM and round robin applies after 16 extents.

We have some datafiles with AUTOEXTEND ON (the new ones) and the old ones are AUTOEXTEND OFF.

Until now i have never seen such a situation.

But with LMT, this is happening because of the extent algorithm of LMT (decreasing extent sizes, if insufficient space is available).

@ Balaji:

Have you checked your MAXEXTEND SIZE, maybe your datafile is AUTOEXTEND on, but you have reached your MAX limit?

Regards

Stefan

[Former Member · Accepted solution]

Thank you Lars bredd.

[lbreddemann · Accepted solution]

Hi Stefan,

unfortunately I was not able to reproduce this behaviour yet (otherwise I would have handed it over to Oracle so that they can fix the bug).

Nevertheless I worked several times on support messages where exactly this happened.

Most often a large Batchjob was aborted but ran through just after restarting it.

The usual suspect caus (lack of freespace in filesystem) was never the reason for it.

So - I cannot actually proove it.

KR Lars