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

On Jan 4, 7:14 am, dbagtcol <cx4gt..._at_gmail.com> wrote:
> 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

It appears that you have a Cartesian join with the zzz_MemType table:   "AND COALESCE(z.LVL2ID, b.GroupNumber,'ALL_OTHERS_' ... = x.LVL2ID"

Take a close look at the WHERE clause to see if is possible to eliminate the Cartesian join.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Jan 04 2008 - 06:59:30 CST