ORA-01688: unable to extend table but we have enough space in TS

[former_member190251]

Hello Team

We are getting the below alert in SM21 and ST22 but we have enough space in PSAPSR3 TS (around 150GB).

I have added 1 new data file after that.

I am not able to understand this as we have enough space in TS

Could you please let us know how this error came and how to avoid this?

SM21

ST22

Regards

Sub

[Former Member]

Hi Sub,

Can we see the screenshots of alert log where this error occurs.

It looks like the case Fidel said.. there is not enough contiguous space available. In simple terms, there are not enough sequential oracle free blocks available to create an extent. whatever free space is available is fragmented (uncontiguous). btw what is the oracle version ?

[stefan_koehler]

Hi Sub,

as you hit this issue in case of partitioning you may be affected by a combination of these:

• ASM AU Size And LMT AUTOALLOCATE
• MOS ID #1295484.1

We already discussed it here as well:

Regards

Stefan

[Brindavan_M]

Hello ,

This is most common issue ORA-01688. How much size you have added the datafile. As per the screen shot its show 8192 in tablespace PSAPSR3. Have you add the datafile enough space and tablespace is autoextend?

For example like below

ALTER TABLESPACE users ADD DATAFILE  ‘/ora01/oracle/oradata/booktst_users_02.dbf’ size <>;

resize the datafile

ALTER TABLESPACE users ADD DATAFILE ?/ora01/oracle/oradata/booktst_users_02.dbf? resize <>;

If everthing fine as you created then restart the SAP system as well DB and check the same if the issue still come.

Thanks,

Brindavan M

[fidel_vales]

Subbu Samb wrote:

Hello Team

We are getting the below alert in SM21 and ST22 but we have enough space in PSAPSR3 TS (around 150GB).

I have added 1 new data file after that.

I am not able to understand this as we have enough space in TS

Could you please let us know how this error came and how to avoid this?

... 
Regards
Sub

Hi,

you have to keep in mind that you need "contiguous" space on the "same" datafile in order to allocate one extent. It might be that "globally" you have enough space but that is adding all "small" spaces that might be not possible to use. in general, this is the typical case for the error you have.

[Former Member]

Hi Sub,

Kindly try adding data file to tablespace PSAPSR3. Refer below note and link for more details-

SAP note#3155 - Termination due to tablespace overflow

ORA-01688- unable to extend table tips

Regards,

Ashish