ORA-04021：timeout occurred while waiting to lock object

1. 先查詢是否被Lock 

SELECT

b.SID,

b.USERNAME,

b.MACHINE,

b.SERIAL#

FROM

V$ACCESS a,

V$SESSION b

WHERE

a.SID = b.SID

AND a.OBJECT = '%PACKAGE_NAME%'

AND a.TYPE = 'PACKAGE';

2. 若是被Lock, 砍掉該筆session

alter system kill session 'sid,serial#'

[EBS]取得responsibility

 Useful query to get user id, responsibility id and application id in EBS.

To know about User Id use below Query:-

SQL> select user_id from fnd_user where user_name=’DOYEN’;

To know about Responsibility id use below Query:-

SQL> select responsibility_id,RESPONSIBILITY_NAME from fnd_responsibility_vl where responsibility_name like ‘%India%Local%Payables%’;

To Know about Application id use below Query:-

https://doyensys.com/blogs/useful-query-to-get-user-id-responsibility-id-and-application-id-in-ebs/

SQL>select APPLICATION_ID from fnd_responsibility_vl where responsibility_id=50366;

To Know about Org id from po:-

SQL>select org_id from po_headers_all where segment1=’&PO’;

To know about Requisition Org id from requisition number:-

$SQL>select hr.name, prh.segment1, prh.org_id from po_requisition_headers_all prh, hr_all_organization_units hr where prh.org_id = hr.organization_id and prh.segment1 = ‘&Enter_Req_Number’;

[EBS] 查看Concurrent

 SELECT DISTINCT

       fa.APPLICATION_SHORT_NAME, --application（模組）

       fat.APPLICATION_NAME,

       fcp.CONCURRENT_PROGRAM_NAME, --program（應用程式）

       fcpt.USER_CONCURRENT_PROGRAM_NAME,

       fcpT.DESCRIPTION,

       fcpt.LANGUAGE

  FROM apps.fnd_application_tl fat,

       apps.fnd_application fa,

       apps.fnd_concurrent_programs_tl fcpt,

       apps.fnd_concurrent_programs fcp,

       apps.fnd_executables fe

WHERE fat.APPLICATION_ID = fa.APPLICATION_ID

       AND fat.LANGUAGE = fcpt.LANGUAGE

       AND fa.APPLICATION_ID = fcp.APPLICATION_ID

       AND fcpt.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID

       AND fcp.executable_id = fe.executable_id

       AND fcpt.USER_CONCURRENT_PROGRAM_NAME='HUB flow concurrent'; 

       --報表名稱

      

SELECT

   distinct user_concurrent_program_name,

    responsibility_name,

    request_date,

    argument_text,

    request_id,

    phase_code,

    status_code,

    logfile_name,

    outfile_name,

    output_file_type,

    hold_flag,

    user_name

FROM

    apps.fnd_concurrent_requests fcr,

    apps.fnd_concurrent_programs_tl fcp,

    apps.fnd_responsibility_tl fr,

    apps.fnd_user fu

WHERE

    fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id

    and fcr.responsibility_id = fr.responsibility_id

    and fcr.requested_by = fu.user_id

    --and user_name = upper('HIMSINGH')

    and user_concurrent_program_name in ('deliverry flow concurrent')

    --and Phase_code='P'

ORDER BY REQUEST_DATE DESC;

--查看request執行狀況

SELECT   /*+ rule */

         rq.parent_request_id,

         rq.request_id,

         tl.user_concurrent_program_name,

         rq.actual_start_date,

         rq.actual_completion_date,

         ROUND((rq.actual_completion_date - rq.actual_start_date) * 1440, 2) run_time_min

FROM     apps.fnd_concurrent_programs_vl tl,

         apps.fnd_concurrent_requests rq

WHERE    tl.application_id = rq.program_application_id

AND      tl.concurrent_program_id = rq.concurrent_program_id

AND      rq.actual_start_date IS NOT NULL

AND      rq.actual_completion_date IS NOT NULL

AND      tl.user_concurrent_program_name = 'employee master data'

ORDER BY rq.actual_completion_date DESC;

[EBS]彈性欄位設定

1. 查看要設定的畫面Title是什麼, 請參考另一篇: [EBS]查詢某個畫面的彈性欄位

2.切換權限Application Developer", Flexfield > Descriptive > Register

按[Segments] > [New], 編輯相關欄位

