Skip to Content
avatar image
Former Member

ORA-1653: unable to extend table ...

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 24, 2014 at 09:00 AM

    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


    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Stefan Koehler

      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.

  • Jan 24, 2014 at 08:57 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 24, 2014 at 08:57 AM

    Hi,

    Seems a very rare situation, may below link help.

    Oracle Tablespace Reorganization

    Regards,

    Nick Loy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 24, 2014 at 11:48 AM

    Dear All

    Reorganiz Table sapce or Table ,,,,

    Nainesh

    Add comment
    10|10000 characters needed characters exceeded