Solved

Oracle Table and Materialized View with Same Name - How to Update the Table

Posted on 2014-01-23
20
3,961 Views
Last Modified: 2014-01-23
Hi, probably another super easy one...

My client named a materialized view the same as his table. When I try to insert into the table, I get "ORA-01732: data manipulation operation not legal on this view" since it just assumes I mean the view.

How do I tell it insert into the table? Is there some way to do like INSERT INTO schema.alltables.tablename (..  or something? So it knows I mean the table and not the view?

I don't have the option of dropping or changing the view. It's the client's setup, as dumb as it may be. :(
0
Comment
Question by:Jay Redd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 5
20 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39803976
specify the schema with the table


insert into schema_name.table_name (x,y,z) values (1,2,3);
0
 

Author Comment

by:Jay Redd
ID: 39804015
I tried that earlier, same error.

 INSERT INTO schema_user.OWNER_DATA (ID,EXTE,PORT,FACID,ORG_ID)
 SELECT 207548,'P',1367113,9734303,67691401 from dual;
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39804017
>>>  it just assumes I mean the view.

either you're logged in as the mv owner or there is a synonym that points to the mv.  There are no "assumptions" in how names are resolved.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 39804026
what are the results of these 2 queries?

select  * from all_objects where object_name = 'OWNER_DATA';

select * from all_synonyms where synonym_name = 'OWNER_DATA';


also, with what user are logged in?
or more accurately, what is your current_schema resolving?

usually that will be the same as your user, but if you do an alter session you can change it.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39804036
To determine schema that contains the table -
select owner, object_type from all_objects
where object_name = <NAME OF THE TABLE OR VIEW>
and then you can insert into owner.object_name ...
using the owner as the schema where the object_type is 'TABLE'
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39804043
essentially, what we're getting at is...
what you have described and what you are seeing don't coincide.


You can't have the same table and mv in the same schema.

So, if you specify the table's owner along with the table name, then you are assured that your insert is operating on the table in that schema.

If you're not seeing that, then either you have hit a bug (unlikely) or your schema or your table name is not correct.

Or... your insert is working correctly but then a trigger fires causing some illegal operation.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39804052
>>I tried that earlier, same error.
 INSERT INTO schema_user.OWNER_DATA (ID,EXTE,PORT,FACID,ORG_ID)<<
Then schema_user is the schema for the view and not the table.
0
 

Author Comment

by:Jay Redd
ID: 39804055
When I look at the database in Sql Developer, I see under tables OWNER_DATA and under materialized views OWNER_DATA, there are no OWNER_DATA in my synonyms. I can open the table or the view. Are you trying to say they can't have the same name?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39804057
What did the query on all_objects produce?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39804058
They can not have the same name in the same schema.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39804065
>> Are you trying to say they can't have the same name?

yes, as noted in your previous question they can't have the same name.

HOWEVER....

a materialized view IS a table.  That's where the view query is "materialized"
So, in that sense I guess you say it's a "loophole" in the namespace

Are you getting the two confused?  Are you trying to update the mv's table directly?  Not all mv's are updateable
0
 

Author Comment

by:Jay Redd
ID: 39804073
what are the results of these 2 queries?

select  * from all_objects where object_name = 'OWNER_DATA';

select * from all_synonyms where synonym_name = 'OWNER_DATA';

all_objects returns two rows, one with

OBJECT_TYPE = TABLE

and the other

OBJECT_TYPE= MATERIALIZED VIEW

Its all under the same user/schema.

nothing in synonyms.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39804087

OBJECT_TYPE = TABLE

and the other

OBJECT_TYPE= MATERIALIZED VIEW

Its all under the same user/schema.


then you're seeing what I'm describing above.  The MV and the Table are the SAME thing.


Here's an example...
SQL> create table testtable(n number);

Table created.

SQL> create materialized view testmv as select * from testtable;

Materialized view created.

SQL> select owner,object_name,object_type from all_objects where object_name = 'TESTMV';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SDS                            TESTMV                         TABLE
SDS                            TESTMV                         MATERIALIZED VIEW

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39804089
>>all_objects returns two rows, one with
OBJECT_TYPE = TABLE
and the other
OBJECT_TYPE= MATERIALIZED VIEW<<
and what did they each show as OWNER?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39804099
The  two objects do have distinct object id's because the TABLE part of an MV is just that, a TABLE with rows and columns,  the MV part contains the view query and refresh information.

They are managed in the data dictionary as two objects because each piece is responsibile for describing different aspects of the MV; but they are inseparable.
0
 

Author Comment

by:Jay Redd
ID: 39804101
et_owner for both.

if they are the same thing, why should there be two rows? does this mean i just can't insert at all in this instance?
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 300 total points
ID: 39804107
>>> why should there be two rows?

answered above

>>>  does this mean i just can't insert at all in this instance?

yes, the MV (and hence it's corresponding table) has not been defined as updateable.

To create a new row in the MV, you'd have to insert and/or update the table or tables that make up the defining query of the view.

So, in my example, you couldn't insert into testmv,  but you could insert to testtable which would then update testmv the next time it was refreshed.


SQL> create table testtable(n number);

Table created.

SQL> create materialized view testmv as select * from testtable;

Materialized view created.

SQL> select owner,object_name,object_type from all_objects where object_name = 'TESTMV';

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SDS                            TESTMV                         TABLE
SDS                            TESTMV                         MATERIALIZED VIEW

SQL> select * from testmv;

no rows selected

SQL> insert into testmv(n) values(1);
insert into testmv(n) values(1)
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> insert into testtable(n) values(1);

1 row created.

SQL> exec dbms_snapshot.refresh('TESTMV');

PL/SQL procedure successfully completed.

SQL> select * from testmv;

         N
----------
         1

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39804178
To see MV/TABLE relations as well as updatable, look in all_snapshots

select owner,name,table_name,updatable from all_snapshots where name = 'OWNER_DATA';
0
 

Author Closing Comment

by:Jay Redd
ID: 39805090
thanks!
0
 

Author Comment

by:Jay Redd
ID: 39805095
I was able to just filter it out for my purposes. Thanks for verifying how all this works for me!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question