Question about creating user and tablespace with Oracle 9.0 
Author Message
 Question about creating user and tablespace with Oracle 9.0

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;.



Wed, 24 Mar 2004 03:05:49 GMT
 Question about creating user and tablespace with Oracle 9.0


Quote:
> 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



Wed, 24 Mar 2004 03:25:58 GMT
 Question about creating user and tablespace with Oracle 9.0


Quote:
> 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



Fri, 26 Mar 2004 16:58:09 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Creating a Stored Procedure in Oracle 9.0

2. Creating users conditionally on tablespace...

3. Specifying default tablespace when creating an user?

4. moving data from 8.0.5 user_data tablespace to 8.1.7 users tablespace

5. Dropping tablespace (create zero-block tablespace)

6. Questions of a new user -- Unable to create a database with Oracle 8 Enterprise Evaluation

7. question of user and tablespace

8. Question on SQL CREATE TABLESPACE command

9. Question about schemas and tablespaces and users

10. Question: seting tablespace priv for user

11. create new tablespace (redhat linux 6.2 + oracle 8i)

12. Creating Oracle user based on an existing user


 
Powered by phpBB® Forum Software