OraFAQ Forum: Performance Tuning » ORA-01652-unable to extend temp segment by 128 in tablespace TEMP

Home » RDBMS Server » Performance Tuning » ORA-01652-unable to extend temp segment by 128 in tablespace TEMP (11.1.0.7.0)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #561796]

Wed, 25 July 2012 06:18

Scowron
Messages: 4
Registered: February 2012
Location: Poland

Junior Member

Welcome

One of our customer have problem with following sql statement:

SELECT c.table_name, c.column_name
  FROM user_tab_columns c, user_tables t
 WHERE c.table_name = t.table_name
   AND c.data_type IN ('CLOB', 'BLOB');

During execution it takes all the TEMP tablespace size(8GB).

I gather system stats (dbms_stats.gather_dictionary_stats(estimate_percent=>null)) but it doesn't resolve problem.
Above sql statement works fine with RULE hint but I want to know what is the reason of problem with temporary tablespace.
I don't want to use any hints.

Thank You in advance
Peter

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #561810 is a reply to message #561796]

Wed, 25 July 2012 06:41

cookiemonster
Messages: 9147
Registered: September 2008
Location: Rainy Manchester

Senior Member

Since it's a query against dictionary views I'd recommend asking oracle themselves.

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #561816 is a reply to message #561796]

Wed, 25 July 2012 06:57

Michel Cadot
Messages: 54165
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In addition, the join with dba_tables is useless here and owner is missing in it (and I think this is the reason of the error).

Regards
Michel

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #561819 is a reply to message #561816]

Wed, 25 July 2012 07:06

cookiemonster
Messages: 9147
Registered: September 2008
Location: Rainy Manchester

Senior Member

?
Michel - the query states user_tables, not dba_tables.
However the query could, and should, be run off of user_tab_columns alone.

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #561822 is a reply to message #561819]

Wed, 25 July 2012 07:58

Scowron
Messages: 4
Registered: February 2012
Location: Poland

Junior Member

You are right, user_tables is useless.
But if I change user_tab_columns to user_tab_cols it works fine too.
It's very strange for me...

Peter

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #561838 is a reply to message #561819]

Wed, 25 July 2012 10:57

Michel Cadot
Messages: 54165
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

cookiemonster wrote on Wed, 25 July 2012 14:06

?
Michel - the query states user_tables, not dba_tables.
However the query could, and should, be run off of user_tab_columns alone.

Fuzzy eyes this afternoon, maybe because of the 35°C in my office...

Regards
Michel

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #561839 is a reply to message #561822]

Wed, 25 July 2012 11:01

Michel Cadot
Messages: 54165
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

But if I change user_tab_columns to user_tab_cols it works fine too.

Do not use user_tab_cols, use user_tab_columns, you don't need to see hidden columns.

Documentation says:

Quote:

To gather statistics for this view, use the ANALYZE SQL statement.

It is an exception of the rule, always use dbms_stats.

Regards
Michel

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #563079 is a reply to message #561839]

Wed, 08 August 2012 06:29

Scowron
Messages: 4
Registered: February 2012
Location: Poland

Junior Member

Hi
I know the reason of using user_tables. View user_tab_columns contain column name for table and VIEWS too.
Expected result could contain only table columns.

I should use ANALYZE SQL statement for gathering statistic but I found something like that:
Quote:

Restrictions on Analyzing Tables Analyzing tables is subject to the following restrictions:

You cannot use ANALYZE to collect statistics on data dictionary tables.

What's different between data dictionary views and data dictionary table? I thought it is the same.

Peter

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #563081 is a reply to message #563079]

Wed, 08 August 2012 06:45

Michel Cadot
Messages: 54165
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I think (and I should thought about it before) documentation is misleading as you cannot gather statistics on a view anyway.

SQL> analyze table ALL_TAB_COLUMNS compute statistics;
analyze table ALL_TAB_COLUMNS compute statistics
              *
ERROR at line 1:
ORA-01702: a view is not appropriate here

Regards
Michel

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #564766 is a reply to message #563081]

Tue, 28 August 2012 09:45

Scowron
Messages: 4
Registered: February 2012
Location: Poland

Junior Member

Hi again

I've got two Explain Plans. One from database with above problem and second one from another database where problem doesn't exist.

Oracle 11.1.0.7.0 optimizer_mode=ALL_ROWS

