CODEWITHSUNDEEP
oracle-database
Thiago Melo
Thiago Melo

Reputation: 1187

"data manipulation operation not legal on this view" happening in a user table

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?

Upvotes: 9

Views: 56674

Answers (8)

user5554384
user5554384

Reputation: 21

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.

Upvotes: 2

SimpleMindedTech
SimpleMindedTech

Reputation: 1

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.

This data should no longer be present in tables 1, 2 and 3 after a refresh of the views.

Hope this helps in 2020!

Upvotes: 0

manix
manix

Reputation: 14747

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';

street column does not exists in table house

Upvotes: 0

dayneo
dayneo

Reputation: 492

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.

You can get a list of all of your materialized views by running:

select * from user_mviews

Upvotes: 3

Jeff
Jeff

Reputation: 8148

An alternative answer: I came across this error when I tried to INSERT a record into a materialized view table.

Upvotes: 5

Randy
Randy

Reputation: 16677

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

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48121

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.

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.

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.

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.

Upvotes: 3

APC
APC

Reputation: 146249

First thing: are you really sure 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.

Upvotes: 3

Related Questions