0

Hello I have The tablespace with 8192 and According with this Link From Oracle BigFile I can have 32,768 GB In my tablespace but one of my table have over 1 billion records that make datafile over the 120GB and I getting error with ORA-01652: unable to extend temp segment by XXX in tablespace so what happening here why with 120GB I'm reaching my limit in datafile?

CREATE BIGFILE TABLESPACE TABLE_SPACE 
    DATAFILE 
        '~\TABLE_SPACE.DBF' SIZE 127301910528 AUTOEXTEND ON NEXT 8192 MAXSIZE 35184372064256 
    BLOCKSIZE 8192 
    DEFAULT NOCOMPRESS NO INMEMORY 
    ONLINE 
    SEGMENT SPACE MANAGEMENT AUTO 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

1

ORA-01652: unable to extend temp segment by XXX

This isn't about your 120GB bigfile data tablespace at all.

Your TEMP tablespace isn't big enough to handle whatever sorts or temporary result sets are created by the queries that are being run in your database. If it can't autoextend, then it is probably because you have filled up the disk drive and you don't have room for more, or because the TEMP tablespace data file(s) have reached the maximum size allowed by the file system (typically 32GB each).

If you still have space available on the file system holding your TEMP tablespace, then add one or more more files to the TEMP tablespace. If the partition is completely full, then work with your system and/or storage administrator to determine how to enlarge the file system or where else you could place additional data files.

0
-2

You need more free blocks in your temp tablespace, you should manually extend your temp file(s) size in order to complete your operation.

3
  • 1
    The error message indicates that the storage device is out of space or that the files have reached their maximum size allowed by the file system: it is not possible to extend the existing files at all or the auto-extend setting would have done it already.
    – pmdba
    Apr 27 '21 at 4:38
  • I encountered the same issue when importing a backup (impdp) and had to manually extend the temp tablespace even though auto-extend was set to on and there was of course still space on the disk. (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0)
    – Amine C
    Apr 27 '21 at 13:47
  • I was drawing a distinction between extending existing files (handled by auto extend) and adding additional data files (always a manual process). Your answer appears to speak towards extending existing files.
    – pmdba
    Apr 27 '21 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.