 Posted 1/18/2009 10:24:38 AM
|
|
|
|
Hi,
Anyone can help on this issue. I have a procedure in this procedure one statement
INSERT INTO prod.temp select * from .........
This statement is giving the unable to extend temp segment by 64 in tablespace TEMP error.
This is a big query and when we run that procedure in development and testing environment it's running fine but in staging database it's given the trouble.
|
|
|
Posted 1/18/2009 10:25:12 AM
|
|
|
|
You can set tempfile to be autoextend (check free disk space before):
ALTER DATABASE TEMPFILE 'filename' AUTOEXTEND ON;
But I can't understand why it tries to allocate space in TEMP while INSERT AS SELECT (this shouldn't cause implicit sort)
|
|
|
Posted 1/18/2009 10:25:58 AM
|
|
|
|
You can put temp tablespace into autoextend mode but put it with maxsize clause. If you think just for one query it is not feasible to extend temp tablespace then you can try below commands before running insert into.
Alter session set workarea_size_policy=MANUAL;
alter session set sort_area_size=;
Above will set a big sort area size for your session. Set it according to your requirement.
|
|
|
|