Re: drop temp tablespace takes forever

<div class="mail">
<address class="headers">
<span id="from">
<dfn>From</dfn>: EdStevens <quetico_man_at_yahoo.com>
</span>
<span id="date"><dfn>Date</dfn>: 18 Oct 2006 10:23:55 -0700</span>
<span id="message-id"><dfn>Message-ID</dfn>: <1161192235.480693.246200_at_k70g2000cwa&#46;<!--nospam-->googlegroups.com>
</span>
</address>

maxim2k wrote:
<br>
<i class="quotelev1">> On 10/18/06 5:02 PM, EdStevens wrote:</i><br>
<i class="quotelev2">> > did you remember to alter every user to make their temp tablespace the</i><br>
<i class="quotelev2">> > 'new' one while you drop and recreate the old one? That's a separate</i><br>
<i class="quotelev2">> > operation from simply making the new one the 'default'.</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> Nope, I didn't do that.</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> Is there a quick way to alter every user to use the new temporary</i><br>
<i class="quotelev1">> tablespace?</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> Will the Oracle server complete the drop tablespace command (my SQL is</i><br>
<i class="quotelev1">> still stuck waiting for completion) as soon as I update every user or</i><br>
<i class="quotelev1">> should I force anything?</i><br>
<i class="quotelev1">></i><br>
<i class="quotelev1">> Thanks.</i><br>

Yes, there is a quick way to alter every user. My favorite technique has been to write a sql script that writes a sql script:

spool doit.sql
<br>

SELECT 'alter user ' || user_name || ' temporary tablespace temp2;' from some_table;
<br>

spool off
<br>

@doit&#46;<!--nospam-->sql

I also - just in the last week or so - saw a way to do this with dynamic sql (so no need to spool out a secondary script), but I'd have to look it up again or work it out from scratch.

Will running this un-stuck your current drop? Maybe, maybe not, and if so it may take a while. Altering a user's temp tablespace is not going to move any current operations, so the drop will have to wait for everything to flush through. On a busy system, this may take a loooong time.

I have been doing some re-organization lately, and just happen to have in my back pocket a complete script for moving a temp tablespace:

set head on pages 999 echo on feedback on verify on trimspool on
<strong>CREATE TEMPORARY TABLESPACE TEMP2
</strong>

	TEMPFILE '/db02/oradata/epsdv/temp2.dbf'
	SIZE 20M
	REUSE
	EXTENT MANAGEMENT LOCAL
	UNIFORM SIZE 16M

--
alter database default temporary tablespace TEMP2
/
--
set head off pages 999 echo off feedback off verify off
spool doit.sql
select 'alter user '||username||' temporary tablespace TEMP2;'
	FROM dba_users
/
spool off
set head on pages 999 echo on feedback on verify on trimspool on
@doit&#46;<!--nospam-->sql
--
set head on pages 999 echo on feedback on verify on trimspool on
--
Drop tablespace temp including contents and datafiles
/
CREATE TEMPORARY TABLESPACE TEMP
	TEMPFILE '/db01/oradata/epsdv/temp01.dbf'
	SIZE 20M
	REUSE
	EXTENT MANAGEMENT LOCAL
	UNIFORM SIZE 16M
/
alter database default temporary tablespace TEMP
/
set head off pages 999 echo off feedback off verify off
spool doit.sql
select 'alter user '||username||' temporary tablespace TEMP;'
	FROM dba_users
/
spool off
set head on pages 999 echo on feedback on verify on trimspool on
@doit&#46;<!--nospam-->sql
set head on pages 999 echo on feedback on verify on trimspool on
--
Drop tablespace temp2 including contents and datafiles
/


<span id="received"><dfn>Received on</dfn> Wed Oct 18 2006 - 12:23:55 CDT</span>

</div>