Oracle : Unlimited Tablespace, User Creation

Iím new to Oracle 9i using R2. Iím currently logged in as sysdba and currently tried creating a new user:

SQL> create user john identified by oracle
2 default tablespace users;

User created.

SQL> grant connect to john;

Grant succeeded.

SQL> grant create any table to john;

Grant succeeded.

SQL> grant create table to john;

Grant succeeded.

SQL> grant unlimited tablespace to john;

Grant succeeded.

Why is it that if I donít grant unlimited tablespace, john will not be able to create table? It gives an error :

ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

But I have stated him to use tablespace users when created the user name at first?


You have to assign a quota.

Assign Tablespace Quotas to Users

Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object's segment. The security administrator is responsible for granting the required privileges to create objects to database users and for assigning tablespace quotas, as necessary, to database users.


just drop the user......john

and try this command....

create user john identified by oracle
default tablespace users
quota 5m on users

after firing up this command give required privileges to john to create a table....
then try whether it is working perfectly without granting that unlimited tablespace....


You don't have to delete the user. Just alter it.

alter user john quota unlimited on users;


When you creates the User and Grants him the Connect option by default the User gets allocated with the Unlimited Tablespace only you need to do is eigther Revoke that Option. or as bobanjayan said u just alter the user and add the Quota to that user.


Connect role does't have the privilege of unlimited tablespace.
Unlimited tablespace is with Resource role.

If you grant Resource role to user, he will get unlimited quota on SYSTEM tablespace also.
Be aware.

Oracle Database

Also read:

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.