| Temp Tablespace not being flushed automatically [message #50792] |
Mon, 08 April 2002 01:02  |
Hitesh Shenoy
Messages: 11
Registered: January 2002
|
Junior Member |
|
|
OS : TrueUnix 5.0
DB : Oracle 8.1.7 on OPS
The temporary tablespace in my database is being filled in continously and does not get flushed out automatically. The view 'gv$sort_segment' shows me 0 free blocks in my Temp tablespace.
So I am forced to restart my database every day, as once the temp gets full my database operations come to a stand still and performance is poor.
Please advise.
Regards,
Hitesh
|
|
|
|
| Re: Temp Tablespace not being flushed automatically [message #50795 is a reply to message #50792] |
Mon, 08 April 2002 04:57  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
If your Temp space gets full too quickly, then sounds like it is too small for the type of work you are doing. Or you are not hitting the indexes and in turn doing full table scans. May wanna consider adding some space in the temp tablespace.
As for cleaning up the temp space, here is the command. You do not have to restart the db every time. Just use this command
alter tablespace temp default storage (pctincrease 0);
(as system of coarse).
Good luck.
|
|
|
|
| Re: Temp Tablespace not being flushed automatically [message #50886 is a reply to message #50792] |
Fri, 12 April 2002 04:55 |
Hitesh Shenoy
Messages: 11
Registered: January 2002
|
Junior Member |
|
|
Continuing with my issue mentioned above, I have been suggested to create a temporary segment
create temporary tablespace temp01 tempfile 'filename' size 100m;
I have created the 'temp01' segment and the issue has been resolved. Now is it advisable to move my users from the old 'temp' tablespace to the new 'temp01' tablespace. Also can I reduce the size of my old 'temp' and add more new temporary tablespaces. Is there a drawback in doing so?
Also what is the difference between the default 'temp' tablespace which is permanent i beleive and this temporary segment 'temp01'. Cause the new one cant be seen in view dba_tablespaces, the view to check is dba_temp_files and v$tempfile.
Please advise.
Many thanks.
Hitesh
|
|
|
|
| Re: Temp Tablespace not being flushed automatically [message #50894 is a reply to message #50886] |
Fri, 12 April 2002 11:07 |
Kishore
Messages: 45
Registered: September 2000
|
Member |
|
|
Hi,
1. There is no drawback in doing so. You can make whatever changes you want to do to the old 'temp' tablespace.
2. The reason you got the problem at the first place is because, it appears that you have allocated a 'permanent tablespace' for sorting purposes.
3. A tablespace with the name 'temp' may not really a 'temporary tablespace' unless you use the keyword 'TEMPORARY' while creating that tablespace.
4. The issue has been resolved this time is because, you have specified TEMPORARY key word in the syntax and therefore created a real 'temporary tablespace'.
5. In addtion, the syntax you have used is specific to the ''LOCALLY MANAGED TEMPORARY TABLESPACES''. Since, it's locally managed you can only find info in v$TEMFILE or dba_temp_files, but not in dictionary managed views like dba_data_files or v$datafile.
I hope this helps you,
Kishore
|
|
|
|
|
|
| Re: Temp Tablespace not being flushed automatically [message #59672 is a reply to message #59664] |
Mon, 15 December 2003 05:30 |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Patrick,
We were taking about TEMP tablespace and TEMP tablespace are of type TEMPORARY .... In this manner, whenever the DB is recycled, it is cleared out.
But this is really interesting. I did a small test and made TEMP2 'local + temporary' and could not give the 'alter tablespace ...' command.
So I cannot flush the Temp2 .... Please post if you find any alternitives....
Cheers.
--
Sanjay
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
