From:Steve Adams
Date:13-Sep-2000 05:48
Subject:   system_space_invaders.sql

I don't know of any restriction on dropping a tablespace because it has been (or still is) the temporary tablespace for SYS. Certainly, if there was such a restriction in the past, it no longer applies.

The comments on the APT script system_space_invaders.sql suggest that SYS should have a temporary tablespace other than the SYSTEM tablespace primarily to prevent fragmentation in the SYSTEM tablespace, but using a tablespace with temporary contents (preferably a tempfile tablespace) for the temporary tablespace for SYS also reduces the risk of ST enqueue waits.

I was wondering why you state that not even SYS may use the SYSTEM ts as temporary ts. I experienced that if you define for instance the TEMP ts as temporary ts for SYS, it is impossible to drop it afterwards if you redefine the temporary ts to TEMP2 for all users on the fly. After that experience I have always defined the SYSTEM ts as temporary ts for SYS and another ts for all other users.