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

We understand well the issues you point out. The script that does this is within a commercial program that creates its own tablespace which is for our own use. Then we create our own schema and create objects for our own use. By giving this schema owner the same tablespace for both default and temp we keep everything in one place. Some customers don't even like use creating one extra tablespace, much less two, which is what we would have to do if we did it the "proper" way. They also don't want us using one of their existing temp tablespaces to put stuff in, which I can't blame them for that.

Now that Oracle 9 has come along, we decided to put the responsibility on the user/customer to create a user just for the purpose of our product, and let THEM control what the default and temp tablespace is. That gives them more control and we don't have to juggle things around as much.

Makes sense ?

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CE27BBF.F1D66A54_at_exesolutions.com...
> 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 Thu May 16 2002 - 16:26:36 CDT