I am using Oracle 8.1.7 on Solaris. My TEMP tablespace is of size 3GB and I want to maintain it at 1GB. The HWM is at 2.6G, so I created a new temporary tablespace, assigned it to all the users who were using the old one, brought the old one offline and ran the
DROP TABLESPACE TEMP INCLUDING CONTENTS;
The database is live and this process is really slow - it has been running for 2 hours now - as I have renamed the default temp tablespace for all the users I know this tablespace is not being used. Is this time period normal ? If not, what am I doing wrong ? The old temp tablespace was database managed.
I do have lot of extents for this tablespace - around 11944 in number. That may be the reason why it is taking this much time and this number is decreasing slowly.
But I checked the entries in sys.fet$ and they seem to be increasing by the time. Does this mean that the tablespace is being used by someone ?