This content has been marked as final. Show 12 replies
Give out put of
sho parameter WORKAREA_SIZE_POLICY
sho parameter pg_aggregate_target
If PGA aggregate target is not set try set a value and try.
Please check below SQL's output;
SQL> select * from DBA_HIST_TEMPFILE a, v$tablespace b, v$tempfile c
where a.ts# = b.ts#
and c.name = a.filename;
Either Extend the temp tablespace or alter your application so it uses less temporary space.
Run this query:
SELECT name, bytes, status, enabled
Maybe your new datafile is offline (STATUS) or not READ / WRITE mode (ENABLED)
No. Both datafiles are online and read write enabled
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.
Its quite a sufficient value.
From v$sort_usage or v$tempseg_usage check which process is doing huge sorting and try to reduce it
You could try to srink and coalesce temp tablespace:
ALTER TABLESPACE TEMP SRINK SPACE;
ALTER TABLESPACE TEMP COALESCE;
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.