Re: Why I cannot create a table even I have the RESOURCE role?

You need to have quota on your default tablespace, USERS ...

alter user a quota 10m on USERS ; (or some appropriate value)

On Fri, 02 Jun 2000 13:46:12 GMT, david_petit_at_yahoo.com wrote:

>Hello all,
>
> I have a question about Oracle 8.0.5 on Solaris.
>
>***************
>*** User A ****
>***************
>
>SQL> select * from user_role_privs;
>
>USERNAME GRANTED_ROLE ADM DEF OS_
>-------- ------------------------------ --- --- ---
>A CONNECT NO YES NO
>A A_ROLE NO YES NO
>A RESOURCE NO YES NO
>
>3 rows selected.
>
>SQL> select username, default_tablespace, temporary_tablespace
>from user_users;
>
>USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
>-------- ------------------------------ ------------------------------
>A USERS TEMP
>
>1 row selected.
>
>SQL> create table aaa (a number);
>create table aaa (a number)
>*
>ERROR at line 1:
>ORA-01536: space quota exceeded for tablespace 'USERS'
>
>***************
>*** User B ****
>***************
>
>SQL> select * from user_role_privs;
>
>USERNAME GRANTED_ROLE ADM DEF OS_
>-------- ------------------------------ --- --- ---
>B CONNECT NO YES NO
>B A_ROLE NO YES NO
>B RESOURCE NO YES NO
>
>3 rows selected.
>
>SQL> select username, default_tablespace, temporary_tablespace
>from user_users;
>
>USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
>-------- ------------------------------ ------------------------------
>B USERS TEMP
>
>1 row selected.
>
>SQL> create table aaa (a number);
>
>Table created.
>
> Why user A cannot create a table while B can? Both users have the
>same privileges. No profile for these two users. No password file with
>this database. Moreover, I still cannot understand why user A cannot
>create table despite the fact that he has the RESOURCE role. I think a
>user has the RESOURCE role can have the unlimited quota on tablespaces.
>
>
>Thanks,
>David
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Mon Jun 05 2000 - 00:00:00 CEST