4 Replies Latest reply on Jan 29, 2003 5:17 PM by dlindsay

    Table 'XXXXX' already exists

    mlange

      Hi all,

      I encoutered a CMP problem when I wanted to let JBoss (3.2.0RC1) to create my table in oracle. With one table, JBoss always states that it "already exists". However, the table is 100% not in the database. What is strange here, if I rename the <table-name> in jbosscmp-jdbc.xml the table is created then. All other tables are created correctly. What is wrong here? Anything with the cache or a bug?

        • 1. Re:  Table 'XXXXX' already exists
          ashanmugam

          JBoss fires select count(*) from to see if the table exists or not. Double check by logging in using the user id and password that jboss uses.

          • 2. Re:  Table 'XXXXX' already exists
            jboynes

            JBoss actually uses DatabaseMetaData.getTables() to determine is the table exists. With Oracle this can return misleading information if the user can access a table with the same name in another schema.

            You can verify this by logging in as your user and do
            select table_name from all_tables.

            If you intend to use that table you can fully qualify the name; if you don't, you might want to lock down the database a bit.

            • 3. Re:  Table 'XXXXX' already exists
              dlindsay

              You actaully replied to another email of mine, where tables of the same name exists in 2 different schemas, and jboss won't create/deploy into the other schema because it is seen in the MetaData.

              I applolgise, my Oracle dba skills are not well developed. I understand what is happeneing, I just don't know how to fix it.

              What can I do in Oracle to prevent this from happening? Meaning, if a fix is to change the grants, what would I change and how? I have no problem dropping the user and re-creating him.

              • 4. Re:  Table 'XXXXX' already exists
                dlindsay

                My problem turned out to be in the User creation script. The grants were the issue. Before I was granting all priviledges to the users. I'm an ignorant Oracle dba. Much thanks to jboynes insight. Here is my script I finally used to create the user(s) with:

                set echo on;
                drop tablespace devl including contents;
                create tablespace devl datafile 'devl.dbf' size 100M reuse;
                DROP USER devl CASCADE;
                create user devl identified by devl default tablespace devl;
                GRANT RESOURCE TO Devl;
                grant CREATE SESSION to Devl;
                commit;
                exit