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

$ 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程式了.