DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Can a table and a materialized view have the same name in one schema in a DB?

  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Unhappy Can a table and a materialized view have the same name in one schema in a DB?

    Hi Oracle folks ,
    Here is a fundamental question about Oracle objects.
    Can a table and a materialized view have the same name in one schema
    in an oracle db?
    While searching for an object_type I got the following
    SQL>select owner,object_name,object_type,status from dba_objects where object_name='ACCOUNT_D' and owner='CR_STAGE';

    Result:
    -------
    Owner Object_Name Object_Type Status
    ----------- -------------- ------------------- -------
    CR_STAGE ACCOUNT_D TABLE VALID
    CR_STAGE ACCOUNT_D MATERIALIZED VIEW VALID

    As per my fundamental for both table and materialized view namespace is same , the schema . I dont some where I am going wrong.
    Please have a look and advise.
    Thanks
    Armaan

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    I love questions that include the answer, this is one of those cases.

    Answer is: Yes, a table and a materialized view can have the same name in the same schema.

    My follow up question would be... why do you think that's wrong?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Apr 2006
    Posts
    377
    A materialized view is a table, therefore you'd expect to see both of the entries in DBA_OBJECTS. If you have created a materialized view in a schema, then you can't create a table in the same schema with the same name as the materialized view.

    Code:
    SQL> drop materialized view test_mv;
    
    Materialized view dropped.
    
    SQL> drop table test_mv;
    drop table test_mv
               *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    
    
    SQL> CREATE MATERIALIZED VIEW test_mv
      2     BUILD IMMEDIATE
      3     AS SELECT object_name, object_type
      4     from dba_objects;
    
    Materialized view created.
    
    SQL> select owner,
      2     object_name,
      3     object_type,
      4     status
      5  from dba_objects
      6  where object_name='TEST_MV';
    
    OWNER    OBJECT_NAME                    OBJECT_TYPE         STATUS
    -------- ------------------------------ ------------------- -------
    TEST     TEST_MV                        TABLE               VALID
    TEST     TEST_MV                        MATERIALIZED VIEW   VALID
    
    SQL> create table test_mv (a1 number);
    create table test_mv (a1 number)
                 *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width