Re: Under Oracle 9, is there a guarantee of at least 1 temp TS ?

Scott W wrote:

> My company has an installation script for our product that creates a user
> and assigns that user's temp AND default tablespace to point to the same
> tablespace (typically not an actual TEMPORARY tablespace). Now under Oracle
> 9 the TEMP tablespace MUST be a real TEMPORARY tablespace. If you just
> create a user without specifying DEFAULT and TEMPORARY tablespace, Oracle
> will pick a temp tablespace, seemingly at random. I don't have the freedom
> to delete all temp tablespaces to test my theory, so that's why I am asking:
>
> 1) Is there any guarantee that there will be at least 1 temp TS under
> Oracle 9 ? (Not that I am aware of)
>
> 2) What would happen with a create user statement with no TEMP TS clause, if
> there are no temp TSs in the instance ? Would it use SYSTEM ? Error out ?
>
> Thanks, Scott

1. No guarantee though anyone that creates a database without one deserves what they get.
2. SYSTEM though I haven't tested it.

I am a bit concerned with the entire way your company has gone about this thing. Using a non-Temporary tablespace for temp ... what exactly did anyone hope to gain? I ask because I can think of plenty of things you can and did lose.

Why not have your create use script run a select for dba_tablespaces and find a temporary tablespace? Why the seeming resistance to doing things the way Oracle recommends?

It seems like you are trying to use Oracle and at the same time gut it. I just don't get the point.

Daniel Morgan Received on Wed May 15 2002 - 10:16:18 CDT