Assuming that you are using surrogate keys throughout your DB.
leonir wrote:
Yes. Every schema is identical.
How do you will combine multiple schemas into a simgle schema?
You will need to write a utility to do the data migration so that no two records in the same table have the same primary key which is probably an issue because your main hibernate_sequence will currently exist in each of the schemas.
Once you have that you will need to examine your unique constraints.
Basically any unique constraint that encompasses a foreign key to another tables primary key shouldn't cause an issue because your utility has already fixed all the primary keys.
For the remaining unique constraints you will either need to decide if you can live without them or add a new "schema_id" field.
leonir wrote:
You will put a WHERE to every HQL? (or SQL)
A significant number of HQL/SQL's wouldn't need to be changed.
The reason for this is that if you reference a record in one table (by its primary id) and then as part of that query you join to a bunch of other tables the joined records must have originally come from the same schema.
So the only time you would need to add extra where clause is in SQL's where you don't reference a primary (surrogate) key.
However I am guessing that the scope of this will be limited to a few top level tables (probably the same ones you needed to add the extra schema_id fields too).