4 Replies Latest reply on Apr 4, 2013 3:37 AM by user648773

    TEMP space problem

      Hi Folks am getting the below error.

      I am doing an operation as below.
      insert into target t
      select * from source s1,s2,s3,s4  (4 tables)
      It is consuming high volume of TEMP space, Instead of increasing TEMP space size , please suggest any solution

      My query is having joins on 4 tables and dont have any group by and order by clauses. And i dont have any indexes on my table.

      I have seen the explain plan. In that most of the OPTIONS are HASH,FULL and SORT. Please suggest me how can i check which condition is using more TEMP space.
      Note: My version is 11gr2 and i dont have DBA previlages
      ORA-12801: error signaled in parallel query server P088
      ORA-01652: unable to extend temp segment by 32 in tablespace TEMP
      Please help me.

      Edited by: newbie on Apr 3, 2013 1:16 PM
        • 1. Re: TEMP space problem
          Databases are often optimized to handle only a few joins at a time. Depending on how much data you have per table, this will certainly generate a lot of garbled data.


          (1) Talk to your DBA. I'm sure he/she is aware by now (temp tablespaces don't shrink themselves). Maybe your DBA can help you come up with a better solution.

          (2) Try progressively building your table using intermediate results; e.g., CREATE TABLE t1 AS SELECT * FROM s1,s2, then CTAS 't1 X s3' and so on. Because you have a space issue, drop the intermediate tables as you move along. Also, identify a target tablespace for the intermediate and final tables, e.g., CREATE TABLE t1 TABLESPACE <your TBS> ...

          1 person found this helpful
          • 2. Re: TEMP space problem

            I had a chat with DBA, he said we have 500GB temp space available

            Folks can you please suggest me

            Edited by: newbie on Apr 3, 2013 2:21 PM
            • 3. Re: TEMP space problem
              You have four cross joins (also called cartesian products)
              in your statement. They and they alone are the cause of your TEMP space being used up.
              Rewrite the statement.

              Sybrand Bakker
              Senior Oracle DBA
              1 person found this helpful
              • 4. Re: TEMP space problem
                Are you using paralle for any particular reason? or one of the tables has degree in it definition?

                May be you can use the hint no_parallel to avoid the parallel query and reduce the use of temp.

                The query might look similar to this:

                insert into target t
                select /*+ NO_PARALLEL(s1) NO_PARALLEL(s2) NO_PARALLEL(s3) NO_PARALLEL(s4)*/ * from source s1,s2,s3,s4 (4 tables)
                1 person found this helpful