All geek questions in one place

No privileges in the USERS tablespace

I have many tables in which I can insert rows, but I get this error for only 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 an expert in oracle, but as I understood from the error message; The USERS table space is full, and my user does not have permission to expand the table space, but the other tables (which I can insert) the table spaces are the same. here is sql that for the inserted table and the table that gets the 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 errors 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;
+5
source share
2 answers

You can get this effect if your user had the RESOURCE or UNLIMITED TABLESPACE role assigned at the time the tables were created; but since then it has been recalled, and now the table is trying to highlight a new degree. Your user has not explicitly set a quota for the table space; if it were then, you would see that "ORA-01536: the user space table space quota has been exceeded, even if the quota was subsequently deleted 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 the data:

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

But if I insert enough rows to require a second degree allocation, 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 database administrator is doing some housekeeping, possibly overriding RESOURCE as it is deprecated.

As mentioned in the comments, your database administrator must provide you some space in the table space with a specific size or (to match what you had before) without restrictions:

 grant quota unlimited on users to myuser;
+1
source

User MYUSER does not have any privileges to insert data into the USERS tablespace. You must grant the user the right or quota to insert into the USERS tablespace. You can do this in several ways:

  • You can tell the user, for example. MYUSER unlimited quota in the USERS tablespace:

     ALTER USER MYUSER QUOTA UNLIMITED ON USERS;

  • You can also define the maximum space that the user can highlight in the table space:

     ALTER USER MYUSER QUOTA 100M ON USERS;

  • You can also grant the user the UNLIMITED TABLESPACE privilege, which means that he has an unlimited quota in any table space in the database:

     GRANT UNLIMITED TABLESPACE TO MYUSER;

For more information on resource management for Oracle Database users, see the Oracle Database Documentation .

+6
source

Source: https://habr.com/ru/post/1211065/

More articles:


All Articles