SELECT STATEMENT, GOAL = ALL_ROWS      103  2  610
 CONCATENATION          
  FILTER          
   HASH JOIN      31  1  305
    MERGE JOIN CARTESIAN      29  2  554
     NESTED LOOPS OUTER      28  1  273
      NESTED LOOPS OUTER      27  1  269
       NESTED LOOPS OUTER      26  1  261
        NESTED LOOPS OUTER      25  1  256
         NESTED LOOPS OUTER      24  1  246
          NESTED LOOPS OUTER      23  1  234
           NESTED LOOPS OUTER      22  1  230
            NESTED LOOPS OUTER      20  1  198
             NESTED LOOPS      19  1  170
              NESTED LOOPS      18  1  132
               NESTED LOOPS      17  1  109
                NESTED LOOPS      16  1  106
                 NESTED LOOPS      15  1  76
                  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  14  1  41
                   INDEX RANGE SCAN  SYS  I_OBJ5  13  5  
                    TABLE ACCESS CLUSTER  SYS  TAB$  1  1  12
                     INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
                  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  35
                   INDEX RANGE SCAN  SYS  I_OBJ2  1  1  
                 TABLE ACCESS CLUSTER  SYS  TAB$  1  1  30
                  INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
                TABLE ACCESS CLUSTER  SYS  TS$  1  1  3
                 INDEX UNIQUE SCAN  SYS  I_TS#  1  1  
               INDEX RANGE SCAN  SYS  I_USER2  1  1  23
              TABLE ACCESS CLUSTER  SYS  COL$  1  1  38
               INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
             TABLE ACCESS BY INDEX ROWID  SYS  COLTYPE$  1  1  28
              INDEX UNIQUE SCAN  SYS  I_COLTYPE2  1  1  
            TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  2  1  32
             INDEX RANGE SCAN  SYS  I_OBJ3  1  29  
           INDEX RANGE SCAN  SYS  I_USER2  1  1  4
          TABLE ACCESS CLUSTER  SYS  SEG$  1  1  12
           INDEX UNIQUE SCAN  SYS  I_FILE#_BLOCK#  1  1  
         INDEX RANGE SCAN  SYS  I_HH_OBJ#_INTCOL#  1  1  10
        INDEX RANGE SCAN  SYS  I_OBJ1  1  1  5
       INDEX RANGE SCAN  SYS  I_OBJ1  1  1  8
      INDEX RANGE SCAN  SYS  I_USER2  1  1  4
     BUFFER SORT      28  2048  8192
      FIXED TABLE FULL  SYS  X$KSPPCV  1  2048  8192
    FIXED TABLE FULL  SYS  X$KSPPI  1  1  28
   NESTED LOOPS      2  1  28
    INDEX RANGE SCAN  SYS  I_OBJ4  1  1  8
    INDEX RANGE SCAN  SYS  I_USER2  1  1  20
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
  FILTER          
   HASH JOIN      71  1  305
    MERGE JOIN CARTESIAN      70  1  301
     NESTED LOOPS OUTER      69  1  273
      NESTED LOOPS OUTER      68  1  269
       NESTED LOOPS OUTER      67  1  261
        NESTED LOOPS OUTER      66  1  256
         NESTED LOOPS OUTER      65  1  246
          NESTED LOOPS OUTER      64  1  234
           NESTED LOOPS OUTER      63  1  230
            NESTED LOOPS OUTER      59  2  396
             NESTED LOOPS      58  2  340
              NESTED LOOPS      57  2  264
               NESTED LOOPS      56  2  218
                NESTED LOOPS      55  2  212
                 NESTED LOOPS      54  2  152
                  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  53  2  82
                   INDEX RANGE SCAN  SYS  I_OBJ5  13  219  
                  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  35
                   INDEX RANGE SCAN  SYS  I_OBJ2  1  1  
                 TABLE ACCESS CLUSTER  SYS  TAB$  1  1  30
                  INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
                TABLE ACCESS CLUSTER  SYS  TS$  1  1  3
                 INDEX UNIQUE SCAN  SYS  I_TS#  1  1  
               INDEX RANGE SCAN  SYS  I_USER2  1  1  23
              TABLE ACCESS CLUSTER  SYS  COL$  1  1  38
               INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
             TABLE ACCESS BY INDEX ROWID  SYS  COLTYPE$  1  1  28
              INDEX UNIQUE SCAN  SYS  I_COLTYPE2  1  1  
            TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  2  1  32
             INDEX RANGE SCAN  SYS  I_OBJ3  1  29  
           INDEX RANGE SCAN  SYS  I_USER2  1  1  4
          TABLE ACCESS CLUSTER  SYS  SEG$  1  1  12
           INDEX UNIQUE SCAN  SYS  I_FILE#_BLOCK#  1  1  
         INDEX RANGE SCAN  SYS  I_HH_OBJ#_INTCOL#  1  1  10
        INDEX RANGE SCAN  SYS  I_OBJ1  1  1  5
       INDEX RANGE SCAN  SYS  I_OBJ1  1  1  8
      INDEX RANGE SCAN  SYS  I_USER2  1  1  4
     BUFFER SORT      69  1  28
      FIXED TABLE FULL  SYS  X$KSPPI  1  1  28
    FIXED TABLE FULL  SYS  X$KSPPCV  1  2048  8192
   NESTED LOOPS      2  1  28
    INDEX RANGE SCAN  SYS  I_OBJ4  1  1  8
    INDEX RANGE SCAN  SYS  I_USER2  1  1  20
   TABLE ACCESS CLUSTER  SYS  TAB$  1  1  12
    INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1  
   TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
    INDEX RANGE SCAN  SYS  I_OBJ1  1  1

