I've inherited basic DBA-like responsibilities on an Oracle database and I'm stuck on a ORA-01652 error while trying to run a query that contains lots of joins and grouping. Here's the error I get:

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
01652. 00000 -  "unable to extend temp segment by %s in tablespace %s"
*Cause:    Failed to allocate an extent of the required number of blocks for
           a temporary segment in the tablespace indicated.
*Action:   Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
           files to the tablespace indicated.

I have taken the advice (or at least tried to) of the error message and created a new data file. Here's the command I used for that:

ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ABCDEFG/temp02.dbf'

Now when I look into DBA_TEMP_FILES I see both the old and new files associated with the TEMP tablespace, yet when I attempt to re-run my query, I get the same error message.

So, my question is why does this error still happen, given that the query I'm running is big but not that big. Did I create the data file correctly? How can I diagnose this further?

  • 1
    Try purge recyclebin; – Mihai Sep 3 '14 at 18:06
  • @Mihai Thanks for the suggestion, but I get the same result. I saw nothing in the recyclebin, but I ran the purge command anyway and got the same error when I then tried to run the query. – Chris Farmer Sep 3 '14 at 18:19
  • 8
    You have increased the TEMP tablespace by 4 GB - that is no guarantee for not getting this same error again. Check the value of BYTES and MAXBYTES in DBA_TEMP_FILES for the newly added tempfile. If BYTES equals MAXBYTES, your new tempfile has reached its maximum size and the TEMP tablespace got full again, and I would rather focus on the query - an ineffective execution plan can cause excessive temp usage. What you think (should not be that big) and what the database "thinks" are sometimes completely different. – Balazs Papp Sep 3 '14 at 18:45
  • @BalazsPapp Thanks for your help. That was exactly the problem. It never occurred to me that the new file I created might not have been big enough. – Chris Farmer Sep 3 '14 at 20:28

Each tablespace has one or more datafiles that it uses to store data.

The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max gb per datafile.

run the following for actual allowed size:

select value from v$parameter where name = 'db_block_size';

Compare the result you get with the first column below, and that will indicate what your max datafile size is.

I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).

Block Sz   Max Datafile Sz (Gb)   Max DB Sz (Tb)

--------   --------------------   --------------

   2,048                  8,192          524,264

   4,096                 16,384        1,048,528

   8,192                 32,768        2,097,056

  16,384                 65,536        4,194,112

  32,768                131,072        8,388,224

You can run this query to find what datafiles you have, what tablespaces they are associated with, and what you've currrently set the max file size to (which cannot exceed the aforementioned 32gb):

select bytes/1024/1024 as mb_size,
       maxbytes/1024/1024 as maxsize_set,
from   dba_data_files x

MAXSIZE_SET is the maximum size you've set the datafile to. Also relevant is whether you've set the AUTOEXTEND option to ON (its name does what it implies).

If your datafile has a low max size or autoextend is not on you could simply run:

alter database datafile 'path_to_your_file\that_file.DBF' autoextend on maxsize unlimited;

However if its size is at/near 32gb an autoextend is on, then yes, you do need another datafile for the tablespace:

alter tablespace system add datafile 'path_to_your_datafiles_folder\name_of_df_you_want.dbf' size 10m autoextend on maxsize unlimited;

Also it is generally a good/common practice for you to have a tablespace that is dedicated to indexes (it doesn't seem that you do, because you didn't specify the tablespace in your create index statement, and it is using the default system tablespace), so after creating such a tablespace, you would run something like this (in your case):

tablespace name_of_ts_for_indexes

(After creating that tablespace and a datafile for it), via:

create tablespace name_of_ts_for_indexes datafile
'c:\app\xyz\oradata\orcl\name_of_ts_for_indexes01.dbf' autoextend on maxsize unlimited nologging;

That way your tables would be on one table space and the indexes on another.

Thanks to Brian D. for additional Information.

| improve this answer | |
  • 1
    While the information is correct, this has nothing to do with the original problem which is about the TEMP tablespace not regular tablespaces for table and index data. – a_horse_with_no_name Sep 23 '15 at 18:57

Unless you have some reason to believe that your query should require more than 4 GB of TEMP space, something that would be very unusual unless you are doing something like building aggregates from very very large tables, it sounds like your query has a bug. If I had a query that contained "lots of joins" and consistently exhausted the TEMP tablespace, I would wager that the query was missing one or more join conditions which is causing Oracle to do one or more Cartesian joins (meaning that N rows in one table and M rows in another table becomes N*M rows in the result set). With even moderately large tables, that causes a massive amount of temporary space to be required.

If you look at the query plan, I would wager that you would see one or more MERGE JOIN CARTESIAN operations. If you can identify the tables that are Cartesian joined, that should help you pinpoint which join condition(s) are missing.

If you are using the old join syntax where all the join conditions appear in the WHERE clause

  FROM a,
 WHERE a.foo = b.foo

you might try rewriting the query using the SQL 99 syntax. That generally makes it easier to notice that you're missing a join condition

  FROM a 
       JOIN b
         ON( a.foo = b.foo )
       JOIN c
         <<oops, no join condition>>
| improve this answer | |

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.