oracle TEMP TBS

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
suhinrasheedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrjoltcolaCommented:
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
johnsoneSenior Oracle DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
suhinrasheedAuthor Commented:
AGREED
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.