Oracle schema table name restrictions

Question

Hi

I am looking into using JBoss 5.1.0GA on Oracle, and have seen this, warning that I should explicitly state the name of the schema into which the TIMERS table should be created, as Oracle doesn't permit the same table name to be used twice, even across schemas.

After reading this, I saw this question on StackOverflow, and would like some clarification about the hierarchy of objects in Oracle.

Suppose we have a single Oracle database server. Within this, we create two Databases - D1 and D2. Inside each database, we create two schemas - S1 and S2. Inside each schema on each database, we create a single table - T1 through to T4:

+-D1
| +---S1
| |   +---T1
| +---S2
|     +---T2
+-D2
  +---S1
  |   +---T3
  +---S2
      +---T4

Am I correct in thinking that if I then add another table called T1 inside D1/S2, it will not work because the table names must be unique within the schemas, and T1 already exists in D1/S1, but if I add T1 to either D2/S1 or D2/S2 it will be fine because the two tables named T1 are in different databases?

I have a nasty feeling that my understanding of Oracle schemas is flawed (it is not a database I have used much before) so I'm sorry if I'm asking stupid questions.

Thanks in advance

Rich

Answer 1

As far as Oracle goes, you can have the same table name in two different schemas. There may be something specific to the JBoss usage that you were reading about, but it is not an Oracle limitation.

Answer 2

Hi Rich,

the database hierarchical level doesn't exist in Oracle: an instance (set of processes) can only have one database (set of files) mounted at most. Inside a database you will find schemas which are also the same as users in Oracle.

Each schema has an independent namespace, e.g. schemas S1 and S2 can both have a table named T1 in the same database. You would specifically access these tables by using their owner as a prefix: S1.T1 and S2.T1.

Some objects don't have an owner (or their owner is PUBLIC) : Public synonyms and directories for example. The name of these objects will have to be unique in a database obviously. Use public synonyms wisely (=sparingly in my opinion) to avoid name collisions.