?
Solved

Table and view with same name

Posted on 2014-01-20
13
Medium Priority
?
1,486 Views
Last Modified: 2014-01-23
If I have same name on table and view in oracle, which one is accessed first and why ?
0
Comment
Question by:d27m11y
13 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39795464
>>> same name on table and view

within a single schema that is not possible.
0
 

Author Comment

by:d27m11y
ID: 39795485
Can we create table and MV on the same name in same schema  ?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39795507
no
0
Industry Leaders: 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!

 

Author Comment

by:d27m11y
ID: 39795559
So, can we create any tables and views/MVS or any other db objects with same name in the same schema...
0
 
LVL 26

Expert Comment

by:Tomas Helgi Johannsson
ID: 39795567
Hi!

As previous posts points out you can't have the same name for table and view or two tables or two views in the same schema.

So if you get two rows by executing this query
select table_name from all_tables where table_name = '<yourtable>'
then you will have to add the column owner to the select
select owner,table_name from all_tables where table_name = '<yourtable>'
to determine which table you want to use and select from.
The database does not make that decision for you. And of course you (your login id has to have select privileges on that table to access the data.

Regards,
    Tomas Helgi
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39795597
To add to the list of no's.

Don't think of them as their individual types like table, view, etc...

As you mentioned above:  Think of them as "objects".  Within a single schema, object names are unique.

I suggest you just try it.  Log into a user and issue the two following statements:
create table myobject(col1 char(1));
create view myobject as select * from dual;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39795689
I'm afraid I have to disagree with both of the previous posts...

yes, you CAN have multiple objects in the same schema with the same name.

However, there are restrictions on the types of objects that can share a namespace.

For example...
SQL> create table samename(n number);

Table created.

SQL> create view samename as select * from user_tables;
create view samename as select * from user_tables
                                      *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> create index samename on samename (n);

Index created.

SQL> select object_name,object_type from dba_objects where object_name = 'SAMENAME';

OBJECT_NAME                         OBJECT_TYPE
----------------------------------- -----------------------
SAMENAME                            INDEX
SAMENAME                            TABLE

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39795694
For more information on the rules of naming objects, specifically which object types share a namespace  see the SQL Reference'

http://docs.oracle.com/cd/E16655_01/server.121/e17209/sql_elements008.htm#SQLRF51129


Within a namespace, no two objects can have the same name.

The following schema objects share one namespace:

    Tables

    Views

    Sequences

    Private synonyms

    Stand-alone procedures

    Stand-alone stored functions

    Packages

    Materialized views

    User-defined types

    User-defined operators

Each of the following schema objects has its own namespace:

    Indexes

    Constraints

    Clusters

    Database triggers

    Private database links

    Dimensions
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39795698
So, given above, you can now continue my example above...

create trigger samename
before insert or update or delete on samename
begin
null;
end;

ALTER TABLE samename ADD CONSTRAINT samename UNIQUE (N);


etc
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39795707
The real question is not so much "can" you do it, but rather "SHOULD" you do it?

And the answer is usually NO.  There is no advantage to naming two different objects with the same name,  it simply creates ambiguity.

Having said that,  if you create a unique or primary constraint on a table and there isn't already another index available to support it,  the constraint will, by default, create an index of the same name.

Is this good or bad?  I don't know.  Most people that use oracle regularly are probably used to it even if it does violate their best practices for all other object types.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 39796114
just create a synonym  and point it to whatever object it has to point

create table data_table (x number);

create view data_view as select * from data_table;

create synonym data for data_view;
or
create synonym data for data_table;
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39796139
to keep it simple and short, it depends on which object types can have the same names and which cannot in the same schema. I guess you should have understood the answer from the posts above. I got curious as to why made you ask this question and what is your requirement ?

It is possible that at times if you can put down your requirement, may be one of the experts can give the ideal solution to suit your requirement.

I guess you might have asked this to just understand from technical point/interview questions etc..

Thanks,
0
 

Author Comment

by:d27m11y
ID: 39805248
I was asked this question in interview and I said we may not be able to create it. Looks like I was wrong in answering that question.

Thank you for your help and valuable suggestions..
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

862 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