OraFAQ Forum: Server Administration » ORA-01652 unable to extend temp segment

Home » RDBMS Server » Server Administration » ORA-01652 unable to extend temp segment

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: ORA-01652 unable to extend temp segment [message #195894 is a reply to message #195886]

Tue, 03 October 2006 01:11

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

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....

Report message to a moderator

Re: ORA-01652 unable to extend temp segment [message #195913 is a reply to message #195901]

Tue, 03 October 2006 02:26

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.

Report message to a moderator

Re: ORA-01652 unable to extend temp segment [message #195917 is a reply to message #195913]

Tue, 03 October 2006 02:44

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

Report message to a moderator

Re: ORA-01652 unable to extend temp segment [message #195919 is a reply to message #195917]

Tue, 03 October 2006 02:51

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

you're welcome

Report message to a moderator

Re: ORA-01652 unable to extend temp segment [message #195958 is a reply to message #195901]

Tue, 03 October 2006 05:58

Mahesh Rajendran
Messages: 10707
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.

Report message to a moderator

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.

Report message to a moderator

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

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 08:38:59 CDT 2024