1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Ora-01732: data manipulation operation not legal on this view

Discussion in 'SQL PL/SQL' started by Ashu, Jul 26, 2015.

  1. Ashu

    Ashu Starter

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    califonia
    Hi All,



    I am trying to delete the contents of a table but getting the error ORA-01732: data manipulation operation not legal on this view. I checked in the Materialized view section and found one of the MV has the same name as my table but this MV is created using some other tables. How can i differentiate between my table and MV.


    delete from <tablename> - How should i modify this statement so that it will contain the table not the MV.


    Regards,

    Ashu
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Specify the owner of the table in the delete statement.
     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi ,

    As I know once we create a Materialized View automatically with the same name one table also will gets created in oracle. May be you are getting problem with this.


    Regards
    Sambasiva Reddy.K
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to explain that remark as it doesn't make any sense. Materialized views are based on tables, not the other way around. And two objects cannot have the same name in the same schema.
     
  5. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Per the Oracle documentation: (https://docs.oracle.com/cd/E11882_01/timesten.112/e21642/names.htm#BCFICJDE)

    "Any tables, views, materialized views, sequences, private synonyms, PL/SQL packages, functions, procedures, and cache groups owned by the same user share one namespace and so the names for each of these objects must be unique within that namespace."

    As David indicated, it is impossible for a table and a materialized view in a given schema to have the same name. Any time two different object types share a namespace, there can only be one object with a given name per schema.

    As a side note, it is possible to have two objects with the same name in a given schema if they are in different namespaces. For example, you can have a table and an index with the same name.
     
  6. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi David&Matthew,

    I dont know deeply about the namespaces . Have you checked in all_objects table for any MV where object_name = '<MV Name>' ? . The same I attached as screenshot.

    Can you plz explain what is happening in this case(screenshot result)?


    Regards
    Sambasiva Reddy.K
     

    Attached Files:

  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
  8. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    That means It is possible for a table and a materialized view in a given schema to have the same name. Right?

    Now I think Ashu is trying to delete the data from the table which is created by MV.

    Regards
    Sambasiva Reddy.K
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, that is due to how Oracle creates a materialized view. Under normal circumstances (such as two tables, not materialized views) it can't happen.

    Unfortunately it's impossible to delete rows from a materialized view directly; you will need to either:

    drop the materialized view and rebuild it with the desired data
    or
    delete the desired data from the source table and refresh the materialized view.
     
  10. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    It depends on what you mean by that. The reason for namespaces is to prevent situations in which it is impossible for the database to determine which object is being referenced. For example, if the SCOTT schema had both a table named EMP and a view named EMP (note that namespaces makes this impossible), and the command SELECT * FROM SCOTT.EMP was executed... which of the two objects would be queried? Without namespaces, some additional syntax or addressing would be required to distinguish between the objects in SQL operations.

    Materialized views, unlike standard views actually store data. That data must be stored somewhere and the perfect container for doing so is a table object. Materialized views are the love-child of standard views and tables. The MATERIALIZED VIEW object acts as the query definition for the data to be created and the associated table object acts as the container to hold the results of that query. The two identically-named objects in the database schema are logically the same thing, so there is no question about which should be referenced when the name is used in a SQL operation.

    Whenever you create a materialized view on a prebuilt table, the MV essentially "takes over" the existing object so that it is no longer logically treated as a table but rather it is treated as a materialized view. The difference between the two from Oracle's standpoint is essentially how data gets updated in them.

    As the example below shows, trying to create a materialized view when a table of the same name already exists and the "ON PREBUILT TABLE" clause is omitted will fail with an error. Trying to create a materialized view on a prebuilt table that does not match the column count/definitions of the materialized view query also fails. The upshot is that you cannot have a materialized view and a table of the same name in a given schema that are separate logical entities.

    Code (Text):

    create table mv_1 (
    col_1   VARCHAR2(5));

    create table source_tb_2 (
    col_1   VARCHAR2(5),
    col_2   NUMBER);

    CREATE MATERIALIZED VIEW mv_1 AS
    SELECT * FROM source_tb_2;

    Error at Command Line : 9 Column : 15
    Error report -
    SQL Error: ORA-00955: name is already used by an existing object
    00955. 00000 -  "name is already used by an existing object"
    *Cause:  
    *Action:

    CREATE MATERIALIZED VIEW mv_1 ON PREBUILT TABLE AS
    SELECT * FROM source_tb_2;

    SQL Error: ORA-12060: shape of prebuilt table does not match definition query
    12060. 00000 -  "shape of prebuilt table does not match definition query"
    *Cause:    The number of columns or the type or the length semantics of a
               column in the prebuilt table did not match the materialized
               view definition query.
     
  11. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Yes -- based on OPs initial post, if he is referring to a table and MV in the same schema, he is trying to perform DML on the MV, and this will not work.