SQL 错误:ORA-00942 表或视图不存在 [英] SQL Error: ORA-00942 table or view does not exist

问题描述

我使用 SQL 开发人员并与系统用户建立了到我的数据库的连接，之后我创建了一个用户并与该用户建立了另一个连接并具有所有所需的权限.

I use SQL developer and i made a connection to my database with the system user, after I created a user and made a another connection with that user with all needed privileges.

但是当我尝试继续执行以下操作时，出现 SQL 错误

But when I try to proceed following I get the SQL Error

ORA-00942 表或视图不存在.:

ORA-00942 table or view does not exist.:

<小时>

INSERT INTO customer (c_id,name,surname) VALUES ('1','Micheal','Jackson')

推荐答案

因为这个帖子是stackoverflow上搜索ORA-00942:表或视图不存在插入"时找到的最上面的帖子，所以我想提一个可能的此错误的原因(至少在 Oracle 12c 中):表使用序列来设置默认值，并且执行插入查询的用户没有对该序列的选择权限.这是我的问题，我花了很长时间才弄明白.

Because this post is the top one found on stackoverflow when searching for "ORA-00942: table or view does not exist insert", I want to mention another possible cause of this error (at least in Oracle 12c): a table uses a sequence to set a default value and the user executing the insert query does not have select privilege on the sequence. This was my problem and it took me an unnecessarily long time to figure it out.

要重现该问题，请以 user1 身份执行以下 SQL:

To reproduce the problem, execute the following SQL as user1:

create sequence seq_customer_id;

create table customer (
c_id number(10) default seq_customer_id.nextval primary key,
name varchar(100) not null,
surname varchar(100) not null
);

grant select, insert, update, delete on customer to user2;

然后，以user2的身份执行这个插入语句:

Then, execute this insert statement as user2:

insert into user1.customer (name,surname) values ('michael','jackson');

结果将是ORA-00942:表或视图不存在"，即使 user2 确实对 user1.customer 表具有插入和选择权限并且是正确的使用架构所有者名称为表添加前缀.为避免该问题，您必须授予对序列的选择权限:

The result will be "ORA-00942: table or view does not exist" even though user2 does have insert and select privileges on user1.customer table and is correctly prefixing the table with the schema owner name. To avoid the problem, you must grant select privilege on the sequence:

grant select on seq_customer_id to user2;

这篇关于SQL 错误:ORA-00942 表或视图不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！