Hi all.
How I can free segments in temporary tablespace?(No shutdown DB way)
When temporary segments deleted?
Best regards.
Printable View
Hi all.
How I can free segments in temporary tablespace?(No shutdown DB way)
When temporary segments deleted?
Best regards.
You can make temp tablespace offline, then delete. However, you make sure there are no sort operations or other operations involving temp tablespace are taking place in the database at that time.
Hi Raminder.
"you make sure there are no sort operations or other operations involving temp tablespace are taking place in the database at that time. "
How ? with V$sort_usage ? or another way ?
Just do
'Alter tablespace temp_ts offline;'
if ther are any active operations going on in the tablespace, it will give an error.
Thank you Raminder.
How many temp tablespace needed(or is better) for one DB with about 200 concurrent user?(one,two or more ?).
If i have more than one temp tablespace , what is distribution of temporary extents allocation?
Sorry for my english!!
Best Regards.
you can have one tablespace and several datafiles so the point is not how many tablespace you should have, is how well are they distributed physically, tablespace is something logical, transparent to users, applications. Having more than one may provide more availability just in case one gets corrupted at physical level
Why would you want to free up the extents in a temporary tablespace?
My temporary tablespace (with 2 datafile) is about 4GB , and I want reduce size of this temporary tablespace by deleting one datafile(or reducing size of two datafile) because Hard disk is near full.
If I have 4 temp tablespace and 2000 user then I can assign each temp tablespace for 500 user.(alter user temporary tablespace temp1 ; ....). Is it correct?
Sorry for my english!!!
Best regards.
Hi ALL.
I want know finally :
1) Why oracle doesn't deallocate automatically(periodically) unused temporary extents in one temporary tablespace ?
2) for one server that can't shutdown , what is best way for temporary extents deallocations?
Best Regards.
Have you created your temporary tablespace with the TEMPFILE option? If yes, I do not see the point of having 2 datafiles. If no, I suggest you create them as TEMPFILEs.Quote:
My temporary tablespace (with 2 datafile) is about 4GB
1) To improve performance, extents are used over and over again, if SMON has to deallocate then that is extra workQuote:
Originally posted by mahoori
Hi ALL.
I want know finally :
1) Why oracle doesn't deallocate automatically(periodically) unused temporary extents in one temporary tablespace ?
2) for one server that can't shutdown , what is best way for temporary extents deallocations?
Best Regards.
2) I dont think there is a way to force SMON to free the extents, you would have to create a new temp tablespapce assign to users and drop the old one
Hi All,
I've got a similar problem to Mahoori. My temporary tablespace has four temp files shared over 4 logical volumes. This is because we are set up for parallelisation with a degree of 4 on underlying objects (striped logically across 4 volumes). We've tested this set up and it works the fastest for our bespoke applications.
We have a result_set tablespace that holds result sets from queries. This tablespace resides on the same volumes as the temporary tablespace.
I needed to allocate more space to the result_set tablespace, and attempted to free up some space by reducing the size of the temporary_tablespace...................
alter database tempfile 'S:\ORADATA\HCSP\TMP04.DBF' resize 1000 M;
ORA-03297: file contains used data beyond requested RESIZE value
So, anyone know of a way round this...............