Oracle 10.2.0.3.0 optimizer_mode=CHOOSE

SELECT STATEMENT, GOAL = CHOOSE      304  1  273
 FILTER          
  HASH JOIN      304  15  4095
   MERGE JOIN CARTESIAN      303  15  4035
    NESTED LOOPS OUTER      300  15  3630
     NESTED LOOPS OUTER      298  15  3570
      NESTED LOOPS OUTER      297  15  3450
       NESTED LOOPS OUTER      296  15  3375
        NESTED LOOPS OUTER      294  15  3225
         NESTED LOOPS OUTER      293  15  3060
          NESTED LOOPS OUTER      291  15  3000
           NESTED LOOPS OUTER      221  15  2520
            NESTED LOOPS      219  15  2100
             NESTED LOOPS      214  48  4944
              NESTED LOOPS      200  47  3196
               NESTED LOOPS      195  47  3055
                TABLE ACCESS FULL  SYS  OBJ$  188  78  2730
                TABLE ACCESS CLUSTER  SYS  TAB$  1  1  30
                 INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
               TABLE ACCESS CLUSTER  SYS  TS$  1  1  3
                INDEX UNIQUE SCAN  SYS  I_TS#  1  1  
              TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  35
               INDEX RANGE SCAN  SYS  I_OBJ2  1  1  
             TABLE ACCESS CLUSTER  SYS  COL$  1  1  37
              INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
            TABLE ACCESS BY INDEX ROWID  SYS  COLTYPE$  1  1  28
             INDEX UNIQUE SCAN  SYS  I_COLTYPE2  1  1  
           TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  5  1  32
            INDEX RANGE SCAN  SYS  I_OBJ3  1  53  
          TABLE ACCESS CLUSTER  SYS  USER$  1  1  4
           INDEX UNIQUE SCAN  SYS  I_USER#  1  1  
         TABLE ACCESS CLUSTER  SYS  SEG$  1  1  11
          INDEX UNIQUE SCAN  SYS  I_FILE#_BLOCK#  1  1  
        INDEX RANGE SCAN  SYS  I_HH_OBJ#_INTCOL#  1  1  10
       INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  5
      TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  8
       INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
     TABLE ACCESS CLUSTER  SYS  USER$  1  1  4
      INDEX UNIQUE SCAN  SYS  I_USER#  1  1  
    BUFFER SORT      302  1  27
     FIXED TABLE FULL  SYS  X$KSPPI  0  1  27
   FIXED TABLE FULL  SYS  X$KSPPCV  0  1436  5744
  TABLE ACCESS CLUSTER  SYS  TAB$  1  1  12
   INDEX UNIQUE SCAN  SYS  I_OBJ#  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1  
  TABLE ACCESS BY INDEX ROWID  SYS  OBJ$  1  1  29
   INDEX UNIQUE SCAN  SYS  I_OBJ1  1  1

It will be possible that CONCATENATION cause an error?

Thank You in advance

Piotr

Report message to a moderator

Re: ORA-01652-unable to extend temp segment by 128 in tablespace TEMP [message #565132 is a reply to message #564766]

Thu, 30 August 2012 15:25

wakula
Messages: 149
Registered: February 2008
Location: Poland

Senior Member

Is that plan REALLY for the query presented?

Michel is almost the most reliable Oracle expert I have seen (sorry Michel - Tom Kyte beats you a bit Wink

) so I would double-read the post about join... you are creating a projection which goes into RAM (or TEMP) consuming some of the system resources. I guess you did much much more in the same session/transaction (or different session/transaction) and TEMP is totally consumed (probably RAM the same... I mean PGA and/or SGA) and the point of failure is the query you tried to show...
Imagine a bucket of water which is full... You tried to add a single drop to it and then it poured out... Is the drop responsible or rather the fact that bucket was already full?

Good luck
Regards

Report message to a moderator

Previous Topic:	SQL Tuning Assistance Required
Next Topic:	How to Control concurrent Inserts of a record by parallel sessions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 21 11:49:42 CDT 2013

Total time taken to generate the page: 0.08720 seconds