7

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'
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M  MAXSIZE 4096M;

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?

4
  • 1
    Try purge recyclebin;
    – Mihai
    Commented Sep 3, 2014 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. Commented Sep 3, 2014 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. Commented Sep 3, 2014 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. Commented Sep 3, 2014 at 20:28

3 Answers 3

2

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,
       x.*
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):

CREATE INDEX FTS_INDEX ON FILTERED_TEKLI_IIS_TABLOSU (ilAdi,ilceAdi,caddeAdi)
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.

1
  • 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.
    – user1822
    Commented Sep 23, 2015 at 18:57
1

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

SELECT ...
  FROM a,
       b,
       c
 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

SELECT ...
  FROM a 
       JOIN b
         ON( a.foo = b.foo )
       JOIN c
         <<oops, no join condition>>
0

I received this error while performing this kind of select:

SELECT tbl1.data
FROM tbl1
WHERE tbl1.id_of_tbl2 IN
    (SELECT tbl2.id
     FROM tbl2
     WHERE tbl2.id_of_tbl3 IN
         (SELECT tbl3.id
          FROM tbl3))

(I am not a DBA to tamper with TEMP space.)
My only successful attempt.
Creating an intermediate table:

CREATE TABLE z1 AS
SELECT DISTINCT tbl2.id 
FROM tbl2
INNER JOIN tbl3
ON tbl3.id = tbl2.id_of_tbl3;

SELECT tbl1.data
FROM tbl1
WHERE tbl1.id_of_tbl2 IN (SELECT * FROM z1);

Failed attempts.

1 of 2 WHERE reformed into JOIN

SELECT tbl1.data
FROM tbl1
WHERE tbl1.id_of_tbl2 IN 
    ( SELECT DISTINCT tbl2.id 
      FROM tbl2
      INNER JOIN tbl3
      ON tbl3.id = tbl2.id_of_tbl3 );

2 of 2 WHERE reformed into JOIN

SELECT tbl1.data
FROM tbl1
INNER JOIN ( SELECT DISTINCT tbl2.id 
             FROM tbl2
             INNER JOIN tbl3
             ON tbl3.id = tbl2.id_of_tbl3 ) x
ON x.id = tbl1.id_of_tbl2 ;

Introducing WHERE EXISTS

SELECT tbl1.data
FROM tbl1
WHERE EXISTS ( SELECT 1
               FROM ( SELECT DISTINCT tbl2.id 
                      FROM tbl2
                      INNER JOIN tbl3
                      ON tbl3.id = tbl2.id_of_tbl3 ) x
               WHERE x.id = tbl1.id_of_tbl2  );

Introducing WITH

WITH x AS
    (SELECT DISTINCT tbl2.id 
     FROM tbl2
     INNER JOIN tbl3
     ON tbl3.id = tbl2.id_of_tbl3 )
SELECT tbl1.data
FROM tbl1
WHERE tbl1.id_of_tbl2 IN (SELECT x.id FROM x);

Creating a view

CREATE VIEW w1 AS
SELECT DISTINCT tbl2.id 
FROM tbl2
INNER JOIN tbl3
ON tbl3.id = tbl2.id_of_tbl3;

SELECT tbl1.data
FROM tbl1
WHERE tbl1.id_of_tbl2 IN (SELECT * FROM w1);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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