Oracle Schema / Tablespaces / Tables

piojosnos · **Joined:** Sun Jan 29, 2006 9:27 pm **Posts:** 10

This may be an Oracle - Hibernate question. So I posted in an Oracle forum and here ;-)

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 written in Java (Using Hibernate). Both are made with the same framework, so 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 underestanding well the concepts) with two different users. I create the users and tablespaces for each application in the following way:

For the first app:

CREATE TABLESPACE CDI_TBL DATAFILE '/path/to/CDI_TBL' SIZE 1M AUTOEXTEND ON NEXT 50K;
CREATE USER CDI_USER IDENTIFIED BY sa003 DEFAULT TABLESPACE CDI_TBL;
GRANT RESOURCE, CONNECT, IMP_FULL_DATABASE TO CDI_USER;

For the second is the same procedure but replacing CDI_TBL for ONT_TBL and CDI_USER for ONT_USER.

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?

dslevine · **Joined:** Thu May 26, 2005 12:20 am **Posts:** 72

If you are connecting to the database (through your hibernate config file) you should not have any problem with overlapping names -- they should be created in different schemas. Are you SURE you are connecting to the DB with different logins for each app? Can you post your two different hibernate Session configs?

Also, you could be doing something with the Oracle permissions that would allow them to see and reference each other's tables that would cause that -- public synonyms or something. You could check that by logging into oracle as each user and running the scripts manually. If you can do that, it should be fine through hibernate!

PS: how are you having Hibernate create the tables? manually, or using the hbx_to_ddl tools?

dan

piojosnos · **Joined:** Sun Jan 29, 2006 9:27 pm **Posts:** 10

I'm using two different users for each app. In my previous post is the way I created the users in oracle. I let hibernate create the database from the mapping files. When I run the first app it creates the tables, but when I run the seccond app it says that the tables with the same name in both applications already exists :-(

For the first app, using user name CDI_USER

Code:

<hibernate-configuration>
   <session-factory>
      <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
      <property name="hibernate.connection.url">jdbc:oracle:thin:@127.0.0.1:1521:ORCL</property>
      <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
      <property name="hibernate.connection.username">CDI_USER</property>
      <property name="hibernate.connection.password">somepwd</property>
      <property name="hibernate.connection.pool_size">5</property>
      <property name="hibernate.jdbc.batch_size">0</property>

      <property name="hibernate.cglib.use_reflection_optimizer">false</property>
      <property name="show_sql">false</property>

      <property name="hibernate.hbm2ddl.auto">update</property>

      <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
      <property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>

      <!-- The mapping files, this is a common class betwen the two apps, but I whould like it to have nothing to do betwen them -->
      <mapping resource="com/minotauro/pnf/model/audit/MTransactionAudit.hbm.xml"/>

               <!-- Here there are more mapping files (one for class / table)-->

   </session-factory>
</hibernate-configuration>

Seccond app, using ONT_USER

Code:

<hibernate-configuration>
   <session-factory>
      <property name="hibernate.connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
      <property name="hibernate.connection.url">jdbc:oracle:thin:@127.0.0.1:1521:ORCL</property>
      <property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>
      <property name="hibernate.connection.username">ONT_USER</property>
      <property name="hibernate.connection.password">sa003</property>
      <property name="hibernate.connection.pool_size">5</property>
      <property name="hibernate.jdbc.batch_size">0</property>

      <property name="hibernate.cglib.use_reflection_optimizer">false</property>
      <property name="show_sql">false</property>
      
      <property name="hibernate.hbm2ddl.auto">update</property>

      <property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
      <property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>

      <!-- The mapping files, this is a common class betwen the two apps, but I whould like it to have nothing to do betwen them -->
      <mapping resource="com/minotauro/ont/model/audit/MTransactionAudit.hbm.xml"/>

               <!-- Here there are more mapping files (one for class / table)-->
   
   </session-factory>
</hibernate-configuration>

Regards, Demián.

piojosnos · **Joined:** Sun Jan 29, 2006 9:27 pm **Posts:** 10

Ok, the problem was solved a long time ago. I'll post it here in case some one else may need it.

We implemented a custom NamingStrategy, here it goes:

Code:

public class CledaNamingStrategy extends ImprovedNamingStrategy {

  protected Configuration configuration;

  public CledaNamingStrategy(Configuration configuration) {
    this.configuration = configuration;
  }

  public String tableName(String tableName) {
    String url = (String) configuration.getProperties().get("hibernate.connection.url");

    // Oracle should use a "username"."tablename" schema or we will have
    // problems with different unrelated databases with common table names
    if (url.contains("oracle")) {
      return configuration.getProperty("hibernate.connection.username") + "." + super.tableName(tableName);
    }

    return super.tableName(tableName);
  }

I discussed with an Oracle Administrator and we concluded that the main problem was that hibernate was not including the user in the table name when creating/testing if the table was found, so the NamingStrategy solved the problem.
[/code]