I'm thinking that more and more.

But the strange thing is that if I log in via sqlplus, that user cannot see any of the tables in the other users account. The problem only happens in the table creation/droping via the driver registered on a jboss instance. When the beans are run, they realise that the tables aren't there.

Perhaps I should be defining the schema name in the table name for the bean?

This is the used to create one of the users (for the other user, I substituted dave with devl):


drop tablespace dave including contents;
create tablespace dave datafile 'dave.dbf' size 100M reuse;
DROP USER dave CASCADE;
create user dave identified by dave default tablespace dave;
grant all privileges to dave;
commit;

FYI- Thank you for all the help so far.

Well I changed the script to say:

grant create session, alter session, create table, create synonym, create sequence to dave;

Thinking that would help, but it didn't seem to. The query "SELECT * FROM USER_SYS_PRIVS;" did list 'SELECT ANY TABLE'. This is a pretty much vanilla install of Oracle9i on a G4. My oracle dba skills are not my strong point.

Right now, if I undeploy the beans by removing the jar, I get a "Could not drop table XXX" on the same named tables in the DEVL schema. The tables that don't exist in the DEVL were all successfully dropped from the DAVE scheam. If I redeploy the beans by redeploying the jar, I get "Table 'XXX' already exists" if that beans table is in the DEVL schema, and "Created table 'XXX' successfully" if the table is not in the DEVL schema. Using sqlplus verifies that these tables were actually dropped/created in DEVL.

When I test the beans by inserting/selecting data, I get error messages about tables existing/not-existing which accurately reflect the state of the DAVE schema that I saw via sqlplus (missing/present tables).

So this "shared table" stuff only occurs when dropping/creating the tables. If I swith the jboss' to using DefaultDS (Hypersonic), I don't see the problem. So I'm certain it's somewhere between the jdbc drivers and the database setup.

I'm wondering if the oracle-service.xml file is set up properly- (this is a snippet):

jdbc:oracle:thin:@valkyrie:1521:ab
oracle.jdbc.driver.OracleDriver
dave
dave

Again, I thank anyone who can help me in this-

FYI- Thank you for all the help so far.

Well I changed the script to say:

grant create session, alter session, create table, create synonym, create sequence to dave;

Thinking that would help, but it didn't seem to. The query "SELECT * FROM USER_SYS_PRIVS;" did list 'SELECT ANY TABLE'. This is a pretty much vanilla install of Oracle9i on a G4. My oracle dba skills are not my strong point.

Right now, if I un-deploy the beans by removing the jar, I get a "Could not drop table XXX" on the same named tables in the DEVL schema. The tables that don't exist in the DEVL were all successfully dropped from the DAVE schema. If I redeploy the beans by redeploy-ing the jar, I get "Table 'XXX' already exists" if that beans table is in the DEVL schema, and "Created table 'XXX' successfully" if the table is not in the DEVL schema. Using sqlplus verifies that these tables were actually dropped/created in DEVL.

When I test the beans by inserting/selecting data, I get error messages about tables existing/not-existing which accurately reflect the state of the DAVE schema that I saw via sqlplus (missing/present tables).

So this "shared table" stuff only occurs when dropping/creating the tables. If I swith the jboss' to using DefaultDS (Hypersonic), I don't see the problem. So I'm certain it's somewhere between the jdbc drivers and the database setup.

I'm wondering if the oracle-service.xml file is set up properly- (this is a snippet):

jdbc:oracle:thin:@valkyrie:1521:ab
oracle.jdbc.driver.OracleDriver
dave
dave

Again, I thank anyone who can help me in this-

Found it. Was a permissions issue in the user. Needed to have the following in the user creation scripts:

GRANT RESOURCE TO Devl;
grant CREATE SESSION to Devl;


See:
http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/