[Oracle]DB Link Session ORA-04068

 APEX DB 透過DB LINK呼叫EBS Package,

出現幾個怪現象:

1. 

ERROR at line 1:

ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package body "PACKAGE.NAME" has been

invalidated

ORA-06508: PL/SQL: could not find program unit being called:

"PACKAGE.NAME"

ORA-06512: at line 1

2. EBS Package 裡有使用  fnd_global.org_id, fnd_global.user_id, FND_PROFILE.VALUE('GL_SET_OF_BKS_ID')

所以APEX DB執行前會去set_client_info, fnd_global.apps_initialize.

但執行EBS package 寫入EBS table卻出現別人的user_id & org_id.

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

先是懷疑DB Link的session, 查到這篇說明

https://stackoverflow.com/questions/1761595/frequent-error-in-oracle-ora-04068-existing-state-of-packages-has-been-discarde

關鍵字: 

ALTER SESSION CLOSE  DATABASE LINK DBLINK

PRAGMA SERIALLY_REUSABLE

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

ALTER SESSION CLOSE  DATABASE LINK DBLINK:

https://www.uj5u.com/shujuku/28126.html

說明一般企業不使用DB Link原因:不外乎出問題不好查..佔資源...

那如果真的要使用, 該如何管理DB Link Session?

文中有三種方式, 個人認為1和3比較適合, 1為首選

1. 使用完後程式commit, close db link (沒有commit會出現ora-02080錯誤)

指令:

commit;

alter sesssion close database link ;

or 系統包:

DBMS_SESSION.CLOSE_DATABASE_LINK(dblink_name);

EXAMPLE:

DECLARE

  v VARCHAR2(50);

CURSOR r IS

  SELECT name FROM employee@test_db_link WHERE empno = '1';

BEGIN

OPEN r;

    LOOP

FETCH r INTO v;

EXIT WHEN r%NOTFOUND;

    END LOOP;

    CLOSE r;

    COMMIT;

    EXECUTE IMMEDIATE 'alter session close database link test_db_link';

END;

3.定期JOB Kill session

select sid,

 serial#,

 username,

 osuser,

 machine,

 'alter system kill session ' || '''' || sid || ',' || serial# ||''' immediate;' session_level

 from v$session

where machine in ('連線的DB machine1', '連線的DB machine2', '連線的DB machine3');

命令後面要加immediate, 沒加的話v$session中的STATUS列會變為KILLED，但是資源並未釋放

利用方式三可以做成JOB定時kill session，但這樣存在很大的風險。 ==> 可能不小心刪到還在使用的session?? 而且我們沒有EBS DB權限

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

PRAGMA SERIALLY_REUSABLE

https://blog.csdn.net/xiadingling/article/details/82492709

"Packaged public variables and cursors persist for the duration of a session. 

They can be shared by all subprograms that execute in the environment. 

They let you maintain data across transactions without storing it in the database."

Package裡的變數生命週期是會話(session), 所以如果在同一session執行多次 變數可能會使用到上一次執行的值.

PRAGMA SERIALLY_REUSABLE這命令使得Oracle只保持一次呼叫的值.

使用時Package & Package Body都要宣告.

缺點:每次使用都會釋放, 也因此會占用DB內存, 依照使用次數成長, 與登錄用戶數無關, 會大量使用到內存