Re: Oracle 8i temporary tablespace

"Roop" <rupinder99_at_hotmail.com> wrote in message news:<jtBid.19333$v03.316147_at_wagner.videotron.net>...
> Hello
> I want to recreate the temporary tablespace in ORACLE 8i and I am
> doing the following steps:
>
> 1. STARTUP (OPEN MODE)
>
> 2. create tablespace temp_data1
> datafile 'temp_data1.dbf' size 30m autoextend on next 10M
> default storage (
> initial 100k
> next 100k
> minextents 1
> maxextents unlimited
> PCTINCREASE 20)
> online
> temporary;
>
> 3. DROP TABLESPACE TEMP_DATA INCLUDING CONTENTS; //DROPS THE OLD TEMPORARY
> TABLESPACE
>
> 4. ALTER USER SYS TEMPORARY TABLESPACE TEMP_DATA2; //ASSIGN USERS
> NEW TEMPORARY TABLESPACE
> 5. ALTER USER SYSTEM TEMPORARY TABLESPACE TEMP_DATA2;
>
>
> Are these steps ok (how is default temporary tablespace assigned in 8i) and
> am i missing any of the steps.
>
> Thank you in advance

You should probably alter all users whose temporary tablespace is the tablespace you wish to drop prior to dropping it.

1- create new temp tablespace
2- assign all users to new temp tablespace
3- wait for all sorts already in progress to complete
4- alter old temp tablespace offline
5- drop old temp

HTH -- Mark D Powell -- Received on Fri Nov 05 2004 - 15:40:01 CST