Hello,

I joined a JBOSS/JPA/Hibernate/MySQL project that is in production for 6 months now. We want to migrate the database to Oracle 10g (not only the schema, but also the actual content) and we have some trouble with identifier columns (primary keys).

We use default identifier generators for now, i.e. IDENTITY on MySQL and SEQUENCE on Oracle. The first problem after generating the Oracle schema and migrating the data is that the generated HIBERNATE_SEQUENCE starts from 0, or 20, and the first time we generate a key, it will colide with an existing key migrated from MySQL.

Question 1: Is there a means to tell Hibernate how to create the HIBERNATE_SEQUENCE, such that it will start at a number that is higher than the highest identifier in all existing tables?

The second problem is that we actually don't want Hibernate to use a single sequence for all table identifiers (for issues related to the length of the keys after some time). We'd actually like to have with Oracle the same behaviour as with MySQL, like unique keys per table, not per database.

Question 2: How can we best "emulate" on Oracle the MySQL IDENTITY gerator? What options are there? What happens if we use IDENTITY generator with Oracle? Or should we use a different sequence for each table? And in this case, should we manually set the sequence's "start" value to cope with the legacy data?

The third problem is that we'd like to be able to switch back and forth between the MySQL and Oracle databases, including moving the data back and forth. We stumble again on the "next identifier" issue, which we'd like to solve "auto-magically".

Question 3: Is it possible to configure Hibernate such that, whatever the database content and the identifier generator strategy, it will "start" new identifiers at values that do not conflict with existing legacy data ?

Thanks,
Pierre