14

i have many tables that i can insert rows, but i get this error only for one table;

Error starting at line 1 in command: INSERT INTO ERRORLOG (MESSAGE) VALUES ('test') Error report: SQL Error: ORA-01950: no privileges on tablespace 'USERS' 01950. 00000 - "no privileges on tablespace '%s'" *Cause: User does not have privileges to allocate an extent in the specified tablespace. *Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

i am not expert on oracle but as i understood from the error message; 'USERS' tablespace is full and my user is not have permission to extend the tablespace but other tables' (that i can insert) tablespaces' are same.. here are sql's that one for insertable table and the table that getting error;

no problem for;

 CREATE TABLE "MYUSER"."HEADSHIP" ( "ID" NUMBER NOT NULL ENABLE, "DESCRIPTION" VARCHAR2(255 BYTE), "ISDELETED" VARCHAR2(1 BYTE) DEFAULT 0 NOT NULL ENABLE, CONSTRAINT "HEADSHIP_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "HEADSHIP_UI" UNIQUE ("DESCRIPTION") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE OR REPLACE TRIGGER "MYUSER"."HEADSHIP_TRG" BEFORE INSERT ON HEADSHIP FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF :NEW.ID IS NULL THEN SELECT HEADSHIP_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL; END IF; END COLUMN_SEQUENCES; END; / ALTER TRIGGER "MYUSER"."HEADSHIP_TRG" ENABLE;

getting error for;

CREATE TABLE "MYUSER"."ERRORLOG" ( "ID" NUMBER NOT NULL ENABLE, "MESSAGE" VARCHAR2(2048 BYTE), "STACKTRACE" VARCHAR2(2048 BYTE), "XDATE" DATE, "USERLDAPNAME" VARCHAR2(127 BYTE), "QUERY" VARCHAR2(2048 BYTE), CONSTRAINT "ERRORLOG_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE OR REPLACE TRIGGER "MYUSER"."ERRORLOG_TRG" BEFORE INSERT ON ERRORLOG FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> BEGIN IF :NEW.ID IS NULL THEN SELECT ERRORLOG_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL; END IF; END COLUMN_SEQUENCES; END; / ALTER TRIGGER "MYUSER"."ERRORLOG_TRG" ENABLE;
Improve this question
5
  • 2
    The tablespace is not full. The user doesn't have the rights to use it. And the error message also tells you what to do: "Action: ... grant the user space resource on the tablespace" Commented Jan 14, 2015 at 10:28
  • It says, you don't have permission for that operation, and should grant appropriate system privileges. Commented Jan 14, 2015 at 10:28
  • as i said, there are a lot of table that i can insert with same tablespace. if the user doesn't have the rights to use, how it can insert the other tables ? Commented Jan 14, 2015 at 11:05
  • I am connecting with oracle sql developer with MYUSER. I can insert other tables but ERRORLOG in same query editor window. So I am trying to insert with same user, don't i? Commented Jan 14, 2015 at 11:30
  • Yes, you do and as @AlexPoole pointed out below it is actually irrelevant anyway. Commented Jan 14, 2015 at 11:46

2 Answers 2

Reset to default
14

Your user MYUSER doesn't have any privileges to insert data into the USERS tablespace. You have to give the user the right or quota to insert into the USERS tablespace. You can do this in several different ways:

  1. You can give the user, e.g. MYUSER unlimited quota in the USERS tablespace:

    ALTER USER MYUSER QUOTA UNLIMITED ON USERS;

  2. You can also define a space maximum that the user is allowed to allocate on the tablespace:

    ALTER USER MYUSER QUOTA 100M ON USERS;

  3. You can also give the user the UNLIMITED TABLESPACE system privilege which means he has unlimited quota on any tablespace within the database:

    GRANT UNLIMITED TABLESPACE TO MYUSER;

To get more information around resource management for Oracle Database users have a look at the Oracle Database Documentation.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Tablespace (segment) usage and quotas apply to the object owner, not whoever is inserting. The resource limits you linked to are not for storage.
if the user (MYUSER) doesn't have any privileges to insert data into the USERS tablespace; then how it can insert the other tables (with same tablespace) ?
You are right @AlexPoole. Just tested it and it does indeed apply to the object owner not the user, my mistake. However, the link I've provided does also contain information on tablespace quotas under Creating User Accounts -> Tablespace Quotas for a User
See this and this... The owner already appears to have a quota assigned as the table was created (with immediate initial extent), and inserts to other tables apparently work.
5

You can get this effect if your user had either the RESOURCE or UNLIMITED TABLESPACE role assigned at the point the tables were created; but that has since been revoked, and the table is now trying to allocate a new extent. Your user has not had a quota explicitly set for the tablespace; if it had then you'd be seeing "ORA-01536: space quota exceeded for tablespace 'USERS'" instead, even if the quota had subsequently been removed by setting it to zero.

To see the effect:

-- grant unlimited tablespace to user; create table t42 (id number) tablespace users; Table t42 created. insert into t42 select level as id from dual connect by level < 1000; 1,999 rows inserted. select extents from user_segments where segment_name = 'T42'; EXTENTS ---------- 1 -- revoke unlimited tablespace from user;

At this point I can still insert data:

insert into t42 values (2000); 1 rows inserted.

But if I insert enough rows to require a second extent to be allocated, it fails with this error:

insert into t42 select level + 2000 as id from dual connect by level < 2000; Error report - SQL Error: ORA-01950: no privileges on tablespace 'USERS' 01950. 00000 - "no privileges on tablespace '%s'" *Cause: User does not have privileges to allocate an extent in the specified tablespace. *Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

Presumably your DBA has been doing some housekeeping of privileges, perhaps revoking RESOURCE since it's deprecated.

As mentioned in comments, your DBA needs to grant you some space on the tablespace, with a specific size or (to match what you had before) no limit:

grant quota unlimited on users to myuser;
Improve this answer

4 Comments

i thought that case. but i have tried to insert one row to a table that contains more columns than table that giving error. it was succeeded. now i tried to insert many rows to a table i got this error. seems to you re right Alex, congratulation and thank you very much.
@AhmetSerdarÇuhadaroğlu - you'll only get the error when the table you're inserting into needs a new extent, which can be from a single-row insert. Inserting into another table which has free space in existing extents is still OK. And the number of columns is irrelevant.
syntax error: grant quota unlimited on Tablespace_Nmae to User_Name * ERROR at line 1: ORA-00990: missing or invalid privilege
@dave - you have to issue that command as a privileged user, e.g. SYS.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.