ipaman
asked on
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
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
paquicuba
grant quota to user "<your_user>" on tablespace 'USERS'.
ASKER
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
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
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;
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;
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).
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).
ASKER
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
Yes/No/Maybe....?
ipaman
Did you try:
Grant RESOURCE to IRDUSER;
Grant RESOURCE to IRDUSER;
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).
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).
ASKER
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
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
ASKER
paquicuba,
Did you try:
Grant RESOURCE to IRDUSER;
Yes, as SYS.
Did you try:
Grant RESOURCE to IRDUSER;
Yes, as SYS.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
paquicuba,
the alter statement still did not work, so I recreated the user your way.
ipaman
the alter statement still did not work, so I recreated the user your way.
ipaman
Good to hear!
Paqui
Paqui