Table and view with same name

>>> same name on table and view

within a single schema that is not possible.

Can we create table and MV on the same name in same schema  ?

no

So, can we create any tables and views/MVS or any other db objects with same name in the same schema...

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

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;

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

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.

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;

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,

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..