sqlplus - no privileges on tablespace

Stephen Badgett — Fri, 23 Apr 2004 11:50:36 GMT

I create a table space ...

create tablespace DRUG_DRUG_IMAXSOFT datafile '/pcsi/app/oracle/oradata/drug/drug.dbf' size 400M REUSE;

then ...

CREATE TABLE DRUG_DRUG_IMAXSOFT.MASTER (
NDC VARCHAR2( 12) NOT NULL, /* X[12] */
NEW_NDC VARCHAR2( 12) NOT NULL, /* X[12] */
SOUNDEX VARCHAR2( 4) NOT NULL, /* X[4] */
GEN_XREF VARCHAR2( 20) NOT NULL, /* X[20] */
TRADENAME VARCHAR2( 20) NOT NULL, /* X[20] */
STRENGTH VARCHAR2( 8) NOT NULL, /* X[8] */
FORM VARCHAR2( 4) NOT NULL, /* X[4] */
MFG VARCHAR2( 20) NOT NULL, /* X[20] */
THERAPY VARCHAR2( 6) NOT NULL, /* X[6] */
CLASS VARCHAR2( 2) NOT NULL, /* X[2] */
MAINTENANCE VARCHAR2( 2) NOT NULL, /* X[2] */
GENERIC VARCHAR2( 2) NOT NULL, /* X[2] */
SCHEDULE NUMBER( 5) NOT NULL, /* I[1] */
DESI VARCHAR2( 2) NOT NULL, /* X[2] */
PKGSIZE NUMBER( 10) NOT NULL, /* I[2] */
PKGQTY NUMBER( 10) NOT NULL, /* I[2] */
UM VARCHAR2( 2) NOT NULL, /* X[2] */
AWP NUMBER( 10) NOT NULL, /* I[2] */
DP NUMBER( 10) NOT NULL, /* I[2] */
MAC NUMBER( 10) NOT NULL, /* I[2] */
NET NUMBER( 10) NOT NULL, /* I[2] */
AWP_DATE NUMBER( 10) NOT NULL, /* I[2] */
DP_DATE NUMBER( 10) NOT NULL, /* I[2] */
MAC_DATE NUMBER( 10) NOT NULL, /* I[2] */
NET_DATE NUMBER( 10) NOT NULL, /* I[2] */
INTERACT_001 NUMBER( 5) NOT NULL, /* 1->I[1] */
INTERACT_002 NUMBER( 5) NOT NULL, /* 2->I[1] */
INTERACT_003 NUMBER( 5) NOT NULL, /* 3->I[1] */
INTERACT_004 NUMBER( 5) NOT NULL, /* 4->I[1] */
INTERACT_005 NUMBER( 5) NOT NULL, /* 5->I[1] */
INTERACT_006 NUMBER( 5) NOT NULL, /* 6->I[1] */
INTERACT_007 NUMBER( 5) NOT NULL, /* 7->I[1] */
INTERACT_008 NUMBER( 5) NOT NULL, /* 8->I[1] */
INTERACT_009 NUMBER( 5) NOT NULL, /* 9->I[1] */
INTERACT_010 NUMBER( 5) NOT NULL, /* 10->I[1] */
INTERACT_011 NUMBER( 5) NOT NULL, /* 11->I[1] */
INTERACT_012 NUMBER( 5) NOT NULL, /* 12->I[1] */
INTERACT_013 NUMBER( 5) NOT NULL, /* 13->I[1] */
INTERACT_014 NUMBER( 5) NOT NULL, /* 14->I[1] */
INTERACT_015 NUMBER( 5) NOT NULL, /* 15->I[1] */
INTERACT_016 NUMBER( 5) NOT NULL, /* 16->I[1] */
INTERACT_017 NUMBER( 5) NOT NULL, /* 17->I[1] */
INTERACT_018 NUMBER( 5) NOT NULL, /* 18->I[1] */
INTERACT_019 NUMBER( 5) NOT NULL, /* 19->I[1] */
INTERACT_020 NUMBER( 5) NOT NULL, /* 20->I[1] */
INTERACT_021 NUMBER( 5) NOT NULL, /* 21->I[1] */
INTERACT_022 NUMBER( 5) NOT NULL, /* 22->I[1] */
INTERACT_023 NUMBER( 5) NOT NULL, /* 23->I[1] */
INTERACT_024 NUMBER( 5) NOT NULL, /* 24->I[1] */
INTERACT_025 NUMBER( 5) NOT NULL, /* 25->I[1] */
INTERACT_026 NUMBER( 5) NOT NULL, /* 26->I[1] */
INTERACT_027 NUMBER( 5) NOT NULL, /* 27->I[1] */
INTERACT_028 NUMBER( 5) NOT NULL, /* 28->I[1] */
INTERACT_029 NUMBER( 5) NOT NULL, /* 29->I[1] */
INTERACT_030 NUMBER( 5) NOT NULL, /* 30->I[1] */
INTERACT_031 NUMBER( 5) NOT NULL, /* 31->I[1] */
INTERACT_032 NUMBER( 5) NOT NULL, /* 32->I[1] */
INTERACT_033 NUMBER( 5) NOT NULL, /* 33->I[1] */
INTERACT_034 NUMBER( 5) NOT NULL, /* 34->I[1] */
INTERACT_035 NUMBER( 5) NOT NULL, /* 35->I[1] */
INTERACT_036 NUMBER( 5) NOT NULL, /* 36->I[1] */
INTERACT_037 NUMBER( 5) NOT NULL, /* 37->I[1] */
INTERACT_038 NUMBER( 5) NOT NULL, /* 38->I[1] */
INTERACT_039 NUMBER( 5) NOT NULL, /* 39->I[1] */
INTERACT_040 NUMBER( 5) NOT NULL, /* 40->I[1] */
ALLERCODE_001 NUMBER( 5) NOT NULL, /* 1->I[1] */
ALLERCODE_002 NUMBER( 5) NOT NULL, /* 2->I[1] */
ALLERCODE_003 NUMBER( 5) NOT NULL, /* 3->I[1] */
ALLERCODE_004 NUMBER( 5) NOT NULL, /* 4->I[1] */
ALLERCODE_005 NUMBER( 5) NOT NULL, /* 5->I[1] */
ALLERCODE_006 NUMBER( 5) NOT NULL, /* 6->I[1] */
ALLERCODE_007 NUMBER( 5) NOT NULL, /* 7->I[1] */
ALLERCODE_008 NUMBER( 5) NOT NULL, /* 8->I[1] */
ALLERCODE_009 NUMBER( 5) NOT NULL, /* 9->I[1] */
ALLERCODE_010 NUMBER( 5) NOT NULL, /* 10->I[1] */
PHARMEX_001 NUMBER( 5) NOT NULL, /* 1->I[1] */
PHARMEX_002 NUMBER( 5) NOT NULL, /* 2->I[1] */
PHARMEX_003 NUMBER( 5) NOT NULL, /* 3->I[1] */
PHARMEX_004 NUMBER( 5) NOT NULL, /* 4->I[1] */
PHARMEX_005 NUMBER( 5) NOT NULL, /* 5->I[1] */
PHARMEX_006 NUMBER( 5) NOT NULL, /* 6->I[1] */
PHARMEX_007 NUMBER( 5) NOT NULL, /* 7->I[1] */
PHARMEX_008 NUMBER( 5) NOT NULL, /* 8->I[1] */
PHARMEX_009 NUMBER( 5) NOT NULL, /* 9->I[1] */
PHARMEX_010 NUMBER( 5) NOT NULL, /* 10->I[1] */
CONSULT NUMBER( 5) NOT NULL, /* I[1] */
MSGNO VARCHAR2( 8) NOT NULL, /* X[8] */
ST_FLAGS_001 VARCHAR2( 2) NOT NULL, /* 1->X[2] */
ST_FLAGS_002 VARCHAR2( 2) NOT NULL, /* 2->X[2] */
ST_FLAGS_003 VARCHAR2( 2) NOT NULL, /* 3->X[2] */
ST_FLAGS_004 VARCHAR2( 2) NOT NULL, /* 4->X[2] */
ST_FLAGS_005 VARCHAR2( 2) NOT NULL, /* 5->X[2] */
ST_FLAGS_006 VARCHAR2( 2) NOT NULL, /* 6->X[2] */
ST_FLAGS_007 VARCHAR2( 2) NOT NULL, /* 7->X[2] */
ST_FLAGS_008 VARCHAR2( 2) NOT NULL, /* 8->X[2] */
ST_FLAGS_009 VARCHAR2( 2) NOT NULL, /* 9->X[2] */
ST_FLAGS_010 VARCHAR2( 2) NOT NULL, /* 10->X[2] */
ST_FLAGS_011 VARCHAR2( 2) NOT NULL, /* 11->X[2] */
ST_FLAGS_012 VARCHAR2( 2) NOT NULL, /* 12->X[2] */
ST_FLAGS_013 VARCHAR2( 2) NOT NULL, /* 13->X[2] */
ST_FLAGS_014 VARCHAR2( 2) NOT NULL, /* 14->X[2] */
ST_FLAGS_015 VARCHAR2( 2) NOT NULL, /* 15->X[2] */
ST_DRUGCODE VARCHAR2( 12) NOT NULL, /* X[12] */
ST_NDC VARCHAR2( 12) NOT NULL, /* X[12] */
STATUS VARCHAR2( 2) NOT NULL, /* X[2] */
GCN_SEQ NUMBER( 10) NOT NULL, /* I[2] */
PEMCODE VARCHAR2( 6) NOT NULL, /* X[6] */
ALTDESC VARCHAR2( 28) NOT NULL, /* X[28] */
DRUGBUF_001 VARCHAR2( 2) NOT NULL, /* 1->X[2] */
DRUGBUF_002 VARCHAR2( 2) NOT NULL, /* 2->X[2] */
DRUGBUF_003 VARCHAR2( 2) NOT NULL, /* 3->X[2] */
DRUGBUF_004 VARCHAR2( 2) NOT NULL, /* 4->X[2] */
DRUGBUF_005 VARCHAR2( 2) NOT NULL, /* 5->X[2] */
DRUGBUF_006 VARCHAR2( 2) NOT NULL, /* 6->X[2] */
DRUGBUF_007 VARCHAR2( 2) NOT NULL, /* 7->X[2] */
DRUGBUF_008 VARCHAR2( 2) NOT NULL, /* 8->X[2] */
DRUGBUF_009 VARCHAR2( 2) NOT NULL, /* 9->X[2] */
DRUGBUF_010 VARCHAR2( 2) NOT NULL, /* 10->X[2] */
DRUGBUF_011 VARCHAR2( 2) NOT NULL, /* 11->X[2] */
DRUGBUF_012 VARCHAR2( 2) NOT NULL, /* 12->X[2] */
DRUGBUF_013 VARCHAR2( 2) NOT NULL, /* 13->X[2] */
DRUGBUF_014 VARCHAR2( 2) NOT NULL, /* 14->X[2] */
DRUGBUF_015 VARCHAR2( 2) NOT NULL, /* 15->X[2] */
DRUGBUF_016 VARCHAR2( 2) NOT NULL, /* 16->X[2] */
DRUGBUF_017 VARCHAR2( 2) NOT NULL, /* 17->X[2] */
DRUGBUF_018 VARCHAR2( 2) NOT NULL, /* 18->X[2] */
DRUGBUF_019 VARCHAR2( 2) NOT NULL, /* 19->X[2] */
DRUGBUF_020 VARCHAR2( 2) NOT NULL, /* 20->X[2] */
DRUGBUF_021 VARCHAR2( 2) NOT NULL, /* 21->X[2] */
DRUGBUF_022 VARCHAR2( 2) NOT NULL, /* 22->X[2] */
DRUGBUF_023 VARCHAR2( 2) NOT NULL, /* 23->X[2] */
DRUGBUF_024 VARCHAR2( 2) NOT NULL, /* 24->X[2] */
DRUGBUF_025 VARCHAR2( 2) NOT NULL, /* 25->X[2] */
DRUGBUF_026 VARCHAR2( 2) NOT NULL, /* 26->X[2] */
DRUGBUF_027 VARCHAR2( 2) NOT NULL, /* 27->X[2] */
DRUGBUF_028 VARCHAR2( 2) NOT NULL, /* 28->X[2] */
DRUGBUF_029 VARCHAR2( 2) NOT NULL, /* 29->X[2] */
DRUGBUF_030 VARCHAR2( 2) NOT NULL, /* 30->X[2] */
DRUGBUF_031 VARCHAR2( 2) NOT NULL, /* 31->X[2] */
DRUGBUF_032 VARCHAR2( 2) NOT NULL, /* 32->X[2] */
DRUGBUF_033 VARCHAR2( 2) NOT NULL, /* 33->X[2] */
DRUGBUF_034 VARCHAR2( 2) NOT NULL, /* 34->X[2] */
DRUGBUF_035 VARCHAR2( 2) NOT NULL, /* 35->X[2] */
DRUGBUF_036 VARCHAR2( 2) NOT NULL, /* 36->X[2] */
DRUGBUF_037 VARCHAR2( 2) NOT NULL, /* 37->X[2] */
DRUGBUF_038 VARCHAR2( 2) NOT NULL, /* 38->X[2] */
DRUGBUF_039 VARCHAR2( 2) NOT NULL, /* 39->X[2] */
DRUGBUF_040 VARCHAR2( 2) NOT NULL, /* 40->X[2] */
DRUGBUF_041 VARCHAR2( 2) NOT NULL, /* 41->X[2] */
DRUGBUF_042 VARCHAR2( 2) NOT NULL, /* 42->X[2] */
DRUGBUF_043 VARCHAR2( 2) NOT NULL, /* 43->X[2] */
DRUGBUF_044 VARCHAR2( 2) NOT NULL, /* 44->X[2] */
DRUGBUF_045 VARCHAR2( 2) NOT NULL, /* 45->X[2] */
DRUGBUF_046 VARCHAR2( 2) NOT NULL, /* 46->X[2] */
DRUGBUF_047 VARCHAR2( 2) NOT NULL, /* 47->X[2] */
DRUGBUF_048 VARCHAR2( 2) NOT NULL, /* 48->X[2] */
DRUGBUF_049 VARCHAR2( 2) NOT NULL, /* 49->X[2] */
DRUGBUF_050 VARCHAR2( 2) NOT NULL, /* 50->X[2] */
DRUGBUF_051 VARCHAR2( 2) NOT NULL, /* 51->X[2] */
DRUGBUF_052 VARCHAR2( 2) NOT NULL, /* 52->X[2] */
DRUGBUF_053 VARCHAR2( 2) NOT NULL, /* 53->X[2] */
DRUGBUF_054 VARCHAR2( 2) NOT NULL, /* 54->X[2] */
DRUGBUF_055 VARCHAR2( 2) NOT NULL, /* 55->X[2] */
DRUGBUF_056 VARCHAR2( 2) NOT NULL, /* 56->X[2] */
DRUGBUF_057 VARCHAR2( 2) NOT NULL, /* 57->X[2] */
DRUGBUF_058 VARCHAR2( 2) NOT NULL, /* 58->X[2] */
DRUGBUF_059 VARCHAR2( 2) NOT NULL, /* 59->X[2] */
DRUGBUF_060 VARCHAR2( 2) NOT NULL, /* 60->X[2] */
DRUGBUF_061 VARCHAR2( 2) NOT NULL, /* 61->X[2] */
DRUGBUF_062 VARCHAR2( 2) NOT NULL, /* 62->X[2] */
DRUGBUF_063 VARCHAR2( 2) NOT NULL, /* 63->X[2] */
DRUGBUF_064 VARCHAR2( 2) NOT NULL, /* 64->X[2] */
DRUGBUF_065 VARCHAR2( 2) NOT NULL, /* 65->X[2] */
IMAXSOFT13_SEQ_NO NUMBER( 20) NOT NULL
);

