Ora 1652: Unable to extend temp segment by 128 in temp ts
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_' || CASEWHEN 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