Hi experts,
I've got a Oracle 10g database hosted on a Sun solaris Server.
Sometimes when users issue huge queries, the TEMP tablespace gets filled and the DB issues random errors whenever a complicated query is issued.
In such situations, when the actual DBA is not there, I just shutdown then restart the DB (it's a small datawarehouse DB) to flush the TEMP tablespace a get a clean one upon restart.
Unfortunately I'm currently facing this problem, and this time restarting the DB does not help.
How could I get a fresh TEMP tablespace? Of course I could extend it (add another datafile), but it is already rather large (about 26 Go). Maybe I should extend it first, and then restart ? (It currently has close to 0% free space)
Any suggestion welcome
Hilaire
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Where are you seeing that TEMP is full? Are you looking in DBA_FREE_SPACE or V$SORT_USAGE?
TEMP space is reused once it is allocated. If at some point all the blocks are used for various sorts the tablespace will appear full if you look for free space in DBA_FREE_SPACE. However the actual usage is stored in V$SORT_USAGE.
Given that this is a warehouse, adding space to TEMP is probably not a bad idea.
Business Accounts
Answer for Membership
by: TolomirPosted on 2007-07-11 at 04:22:55ID: 19461405
I would add another datafile.
Oracle should be able to free the temp space on itself. So give it place to do so.
You might though want to set a quota on temp for the active users thus no one is able to waste all free space.