no privileges on tablespace 'SYSTEM'

[Guest_imported]

I am just getting familiar with Oracle 8i

I create a user and grant the user create table privilege. However, whenever I log on as that that user and try to create a table the following error message appears.

"no privileges on tablespace 'SYSTEM'"



How can I solve this problem.



Paul

 

[karluk]

There are a few issues that you should address.

First, you apparently created the new user without specifying a default tablespace, so Oracle assigned the "system" tablespace as default. That will work, but it's considered extremely poor practise. The system tablespace is supposed to be reserved for the Oracle catalog. To fix this please run the command, supplying the appropriate values for your userid and the desired default tablespace

ALTER USER username DEFAULT TABLESPACE data_tablespace;

The same issue applies to the default temporary tablespace for your new user. If you don't specify a temporary tablespace, Oracle will assign "system" as the default. You can correct this with the command

ALTER USER username TEMPORARY TABLESPACE temp_tablespace;

Finally there's the quota issue that generated the error. You can fix this with something like the following commands

ALTER USER username QUOTA UNLIMITED ON data_tablespace;
OR
ALTER USER username QUOTA 100 M ON data_tablespace;