Re: Help! Can I rebuild TEMP tablespace

From: F Lange <flange_at_pt.lu>
Date: 1997/09/24
Message-ID: <01bcc91c$fa1245a0$8cd79ac2@fql10>#1/1

Hi,

	No problem to do it only.
	Check only the following thing.

		When you will drop the tablespace if there is any user they donnt' have
to have 
			temporary tablespace temp ;

				Change it to on other one during that time.

		Check there is not a query using temporary at that time.

			select * from dba_extents where tablespace_name = 'TEMP' and

segement_type = 'TEMPORARY' ;

                Than drop the tablespace and recreate it whith right default initial and next.

-- 
Regards F. Lange Certified DBA.
29A route du vin.
L5450 Stadtbredimus.
Grand Duchy of Luxembourg.
flqnge_at_pt.lu
(int) 352 697412
(cel) 352 021193652
Phone numbers are variable length.
Luxembourg is a small country of 80 miles on 60 miles between France,
Germany and Belgium.


g.r.s. deisz <g.r.s.deisz_at_ptt-telecom.unisource.nl> wrote in article
<60aerg$ue2_at_hdxl16.telecom.ptt.nl>...


> 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.

> 

Received on Wed Sep 24 1997 - 00:00:00 CDT