Re: Use of TEMP TS with INSERT

Comments embedded.
artmt_at_hotmail.com wrote:
> David,
>
> You don't have to reply to questions that for whatever reason irritate
> you, unless of course you enjoy this tone of conversation.
>
> In any case the entire message reads: "ORA-01652: unable to extend temp
> segment by 128 in tablespace TEMP_DATA" where TEMP_DATA is a temporary
> ts.

Then you need to expand your TEMP_DATA tablespace as I expect your insert into ..,., select ... is using the temporary tablespace as a 'holding area' for the result set of your select and Oracle is finding insufficient space to complete the transaction. I have tried this same transaction on 9.2.0.6, sort_area_size 65536, total SGA of 430688936 bytes and a temp tablespace sized at 640 meg and I have no such problems. Of course, you failed to provide a version or any other configuration information, making it difficult to determine the actual cause.

>
> I did not know temp segments can be created in non-temporary
> tablespaces. Can they?

As I stated earlier in such transasctions every segment created for the table TABLE_A can be considered temporary until a commit is issued, as a rollback would release such segments and their existence would then, indeed, be temporary. This message does not solely apply to the TEMP tablespace and the segments therein.

> Otherwise your response does make sense.
> Thank you.
>
> -Art

David Fitzjarrell Received on Tue Dec 27 2005 - 21:14:28 CST