Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: ORA-01652: unable to extend temp segment by 1280 in tablespace TEMP

    Hie all
    Im having a problem here..

    Im trying to run this piece of code

    Code:
    Insert into J01_CLT_LOYAL_PROG_TO_BAL_UPD
    	(
    	date_loaded, week_no, 
    	reload_date, loyalty_id,   
    	total_reloads_value_j2, 
    	total_reloads_value_j1, 
    	gap, 
    	gap_percentage
    	)
    select 
    	trunc(sysdate)            AS date_loaded,
    	to_char(sysdate,'WW') 	  AS week_no,
    	trunc(a.reload_date)      AS reload_date, 
    	a.loyalty_id 		  AS loyalty_id,
    	trunc(a.reloads_value_j2) AS total_reloads_value_j2,
      	case when trunc(b.reload_date) = a.reload_date then 
                 b.reloads_value_j1 
      	else 
                 0 
      	end AS total_reloads_value_j1,
      	abs(a.reloads_value_j2 - case when trunc(b.reload_date) = trunc(a.reload_date) then 
    					b.reloads_value_j1 
    				 else 
    					0 
      				 end
            ) as gap,
      	abs(a.reloads_value_j2 - case when trunc(b.reload_date) = trunc(a.reload_date) then 
    					b.reloads_value_j1 
    				 else 
    					0 
      				 end
            )/(a.reloads_value_j2 + case when trunc(b.reload_date) = trunc(a.reload_date) then 
                 				b.reloads_value_j1 
      				else 
                 				0 
      				end)*100 AS gap_percentage
    from trap_ra_ppb_balance a, 
         trap_ra_clp_balance b
    where a.loyalty_id = b.loyalty_id
    order by trunc(a.reloads_value_j2), a.loyalty_id;
    but the problem here is that it returns the above error. I have spoke to the dba administrators to increase the tablespace temp and they said that currently having 4GB of space and they do need me to fine tune my query.

    what i have done :
    added this condition to run in just for 1 day
    Code:
    and to_char(a.reload_date, 'mm/dd/yyyy') = '11/22/2005'
    but it still returns the same error
    and also tried running it in sqlplus with this options on 'set autocommit 10000'
    -----

    I have to accept that the selection table's have at least 3 million rows each and im just sitting blur here....

    anyone could help...

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    I'd get rid of the Order by ...
    Why are you using it for an insert?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •