Solved

Table and view with same name

Posted on 2014-01-20
13
549 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 73

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 73

Expert Comment

by:sdstuber
ID: 39795507
no
0
 

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 24

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 76

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 500 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 73

Accepted Solution

by:
sdstuber earned 500 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 73

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 73

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 37

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

744 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now