Solved

oracle TEMP TBS

Posted on 2009-07-07
3
798 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

724 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