[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

No privileges on tablespace error when trying to create table

Posted on 2004-11-03
12
Medium Priority
?
14,005 Views
Last Modified: 2012-08-14
I have Oracle9.2.x Enterprise installed on my computer. I have created the database and have created a user with certain admin privileges. When I try to create a table in sqlplus, I get the following error:

ORA-01950: no privileges on tablespace 'USERS'

How can I grant privileges to the USERS tablespace OR how can I create a new tablespace and grant the privileges necessary to allow my user that I created to create tables?

ipaman
0
Comment
Question by:ipaman
  • 6
  • 5
12 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 12485561
grant quota to user "<your_user>" on tablespace 'USERS'.
0
 

Author Comment

by:ipaman
ID: 12485750
I get:

SQL> grant quota to user "IRDUSER" on tablespace 'USERS';
grant quota to user "IRDUSER" on tablespace 'USERS'
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

ipaman
0
 
LVL 1

Expert Comment

by:mijia
ID: 12486065
what role that user belong to? you may try to grant create table to user, or

you can create tablespace by

create tablespace ts_something
  logging
  datafile '/dbf1/ts_sth.dbf' size 32m
  autoextend on next 32m maxsize 2048m
  extent management local;

and then create table
CREATE TABLE tablename
(
bula, bula
)
TABLESPACE ts_something
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCACHE
NOPARALLEL;
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

by:paquicuba
ID: 12486138
ORA-00990 missing or invalid privilege

Cause: No privileges were specified in a GRANT privilege statement, or one of the specified privileges is invalid.

Action: Enter one or more valid privileges such as SELECT, INSERT, DELETE, UPDATE, ALTER, INDEX, REFERENCES, or ALL. More than one privilege may be granted by entering the privileges in a list separated by commas (,) or by specifying the keyword ALL to grant all privileges.

Do this:
Grant RESOURCE to IRDUSER;

Grantees of the RESOURCE role also receive the UNLIMITED TABLESPACE system privilege as an explicitly grant (not as part of the RESOURCE role).

0
 

Author Comment

by:ipaman
ID: 12486177
I created a IRDUSERROLE role and gave it all the create/drop/etc privileges. Then I assigned IRDUSER to the role of IRDUSERROLE. So IRDUSER has all the privileges it needs. The problem seems like the tablespace needs to allow IRDUSER to act upon it.

Yes/No/Maybe....?

ipaman
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 12486398
Did you try:
Grant RESOURCE to IRDUSER;
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 12486438
CREATE TABLE  
Create tables in own schema. Also allows grantee to create indexes (including those for integrity constraints) on table in own schema. (The grantee must have a quota for the tablespace or the UNLIMITED TABLESPACE privilege.)  
 
That's why you need to do this:
Grant RESOURCE to IRDUSER;

--Grantees of the RESOURCE role also receive the UNLIMITED TABLESPACE system privilege as an explicitly grant (not as part ---of the RESOURCE role).

0
 

Author Comment

by:ipaman
ID: 12486478
Now when I try to create a table I get the following:

create table......
*

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

Like I mentioned above, it seems like the tablespace needs to allow IRDUSER to act upon it.

Ideas?

ipaman
0
 

Author Comment

by:ipaman
ID: 12486927
paquicuba,

Did you try:
Grant RESOURCE to IRDUSER;

Yes, as SYS.
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 200 total points
ID: 12487673
Try:
alter user IRDUSER quota unlimited on tablespace users;

or recreate the user

CREATE USER IRDUSER IDENTIFIED BY <PASSWORD>
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON USERS

0
 

Author Comment

by:ipaman
ID: 12488357
paquicuba,

the alter statement still did not work, so I recreated the user your way.

ipaman
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 12488438
Good to hear!

Paqui
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question