Home » RDBMS Server » Server Administration » temp tablespace error ORA-1652 (HPUX: Version 11.2.0.2.0 )
temp tablespace error ORA-1652 [message #553913] Wed, 09 May 2012 05:14 Go to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Hello Expert,

I am getting error "ORA-1652: unable to extend temp segment by 128 in tablespace" but i can see there is enough free space left in TEMP. I can see we have many active session. how can we drill down to solve this error. Below is the status for all the tablespaces.

Tablespace   STA M A Init     Total MB    Free MB     Used MB  LrgstMB       MaxExt %Fr A
------------ --- - - ---- ------------ ---------- ----------- -------- ------------ --- -
AMINDEX     OLN L S  64K        5,120      5,120           0    3,968   2147483645 100
AMOWNER     OLN L S  64K        5,120      5,120           0    3,968   2147483645 100
BOB_TS       OLN L U  10M       17,240     16,130       1,110    9,740   2147483645  94
DDP_TAB      OLN L U   1M        5,120      3,700       1,420    3,507   2147483645  72
GGS_HOLD     OLN L S  64K       27,000     22,201       4,799    3,968   2147483645  82
GGS_IDX      OLN L U   1M      714,880    274,099     440,781   20,803   2147483645  38
GGS_TAB      OLN L U   1M      473,119    104,097     369,022   28,455   2147483645  22
SYSAUX       OLN L S  64K        5,598      2,597       3,001      462   2147483645  46
SYSTEM       OLN L S  64K       12,848     11,802       1,046    3,903   2147483645  92
TEMP         OLN L U   1M       32,767     32,722          45   32,766              100
UNDOTBS1     OLN L S  64K       80,291     17,307      62,985    3,968   2147483645  22
USERS        OLN L S  64K        5,917      1,152       4,765      944   2147483645  19 *
Re: temp tablespace error ORA-1652 [message #553928 is a reply to message #553913] Wed, 09 May 2012 06:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Value tablespace in the message is important, why don't you post it, it may NOT be TEMP
2/ We don't know the query you executed to get the space information, so we don't know if it is correct and I bet it is not concerning temporary tablespace
3/ Available free space, does not mean available contiguous free space and so a big (free) extent may not be available for the current operation.

Regards
Michel
Re: temp tablespace error ORA-1652 [message #553939 is a reply to message #553928] Wed, 09 May 2012 06:49 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks Michel.

There is insert statement executed from read schema and object (on which insert statement is executed) exist in DBA schema. Default tablespace for read schema is USERS and for it is BOB_TS. I can see, there is small space left in USERS tablespace, so i added 5G for now.

I request you please help me in diagnosis the issue.

INSERT /*+ APPEND */ INTO DBA.BOB_CUSTOMER_RATE (
   ACCTCORP, HOUSE, CUST,
   SERV, CATEGORY, RCTR01,
   RCTR02, RCTR03, RCTR04,
   RCTR05, RCTR06, RCTR07,
   RCTR08, RCTR09, RCTR10,
   RCTR11, RCTR12, RCTR13,
   RCTR14, RCTR15, RCTR16,
   RCTR17, RCTR18, RCTR19,
   RCTR20, SOURCE_UPDATE_DATE, RPTCTR_CONCAT,
   PROMO_FLAG, KILL_FLAG,LNAME,FNAME,RPHON,
   STAT,RAREACD,CUSTPIN,ANNIVDAY)
SELECT B.ACCTCORP, B.HOUSE, B.CUST,
       F.SERV, A.CATEGORY,D.RCTR01,
       D.RCTR02, D.RCTR03,
       D.RCTR04, D.RCTR06,
       D.RCTR05, D.RCTR07,
       D.RCTR08, D.RCTR09,
       D.RCTR10, D.RCTR11,
       D.RCTR12, D.RCTR13,
       D.RCTR14, D.RCTR15,
       D.RCTR16, D.RCTR17,
       D.RCTR19, D.RCTR18,
       D.RCTR20,B.SOURCE_UPDATE_DATE,' ',
       CASE
          WHEN (e.rprmonth>0) THEN 'PROMO'
          WHEN (e.addressable=4 and e.deliverysysid=' ') then 'FEAT'
          ELSE 'PASS'
          END AS PROMO_FLAG,' ',
       LNAME,FNAME,RPHON,STAT,RAREACD,CUSTPIN,ANNIVDAY
  FROM DBA.BOB_CAT_CODES A,
       DBA.BOB_CUST B,
       (select distinct acctcorp,house from DBA.BOB_HOUSE_SERVICE_AVLBLTY
         where svc_avl_type=99) C,
       GGS.IDST_RATE_REPORTING_CENTER D,
       GGS.IDST_RATE_CODE E,
       (SELECT P.ACCTCORP,P.HOUSE,P.CUST,P.SERV FROM DBA.BOB_CUST_RATE P
       UNION ALL
       SELECT Q.ACCTCORP,Q.HOUSE,Q.CUST,Q.NEWCODE AS "SERV" FROM DBA.BOB_CUST_PROMO Q) F,
       DBA.BOB_STATUS X
 WHERE      (D.ACCTCORP = E.ACCTCORP)
        AND (D.DRATECODE = E.RCODE)
        AND (E.ACCTCORP = F.ACCTCORP)
        AND (E.RCODE = F.SERV)
        AND (A.ACCTCORP = D.ACCTCORP)
        AND ((A.RPT_CTR = D.RCTR01)
        OR (A.RPT_CTR = D.RCTR02)
        OR (A.RPT_CTR = D.RCTR03)
        OR (A.RPT_CTR = D.RCTR04)
        OR (A.RPT_CTR = D.RCTR05)
        OR (A.RPT_CTR = D.RCTR06)
        OR (A.RPT_CTR = D.RCTR07)
        OR (A.RPT_CTR = D.RCTR08)
        OR (A.RPT_CTR = D.RCTR09)
        OR (A.RPT_CTR = D.RCTR10)
        OR (A.RPT_CTR = D.RCTR11)
        OR (A.RPT_CTR = D.RCTR12)
        OR (A.RPT_CTR = D.RCTR13)
        OR (A.RPT_CTR = D.RCTR14)
        OR (A.RPT_CTR = D.RCTR15)
        OR (A.RPT_CTR = D.RCTR16)
        OR (A.RPT_CTR = D.RCTR17)
        OR (A.RPT_CTR = D.RCTR18)
        OR (A.RPT_CTR = D.RCTR19)
        OR (A.RPT_CTR = D.RCTR20))
        AND (B.ACCTCORP = C.ACCTCORP(+))
        AND (B.HOUSE = C.HOUSE(+))
        AND (F.ACCTCORP = B.ACCTCORP)
        AND (F.HOUSE = B.HOUSE)
        AND (F.CUST = B.CUST)
        AND (B.ACCTCORP=X.ACCTCORP)
  AND 1 = X.STATUS
  AND 'DIV_EASTERN_3' = x.notes
  AND c.house is null
Re: temp tablespace error ORA-1652 [message #553945 is a reply to message #553939] Wed, 09 May 2012 07:09 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
You are using the APPEND hint. This forces a direct load, which goes into a temporary segment in whatever tablespace you are working in. So as you have worked out, the problem was not to do with your temporary tablespace.
Re: temp tablespace error ORA-1652 [message #553952 is a reply to message #553945] Wed, 09 May 2012 07:23 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Thanks John.

Just one more query, the sql executed by user 'read' whose default tablespace is users and object exist in user dba whose default tablespace is bob_ts. So this direct load will effect users or bob_ts tablespace..??
Re: temp tablespace error ORA-1652 [message #553954 is a reply to message #553952] Wed, 09 May 2012 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 67810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It affects the tablespace where the table is created.
The name of the tablespace is in the error message.

Regards
Michel
Re: temp tablespace error ORA-1652 [message #553960 is a reply to message #553954] Wed, 09 May 2012 07:47 Go to previous messageGo to next message
dba_7722
Messages: 197
Registered: August 2010
Location: Delhi
Senior Member

Michel,

this is the "INSERT /*+ APPEND */ INTO DBA.BOB_CUSTOMER_RATE" code and default tablespace for DBA is BOB_TS, which holds the BOB_CUSTOMER_RATE table. And if you see, there is large free space 16,130MB for this tablespace. So how to manage temporary segment. Also the size for this object is in just MB...

BOB_TS       OLN L U  10M       17,240     16,130       1,110    9,740   2147483645  94
USERS        OLN L S  64K        5,917      1,152       4,765      944   2147483645  19 *
TEMP         OLN L U   1M       32,767     32,722          45   32,766              100
Re: temp tablespace error ORA-1652 [message #553970 is a reply to message #553960] Wed, 09 May 2012 08:10 Go to previous message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
You seem to be ignoring what is being told to you.
The name of the tablespace is in the error message which you do not show after being asked at least two times.
You also ignored the part about contiguous free space.
Previous Topic: Implementing Case-insensitive Search
Next Topic: Cannot start listener (/tmp related)
Goto Forum:
  


Current Time: Wed Apr 14 17:28:05 CDT 2021