Home » RDBMS Server » Server Administration » Unable to extend temp segment (11.2.0.4)
Unable to extend temp segment [message #638193] Fri, 05 June 2015 14:13 Go to next message
varunvir
Messages: 363
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
Re: Unable to extend temp segment [message #638194 is a reply to message #638193] Fri, 05 June 2015 14:16 Go to previous messageGo to next message
John Watson
Messages: 7219
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.
Re: Unable to extend temp segment [message #638195 is a reply to message #638194] Fri, 05 June 2015 14:58 Go to previous messageGo to next message
varunvir
Messages: 363
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

Re: Unable to extend temp segment [message #638197 is a reply to message #638195] Fri, 05 June 2015 15:12 Go to previous messageGo to next message
John Watson
Messages: 7219
Registered: January 2010
Location: Global Village
Senior Member
Nonsense. THat isn't the way partitions work. Order of insert has no relevance.
Re: Unable to extend temp segment [message #638199 is a reply to message #638197] Fri, 05 June 2015 16:08 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks John,
This is what my developer thinks Smile
Anyhow I am thinking to use dbms_redefinition in that case.

Thanks,

Varun
Re: Unable to extend temp segment [message #638203 is a reply to message #638199] Fri, 05 June 2015 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
>Please suggest if there is any ways to avoid this error.

remove the HINT
Re: Unable to extend temp segment [message #638216 is a reply to message #638199] Sat, 06 June 2015 07:42 Go to previous messageGo to next message
EdStevens
Messages: 888
Registered: September 2013
Senior Member
varunvir wrote on Fri, 05 June 2015 16:08
Thanks John,
This is what my developer thinks Smile


Which? He thinks order of insert IS relevent? Or he agrees with John?
If the former, he needs to be re-educated, post haste.
Re: Unable to extend temp segment [message #638278 is a reply to message #638203] Mon, 08 June 2015 15:26 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Blackswan,
Thanks for the response.
Why removing the hint would help getting rid of this error?

Thanks,

Varun
Re: Unable to extend temp segment [message #638280 is a reply to message #638278] Mon, 08 June 2015 17:29 Go to previous messageGo to next message
BlackSwan
Messages: 25797
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?
Re: Unable to extend temp segment [message #638392 is a reply to message #638193] Wed, 10 June 2015 10:01 Go to previous messageGo to next message
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
Re: Unable to extend temp segment [message #638398 is a reply to message #638278] Wed, 10 June 2015 10:46 Go to previous messageGo to next message
John Watson
Messages: 7219
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.
Re: Unable to extend temp segment [message #638400 is a reply to message #638193] Wed, 10 June 2015 11:22 Go to previous messageGo to next message
varunvir
Messages: 363
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
Re: Unable to extend temp segment [message #638401 is a reply to message #638400] Wed, 10 June 2015 13:56 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, 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.
Re: Unable to extend temp segment [message #638435 is a reply to message #638401] Thu, 11 June 2015 11:00 Go to previous messageGo to next message
varunvir
Messages: 363
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
Re: Unable to extend temp segment [message #638436 is a reply to message #638435] Thu, 11 June 2015 11:04 Go to previous messageGo to next message
gazzag
Messages: 907
Registered: November 2010
Location: Bristol, UK
Senior Member
The Buffer Cache in the SGA.
Re: Unable to extend temp segment [message #638437 is a reply to message #638435] Thu, 11 June 2015 11:14 Go to previous messageGo to next message
John Watson
Messages: 7219
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.
Re: Unable to extend temp segment [message #638445 is a reply to message #638437] Thu, 11 June 2015 14:34 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks
Re: Unable to extend temp segment [message #638446 is a reply to message #638400] Thu, 11 June 2015 14:36 Go to previous messageGo to next message
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
Re: Unable to extend temp segment [message #638448 is a reply to message #638446] Thu, 11 June 2015 14:49 Go to previous messageGo to next message
varunvir
Messages: 363
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
Re: Unable to extend temp segment [message #638449 is a reply to message #638448] Thu, 11 June 2015 14:52 Go to previous messageGo to next message
John Watson
Messages: 7219
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
Re: Unable to extend temp segment [message #638460 is a reply to message #638448] Fri, 12 June 2015 03:10 Go to previous messageGo to next message
cookiemonster
Messages: 12992
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.
Re: Unable to extend temp segment [message #638519 is a reply to message #638193] Fri, 12 June 2015 14:14 Go to previous message
varunvir
Messages: 363
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
Previous Topic: databse resources
Next Topic: SGA Size modification
Goto Forum:
  


Current Time: Fri Jan 19 23:57:21 CST 2018

Total time taken to generate the page: 0.01255 seconds