database 对表空间“USERS”没有权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27940522/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
no privileges on tablespace 'USERS'
提问by Ahmet Serdar ?uhadaro?lu
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;
我不是 oracle 的专家，但正如我从错误消息中了解到的；'USERS' 表空间已满，我的用户无权扩展表空间，但其他表'（我可以插入）表空间'是相同的。
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;
采纳答案by Alex Poole
You can get this effect if your user had either the RESOURCEor UNLIMITED TABLESPACErole 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.
如果您的用户在创建表时分配了RESOURCE或UNLIMITED TABLESPACE角色，您就可以获得这种效果；但此后已被撤销，该表现在正试图分配一个新的范围。您的用户没有为表空间明确设置配额；如果有，那么您将看到“ORA-01536：表空间'USERS'的空间配额超出”，即使该配额随后通过将其设置为零而被删除。
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 RESOURCEsince it's deprecated.
大概你的 DBA 一直在做一些特权的内务管理，也许RESOURCE因为它已被弃用而被撤销。
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:
正如评论中提到的，您的 DBA 需要在表空间上授予您一些空间，具有特定大小或（以匹配您之前拥有的）没有限制：
grant quota unlimited on users to myuser;
回答by gvenzl
Your user MYUSERdoesn'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:
您的用户MYUSER没有任何权限将数据插入到 USERS 表空间中。您必须授予用户插入到 USERS 表空间的权限或配额。您可以通过几种不同的方式执行此操作：
You can give the user, e.g.
MYUSERunlimited quota in the USERS tablespace:ALTER USER MYUSER QUOTA UNLIMITED ON USERS;You can also define a space maximum that the user is allowed to allocate on the tablespace:
ALTER USER MYUSER QUOTA 100M ON USERS;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;
您可以给用户，例如
MYUSER在 USERS 表空间中的无限配额：ALTER USER MYUSER QUOTA UNLIMITED ON USERS;您还可以定义允许用户在表空间上分配的空间最大值：
ALTER USER MYUSER QUOTA 100M ON USERS;您还可以授予用户 UNLIMITED TABLESPACE 系统权限，这意味着他对数据库中的任何表空间都有无限配额：
GRANT UNLIMITED TABLESPACE TO MYUSER;
To get more information around resource management for Oracle Database users have a look at the Oracle Database Documentation.
要获取有关 Oracle 数据库用户资源管理的更多信息，请查看Oracle 数据库文档。
