Re: drop temp tablespace
- From: "zhu chao" <chao_ping@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 22 Feb 2004 13:38:09 +0800
hi,
What is your version of oracle and is your temp tablespace locally
managed temporary tablespace? I think you are using dictionary temp
tablespace with inproper initial/next size.
If you used Guang's method, how can it be so long?
----- Original Message -----
From: "David Boyd" <davidb158@xxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, February 19, 2004 2:22 AM
Subject: RE: drop temp tablespace
> Thanks all of you who replied my message. The old temp tablespace has
been
> dropped. It took more than 4 days.
>
>
> >From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
> >Reply-To: oracle-l@xxxxxxxxxxxxx
> >To: oracle-l@xxxxxxxxxxxxx
> >Subject: RE: drop temp tablespace
> >Date: Wed, 18 Feb 2004 23:14:58 +0800
> >
> >
> >If you say that DBA_SEGMENTS show 0 rows, how do you get a Used Size for
> >the Tablespace ?
> >
> >Hemant
> >At 04:35 PM 17-02-04 -0500, you wrote:
> > >Guang,
> > >
> > >Thanks for your response.
> > >
> > >Initially I had to 'drop tablespace temp including contents'.
Currently
> >it
> > >returns 0 for the query of select count(*) from dba_segments where
> > >tablespace_name = 'TEMP'. I can do 'drop tablespace temp' without
> >including
> > >contents option. However the used size is still increasing.
> > >
> > >
> > > >From: "Guang Mei" <gmei@xxxxxxxxxx>
> > > >Reply-To: oracle-l@xxxxxxxxxxxxx
> > > >To: <oracle-l@xxxxxxxxxxxxx>
> > > >Subject: RE: drop temp tablespace
> > > >Date: Tue, 17 Feb 2004 12:52:27 -0500
> > > >
> > > >After you switch all the users to the new temp ts, make sure there is
> >no
> > > >objects sitting in your old temp tablespace:
> > > >
> > > >select count(*) from dba_segments where tablespace_name =
> ><old_temp_ts>;
> > > >
> > > >if you get 0, then run
> > > >
> > > >alter tablespace <old_temp_ts> offline;
> > > >
> > > >-- replace your temp datafiles here:
> > > >
> > > >alter database datafile '/oracle/u2/oradata/YPD/temp01.dbf' offline;
> > > >alter database datafile '/oracle/u2/oradata/YPD/temp02.dbf' offline;
> > > >alter database datafile '/oracle/u2/oradata/YPD/temp03.dbf' offline;
> > > >alter database datafile '/oracle/u2/oradata/YPD/temp04.dbf' offline;
> > > >alter database datafile '/oracle/u2/oradata/YPD/temp05.dbf' offline;
> > > >
> > > >
> > > >drop tablespace <old_temp_ts> ;
> > > >
> > > >
> > > >If this does not work, you proabably have a wait a while and try it
> >again.
> > > >If still not working, you might have to bounce the DB.
> > > >
> > > >HTH.
> > > >
> > > >Guang
> > > >
> > > >-----Original Message-----
> > > >From: oracle-l-bounce@xxxxxxxxxxxxx
> > > >[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of David Boyd
> > > >Sent: Tuesday, February 17, 2004 12:08 PM
> > > >To: oracle-l@xxxxxxxxxxxxx
> > > >Subject: drop temp tablespace
> > > >
> > > >
> > > >Hi List,
> > > >
> > > >Our TEMP tablespace took Oracle default setting that was auto
extending
> >up
> > > >to 32 GB. Last week the tablespace had grown to 18 GB. I tried to
> >drop
> > > >the
> > > >tablespace after I removed any body's temporary tablespace to a newly
> > > >created temp tablespace with much smaller size. The dropping process
> >is
> > > >very slow. I started last Friday. The used size in the old temp
> > > >tablespace
> > > >had been decreased from 18 GB to 3.8 GB yesterday afternoon. I
issued
> >the
> > > >drop command through TOAD job scheduler. There was one failure with
> >the
> > > >job
> > > >when I checked this morning. The dropping process seemed stopped. I
> >had
> > > >to
> > > >kill the job. Now the used size in the old temp tablespace is coming
> >back
> > > >slowly even through I issued another dropping command in SQLPLUS. I
> >was
> > > >wondering if any one has any way to drop the temp tablespace quickly.
> > > >Thanks in advance.
> > > >
> > > >_________________________________________________________________
> > > >Find and compare great deals on Broadband access at the MSN
High-Speed
> > > >Marketplace. http://click.atdmt.com/AVE/go/onm00200360ave/direct/01/
> > > >
> > > >----------------------------------------------------------------
> > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > >----------------------------------------------------------------
> > > >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > >put 'unsubscribe' in the subject line.
> > > >--
> > > >Archives are at //www.freelists.org/archives/oracle-l/
> > > >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > >-----------------------------------------------------------------
> > > >
> > > >----------------------------------------------------------------
> > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > >----------------------------------------------------------------
> > > >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > >put 'unsubscribe' in the subject line.
> > > >--
> > > >Archives are at //www.freelists.org/archives/oracle-l/
> > > >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > >-----------------------------------------------------------------
> > >
> > >_________________________________________________________________
> > >Get fast, reliable access with MSN 9 Dial-up. Click here for Special
> >Offer!
> > >http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/
> > >
> > >----------------------------------------------------------------
> > >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > >----------------------------------------------------------------
> > >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > >put 'unsubscribe' in the subject line.
> > >--
> > >Archives are at //www.freelists.org/archives/oracle-l/
> > >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > >-----------------------------------------------------------------
> >
> >Hemant K Chitale
> >Oracle 9i Database Administrator Certified Professional
> >http://hkchital.tripod.com {last updated 24-Jan-04}
> >
> >
> >----------------------------------------------------------------
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >----------------------------------------------------------------
> >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> >put 'unsubscribe' in the subject line.
> >--
> >Archives are at //www.freelists.org/archives/oracle-l/
> >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> >-----------------------------------------------------------------
>
> _________________________________________________________________
> Stay informed on Election 2004 and the race to Super Tuesday.
> http://special.msn.com/msn/election2004.armx
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
