RE: Temporary tablespace for SYS

From: Leng Kaing <leng.kaing_at_hancorp.com.au>
Date: Thu, 14 Sep 2000 16:07:50 +1100
Message-Id: <10618.116935@fatcity.com>

Hi Steve,

I've just done a test on both 7.3.4 and 8.1.5 and and have been able to confirm that you CAN drop a tablespace that has previously been the temporary tablespace for SYS. So I don't know what version of Oracle this theory may have come from.

Rgs,

Leng.

        H815.SYS> alter user sys temporary tablespace OEM_REPOSITORY;

        User altered.

        select username, default_tablespace, temporary_tablespace from dba_users

        where username = 'SYS';

        USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE

• ------------------------------

  ---

  SYS SYSTEM OEM_REPOSITORY

        H815.ORACLE> alter user sys temporary tablespace temp;

        User altered.

        H815.ORACLE> alter tablespace OEM_REPOSITORY offline;

        Tablespace altered.

        H815.ORACLE> drop tablespace OEM_REPOSITORY;

        Tablespace dropped.

        H815.ORACLE> !rm /opt/app/oracle/hubu/oradata/H815/oemrep01.dbf

        H815.ORACLE> select username, default_tablespace, temporary_tablespace from dba_users

        where username = 'SYS';

        USERNAME DEFAULT_TABLESPACE Received on Thu Sep 14 2000 - 00:07:50 CDT