1

I'm new to Oracle so please bear with me.

After installing Oracle XE 11.2, I created user SALES and granted create session and create table to the user.

Once I logged in with SALES I tried to create a table and I get the error ORA-01950: no privileges on tablespace 'SYSTEM'.

A related question in DBA StackExchange says that I should alter database default tablespace users;. and then assign a higher quota to user, like so: alter user SALES quota 50m on system;

I've already changed the default tablespace to users.

My question is: shouldn't I alter user SALES quota 50m on users; instead of tablespace system?

1

Yes, users should not write to the system or sysaux tablespaces. If the user is expected to have data or objects and will be copied to development instances they should have their own tablespace as it makes moving them easier.

Change your file path to what you have and execute this as a privileged user

create tablespace sales_tbs datafile '/home/oracle/databases/ora11/sales.dbf' 
 size 10M autoextend on maxsize 200M extent management 
 local uniform size 64K; 
alter user SALES quota 50m on sales_tbs;

and to make sure all future objects go to the dedicated tablespace for sales

 alter user sales default tablespace sales_tbs temporary tablespace temp;
  • Thanks. Is /home/oracle/databases/ora11/sales.dbf a virtual path? I can't seem to find it here in the windows server. – rbhat Jun 13 at 18:35
  • @rbhat I don't know what file paths you have so this is a sample. Change your file path to where the datafiles are. They may be .dbf or .ora. Preferably .dbf so you don't confuse them with configuration files. – kevinsky Jun 13 at 18:38
  • Understood. One last question: once I create the tablespace sales_tbs, do I need to set it as default with alter database default tablespace sales_tbs;? – rbhat Jun 13 at 18:53
  • @rbhat yes that is a good idea and will add to answer! – kevinsky Jun 13 at 18:54
  • Personally, I would think twice about making this new SALES_TBS tablespace the database-wide default. Ultimately, it's the DBA's choice, but you need to consider the use and access. – EdStevens Jun 14 at 17:50
0

It is best practice to create a separate tablespace for every user for better manageability by the DBA.

And non-system objects should never be created in the SYSTEM tablespace.

0

A related question in DBA StackExchange says that I should alter database default tablespace users;. ...

Correct.

That tells Oracle that any segments created by this user should be written by default to the USERS tablespace.

... and then assign a higher quota to user, like so: alter user SALES quota 50m on system;

Wrong.

That will give the user 50M to play about with in the SYSTEM tablespace (Bad Idea). They still won't be able to create anything in their default tablespace (USERS), because they still have no quota there.

It should, as you've realised, be:

alter user SALES quota 50m on users;

Users can store objects in any tablespace where they have a quota, but keeping them fenced into "sensible" options is a really Good Idea.

Also, under 11.2, watch out for the RESOURCE Role.
Granting that to any user has the [unfortunate] side-effect of granting them the UNLIMITED TABLESPACE System Privilege, which means that they can store anything anywhere, quote or not. There doesn't seem to be any [visible] Role/Permission structure in the database that does this; it's as if it's been hard-coded into the 11.2 software. 12 and above don't do this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.