 | | | drop temp tablespace | drop temp tablespace 2004-02-17 - By DENNIS WILLIAMS
David
If you can 't bounce the database right away, turn off autoextend on the
datafile. If you haven 't already.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@(protected)
-- --Original Message-- --
From: Guang Mei [mailto:gmei@(protected)]
Sent: Tuesday, February 17, 2004 4:07 PM
To: oracle-l@(protected)
Subject: RE: drop temp tablespace
Are you sure all users have been switched to the new temp ts? "the used size
is still increasing " probably means there are still something going on the
old temp ts. I will just bounce the db (if possible) and then drop the old
temp.
Guang
-- --Original Message-- --
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)]On Behalf Of David Boyd
Sent: Tuesday, February 17, 2004 4:35 PM
To: oracle-l@(protected)
Subject: RE: drop temp tablespace
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@(protected) >
>Reply-To: oracle-l@(protected)
>To: <oracle-l@(protected) >
>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@(protected)
>[mailto:oracle-l-bounce@(protected)]On Behalf Of David Boyd
>Sent: Tuesday, February 17, 2004 12:08 PM
>To: oracle-l@(protected)
>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@(protected)
>put 'unsubscribe ' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://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@(protected)
>put 'unsubscribe ' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://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@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
|
|
|