Bigfile in oracle 12 is full with 120G

All we need is an easy explanation of the problem, so here it is.

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;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply