No privileges on tablespace error when trying to create table

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
ipamanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paquicubaCommented:
grant quota to user "<your_user>" on tablespace 'USERS'.
0
ipamanAuthor Commented:
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
mijiaCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

paquicubaCommented:
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
ipamanAuthor Commented:
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
paquicubaCommented:
Did you try:
Grant RESOURCE to IRDUSER;
0
paquicubaCommented:
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
ipamanAuthor Commented:
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
ipamanAuthor Commented:
paquicuba,

Did you try:
Grant RESOURCE to IRDUSER;

Yes, as SYS.
0
paquicubaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ipamanAuthor Commented:
paquicuba,

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

ipaman
0
paquicubaCommented:
Good to hear!

Paqui
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.