Home » RDBMS Server » Server Administration » ORA-01652 unable to extend temp segment
ORA-01652 unable to extend temp segment [message #195886] |
Mon, 02 October 2006 23:45 |
shilpa.rajput
Messages: 31 Registered: May 2006 Location: Pune
|
Member |
|
|
Hello All,
Currently I'm facing problem with my test database.
Its throwing ORA-01652 unable to extend temp segment by 128.
I tried all the possible solutions.
Like :
Added datafile to the temp tablespace.
Set the size to be of 1GB with unlimited autoextend(even have much physical space on server)
It didnt work.
Then I created new temporary tablespace & assinged it to the user.
But still it throws the same error.
In fact,the same query executes within seconds in live environment,
Just wonder what could be done!
Nay suggestion???
Thanks in advance....
Shilpa
|
|
|
|
Re: ORA-01652 unable to extend temp segment [message #195901 is a reply to message #195894] |
Tue, 03 October 2006 01:53 |
shilpa.rajput
Messages: 31 Registered: May 2006 Location: Pune
|
Member |
|
|
this is how it goes...
In fact i didnt tried to tune it as its working fine in live environment..
select distinct pur_tbl.purodr_tblnum,inv_tbl.itm,thng.in7 old_thng_no,
invcst_tbl.ib4 last_recpt_dt,thng.stocktype,
inv_tbl.issueunit,inv_tbl.binnum,invcst_tbl.lotnum,
inv_tbl.avgcost unit_cost,inv_tbl.location,
invcst_tbl.ib1 department,
pur_tbl.rv_dt,pur_tbl.total_cst,invcst_tbl.stock,pur_tbl.tax2 excise,
round(decode(pur_tbl.rv_dt,0,null,((pur_tbl.total_cst*invcst_tbl.stock)/pur_tbl.rv_dt)),3) cwthex,
round((decode(pur_tbl.rv_dt,0,null,((pur_tbl.total_cst*invcst_tbl.stock)/pur_tbl.rv_dt))-decode(invcst_tbl.stock,0,null,(pur_tbl.tax2 *invcst_tbl.stock)/pur_tbl.rv_dt)),3) cwex,
budget.budgettype
from inv_tbl,invcst_tbl,pur_tbl,tot_inv,thng,purodr_tbl,budget
where inv_tbl.itm = invcst_tbl.itm
and inv_tbl.itm = pur_tbl.itm
and invcst_tbl.itm = pur_tbl.itm
and inv_tbl.loc = invcst_tbl.loc
and inv_tbl.orgid = invcst_tbl.orgid
and inv_tbl.location in ('upper','lower')
and inv_tbl.loc like 'TALLYDEP'
and inv_tbl.orgid like 'SEMENCE'
and invcst_tbl.stock > 0
and invcst_tbl.dept not like '%'
and inv_tbl.itm = thng.itm
and invcst_tbl.itm = thng.itm
and pur_tbl.itm = thng.itm
and tot_inv.itm = thng.itm
and pur_tbl.itm=tot_inv.itm
and invcst_tbl.itm=tot_inv.itm
and pur_tbl.purodr_tblnum=tot_inv.purodr_tblnum
and invcst_tbl.lotnum=tot_inv.recno
and pur_tbl.purodr_tblnum = purodr_tbl.purodr_tblnum
and tot_inv.purodr_tblnum = purodr_tbl.purodr_tblnum
and purodr_tbl.budgetcode = budget.budgetcode(+)
and invcst_tbl.ori_date >= to_date('01-sep-04','dd-mm-yy')
order by invcst_tbl.ib1,inv_tbl.itm
select distinct pur_tbl.purodr_tblnum,inv_tbl.itm,thng.in7 old_thng_no,
invcst_tbl.ib4 last_recpt_dt,thng.stocktype,
inv_tbl.issueunit,inv_tbl.binnum,invcst_tbl.lotnum,
inv_tbl.avgcost unit_cost,inv_tbl.location,
invcst_tbl.ib1 department,
pur_tbl.rv_dt,pur_tbl.total_cst,invcst_tbl.stock,pur_tbl.tax2 excise,
round(decode(pur_tbl.rv_dt,0,null,((pur_tbl.total_cst*invcst_tbl.stock)/pur_tbl.rv_dt)),3) cwthex,
round((decode(pur_tbl.rv_dt,0,null,((pur_tbl.total_cst*invcst_tbl.stock)/pur_tbl.rv_dt))-decode(invcst_tbl.stock,0,null,(pur_tbl.tax2 *invcst_tbl.stock)/pur_tbl.rv_dt)),3) cwex,
budget.budgettype
from inv_tbl,invcst_tbl,pur_tbl,tot_inv,thng,purodr_tbl,budget
where inv_tbl.itm = invcst_tbl.itm
and inv_tbl.itm = pur_tbl.itm
and invcst_tbl.itm = pur_tbl.itm
and inv_tbl.loc = invcst_tbl.loc
and inv_tbl.orgid = invcst_tbl.orgid
and inv_tbl.location in ('upper','lower')
and inv_tbl.loc like 'TALLYDEP'
and inv_tbl.orgid like 'SEMENCE'
and invcst_tbl.stock > 0
and invcst_tbl.dept not like '%'
and inv_tbl.itm = thng.itm
and invcst_tbl.itm = thng.itm
and pur_tbl.itm = thng.itm
and tot_inv.itm = thng.itm
and pur_tbl.itm=tot_inv.itm
and invcst_tbl.itm=tot_inv.itm
and pur_tbl.purodr_tblnum=tot_inv.purodr_tblnum
and invcst_tbl.lotnum=tot_inv.recno
and pur_tbl.purodr_tblnum = purodr_tbl.purodr_tblnum
and tot_inv.purodr_tblnum = purodr_tbl.purodr_tblnum
and purodr_tbl.budgetcode = budget.budgetcode(+)
and invcst_tbl.ori_date >= to_date('01-sep-04','dd-mm-yy')
order by invcst_tbl.ib1,inv_tbl.itm
Thanks....
|
|
|
|
|
|
|
Re: ORA-01652 unable to extend temp segment [message #195993 is a reply to message #195958] |
Tue, 03 October 2006 09:21 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
Additionally, 1652 error does not necessarily mean that the error was in the TEMP tablespace. Yes, it was in a temporary segment, but it could be in the users DEFAULT tablespace. For example, rebuilding an index could give a 1652 erorr in the tablespace where the index resides.
|
|
|
Re: ORA-01652 unable to extend temp segment [message #196058 is a reply to message #195958] |
Tue, 03 October 2006 22:49 |
shilpa.rajput
Messages: 31 Registered: May 2006 Location: Pune
|
Member |
|
|
Mahesh Sir,
Thanks a lot for the imorptant info abt the temporary segments.
Actually, I'm having both production & test database on windows for this perticular application.
As far as data is concerned, i have imported the dump of 1st Sep06 in test environment.
N what which parameters should i look for in the stats gathering? As i'm only a few months old as a DBA, dont know which parameters may affect...
[Updated on: Tue, 03 October 2006 22:50] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Sep 26 18:16:13 CDT 2024
|