在表空间"USERS"上没有特权 [英] no privileges on tablespace 'USERS'

问题描述

我有很多可以插入行的表，但是我只对一个表出现此错误；

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.

我不是Oracle方面的专家，但是从错误消息中可以了解到； 用户"表空间已满，我的用户没有扩展表空间的权限，但其他表(我可以插入)表空间是相同的. 这是sql的那个用于可插入表的表和出现错误的表；

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;

没问题；

  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;

获取错误；

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;

推荐答案

如果在创建表时为用户分配了RESOURCE或UNLIMITED TABLESPACE角色，则可以实现此效果.但这已被撤销，并且该表现在正尝试分配新的扩展区.您的用户尚未为表空间明确设置配额；如果有，那么您将看到"ORA-01536:超出表空间'USERS'的空间配额"，即使该配额随后通过将其设置为零而删除了.

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.

要查看效果，

-- 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.

大概您的DBA一直在做一些特权整理工作，也许是因为已弃用RESOURCE撤销了它.

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

如评论中所述，您的DBA需要为您的表空间分配一定的空间，且该空间应具有特定的大小，或者(为了与您之前的空间相匹配)没有限制:

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;

这篇关于在表空间"USERS"上没有特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！