Re: Default Temporary Tablespace - behavior has changed in Rel 2

From: Richard Foote <Richard.Foote_at_bigpond.com>
Date: Thu, 15 Aug 2002 23:32:12 +1000
Message-ID: <3D5BAD5C.2E18C882@bigpond.com>

Hi Ora,

That's weird ?

SQL> select username, temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                            SYSTEM
SYSTEM                         TEMP
OUTLN                          TEMP
DBSNMP                         TEMP
OEMREP                         TEMP
LBACSYS                        TEMP
OLAPSVR                        TEMP
OLAPDBA                        TEMP
HR                             TEMP
PERFSTAT                       TEMP
BOWIE                          TEMP3

11 rows selected.

SQL> alter database default temporary tablespace temp2;

Database altered.

SQL> select username, temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS                            SYSTEM
SYSTEM                         TEMP2
OUTLN                          TEMP2
DBSNMP                         TEMP2
OEMREP                         TEMP2
LBACSYS                        TEMP2
OLAPSVR                        TEMP2
OLAPDBA                        TEMP2
HR                             TEMP2
PERFSTAT                       TEMP2
BOWIE                          TEMP3

11 rows selected.

SQL> select * from v$version;

BANNER

Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production PL/SQL Release 9.0.1.1.1 - Production
CORE 9.0.1.1.1 Production
TNS for 32-bit Windows: Version 9.0.1.1.0 - Production NLSRTL Version 9.0.1.1.1 - Production

Note that user BOWIE (bless it) and SYS remain unaffected by my changing the default temporary tablespace ?

Cheers

Richard

Ora wrote:
>
> Oracle 9i: Rel 2
>
> In Oracle 9i Rel 1 – when we switch to new default temporary
> tablespace , even all those users who were explicitly allocated a
> different temporay tablespace were also switched to the default
> temporary tablespace.
>
> This has behavior has pleasantly changed in Oracle 9i Rel 2 .
>
> The explicit allocations stays. Those users continue in their
> allocated Temp tablespace. ..no need to redo all those explicit
> allocations again ..
>
> Great Relief !! Please take note up this .. for those who have not
> noticed !!!
>
> OrA
Received on Thu Aug 15 2002 - 08:32:12 CDT