I read a recent article about the way in which an table name supposedly could not have the same name as a the database name  in SQL Server 2008, this was possibly valid at some time in a previous version. Now most are aware that objects in a different schema in an Oracle DB can have the same name, but can a table and a view, or a table and an index have the same names. So here is a few little tests and lets see what I found.

CREATE USER test1 IDENTIFIED BY test1 DEFAULT TABLESPACE test1;

GRANT CREATE TABLE TO test1;

GRANT CREATE TYPE TO test1;

GRANT CREATE VIEW TO test1;

GRANT CREATE SESSION to test1;

Log in as user TEST1

SQL> create table test1(                                     2  test1 varchar2(10),   3  test1 number(10,2)); test1 number(10,2)) * ERROR at line 3: ORA-00957: duplicate column name Ok thats fair enough

SQL> create table test1(   2  test1 char(10),   3  test2 varchar2(10),   4  test3 number(10,2));

Table created.

SQL> create index test1 on test1(test1);

Index created.

create view test1 as (select test1 from test1); create view test1 as (select test1 from test1)             * ERROR at line 1: ORA-00955: name is already used by an existing object

And that one makes sense as well

So it really makes sense when you think about it if I want to select from an object in the context it is used eg select test1 from test1 is valid to I can have a column in the table test1 called test1 I can have an index the same name but I could not have a link in this schema as test1 as that would be confusing with this schema. I could not have a global link in the database as selecting from the test 1 schema or the Link would not differentiate, I can however have any number of the same object names across many schemas, many developers know this often having their own schema in the database for the code they are working on.

Juast a side note I saw in a Don Burleson article a mention that GRANT CREATE INDEX doesn’t work in some distributions, I found that to be true in my 11.2 distribution on Linux x64. It would appear that the GRANT CREATE TABLE provides the support for creating indexes.

I hope this might help in someone understanding of using object names in Oracle databases.

Oh what was the answer with SQL Server, well it was like Oracle a database and table name can be the same.

See ya round

Peter