Re: Question about creating user and tablespace with Oracle 9.0

"André Larouche" <andre.larouche_at_bnc.ca> wrote in message news:35ce1894.0110051105.60175707_at_posting.google.com...
> 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;.

The hard coded section is wrong anyway: it shouldn't use one tablespace as default and as temporary.
Oracle 9.0 evidently wants to enforce the temporary tablespace being temporary.
The only solution I can see is lowering the compatible parameter of your database. I'm not sure how to do that, as evidently 9.0 is a major release, which usually precludes lowering it below the lowest version in that release.

Maybe you should open a TAR with Oracle and chase the supplier of the code to change their create user command. Especially is this is commercial sw, multiple sites are suffering from it now, and will suffer more when they upgrade

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Fri Oct 05 2001 - 14:25:58 CDT