Ora 1652: Unable to extend temp segment by 128 in temp ts

From: dbagtcol <cx4gtcol_at_gmail.com>
Date: Fri, 4 Jan 2008 04:14:05 -0800 (PST)
Message-ID: <6cadba53-64a9-4185-9efc-d89ecaef0f31@5g2000hsg.googlegroups.com>


Hi all,

INSERT INTO co_ClmLvl2

(

        ..146 Columns..
)
SELECT
        ..146 Columns.. {all from H_I.HI_CLMS} FROM

        H_I.HI_CLMS a,
        H_RULES.HR_175_EmpGrp b,
        zzz_ClmLvl2 x,
        zzz_MemType z
WHERE
        UPPER(a.MEMBER_GROUP_CODE) = b.GroupNumber
        AND CASE WHEN a.PPar_Flag = '1' THEN 'Y_' ELSE 'N_' END ||
REPLACE(a.CLAIM_ID,'-','') || '+' || TO_CHAR(a.LINE_NUMBER) = x.ClmNum
        AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' || CASE
WHEN SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'S' THEN 'SUPERMAX' WHEN SUBSTR(a.MEMBER_GROUP_CODE,1,1) = 'B' THEN 'BLUEADV' ELSE 'NA' END)
  • x.LVL2ID; COMMIT; /

Sql above gives me Ora-1652 even when I added two more tempfiles each 30gig to temp ts. Something peculiar is that when I do a simple select count(*) from H_I, it takes hours to complete. I checked dba_objects and it shows H_I as valid. And the sql is not even listed in v $session_longops. The under mentioned sql doesn't sum up to more than 4 gig at any point of time:

Code:
SELECT
(Sum(vss.Value)/1024/1024/1024) GB
  FROM

    v$session vs,
    v$sesstat vss,
    v$statname vsn

 WHERE
    (vss.statistic#=vsn.statistic#)
    AND (vs.sid = vss.sid)
    AND (vsn.name like '%sort%')
    AND vss.Value>0;

Also, when i check V$TEMP_SPACE_HEADER, BYTES_FREE is always 0. I am just confused is it an issue with temp files, does the sql need tuning, or is it a problem with H_I table? Expected number of result rows is 3 million, avg_row_len is 700.

Looking forward for you advices.

Thanks in advance,
gtcol Received on Fri Jan 04 2008 - 06:14:05 CST

Original text of this message