ORA-00600 on new table in other schema

Hi 

I have created a table in an other Oracle schema and when I try to insert a row into it I get an ORA-00600 error for the first two inserts but then it seems to 'fix itself' and behaves fine.   The following script seems to reproduce the issue :

 

drop user test_db cascade;

create user test_db identified by test_db default tablespace users temporary tablespace temp;

grant connect,resource,unlimited tablespace,jcc_basic_role,create table, create sequence to test_db;

alter user test_db default role all;

create table test_db.structures (casnum varchar2(32), common_name varchar2(256), structure clob);

create index test_db.struct_jchem_idx on test_db.structures (structure) 

  indextype is jchem.jc_idxtype 

  parameters('tableType=anyStructures,fp_bit=2,pat_length=6,fp_size=24');

Insert into TEST_DB.STRUCTURES (CASNUM,COMMON_NAME,STRUCTURE) values ('58-08-2','Caffeine','CN1C=NC2=C1C(=O)N(C)C(=O)N2C');

 

The insert will fail with :

Error starting at line 1 in command:

Insert into TEST_DB.STRUCTURES (CASNUM,COMMON_NAME,STRUCTURE) values ('58-08-2','Caffeine','CN1C=NC2=C1C(=O)N(C)C(=O)N2C')

Error report:

SQL Error: ORA-29875: failed in the execution of the ODCIINDEXINSERT routine

ORA-29532: Java call terminated by uncaught Java exception: oracle.jdbc.driver.OracleSQLException: ORA-00600: internal error code, arguments: [12260], [235], [], [], [], [], [], []

ORA-06512: at "JCHEM.JCHEM_CLOB_PKG", line 113

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 577

29875. 00000 -  "failed in the execution of the ODCIINDEXINSERT routine"

*Cause:    Failed to successfully execute the ODCIIndexInsert routine.

*Action:   Check to see if the routine has been coded correctly.

 

As I say this will fail twice but then it seems OK so I can't believe I am doing something fundamentally wrong....

 

This is my environment :

 

JCHEM_CORE_PKG.GETENVIRONMENT()                             

------------------------------------------------------------

Oracle environment:                                         

Oracle Database 10g Release 10.2.0.4.0 - 64bit Production   

PL/SQL Release 10.2.0.4.0 - Production                      

CORE 10.2.0.4.0 Production                                 

TNS for Linux: Version 10.2.0.4.0 - Production              

NLSRTL Version 10.2.0.4.0 - Production                      

 

JChem Server environment:                                   

Java VM vendor: Sun Microsystems Inc.                       

Java version: 1.6.0_13                                      

Java VM version: 11.3-b02                                   

JChem version: 5.2.0                                        

JChem Index version: 5020005                                

JDBC driver version: 11.1.0.7.0-Production                  

 

 

 

Hi Steve,

ORA-00600 errors are (as the message indicates) internal Oracle errors and indicate either an Oracle software bug or (less often) a hardware problem.

Using a slightly modified version of your script, I haven't been able to reproduce the problem so far:

drop user test_db cascade;
create user test_db identified by test_db default tablespace users temporary tablespace temp;
grant connect,resource,unlimited tablespace,pkovacsrole_520,create table, create sequence to test_db;
alter user test_db default role all;

connect test_db/test_db
call jchem_core_pkg.use_password('test_db');
create table test_db.structures (casnum varchar2(32), common_name varchar2(256), structure clob);
create index test_db.struct_jchem_idx on test_db.structures (structure)
  indextype is pkovacs_520.jc_idxtype
  parameters('tableType=anyStructures,fp_bit=2,pat_length=6,fp_size=24');
Insert into TEST_DB.STRUCTURES (CASNUM,COMMON_NAME,STRUCTURE) values ('58-08-2','Caffeine','CN1C=NC2=C1C(=O)N(C)C(=O)N2C');
Perhaps the problem is specific to your operating system version. I have tested on this kernel: Linux negev 2.6.18-92.1.22.el5 #1 SMP Tue Dec 16 11:57:43 EST 2008 x86_64 x86_64 x86_64 GNU/Linux

Regards,

Peter

Hi Peter,

Thanks for looking into this for me.  I think the key difference here is that my script is being run by a different user to 'test_db'.  If I run this as test_db (i.e via a connect as you have) then yes, it works fine.  This is a fragment of an automated procedure that creates tables for holding chemistry data in a different schema.

The script to create the user is :

 

 

grant connect,resource,unlimited tablespace,

 drop user,alter user,create user,

 create any sequence,drop any sequence,

 drop any index,create any index,alter any index,

 insert any table,delete any table,select any table,update any table,

 grant any privilege,grant any object privilege,grant any role,

 create public synonym,drop public synonym,

 create any view, drop any view,

 create any table,drop any table,alter any table,

 create any directory,

 create any trigger,

 analyze any,

 exp_full_database,imp_full_database

   to <user>;

 

 

begin

  dbms_java.grant_permission( '<user>', 'SYS:java.net.SocketPermission', 'localhost', 'resolve');

  dbms_java.grant_permission( '<user>', 'SYS:java.net.SocketPermission', '127.0.0.1:1099', 'connect,resolve');

end;

/

grant jcc_basic_role to <user> with admin option;

alter user <user> default role all;

 

 

 

Hi Steve,

I'd say that the following line

dbms_java.grant_permission( '<user>', 'SYS:java.net.SocketPermission', '127.0.0.1:1099', 'connect,resolve');

should rather read like this:

dbms_java.grant_permission( '<user>', 'SYS:java.net.SocketPermission', '127.0.0.1', 'connect,resolve');

-- i.e. without the port specification. (The configured JChem Server port [which is 1099 by default] is just an entry point to clients, JChem Server will create server ojbects at arbitrary other ports.)

Apart from this, if the problem really is that user A is creating a table in the schema of user B, then creates an index on it and then INSERTs into the table, I start to wonder whether, given this level of promiscuity, having just one user doing this in its own schema would make a big difference.

Peter

 

Thanks Peter.

Does this mean that what I am trying to do is not supported (create,index & insert into tables in another schema) or just no recommended?

Did you manage to reproduce the problem at all?

Thanks,

Steve

Hi Steve,

This usage is supported by JChem Cartridge. (At least, I am not aware of any information which would say  it should not be.)

I will test this case soon.

Regards

Peter

Thanks Peter, your help is much appreciated.

Steve

Hi Steve,

The command sequence in the attached log file worked for me. Note that it introduces a second user who is doing the actual operations in the test_db schema.

Let me know if more clarification is needed.

Regards,

Peter

Hi Peter,

Thanks for this, but I can't see any attachment.  Do I need to do anything special to find it / get it?

Steve

 

Sorry, Steve. Probably the silly extension restrictions. I hope it will work this time.

Thanks Peter.

Is the key difference here the creation of the 'first' table / index in the test_db schema?  Does this kind of 'seed' something for use later?

Thanks

Steve

Is the key difference here the creation of the 'first' table / index in the test_db schema?

Yes,

P.

Thanks for your help here Peter.

FYI... I seem to have refined / changed this to creating a temporary database link to the 'test_db' schema (I am running this from a stored proc so I cannot use connect) and then insert one row in to the table over the DB link, index the table, and then drop the 'dummy' row. 

From then on the table seems to behave fine.

Regards,

Steve H