Oracle Schema Components Oracle Database Tips by Donald Burleson

Question:

This may be a very dummy question, but I'm new to Oracle (I'm used to MySQL / PostgreSQL, an others) but Oracle Users / Tablespaces are just driving me crazy.

I have two WEB applications.  They have some tables with the same names (t_adm_user, t_adm_crashreport, etc). I connect to an Oracle database (only one instance if I'm understanding well the concepts) with two different users.

When I run the first app (connecting as CDI_USER), the framework I use (Hibernate) creates the tables for the first app (Including the tables with common names) and then, when I run the second app (connecting as ONT_USER) the framework creates the tables for the second app but tells me that tables with common names are already created.

I would like to be able to have to two different "schemas or namespaces" (Here is where Oracle terminology gets me confused) for each app, so no matter if they have some tables with common names, they don't get shared. Is there any way?

Answer:

It's fairly easy to remember the relationships between Oracle schema components once you can visualize the architecture.  Here are some simple concepts:
 

• A "database" component can be thought-of as a set of xxx.dbf files
   

• An "instance" component is a running set of programs that read the .dbf files (Oracle RAC allows many instances to connect to the database).
   

• An "object" components is a table, index, cluster table, &c
   

• The "user" component creates objects as the "schema owner" of those objects.
   

• The "synonym" component allows you to "alias" an object to make the schema owner hidden:
  
  create synonym food for cat.food;
   

• With "public synonym" components, the table owner name ("the user ID") is kept inside the synonym definition and appended to the table name at runtime to remove duplicates:
  
  create public synonym food for cat.food;
   

• Here are details on Oracle File & Tablespace Management
   

Within a database we have schemas, each owned by a specific user_id. Hence, we can think of a USER and a OWNER as the same in the sense that a schema owner is a single, distinct user ID:

           schema_owner = user_id

Also, we can refer to tables with their "fully-qualified" names, where we include the schema owner. Here we see two tables named "food", one created by the user "cat" and the other schema owner "dog":
 

   select stuff from cat.food;
  union
 select stuff from dog.food;
 

Finally, Oracle has Optimal Flexible Architecture (OFA) naming conventions for all Oracle schema components including data files, tablespaces, users (schema owners), tables, and so on.

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.