Re: Help! Can I rebuild TEMP tablespace

In article <19970924000001.UAA27721_at_ladder02.news.aol.com>,

   leidad_at_aol.com (LeidaD) wrote:
>I have 3 datafiles for the TEMP tablespace that totals to 3G. I kept
>running out of extents to do a massive sort, so I finally added the 3rd
>datafile to TEMP at 2G. Now that I have the table sorted and right where it
>needs to be, I do not want to waste space on the system with this 2G
>datafile hanging there after what I did.
>Question: Is it safe when the system is completely idle to Delete and
>recreate the TEMP tablespace to eliminate the extra datafiles WITHOUT
>crashing the system? I guess I want to know what the risk is since I've
>never tried this on TEMP tablespace.
>
>Thank you for those who can shed insight to this..
>We have Oracle 7.1.6 on an HP 10.01 o/s.

I had exactly the same problem. I solved it by starting the database in restricted mode, disabling all jobs, dropping TEMP, removing the datafiles and recreating TEMP at the right size.
Worked just fine on 7.1.4.1.0
You should check the rights on the datafiles after creating them, just to make sure they are the same they are right now. Also make sure the default storage parameters are OK. It's no use creating a TEMP tablespace of 1G, and allowing temporary segments to only grow to 500M.

Of course I made a backup before starting my action.

Stefan.

Name      :G.R.S. Deisz
Phone     :+31-50-5855954
E mail    :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
DISCLAIMER:This statement is not an official statement from, nor
           does it represent an official position of, PTT Telecom BV.