A question about create table

[Wang,MinJie]

Hey every one
When I "sqlplus /nolog" and "conn jay/jay;" and "create table test(name varchar(20))",it said "no privilege of the tablespace".
Why? What can I do.
Thank you in advanced

[Peter Godron]

Hi,
as tablespace owner:
grant create table to jay;

Also please check the default tablespace for your user:
select default_tablespace from dba_users where username = 'JAY';

When you created the user, did you specify the tablespace?

[Wang,MinJie]

No I didn't
I used the default tablespace
Can I change it?

[Peter Godron]

alter user jay default tablespace temp;

[Wang,MinJie]

"temp"?
Should I create this tablespace,use another name and configure it?

[Peter Godron]

Hi,
no need to create sepearet tablespace, it should already exist:
select TABLESPACE_NAME from dba_tablespaces;

Setting the default was only an example, you can not set the default to temp!

Try the grant command first.

[Bill Thorsteinson]

You need the privilege CREATE TABLE
You need a default tablespace on which you have privileges.

Try the following queries

select * from role_sys_privs
/
select * from user_sys_privs
/
select * from user_users
/
select * from user_tablespaces
/

[Indira Aramandla]

Hi Wang,

For a user to be able to create objects (tables, indexes,.......etc) in a tablespace the user needs to have the quota of bytes or unlimited on that tablespace. And the user should also have the privilege to create the object (eg:- create table privilege).

When a user is created, a default tablespace and a temporary tablespace are specified in the creation statement (it could be the USERS tablespace and TEMP tablespace or some other tablespace) assigned to the user. If the user was created without specifying the default and the temporary tablespace then the user will have the SYSTEM tablespace as the default and temporary tablespace.

After the user has been created, the user is granted roles / privileges. If the user is granted the RESOURCE role then the user gets unlimited quota on the default tablespace, if this role is not given then the user needs some quota of bytes on the tablespace to create tables in it.

So in your case it is either you do not have quota on the tablespace or you have the SYSTEM tablespace as your default. You need not create any of these tablespaces, as they already exists (SYSTEM, USERS, TEMP, UNDO/RBS and other tablespaces). Now as the previous posts mentions query the views user_users and user_sys_privs to find out the default tablespace of the user and the privileges of the user and once they are in place then you should be able to create the table in that tablespace.


Indira A

[Wang,MinJie]

Thankyou!
Now I make it clear that I can create table whatever I want!