ORA-00600 on new table in other schema

User 952e1d9361

02-06-2009 09:25:31

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                  



Many thanks,


Steve H


 


 


 

ChemAxon aa7c50abf8

02-06-2009 12:22:48

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

User 952e1d9361

02-06-2009 12:46:32

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;




The mystery to me is why it fails twice but then works fine?!


Thanks,

Steve


 


 


 

ChemAxon aa7c50abf8

03-06-2009 09:52:22

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


 

User 952e1d9361

03-06-2009 11:41:35

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

ChemAxon aa7c50abf8

04-06-2009 13:13:26

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

User 952e1d9361

04-06-2009 14:13:57

Thanks Peter, your help is much appreciated.


Steve

ChemAxon aa7c50abf8

05-06-2009 16:40:25

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

User 952e1d9361

05-06-2009 21:50:44

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


 

ChemAxon aa7c50abf8

05-06-2009 21:59:51

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

User 952e1d9361

05-06-2009 22:09:15

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

ChemAxon aa7c50abf8

07-06-2009 09:25:16

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


Yes,


P.

User 952e1d9361

08-06-2009 12:51:09

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