Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-01-23
20
Medium Priority
?
4,354 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

670 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