ORA-1653: unable to extend table ...

[Former Member]

Hi All,

We have faced a very odd issue a few days ago. In our BW environment (Oracle 11.2.3) one load failed with the tipical space message:

ORA-1653: unable to extend table SAPXXX./BIC/AGPAO10A00 by 8192 in                 tablespace PSAPXXX

But the free space in PSAPXXX was 480GB !!!

We added another Datafile of 30GB but the error happened again. Finally we add 120GB more and the problem was solved.

The metioned table (SAPXXX./BIC/AGPAO10A00) growths only 2GB after the load.

The only logic explanation is that the TS is so fragmentated that was not able to allocate 2 GB continous, but with 480 GB free it sounds rare.

Any other idea? Do you know any method to get the fragmentation level of a TS?

Kind Regards

[Former Member · Accepted solution]

One more check:

Is it a DMTS? if yes, then the best option would be converting the TS to LMTS (so that extent management issue can be fixed).

Regards,

Nick Loy

[Former Member · Accepted solution]

Hi,

It is a LMTS tablespace.

We wouldn't like to got for Autoextent option. We want to have our DB space in a more controlled manner.

Having almost 500 GB of free space and not be able to extent by 8MB is not very normal in my eyes. We work on daily basis to keep the free space enough but in this case 500GB was not enough .

Kind Regards

[stefan_koehler · Accepted solution]

Hi Jose,

> It is a LMTS tablespace.

Your issue sounds like someone has converted this tablespace with PL/SQL procedure dbms_space_admin.tablespace_migrate_to_local and your extents are still allocated per storage clause (allocation type USER). Using the mentioned PL/SQL procedure just changes the way how the allocation is managed internally (at data file level).

What is your allocation type?

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

The allocation type of the TS is SYSTEM.

It's also true that this DB has suffered several radical growths up and drops down in the last year. Maybe it's not so strange to have this fragmentation level, see this graph, Make it any sense?

Kind Regards

[stefan_koehler · Accepted solution]

Hi Jose,

usually not (but depends on the extents size as it is scaled down to 1 MB and not to 64 KB free space) as you have mentioned that you have added a 30 GB (new data file), but the growths was only 2 GB. So i assume that the table already (>= 1 GB) exists and so the extent size should only be 32 x 64 MB (without considering the scale down option in LMTs here) and fit into it.

However as you are using LMTs with SYSTEM, i assume that you are using ASSM too (and have no corruptions in its bitmaps) and in this case you may hit the problem that is described in MOS ID #1539699 - ORA-01654, ORA-01653 When Tablespace have plenty of free space.

If you don't have MOS access you can also check a blog post by Randolf Geist about this issue. Do not be confused about this Exadata stuff he mentions.

Regards

Stefan

[Former Member · Accepted solution]

Hi again Stefan,

I went through the MOS ID #1539699 but the workaround is to switch to MSSM but I'm wondering if just enabling event 60060 level 1 should be enough to solve the isuue.

Am I wrong?

Regards

[stefan_koehler · Accepted solution]

Hi Jose,

no you are not wrong, if your issue is caused by the extent alignment to AU boundaries. My previous post (check the start of it) was based on the assumption that you have enough continuous 1 MB space blocks (with an existing table) and the AU boundaries are different.

Martin Bach has recently published a blog post about another root cause of such effects. However at first you need to figure out the root cause in your specific case, before implementing some parameter changes or whatever.

Regards

Stefan

[Former Member · Accepted solution]

Hi Stefan,

This last blog looks very similar to my case as being a SAP BW environment the tables that suffered the issue were partitioned tables and with a huge number of partitions (one of them has 931 partitions).

The problem is the suggested workaround:

For now the work around is to move segments serially

As you know in a SAP environment is not always easy to avoid parallel processing.

Many thanks for your support.

[Former Member]

Dear All

Reorganiz Table sapce or Table ,,,,

Nainesh

[Former Member]

Agree with you but with TS of 5 TB and business of 24x7 is not always to do.

Anyway, thanks for the advice.

Jose

[Former Member]

TS of 5 TB

Online reorg may help!

Regards,

Nick Loy

[Former Member]

Hi,

Seems a very rare situation, may below link help.

Oracle Tablespace Reorganization

Regards,

Nick Loy

[former_member188883]

Hi Jose,

You need to check the free space in the next extents for the tablespace.

Possible reason is that you have datafiles with autoextend =ON. In such case if we do not have enough space in the tablespace next extents, operation will fail due to insufficient space.

Hope this helps.

Regards,

Deepak Kori