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
Solved

oracle TEMP TBS

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Query to identify changes between rows of two tables 8 55
su - oracle could not open session 6 95
Oracle sql query 7 74
Oracle - SQL Query with Function 3 53
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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…

838 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