This content has been marked as final. Show 12 replies
There are two solutions for this error
1) Tune the sql and try to reduce the sorting operations.
2) Extend the temp tablespace.
Mostly for test and Stage better go fo the sql tuning and extending the space simultaniously (as part of testing). for Prod try to tune the sql and reduce the data its sorting out.
one other method is using the materialized views insted of the subquries in the query which is giving error.
When you talk about TEMP tablespace, always bear in mind that TEMP is shared between all sessions.
So you can have all "sized right" but two or three session are using 99% of TEMP, when you need a more 2% you get the error.
So, typical questions when you hit these error are:
there are other session active in the DB?
What are they doing?
Is the TEMP tablespace free enough?
Let me give you a really big secret but keep it between you and me!
Create another temporary tablespace and make the default one for the relevant user! So your user will only use that temporary tablespace rather than using the common one, TEMP!
Read more here;
SQL> alter user ogan identified by password
temporary tablespace TEMPORARY_TABLESPACE_NAME;
Hope it helps,
user588120 wrote:Well you could be trying to insert more data into the temporary table than there is space to accomodate it. Also the tablespace may be in use by other processes.
Even though i added one more datafile in TEMP tablespace,i get this error message when i'm trying to insert some data into a temporary table.What could be wrong
Thanks in Advance
Check the V$TEMPSEG_USAGE view for details on what is taking up the space while your process is running.