-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|