[20171215]oracle执行java程序.txt

[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.

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

*

Process ID: 55886

Session ID: 94 Serial number: 3

4.如何解决:

--//我测试刷新共享池不行.

SCOTT@book> alter system flush shared_pool;

System altered.

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(6,7) from dual;

select sum_java(6,7) from dual

Process ID: 56036

--//如果不重启,可以执行如下(以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');

SYS@book> alter system set java_jit_enabled=false scope=memory;

SYS@book> create or replace java system

  2  /

Java created.

--//注:这步有点慢,大约1分钟上下.

SYS@book> alter system set java_jit_enabled=true scope=memory;

SCOTT@book> select sum_java(6,7) from dual;

SUM_JAVA(6,7)

           13

GETVERSION

-----------

11.2.0.4.0

--//OK,现在能正常使用java程序了.