Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

oracle TEMP TBS

Posted on 2009-07-07
3
Medium Priority
?
826 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 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

610 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