6 Replies Latest reply on Jan 29, 2003 5:20 PM by David

    EJBs partielly deployed to wrong Oracle schema

    David Newbie

      I have 2 jboss' on an internal network, each using a different datasource. When I deploy the same beans into both, I get

      Table 'XXX' already exists, but either it really doesn't or it exists in the other servers datasource. And/or the tables are created in the wrong schema.

      I don't get it?

        • 1. Re: EJBs partielly deployed to wrong Oracle schema
          Stephen Coy Novice

          You need to set up the permissions for each (Oracle) user so that they cannot "see" each others tables.

          Steve

          • 2. Re: EJBs partielly deployed to wrong Oracle schema
            David Newbie

            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;

            • 3. Re: EJBs partielly deployed to wrong Oracle schema
              Jeremy Boynes Newbie

              dave has select_any_table so can definitely see the tables in the other user's account. This is reported in DatabaseMetaData which is why the create is not performed. However, the select ... from table does not specify the schema and hence fails.

              You can specify the schema name or as Steve suggests lower dave's privileges.

              • 4. Re: EJBs partielly deployed to wrong Oracle schema
                David Newbie

                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-

                • 5. Re: EJBs partielly deployed to wrong Oracle schema
                  David Newbie

                  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-

                  • 6. Re: EJBs partielly deployed to wrong Oracle schema
                    David Newbie

                    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/