then this message has comes up ...

CREATE TABLE DRUG_DRUG_IMAXSOFT.MASTER (
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

I do not know what I did wrong and I do not know what I should do next.

Steve

Re: sqlplus - no privileges on tablespace

Jeff_Traigle — Fri, 23 Apr 2004 12:55:48 GMT

What user are you logged in as?

It looks like you're logeed in as a user that has the DEFAULT TABLESPACE set to SYSTEM (not a good thing, in general, anyway), but does not have CREATE TABLE privileges granted on that tablespace. If you want the table to be placed in the tablespace you just created, you need to add the TABLESPACE clause to your CREATE TABLE command so it knows that. You (or SYSTEM, if that's not you) will probably also need to GRANT appropriate permissions and set QUOTA so the table can be created in that tablespace as well as anything else you might need.

Re: sqlplus - no privileges on tablespace

Jim Butler — Sat, 24 Apr 2004 09:07:14 GMT

Jeff - I agree -
Steve - you can use the alter user command set to set the user's tablespace to the newly created one, and then log back in as that user, and create the data file.

Its been a couple of years, but if I remember - its trivial.

Re: sqlplus - no privileges on tablespace

Hein van den Heuvel — Sat, 24 Apr 2004 09:56:28 GMT

>> CREATE TABLE DRUG_DRUG_IMAXSOFT.MASTER

