Solved

oracle TEMP TBS

Posted on 2009-07-07
3
791 Views
Last Modified: 2013-12-19
My Oracle temp tablespace is 100% utilized.
I am trying to Flush it out by re-starting the database but still after the re-start it shows 100% utilized.

Can any one tell me what might be the reason.

As far as i know Oracle(i m using 10g) frees temp tablespace automatically .

Now i have an option to re-create the TEMp tbs AND fix this issue but i would like to know why issue is not getting
fixed after restart
0
Comment
Question by:suhinrasheed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24800824
I believe there is a known bug with 9i and possibly 10g about this issue.

You can try creating a new temp tablespace and setting the database default temporary tablespace to temp2, dropping the old one. Then you can just leave it with temp2 or you can re-create an even larger one and revert back.


SQL> create temporary tablespace temp2 tempfile 'c:\oracle\dev\temp02.dbf' size 1024m;

Tablespace created.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> drop tablespace temp including contents and datafiles;

Tablespace dropped.


0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 24802901
Oracle doesn't "free" temp tablespace space.  It reuses it.  To see the actual usage in the temp tablespace, look in V$SORT_USAGE.

There are known bugs with temp space not being reused, however the temp tablespace having nothing free is not usually an issue.  Are you getting cannot extend messages?
0
 

Author Closing Comment

by:suhinrasheed
ID: 31600955
AGREED
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 60
MS SQL Server Management Studio R2 4 59
Oracle Date 6 39
Force XMLSEQUENCE to return empty tags for null values. 10 45
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question