Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SELECT takes much more temp space when used for INSERT INTO...SELECT

216630Jun 14 2007 — edited Nov 23 2007

Hello,
I have a query that takes about 15 min and uses ~80.000 blocks when run from SQL*PLUS.
Now I want to use the same query to fill a table with

INSERT INTO <table> SELECT

and it uses > 220.000 blocks ending with

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

We had to increase temp space from 500 MB to 2.5 GB on the testing machine.

Now I have two questions:
1. Why?
2. Is there a way to limit the temp space needed?

Regards
Marcus

Posted originally in SQL-Forum 1899522

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 21 2007

Added on Jun 14 2007

#general-database-discussions

16 comments

4,499 views