Steve,

There seems to be a confusion between 'schema' and tablespace. The sntax you used suggest a schema called DRUG_DRUG_IMAXSOFT in the DB.

Like the other replies suggest, you must use 'alter user' to change the default tablespace if you want all or many future objects to be created in DRUG_DRUG_IMAXSOFT. Or you can explicitly call for a certain tablespace on the 'create table'.

I would recommend testing with a trivial table. Upon success delete the test table and create the real McCoy. Something like:

CREATE TABLE TEST (( employee_id NUMBER(6)
, first_name VARCHAR2(20)
, last_name VARCHAR2(25) ) TABLESPACE DRUG_DRUG_IMAXSOFT;
DROP TABLE TEST;

Check the (online) DOCumentation: SQL ref man.

hth,
Hein.

Re: sqlplus - no privileges on tablespace

Yogeeraj_1 — Sun, 25 Apr 2004 23:16:20 GMT

hello,

This is occuring because this user has been created with default tablespace = SYSTEM!

So, either you create the table by specifying the tablespace

e.g.

CREATE TABLE DRUG_DRUG_IMAXSOFT.MASTER (
NDC VARCHAR2( 12) NOT NULL, /* X[12] */
NEW_NDC VARCHAR2( 12) NOT NULL, /* X[12] */
SOUNDEX VARCHAR2( 4) NOT NULL, /* X[4] */
...
DRUGBUF_065 VARCHAR2( 2) NOT NULL, /* 65->X[2] */
IMAXSOFT13_SEQ_NO NUMBER( 20) NOT NULL
)
Tablespace DRUG_DRUG_IMAXSOFT;

or, do a "alter user default tablespacce DRUG_DRUG_IMAXSOFT;" then create the table.

hope this helps too!

regards
Yogeeraj

Re: sqlplus - no privileges on tablespace

T G Manikandan — Sun, 25 Apr 2004 23:22:49 GMT

By default users do not privilege to create tables on the SYSTEM tablespace.The system tablespace is used to store the database related information.

check what is the default tablespace for the user

sql>select username,default_tablespace,temporary_tablespace from dba_users;

change the default tablespace of the user

sql>alter user identified by default tablespace DRUG_DRUG_IMAXSOFT temporary tablespace temp;

topic Re: sqlplus - no privileges on tablespace in Operating System - HP-UX

sqlplus - no privileges on tablespace

Re: sqlplus - no privileges on tablespace

Re: sqlplus - no privileges on tablespace

Re: sqlplus - no privileges on tablespace

Re: sqlplus - no privileges on tablespace

Re: sqlplus - no privileges on tablespace