Re: Question about creating user and tablespace with Oracle 9.0

In article <35ce1894.0110051105.60175707_at_posting.google.com>, andre.larouche_at_bnc.ca says...
> In Oracle 8.1.7, our application create users with those commands:
>
> Script:
> CREATE TABLESPACE USER_DATA ONLINE PERMANENT;
>
> Hard coded:
> CREATE USER <USER> IDENTIFIED BY <PWD>;
> ALTER USER <USER> DEFAULT TABLESPACE USER_DATA;
> ALTER USER <USER> TEMPORARY TABLESPACE USER_DATA;
>
> The same hard coded command doesn&#8217;t work in 9.0.
>
> The message is that a permanent table space cannot be used as
> temporary.
>
>
> When I create the tablespace USER_DATA as following:
> CREATE TABLESPACE USER_DATA ONLINE TEMPORARY;
>
> We get the message that a default tablespace cannot be temporary. We
> don&#8217;t have the source code to modified the hard coded section.
> Is there a solution&#8230;.
>

It doesn't look like it. The application code will need changing for 9. This is no bad thing actually as it is less than ideal for 8i.

A user will need a default tablespace to put their objects - thus it cannot be a temporary tablespace. Similarly they will need atemp tablespace to do sorts etc. As of 9 this cannot be permanent. Thus in 9 you cannot use the same tablespace for the users default and temporary tablespace. However you should never be mixing permanent and temporary segments anyway so the code you give is bad even for 8i

HTH

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK