Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

A question about create table

Wang,MinJie
Super Advisor

A question about create table

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
8 REPLIES
Peter Godron
Honored Contributor

Re: A question about create table

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
Super Advisor

Re: A question about create table

No I didn't
I used the default tablespace
Can I change it?
Peter Godron
Honored Contributor

Re: A question about create table

alter user jay default tablespace temp;
Wang,MinJie
Super Advisor

Re: A question about create table

"temp"?
Should I create this tablespace,use another name and configure it?
Peter Godron
Honored Contributor

Re: A question about create table

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
Honored Contributor

Re: A question about create table

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
Honored Contributor

Re: A question about create table

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
Never give up, Keep Trying
Wang,MinJie
Super Advisor

Re: A question about create table

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