oracle 用户表中发生的“此视图上的数据操作操作不合法”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7444956/
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
"data manipulation operation not legal on this view" happening in a user table
提问by Thiago Melo
This error is happening when I'm trying to insert values on a table belong to the user which I'm using:
当我尝试在属于我正在使用的用户的表上插入值时，会发生此错误：
SQL Error: ORA-01732: data manipulation operation not legal on this view
*Cause:
*Action:
In all the places the people say that it occurs when trying to insert/update/delete on a view, but it is certainly a table, and the user has the privileges needed.
在所有地方，人们都说在视图上尝试插入/更新/删除时会发生这种情况，但它肯定是一个表，并且用户具有所需的权限。
So, there is any other cause for this happening beyond trying to do ilegal operation with a view?
那么，除了试图以一种观点进行非法操作之外，还有其他原因会导致这种情况发生吗？
采纳答案by Dave Costa
I would activate SQL tracing and try the insert again, then look at the trace file to determine exactly what SQL statement is causing the error.
我会激活 SQL 跟踪并再次尝试插入，然后查看跟踪文件以确定究竟是什么 SQL 语句导致了错误。
If, as suggested by others, it is SQL executed by a trigger against some other object, you'll see that in the trace as recursive SQL and it will be clear what object is involved.
如果正如其他人所建议的那样，它是由触发器针对某个其他对象执行的 SQL，您将在跟踪中看到它是递归 SQL，并且很清楚所涉及的对象。
If there is no recursive SQL in the trace, then it is likely that you are not accessing the object you think you are, and the trace should show the actual object ID being used, which you can check against the data dictionary.
如果跟踪中没有递归 SQL，那么很可能您没有访问您认为的对象，并且跟踪应该显示正在使用的实际对象 ID，您可以对照数据字典进行检查。
If it really is just a table, and there is no recursive SQL involved, then it sounds like you have a case for Oracle support.
如果它真的只是一个表，并且不涉及递归 SQL，那么听起来您有 Oracle 支持的案例。
回答by Jeff
An alternative answer: I came across this error when I tried to INSERT a record into a materialized view table.
另一个答案：当我尝试将记录插入物化视图表时遇到了这个错误。
回答by APC
First thing: are you reallysure it is a table? From the way you have framed the question it might be that the user experiencing this problem doesn't own the table. In which case, perhaps there's a synonym or something which is really pointing at a view.
第一件事：你真的确定这是一张桌子吗？从您提出问题的方式来看，遇到此问题的用户可能并不拥有该表。在这种情况下，也许有一个同义词或一些真正指向视图的东西。
Second thing it might be, is if there's a trigger on the table and it is the action it fires causing the problem.
第二件事可能是，如果桌子上有一个触发器，并且是它触发的操作导致了问题。
回答by user5554384
My guess is that you are most likely looking at a materialized view and not a table. Search for
我的猜测是，您很可能正在查看物化视图而不是表。搜索
Select from user_objects
in the dictionary to see the object type.
在字典中查看对象类型。
回答by dayneo
I know this is a super old post, but FYI, the "table" is actually a materialized view (mview). The mview is not configured to allow for updates and you therefore cannot insert/update/delete on it.
我知道这是一个超级旧的帖子，但仅供参考，“表”实际上是一个物化视图（mview）。mview 未配置为允许更新，因此您无法在其上插入/更新/删除。
You can get a list of all of your materialized views by running:
您可以通过运行以下命令获取所有物化视图的列表：
select * from user_mviews
回答by Randy
one more thought...
还有一个想法……
try it with an explicit identification on the table like this:
尝试在桌子上使用明确的标识，如下所示：
insert into myschema.mytable () values ()...
just in case there is another one locally..
以防万一本地还有另一个..
回答by manix
Another case to issue this error is when you are trying to update data using a column that does not exists:
发出此错误的另一种情况是当您尝试使用不存在的列更新数据时：
update house set color = 'blue' where street = 'ABC';
streetcolumn does not exists in tablehouse
street表中不存在列house
回答by SimpleMindedTech
Since you can not perform deletes on materialized views, you will need to delete the data from the table that is pushed that updates the materialized views, then refresh those tables.
由于您无法对实体化视图执行删除操作，因此您需要从更新实体化视图的推送表中删除数据，然后刷新这些表。
example: I want to delete data from Table 1 and Table 2 which are Materialized views. Currently, the data for these 2 tables are pushed from table 3. I will need to delete the data from table 3, then refresh the view for tables 1 and 2.
示例：我想从表 1 和表 2 中删除数据，它们是物化视图。目前，这两个表的数据是从表 3 中推送的。我需要从表 3 中删除数据，然后刷新表 1 和表 2 的视图。
This data should no longer be present in tables 1, 2 and 3 after a refresh of the views.
刷新视图后，这些数据不应再出现在表 1、2 和 3 中。
Hope this helps in 2020!
希望这对 2020 年有所帮助！