退出時，check [Freeze Fexfild definitions]，並[Compile]

[EBS]查詢某個畫面的彈性欄位

 1. Situation: 想查詢訂單明細的彈性欄位:SKU ID

2. 先找出這隻程式相關資訊: 

比如說,這畫面上可以看到是Additional Line Attribute Information

或是Help > Record History 查看一下Table, 通常會跟這個名字類似...

或是Help > Diagnostics > Examine 查看(Block輸入$DESCRIPTIVE_FLEXFIELD$, Field輸入要查的項目, 按Tab查詢出Value)

3.  切換權限到Application Developer", Flexfield > Descriptive > Register

F11查詢, 例如輸入Table Name=OE_ORDER_LINES%, Ctrl+F11執行查詢.

再比對一下Title.

4. 記下Ttile再到 Flexfield > Descriptive > Segments  查詢欄位

找到SKU_ID是使用OE_ORDER_LINES_ALL.Attribute7

[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內存, 依照使用次數成長, 與登錄用戶數無關, 會大量使用到內存

Oracle PLSQL Call API

 

Ajax Callback:  呼叫DB Funcction取得值

Process_name=set_clock_in

declare

  v_clock_in varchar2(20); 

begin

  SELECT get_clock_in_fun( to_char(sysdate,'yyyy-mm-dd')) 

  into v_clock_in

  from dual;

  htp.p(v_clock_in);

end;

Javascript: AJAX Call API

apex.server.process("set_clock_in" //Process or AJAX Callback name

                    , null          //Parameter

                    , {  

                       success: function(pData){

                           apex.item("P1_CLOCK_IN").setValue(pData);

                           //alert('Record loaded successfully');

                        },

                        dataType: "text"

                    }

                   );

PLSQL Call API Function:

get_clock_in_fun

CREATE OR REPLACE function get_clock_in_fun( v_date IN varchar2)

return varchar2

is 

      -- Local variables here

      i      integer;

      l_resp clob;

      g_url  varchar2(300) := 'https://example-api.company.com/clock-in/v_date;

      v_hashkey  varchar2(128) := '.........................................................................';

      v_hashdate varchar2(20);

      v_token    varchar2(200);

      v_resp varchar2(4000);

     

      web_failure_detected EXCEPTION;

      PRAGMA EXCEPTION_INIT(web_failure_detected, -29273);

begin

    --Authorization : {Hash Key}:{UTC datetime} 

    select to_char(systimestamp at time zone 'UTC','yyyymmddhh24miss') 

    into v_hashdate

    from dual;

      

    select utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw(v_hashkey||':'||v_hashdate))) 

    into v_token

    from dual;

    --utl_encode.base64_encode()編碼生成的字串以64個字元為一組其後添加回車換行字元

    --利用正則式替換掉所有空白字元,不管是否為回車換行字元

    v_token:=regexp_replace(v_token, '\s', '');

           

    apex_web_service.g_request_headers(1).name := 'Authorization';

    apex_web_service.g_request_headers(1).value := 'Bearer '||v_token;

      

    -- Content-Type

    apex_web_service.g_request_headers(2).name := 'Content-Type';

    apex_web_service.g_request_headers(2).Value := 'text/html';

    --HTTP 411 header加入Content-Length

    apex_web_service.g_request_headers(3).name := 'Content-Length'; 

    apex_web_service.g_request_headers(3).value := 0; 

    

    l_resp := apex_web_service.make_rest_request(p_url         => g_url,

                                                 p_http_method => 'GET'

                                                   --p_parm_name  => apex_util.string_to_table('',':'),

                                                   --p_parm_value => apex_util.string_to_table('',':')

                                                   --p_wallet_path =>null,

                                                   --p_wallet_pwd  => null

                                                 );

    v_resp := substr(l_resp, 1, 4000);

    with date_array (col) as

   (select v_resp

       from dual)

    SELECT min(val) INTO v_resp FROM 

    (

select regexp_substr(replace(substr(col,instr(col,'[')+1, instr(col,']')-2), '"', ''), '[^,]+', 1, level) val

from date_array

connect by level <= regexp_count(col, ',') + 1

    );

    

    return v_resp;

EXCEPTION 

    WHEN web_failure_detected THEN

       DBMS_OUTPUT.put_line ('web failure detected');

       RETURN ':';

    WHEN OTHERS THEN 

       RETURN '--';

END;