How to clear Temp Tablespace in oracle 9i
Posted on 2006-11-24
I am writing a very large materialised view to be stored in a custom tablespace.
The temp tablespace is on a RAM drive, and is fixed at 1.5Gb. This cannot be extended any more (on the RAM drive).
My problem is that creating my view in the custom tablespace fllls up the temp tablespace, and causes errors when it cannot be extended.
My questions are as follows:
1) If I extend my temp tablespace via a hard drive datafile, is there any way to specify that I wish for my RAM drive to be used primarily?
2) Even when my mat view is created successfully in my custom tablespace, the temp tablespace is not cleared after. Is there any way to clear this without having to shutdown and restart?
3) Is there anything fundamentally worng with having a temp tablespace of only 1.5Gb in the first place?
Thank you in advance for your efforts.