[20171215]oracle执行java程序.txt
--//我以前提到假如oracle调用执行java程序会在/dev/shm下建立一堆JOXSHM_EXT开头的文件(实际上在内存中)
--//如果异常关闭数据库这些文件就会留在内存中.
--//参考链接:http://blog.itpub.net/267265/viewspace-2141564/
--//如果在运行中java component发生中断,也会导致java related packages执行发生错误.
--//通过例子演示以及解决方法.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
$ ls -l /dev/shm/
total 0
2.建立java程序例子,我不熟悉java,抄一个现成的例子:
create or replace and compile java source named "math_java"
as
public class math_java
{
public static int sum_java(java.lang.Integer a, java.lang.Integer b)
{
java.lang.Integer sum;
sum = a+b;
return sum;
}
}
/
CREATE OR REPLACE FUNCTION sum_java (a NUMBER, b NUMBER) RETURN NUMBER AS LANGUAGE JAVA NAME 'math_java.sum_java(java.lang.Integer, java.lang.Integer) return java.lang.Integer' ;
/
Function created.
$ ls -l /dev/shm/
total 16
-rwxrwx--- 1 oracle oinstall 4096 2017-12-15 15:39:05 JOXSHM_EXT_0_book_229441542
-rwxrwx--- 1 oracle oinstall 4096 2017-12-15 15:39:05 JOXSHM_EXT_1_book_229441542
-rwxrwx--- 1 oracle oinstall 4096 2017-12-15 15:39:06 JOXSHM_EXT_2_book_229441542
-rwxrwx--- 1 oracle oinstall 4096 2017-12-15 15:39:06 JOXSHM_EXT_3_book_229441542
--//可以发现现在/dev/shm存在一些文件.
SCOTT@book> select sum_java(5,7) from dual;
SUM_JAVA(5,7)
-------------
12
--//ok计算正确.
3.现在删除/dev/shm的内容:
$ rm -f /dev/shm/JOXSHM_EXT_*_book_229441542
--//退出再执行(注不退出执行可以正确执行,估计文件描述没有释放的原因).
SCOTT@book> select sum_java(5,7) from dual;
select sum_java(5,7) from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 55861
Session ID: 274 Serial number: 7
SCOTT@book> SELECT DBMS_JAVA.GETVERSION FROM dual;
SELECT DBMS_JAVA.GETVERSION FROM dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 55886
Session ID: 94 Serial number: 3
4.如何解决:
--//我测试刷新共享池不行.
SCOTT@book> alter system flush shared_pool;
System altered.
SCOTT@book> select sum_java(5,7) from dual;
select sum_java(5,7) from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 55900
Session ID: 106 Serial number: 7
--//重启数据库当然是一种解决问题的方法.
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area 634732544 bytes
Fixed Size 2255792 bytes
Variable Size 197133392 bytes
Database Buffers 427819008 bytes
Redo Buffers 7524352 bytes
Database mounted.
Database opened.
SCOTT@book> select sum_java(5,7) from dual;
SUM_JAVA(5,7)
-------------
12
--//重复前面的删除操作:
SCOTT@book> select sum_java(6,7) from dual;
select sum_java(6,7) from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 56036
Session ID: 274 Serial number: 7
--//如果不重启,可以执行如下(以sys用户执行):
execute sys.dbms_registry.loaded('JAVAVM');
execute sys.dbms_registry.valid('JAVAVM');
alter system set java_jit_enabled=false scope=memory;
create or replace java system
/
alter system set java_jit_enabled=true scope=memory;
SYS@book> execute sys.dbms_registry.loaded('JAVAVM');
PL/SQL procedure successfully completed.
SYS@book> execute sys.dbms_registry.valid('JAVAVM');
PL/SQL procedure successfully completed.
SYS@book> alter system set java_jit_enabled=false scope=memory;
System altered.
SYS@book> create or replace java system
2 /
Java created.
--//注:这步有点慢,大约1分钟上下.
SYS@book> alter system set java_jit_enabled=true scope=memory;
System altered.
SCOTT@book> select sum_java(6,7) from dual;
SUM_JAVA(6,7)
-------------
13
SCOTT@book> SELECT DBMS_JAVA.GETVERSION FROM dual;
GETVERSION
-----------
11.2.0.4.0
--//OK,现在能正常使用java程序了.
