I posted this in the wrong forum (JBoss Messaging) but Tim informed me to post it in the JBoss AS forum. I couldn't find one with that name so I am trying here.
===== original post below =====
When I startup my JBoss Server (4.2.0 GA with Messaging 1.3) I find that the following tables are missing from my schema:
I believe these tables are supposed to be created at startup.
My colleague did not have this problem with missing tables (we use different schemas / users but the same database instance). Just for a test, I dropped his tables and started my server again. Bingo, my tables are all there. Then he started his server and he started getting the same table or view not found exceptions as I was getting. So, in short, it appears that we cannot start two completely separate JBoss servers using Messaging 1.3 and Oracle with the same database instance (even though we are using separate users). This leads me to believe that when JBoss checks the database for the existence of these tables at startup it casts two wide a net e.g. perhaps the code uses:
select count(*) from all_tables where table_name = 'HILOSEQUENCES'
select count(*) from user_tables where table_name = 'HILOSEQUENCES'
Please note, this is just a guess on my part.
I have checked the database users. Neither of them have access to one another's objects via SQL*Plus unless we explicitly prefix each object name with the schema name. In other words, when I am logged in as user1, I cannot see user2's tables and vice versa.
Is this a known limitation / issue? Is there a workaround? I don't believe we should have to use two database instances i.e. one per JBoss.
Fixed it. The problem was that our database users had the DBA privilege (I didn't create the users). Once I revoked that privilege, everything starts up fine.