Create tablespace (newbie question) 2

[lbrechler]

Ok -- I'm sure this is easy enough, but any help would be appreciated...

I've just created a new database called MYDB using the Oracle 9i DBCA (Database Configuration Assistant).

I then logged in to SQL*Plus as SYSTEM, and tried to create a tablespace called 'general' using the following script:

SQL> CREATE TABLESPACE general
DATAFILE 'C:\oracle\oradata\MYDB\general01.dbf'
SIZE 10M
AUTOEXTEND ON NEXT 10M
MAXSIZE 100M;

(By the way, there is no file in that location called general01.dbf).

I then tried to create a new user and assign 'general' as their default tablespace. I also granted CREATE SESSION, CREATE TABLE, CREATE VIEW, and CREATE SEQUENCE privileges to that user. The user and grants seemed to work successfully, so I logged out and then back in as the new user. Now I'm having trouble running my table creation scripts as that user. I get the following error:

ORA-01950: no privileges on tablespace 'GENERAL'


Any ideas of what I've done wrong? Is it the lack of the general01.dbf file? Something else?

Thanks in advance,
~Lindsay

 

[sem]

Try to grant RESOURCE to this user. Regards, Dima

 

[stefanhei]

Did you declare general as default tablespace for the user? You can check with:
select default_tablespace from dba_users where username = 'YOURUSER'
If the default tablespace is still SYSTEM I'd change it as soon as possible, probably to general with:
alter user YOURUSER default tablespace general
If the user already has a proper default-tablespace you can explicitly grant a quota on general to the user with:
alter user YOURUSER quota unlimited on general
You may specify the space the user is granted instead of setting to unlimited.

Stefan

 

[Leon1977]

First in order to have the possibility to create something you should have a Quota on some tablespace. Set a default tablespace to your user, then give him some quota.
Do not use the Resource role because it gives the Unlimited tablespace priviledge so the user could write in the system tablespace

 

[lbrechler]

Got it -- thanks!

Looks like I had already assigned a default tablespace, but had not altered the quota for that user. Once I did that, it worked like a charm.

~Lindsay