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 Go to next message
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 #195894 is a reply to message #195886] Tue, 03 October 2006 01:11 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi
can u plz. post ur query.

what is ur oracle version.
bcoz it is possible in Oracle 9i bug.

and also post

sql>select username,deafult_tablespace,temporary_tablespace from dba_users;


all user must have temp as temporary tablespace.


thanx

[Updated on: Tue, 03 October 2006 01:19]

Report message to a moderator

Re: ORA-01652 unable to extend temp segment [message #195901 is a reply to message #195894] Tue, 03 October 2006 01:53 Go to previous messageGo to next message
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 #195913 is a reply to message #195901] Tue, 03 October 2006 02:26 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

hi

1.First if ur on Oracle 9i then may be it is (bug)
2.Create new temp tempspace with enough size. and drop old one.
3.Assign All users temp tbs is new ones.

then try to perform ur operation.

if still problem is persists then call for oracle support.

thanx.
Re: ORA-01652 unable to extend temp segment [message #195917 is a reply to message #195913] Tue, 03 October 2006 02:44 Go to previous messageGo to next message
shilpa.rajput
Messages: 31
Registered: May 2006
Location: Pune
Member

Thanks.. I'll just check this out.
N yeah.. forgot to tell but u guessed it right. Its Oracle9i
Re: ORA-01652 unable to extend temp segment [message #195919 is a reply to message #195917] Tue, 03 October 2006 02:51 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

you're welcome
Re: ORA-01652 unable to extend temp segment [message #195958 is a reply to message #195901] Tue, 03 October 2006 05:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Set the size to be of 1GB with unlimited autoextend(even have much physical space on server)

Unlimited autoextend has no use with temporary tablespaces.
Extents in temp tablespaces are re-used.
You are doing a huge sort. I would start with a temp tablespace
of about 20gb or even more without autoextend on.
But before doing it,

>>In fact,the same query executes within seconds in live environment,
Then what is different here?
Are the production and test environments are in different versions/data?
When was the last time you collected stats on these tables/indexes?
Use dbms_stats to collect the stats and try again.
As stated early by user52, there are several related bugs.
But try these again and we can come to a conclusion.
Re: ORA-01652 unable to extend temp segment [message #195993 is a reply to message #195958] Tue, 03 October 2006 09:21 Go to previous messageGo to next message
joy_division
Messages: 4641
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 Go to previous message
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

Previous Topic: Need Help
Next Topic: Encounter problem during Oracle Database Post Clone activity
Goto Forum:
  


Current Time: Wed Dec 07 12:30:01 CST 2016

Total time taken to generate the page: 0.11299 seconds