topic Re: A question about create table in Operating System - HP-UX

A question about create table

Wang,MinJie — Wed, 26 Apr 2006 08:41:34 GMT

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

Re: A question about create table

Peter Godron — Wed, 26 Apr 2006 08:53:30 GMT

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?

Re: A question about create table

Wang,MinJie — Wed, 26 Apr 2006 08:56:00 GMT

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

Re: A question about create table

Peter Godron — Wed, 26 Apr 2006 09:00:09 GMT

alter user jay default tablespace temp;

Re: A question about create table

Wang,MinJie — Wed, 26 Apr 2006 09:10:16 GMT

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

Re: A question about create table

Peter Godron — Wed, 26 Apr 2006 09:13:22 GMT

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.

Re: A question about create table

Bill Thorsteinson — Wed, 26 Apr 2006 09:20:28 GMT

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
/

Re: A question about create table

Indira Aramandla — Wed, 26 Apr 2006 19:50:02 GMT

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

Re: A question about create table

Wang,MinJie — Wed, 26 Apr 2006 22:05:36 GMT

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