I have problem with tablespace temp that increases and never decreases.
In test environments I created it with AUTOEXTEND ON and run out of
disk space few times (temp tablespace get 68 GB large mainly when MV
are created! ! !). In test environments i used DROP and CREATE to
resize it. This method cannot be used in production environment (cannot
drop default temp tablespace)..

In Oracle docs the only thing I found regarding this issue is in
Database Concepts 10g Release 2 (10.2)

http://download-uk.oracle.com/docs/c....htm#sthref399

"2 Data Blocks, Extents, and Segments
Extents in Temporary Segments

Multiple sorts, however, can use sort segments in temporary tablespaces
designated exclusively for sorts. These sort segments are allocated
only once for the instance, and they are not returned after the sort,
but remain available for other multiple sorts."

There's no tuning in queries that require many GBs of temp segments.
There are very very simple GROUP BY or MINUS queries

Any solution to let Oracle free temp segments to avoid "ORA-01652
unable to extend temp segment.." in production environment ?

News a écrit :

I found out this in metalink

http://metalink.oracle.com/metalink/...p_id=1039341.6

This problem raised since we upgraded to 10g and because, as far as I
understood from upgrade information tool, temporary tablaspace in 10g
must be created with TEMPORARY clause otherwise one gets warning about
"not enough space in temp tablespace" during upgrade. In previous
releases we used PERMANENT tablespace to hold temp objects which are
released by smon after completion of the process using them. This is
why we never encountered this problem in 7, 8 or 9i.

Is creating temp tablespace as permanent in 10g may be a solution to
this problem ??

News schreef:
Temporary files for temporary tablespaces have been around ever
since 8.0 - I fail to see why you are not using them.

Permanent tablespaces have overhead - your performance will increase!

And on MV's - don't recreate them - refresh then with the
changes only - it's called a fast refresh.
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Frank van Bortel a écrit :
I agree temp tablespaces created with clause TEMPORARY performs better
(no segment allocation and deallocation and no release by smon) but how
to avoid the problem without shutting and restarting server ??

Fast refresh didn't work for the main MVs. It takes eternity.

"Note:209571.1 from metalink:
- Fast refreshes are more efficient than complete refreshes WHEN there
are few changes to the master table(s) "

In some cases it never ends. Each day I recreate 44 MVs. It takes 4
hours and consumes 150 GB. Oracle cannot do better.

News schreef:

Problem? What problem? Just create temp tablespace(s). Then alter the
db
to use a (new) global temp TS.
Drop the old one(s)

You can also have multiple datafiles making up your TEMP tablespace
(which can be offlined, droped and recreated - tricky), or use a
tablespace group made up of multiple tablespaces. Either option allow
you to resize the TEMP tablespace without droping it - which saves you
the trouble of reassigning the default tablespace for your users.
However, this should not be part of your regular procedure, as you are
downgrading the performance of Oracle. There is a reason why Oracle
does not waste time deallocating the space taken by the TEMP segments -
it is to avoid having to use more resources to allocate more segments
the next time you need them.
You basically have two distinct issues: - how much temp space a certain
process consumes (may be worth reducing), and - freeing up allocated
temp space (typically should be avoided, unless the TEMP files didn't
have an appropriate maxsize)

My opinion