OraFAQ Forum: Server Administration » Unable to extend temp segment

Home » RDBMS Server » Server Administration » Unable to extend temp segment (11.2.0.4)

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

Unable to extend temp segment [message #638193]

Fri, 05 June 2015 14:13

varunvir
Messages: 389
Registered: November 2007

Senior Member

Hi Experts,
I am trying to insert 481554905 rows with following command:-

INSERT INTO T_HISTTMP1 SELECT /*+ PARALLEL(H) */ * FROM T_HISTTMP1_NOT_PART H ORDER BY CTU_BUS_DATE;
I am getting the following errors:-

ERROR at line 1:
ORA-12801: error signaled in parallel query server P009, instance danur:ct000011 (1)
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Please suggest if there is any ways to avoid this error.

Thanks,

Varun

Report message to a moderator

Re: Unable to extend temp segment [message #638194 is a reply to message #638193]

Fri, 05 June 2015 14:16

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

You could remove the ORDER BY clause.

Do you intend to make the INSERT parallel? It will not be unless you enable parallel DML and add the hint.

Report message to a moderator

Re: Unable to extend temp segment [message #638195 is a reply to message #638194]

Fri, 05 June 2015 14:58

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks John for the response.
The only issue is that Order by clause is needed since data is inserted into the partitions in order, and
later data doesn't wind up in earlier partitions,
as that would screw us up down the road when it came time to purge partitions.
I wonder what could be done in such case?

Thanks,

Varun

Report message to a moderator

Re: Unable to extend temp segment [message #638197 is a reply to message #638195]

Fri, 05 June 2015 15:12

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

Nonsense. THat isn't the way partitions work. Order of insert has no relevance.

Report message to a moderator

Re: Unable to extend temp segment [message #638199 is a reply to message #638197]

Fri, 05 June 2015 16:08

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks John,
This is what my developer thinks Smile

Smile

Anyhow I am thinking to use dbms_redefinition in that case.

Thanks,

Varun

Report message to a moderator

Re: Unable to extend temp segment [message #638203 is a reply to message #638199]

Fri, 05 June 2015 17:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Please suggest if there is any ways to avoid this error.

remove the HINT

Report message to a moderator

Re: Unable to extend temp segment [message #638216 is a reply to message #638199]

Sat, 06 June 2015 07:42

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

varunvir wrote on Fri, 05 June 2015 16:08

Thanks John,
This is what my developer thinks

Which? He thinks order of insert IS relevent? Or he agrees with John?
If the former, he needs to be re-educated, post haste.

Report message to a moderator

Re: Unable to extend temp segment [message #638278 is a reply to message #638203]

Mon, 08 June 2015 15:26

varunvir
Messages: 389
Registered: November 2007

Senior Member

Hi Blackswan,
Thanks for the response.
Why removing the hint would help getting rid of this error?

Thanks,

Varun

Report message to a moderator

Re: Unable to extend temp segment [message #638280 is a reply to message #638278]

Mon, 08 June 2015 17:29

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Why removing the hint would help getting rid of this error?

did the error occur after removing the HINT?

Report message to a moderator

Re: Unable to extend temp segment [message #638392 is a reply to message #638193]

Wed, 10 June 2015 10:01

bpeasland
Messages: 51
Registered: February 2015
Location: United States

Member

Removing the hint may or may not help. The only way to know for sure is to test it out both ways.

The reason temp space is being used in the first place is because of that ORDER BY clause. If that clause is removed, then you won't hit this error. You stated the table receiving the rows of data is partitioned. The partition key will determine the partition the row belongs to. Ordering the rows on INSERT has zero effect.

If you insist on the ORDER BY clause, an index on the ordering column may help as the index maintains sorted order and Oracle may not need to order the data in memory.

And then there is the obvious solution to the problem. Increase the size of the TEMP tablespace!

Cheers,
Brian

Report message to a moderator

Re: Unable to extend temp segment [message #638398 is a reply to message #638278]

Wed, 10 June 2015 10:46

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

varunvir wrote on Mon, 08 June 2015 21:26

Hi Blackswan,
Thanks for the response.
Why removing the hint would help getting rid of this error?

Thanks,

Varun

Your hint is making the unnecessary sort use more temp space than it would if run serailly. And the hint is in any case useless, because it applies only to the SELECT, not the INSERT.

Report message to a moderator

Re: Unable to extend temp segment [message #638400 is a reply to message #638193]

Wed, 10 June 2015 11:22

varunvir
Messages: 389
Registered: November 2007

Senior Member

How does order by clause affect the usage of temp space?
Even if I don't use order by clause, won't oracle fetch all the records to temp tablespace anyways?

Thanks,

Varun

Report message to a moderator

Re: Unable to extend temp segment [message #638401 is a reply to message #638400]

Wed, 10 June 2015 13:56

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

The ORDER BY clause forces Oracle to load the dataset into memory to sort it (ORDER BY). Hence the use of the TEMP tablespace. If you don't ORDER BY Oracle will simply read block by block, eliminating the sorting overhead.

Report message to a moderator

Re: Unable to extend temp segment [message #638435 is a reply to message #638401]

Thu, 11 June 2015 11:00

varunvir
Messages: 389
Registered: November 2007

Senior Member

So if you dont use sort ,after reading the block by block,where would oracle keep the data until the query is finished?

Thanks,

Varun

Report message to a moderator

Re: Unable to extend temp segment [message #638436 is a reply to message #638435]

Thu, 11 June 2015 11:04

gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK

Senior Member

The Buffer Cache in the SGA.

Report message to a moderator

Re: Unable to extend temp segment [message #638437 is a reply to message #638435]

Thu, 11 June 2015 11:14

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

It won't keep it anywhere. Read a block, insert the rows, throw it away, read the next block.

Report message to a moderator

Re: Unable to extend temp segment [message #638445 is a reply to message #638437]

Thu, 11 June 2015 14:34

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks

Report message to a moderator

Re: Unable to extend temp segment [message #638446 is a reply to message #638400]

Thu, 11 June 2015 14:36

bpeasland
Messages: 51
Registered: February 2015
Location: United States

Member

varunvir wrote on Wed, 10 June 2015 11:22

How does order by clause affect the usage of temp space?

The ORDER BY clause is telling Oracle to sort the data. If the sort cannot be performed entirely in memory, that sort must be done in pieces. Disk space is used to hold pieces that cannot fit into memory. Which disk space is that? The TEMP tablespace.

varunvir wrote on Wed, 10 June 2015 11:22

Even if I don't use order by clause, won't oracle fetch all the records to temp tablespace anyways?

No...Oracle will only use the TEMP ts for sorting operations. If you don't do ORDER BY, then no sort, hence no need for TEMP in that situation.

HTH,
Brian

Report message to a moderator

Re: Unable to extend temp segment [message #638448 is a reply to message #638446]

Thu, 11 June 2015 14:49

varunvir
Messages: 389
Registered: November 2007

Senior Member

So If I have a table a that has 10 million record and I do
select * from table a;
So where would oracle save these records before displaying, I assume in the memory.
What if memory is not enough to hold all those records? or
is it a different mechanism involved?

Thanks,

Varun

Report message to a moderator

Re: Unable to extend temp segment [message #638449 is a reply to message #638448]

Thu, 11 June 2015 14:52

John Watson
Messages: 8974
Registered: January 2010
Location: Global Village

Senior Member

you need to do some reading. Start here,
http://docs.oracle.com/database/121/CNCPT/toc.htm

Report message to a moderator

Re: Unable to extend temp segment [message #638460 is a reply to message #638448]

Fri, 12 June 2015 03:10

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

varunvir wrote on Thu, 11 June 2015 20:49

So If I have a table a that has 10 million record and I do
select * from table a;
So where would oracle save these records before displaying, I assume in the memory.
What if memory is not enough to hold all those records? or
is it a different mechanism involved?

Thanks,

Varun

It's a different mechanism - which is described in the guide John linked to above.

Report message to a moderator

Re: Unable to extend temp segment [message #638519 is a reply to message #638193]

Fri, 12 June 2015 14:14

varunvir
Messages: 389
Registered: November 2007

Senior Member

So I removed the hint and removed the order by clause and tested it out in UAT server.
Every thing seems to be working fine.
Thanks all for your help.

Varun

Report message to a moderator

Previous Topic:	databse resources
Next Topic:	SGA Size modification

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 17 06:22:47 CDT 2025