CU locks????
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: CU locks????

  1. #1
    Join Date
    Feb 2000
    Posts
    142
    Hi,
    I am running a procedure which is within a package on dev(8.0.5) but it just does not come back. It hangs with no errors. It works fine on QC environment(8.1.6).

    I checked v$lock and it gives me 'CU' in 'Type' column. Any ideas what this is about? How do I resolve this issue?

    Please help. URGENT.


    Thanks.



  2. #2
    Join Date
    Feb 2001
    Posts
    75

    CU lock

    Hi,

    Cu lock type is for cursor bind. Ref. V$lock in oracle documentation. I can not say much about your problem, unless I have more details of your procedure etc.

    Kailash Pareek

  3. #3
    Join Date
    Feb 2000
    Posts
    142
    The procedure is as follows: Can you help me? It's urgent!!!

    Thanks.




    Procedure: SP_Tran_Summary
    -- Modification History
    -- Person Date Comments
    -- -------------------------------------------------
    -- Krish 8/19/99 Initial Coding
    -- Laya 9/13/1999 Changed Pcurrency parameter to pccy_acct
    --------------------------------------------------------------------------------------------------------

    Procedure SP_Tran_Summary(
    pbranch IN varchar2 := '*',
    pcustomer IN varchar2 := '*',
    paccount IN varchar2 := '*',
    pacctgrp IN varchar2 := '*',
    ENTRY_DATE_RANGE IN varchar2 := '*',
    pvdaterange IN varchar2 := '*',
    PAMTRANGE IN varchar2 := '*',
    PDEBITCREDIT IN varchar2 := '*',
    prodtype IN varchar2 := '*',
    -- ptrantyp IN varchar2 := '*',
    SYSCLIENTID IN varchar2 := '*',
    SYSLANGUAGE IN varchar2 := 'en_US',
    SYSORACHARSET IN varchar2 := '*',
    pccy_acct IN varchar2 := '*',
    SYSBASECURRENCY IN varchar2 := '*',
    pccy_tran IN varchar2 := '*',
    PTRANDESC IN varchar2 := '*',
    SYSUSERID IN varchar2 := '*',
    PCHQONLY IN varchar2 := '*',
    Tran_Summ_Cur In Out btr_types.Tran_Summ_Curr);
    Pragma RESTRICT_REFERENCES(SP_Tran_Summary, WNDS);




    Procedure: SP_Tran_Summary
    -- Modification History
    -- Person Date Comments
    -- -------------------------------------------------
    -- Krish 8/17/99 Initial Coding
    -- Krish 9/08/99 Modified joins for type_values
    -- Laya 9/13/1999 Modified to Change parameter from Currency to Account Currency
    --------------------------------------------------------------------------------------------------------

    Procedure SP_Tran_Summary(
    pbranch IN varchar2 := '*',
    pcustomer IN varchar2 := '*',
    paccount IN varchar2 := '*',
    pacctgrp IN varchar2 := '*',
    ENTRY_DATE_RANGE IN varchar2 := '*',
    pvdaterange IN varchar2 := '*',
    PAMTRANGE IN varchar2 := '*',
    PDEBITCREDIT IN varchar2 := '*',
    prodtype IN varchar2 := '*',
    -- ptrantyp IN varchar2 := '*',
    SYSCLIENTID IN varchar2 := '*',
    SYSLANGUAGE IN varchar2 := 'en_US',
    SYSORACHARSET IN varchar2,
    pccy_acct IN varchar2 := '*',
    SYSBASECURRENCY IN varchar2 := '*',
    pccy_tran IN varchar2 := '*',
    PTRANDESC IN varchar2 := '*',
    SYSUSERID IN varchar2 := '*',
    PCHQONLY IN varchar2 := 'INC_CHQ',
    Tran_Summ_Cur In Out btr_types.Tran_Summ_Curr) is

    Entry_FromDate Date;
    Entry_ToDate Date;
    Value_FromDate Date;
    Value_ToDate Date;
    Amt_From number;
    Amt_To number;
    vc_pCustomer rpt_param.rpt_nam%type;
    vc_pAccount rpt_param.rpt_nam%type;
    row_cnt integer;
    row_limit integer;

    vc_tran_sel_req varchar2(1);

    BEGIN
    Value_FromDate := to_date(rpad(substr(PvdateRange, 1, instr(PvdateRange,'To')-2 ),17,' 00:00:00'),'YYYYMMdd HH24:MI:SS');
    Value_ToDate := to_date(RPAD(substr(PvdateRange, instr(PvdateRange,'To')+3),17,' 23:59:59'),'YYYYMMdd HH24:MI:SS');
    Entry_FromDate := to_date(rpad(substr(entry_date_Range, 1, instr(entry_date_Range,'To')-2 ),17,' 00:00:00'),'YYYYMMdd HH24:MI:SS');
    Entry_ToDate := to_date(RPAD(substr(entry_date_Range, instr(entry_date_Range,'To')+3),17,' 23:59:59'),'YYYYMMdd HH24:MI:SS');
    Amt_From := To_Number(substr(PAMTRANGE, 1, instr(PAMTRANGE,'To')-2));
    Amt_To := To_Number(substr(PAMTRANGE, instr(PAMTRANGE,'To')+3));
    vc_pCustomer := pCustomer;
    vc_pAccount := pAccount;
    row_cnt :=0;
    -- row_limit := 5000;
    vc_tran_sel_req := '';

    select TO_NUMBER(rpt_lbl_des)
    into row_limit
    from report_label
    where rpt_lbl_nam = 'LBL_TXN_SUMM_RPT_LMT'
    and lang_id = 'en_US';

    -- row_limit := 1000;

    if substr(pCustomer,1,2) = '*:' Then
    vc_pCustomer := substr(pCustomer,3);
    end if;

    if substr(pAccount,1,2) = '*:' Then
    vc_pAccount := substr(pAccount,3);
    end if;

    if Entry_Date_Range = '*' and pvdaterange = '*' and
    pccy_tran = '*' and prodtype = '*' and PCHQONLY = '*' and
    pamtrange = '*' and PDEBITCREDIT = '*' then
    vc_tran_sel_req := 'N';
    else
    vc_tran_sel_req := 'Y';
    end if;


    row_cnt := BTR_UTIL.get_row_count('TRAN_DETL',
    pbranch,
    vc_pcustomer,
    vc_paccount,
    pccy_acct,
    Entry_Date_Range,
    Entry_FromDate,
    Entry_ToDate,
    pvdaterange,
    Value_FromDate,
    Value_ToDate,
    '*',
    '*',
    null,
    null,
    '*',
    pacctgrp,
    '*',
    pamtrange,
    Amt_From,
    Amt_To,
    '*',
    pccy_tran,
    prodtype,
    '*',
    PDEBITCREDIT,
    PCHQONLY,
    ptrandesc,
    syslanguage,
    sysoracharset,
    row_limit,
    sysclientid,
    sysuserid);



    if row_cnt > row_limit then

    if Entry_Date_Range = '*' and pvdaterange = '*' and
    pccy_tran = '*' and prodtype = '*' and PCHQONLY = '*' and
    pamtrange = '*' and PDEBITCREDIT = '*' and ptrandesc = '*' then
    open tran_summ_cur for
    SELECT /*+ ORDERED FIRST_ROWS PUSH_SUBQ index(asd asd_ndx_3) index(s1 rds_statement_idx_5) */
    null as brn_nm,
    asd.br_cod as brn_no,
    null as bnk_nm,
    asd.acct_no as acct_no,
    null as acct_typ,
    null as acct_id,
    nvl(asd.ap1_acct_fam_nam,asd.ap2_acct_fam_nam) as acct_nm,
    null as acct_grp,
    null as acct_grp_desc,
    null as cust_no,
    null as cust_nm,
    null as cr_dr_mrk,
    s1.stmt_dt as ent_dt, -- 5/31 changed postdt to stmt_dt for short rpt
    null as prod_typ,
    null as prod_typ_des,
    null as tran_no,
    null as tran_des,
    null as swift_tran_cod,
    null as val_dt,
    null as bnk_ref,
    null as cust_ref,
    null as ft_confirm_ref,
    null as tran_ccy,
    null as acct_ccy,
    s1.cr_cnt_posn as tran_ccy_decimal_cnt, -- bogus field for rpt 5/31
    s1.dr_cnt_posn as acct_ccy_decimal_cnt,-- bogus field for rpt 5/31
    null as base_ccy_decimal_cnt,
    0 as tran_amt,

    null as by_ord_of,
    0 as row_num,
    null as Conv_rt,
    null as Math_Actn

    from
    acct_static_details asd,
    rds_statement s1


    Where
    asd.acct_id = s1.acct_id and
    asd.acct_sta_typ = s1.acct_sta_typ and
    s1.actv_ind = 'Y' and -- added 2/25/2000



    (asd.clnt_id = SYSCLIENTID) and

    (pbranch = '*' or
    (asd.br_cod in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = pbranch and
    rp.user_id = sysuserid and
    rp.field_name = 'PBRANCH'))) and

    (vc_pcustomer ='*' or
    (asd.gfcid_id in(Select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_pcustomer and
    rp.user_id = sysuserid and
    rp.field_name = 'PCUSTOMER')) or
    (asd.cust_bas_no_id in(Select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_pcustomer and
    rp.user_id = sysuserid and
    rp.field_name = 'PCUSTOMER'))) and

    (vc_paccount = '*' or
    (asd.acct_id in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_paccount and
    rp.user_id = sysuserid and
    rp.field_name = 'PACCOUNT'))) and

    (pccy_acct = '*' or
    (asd.acct_ccy in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = pccy_acct and
    rp.user_id = sysuserid and
    rp.field_name = 'PCCY_ACCT'))) and

    (pacctgrp = '*' or
    (asd.acct_id in (select cl.acct_id from account a1,client_group_accounts cl
    where a1.acct_id = cl.acct_id and cl.acct_grp_id in
    (select field_value_item from RPT_PARAM rp
    where rp.rpt_nam = pacctgrp and
    rp.user_id = sysuserid and
    rp.field_name = 'PACCTGRP'))));


    else
    open tran_summ_cur for
    SELECT /*+ ORDERED FIRST_ROWS PUSH_SUBQ index(asd asd_ndx_3) index(s1 rds_statement_idx_5) */
    null as brn_nm,
    asd.br_cod as brn_no,
    null as bnk_nm,
    asd.acct_no as acct_no,
    null as acct_typ,
    null as acct_id,
    nvl(asd.ap1_acct_fam_nam,asd.ap2_acct_fam_nam) as acct_nm,
    null as acct_grp,
    null as acct_grp_desc,
    null as cust_no,
    null as cust_nm,
    null as cr_dr_mrk,
    s1.stmt_dt as ent_dt, -- 5/31 changed postdt to stmt_dt for short rpt
    null as prod_typ,
    null as prod_typ_des,
    null as tran_no,
    null as tran_des,
    null as swift_tran_cod,
    null as val_dt,
    null as bnk_ref,
    null as cust_ref,
    null as ft_confirm_ref,
    null as tran_ccy,
    null as acct_ccy,
    s1.cr_cnt_posn as tran_ccy_decimal_cnt, -- bogus field for rpt 5/31
    s1.dr_cnt_posn as acct_ccy_decimal_cnt,-- bogus field for rpt 5/31
    null as base_ccy_decimal_cnt,
    0 as tran_amt,

    null as by_ord_of,
    0 as row_num,
    null as Conv_rt,
    null as Math_Actn

    from
    acct_static_details asd,
    rds_statement s1


    Where
    asd.acct_id = s1.acct_id and
    asd.acct_sta_typ = s1.acct_sta_typ and
    s1.actv_ind = 'Y' and -- added 2/25/2000


    (asd.clnt_id = SYSCLIENTID) and

    (pbranch = '*' or
    (asd.br_cod in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = pbranch and
    rp.user_id = sysuserid and
    rp.field_name = 'PBRANCH'))) and

    (vc_pcustomer ='*' or
    (asd.gfcid_id in(Select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_pcustomer and
    rp.user_id = sysuserid and
    rp.field_name = 'PCUSTOMER')) or
    (asd.cust_bas_no_id in(Select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_pcustomer and
    rp.user_id = sysuserid and
    rp.field_name = 'PCUSTOMER'))) and

    (vc_paccount = '*' or
    (asd.acct_id in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_paccount and
    rp.user_id = sysuserid and
    rp.field_name = 'PACCOUNT'))) and

    (pccy_acct = '*' or
    (asd.acct_ccy in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = pccy_acct and
    rp.user_id = sysuserid and
    rp.field_name = 'PCCY_ACCT'))) and

    (pacctgrp = '*' or
    (asd.acct_id in (select cl.acct_id from account a1,client_group_accounts cl
    where a1.acct_id = cl.acct_id and cl.acct_grp_id in
    (select field_value_item from RPT_PARAM rp
    where rp.rpt_nam = pacctgrp and
    rp.user_id = sysuserid and
    rp.field_name = 'PACCTGRP')))) and

    s1.stmt_no in
    ( select
    t1.stmt_no from rds_transaction t1 where

    t1.backval_ind is null and

    ((ENTRY_DATE_RANGE ='*' ) or
    ((t1.tran_post_dt) between Entry_FromDate and Entry_ToDate)) and
    ((pvdaterange ='*' ) or
    ((t1.tran_val_dt) between Value_FromDate and Value_ToDate)) and

    ((PCHQONLY = 'NO_CHQ' AND T1.SWIFT_TRAN_COD <> 'CHK') OR
    (PCHQONLY = 'ONLY_CHQ' AND T1.SWIFT_TRAN_COD = 'CHK') OR
    (PCHQONLY = 'INC_CHQ' ) ) and

    (PTRANDESC = '*' OR
    (nvl(decode(t1.tran_cod,'MSC',NULL,convert((btr_util.get_tran_desc(t1.tran_cod,SYSLANGUAGE,asd.brn_i d)),SYSORACHARSET)),
    t1.fthr_ref_data) = ptrandesc) ) and

    ((PAMTRANGE = '*') or
    (DECODE(t1.cr_dr_mrk, 'D ' , -1.0*t1.tran_amt,
    'RC', -1.0*t1.tran_amt,
    t1.tran_amt) between Amt_From and Amt_To)) and

    (pccy_tran = '*' or
    (t1.iso_ccy_cod in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = pccy_tran and
    rp.user_id = sysuserid and
    rp.field_name = 'PCCY_TRAN'))) and
    (prodtype = '*' or
    (t1.prod_typ in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = prodtype and
    rp.user_id = sysuserid and
    rp.field_name = 'PRODTYPE'))) and

    ((PCHQONLY = 'NO_CHQ' AND T1.SWIFT_TRAN_COD <> 'CHK') OR
    (PCHQONLY = 'ONLY_CHQ' AND T1.SWIFT_TRAN_COD = 'CHK') OR
    (PCHQONLY = 'INC_CHQ' ) ) and

    ((PDEBITCREDIT = '*') OR
    (PDEBITCREDIT = 'B' AND (CR_DR_MRK = 'C' OR CR_DR_MRK = 'RC' OR CR_DR_MRK = 'D' OR CR_DR_MRK = 'RD')) OR
    (PDEBITCREDIT = 'C' AND (CR_DR_MRK = 'C' OR CR_DR_MRK = 'RD')) OR
    (PDEBITCREDIT = 'D' AND (CR_DR_MRK = 'D' OR CR_DR_MRK = 'RC'))) and

    s1.stmt_no = t1.stmt_no);


    end if;
    else

    open tran_summ_cur for

    SELECT /*+ ORDERED FIRST_ROWS PUSH_SUBQ INDEX(ASD ASD_NDX_3) INDEX(s1 rds_statement_idx_5) INDEX(t1 rds_transaction_ndx_5) */

    asd.brn_nm as brn_nm,
    asd.br_cod as brn_no,
    asd.bnk_nm as bnk_nm,
    asd.acct_no as acct_no,
    nvl(convert(atm.acct_typ_des,SYSORACHARSET),ate.acct_typ_des) as acct_typ,
    asd.acct_id as acct_id,
    nvl(asd.ap1_acct_fam_nam,asd.ap2_acct_fam_nam) as acct_nm,
    cl.acct_grp_id as acct_grp,
    cag.acct_grp_des as acct_grp_desc,
    asd.cust_ref_id as cust_no,
    asd.lgl_nam as cust_nm,
    t1.cr_dr_mrk as cr_dr_mrk,
    t1.tran_post_dt as ent_dt,
    t1.prod_typ as prod_typ,
    nvl(convert(tvm_1.typ_val_des,SYSORACHARSET),tve_1.typ_val_des) as prod_typ_des,
    t1.tran_no as tran_no,
    nvl(decode(t1.tran_cod,'MSC',NULL,convert((btr_util.get_tran_desc(t1.tran_cod,SYSLANGUAGE,asd.brn_id )),SYSORACHARSET)),
    nvl(t1.fthr_ref_data,nvl(convert(tvm_2.typ_val_des,SYSORACHARSET),tve_2.typ_val_des))) as tran_des,
    t1.swift_tran_cod as swift_tran_cod,
    t1.tran_val_dt as val_dt,
    CONVERT(t1.othr_ref_no,SYSORACHARSET) as bnk_ref,
    CONVERT(t1.ref_no,SYSORACHARSET) as cust_ref,
    nvl(CONVERT(tp2.ptcp_bank_rte_sys_id,SYSORACHARSET),CONVERT(tp3.ptcp_bank_rte_sys_id,SYSORACHARSET)) as ft_confirm_ref,
    t1.iso_ccy_cod as tran_ccy,
    -- CONVERT(tp2.ptcp_bank_rte_sys_id,SYSORACHARSET) as ft_fed_imad_no,
    -- CONVERT(tp3.ptcp_bank_rte_sys_id,SYSORACHARSET) as ft_chips_ssn,
    asd.acct_ccy as acct_ccy,
    tc.ccy_deci_plac as tran_ccy_decimal_cnt,
    ac.ccy_deci_plac as acct_ccy_decimal_cnt,
    ec.ccy_deci_plac as base_ccy_decimal_cnt,
    DECODE(t1.cr_dr_mrk, 'D ' , -1.0*t1.tran_amt,
    'RC', -1.0*t1.tran_amt,
    t1.tran_amt) as tran_amt,

    -- NVL(CONVERT(tp1.ptcp_pty_nam,SYSORACHARSET), CONVERT(tp4.ptcp_pty_nam,SYSORACHARSET)) as by_ord_of,
    DECODE(t1.CR_DR_MRK,'C ', CONVERT(tp4.ptcp_pty_nam,SYSORACHARSET),'RD', CONVERT(tp4.ptcp_pty_nam,SYSORACHARSET),
    'D ', CONVERT(tp1.ptcp_pty_nam,SYSORACHARSET),'RC', CONVERT(tp1.ptcp_pty_nam,SYSORACHARSET),' ')
    as by_ord_of,

    --row_num needed to make rows with same tran_amt unique on reports
    ROWNUM as row_num,
    NVL(Decode(e4.math_actn,'M',
    (decode(e3.math_actn,'M',1 / e3.conv_rt,1 * e3.conv_rt))* e4.conv_rt,
    (decode(e3.math_actn,'M',1 / e3.conv_rt,1 * e3.conv_rt))/ e4.conv_rt)

    ,NVL((decode(e2.math_actn,'M',(decode(e1.math_actn,'M',1 * e1.conv_rt,1 / e1.conv_rt))/
    e2.conv_rt,(decode(e1.math_actn,'M',1 * e1.conv_rt,1 / e1.conv_rt))*e2.conv_rt)),

    NVL((decode(e6.math_actn,'M',(decode(e5.math_actn,'M',1 * e5.conv_rt,1 / e5.conv_rt))/
    e6.conv_rt,(decode(e5.math_actn,'M',1 * e5.conv_rt,1 / e5.conv_rt))*e6.conv_rt)) ,

    NVL( (decode(e2.math_actn,'M',(decode(e5.math_actn,'M',1 * e5.conv_rt,1 / e5.conv_rt))/
    e2.conv_rt,(decode(e5.math_actn,'M',1 * e5.conv_rt,1 / e5.conv_rt))*e2.conv_rt)) ,

    (decode(e6.math_actn,'M',(decode(e1.math_actn,'M',1 * e1.conv_rt,1 / e1.conv_rt))/
    e6.conv_rt,(decode(e1.math_actn,'M',1 * e1.conv_rt,1 / e1.conv_rt))*e6.conv_rt) ))))) as conv_rt,
    ' ' as Math_Actn

    from
    acct_static_details asd,
    rds_statement s1,
    rds_transaction t1,
    rds_thrd_pty_info tp1,
    rds_thrd_pty_info tp2,
    rds_thrd_pty_info tp3,
    rds_thrd_pty_info tp4,
    client_group_accounts cl,
    client_account_group cag,
    type_values tvm_1,
    type_values tve_1,
    type_values tvm_2,
    type_values tve_2,
    account_type ate,
    account_type atm,
    -- type_values tvm_3,
    -- type_values tve_3,
    currency ac,
    currency tc,
    currency ec,
    exchange_rate e1,
    exchange_rate e2,
    exchange_rate e3,
    exchange_rate e4,
    exchange_rate e5,
    exchange_rate e6

    ,
    (select pty_id as c2_pty_id,pty_sta_typ as c2_pty_sta_typ,
    pty_role_typ as c2_pty_role_typ,acct_id as c2_acct_id,
    acct_sta_typ as c2_acct_sta_typ,acct_pty_sta_typ as c2_acct_pty_sta_typ,
    min(acct_grp_id)AS c2_acct_grp_id from
    client_group_accounts c2
    where acct_grp_sta_typ = 'PROCESSED'
    group by pty_id,pty_sta_typ,pty_role_typ,
    acct_id, acct_sta_typ,acct_pty_sta_typ)


    Where

    asd.acct_id = s1.acct_id and
    asd.acct_sta_typ = s1.acct_sta_typ and
    -- s1.acct_id = t1.acct_id and
    s1.stmt_no = t1.stmt_no and
    s1.actv_ind = 'Y' and -- added 2/25/2000
    t1.backval_ind is null and

    asd.clnt_id = c2_pty_id(+) and
    asd.ap_pty_sta_typ = c2_pty_sta_typ(+) and
    asd.ap_pty_role_typ = c2_pty_role_typ(+) and
    asd.acct_id = c2_acct_id(+) and
    asd.ap_acct_sta_typ = c2_acct_sta_typ(+) and
    asd.ap_acct_pty_sta_typ = c2_acct_pty_sta_typ(+) and

    asd.clnt_id = cl.pty_id(+) and
    asd.ap_pty_sta_typ = cl.pty_sta_typ(+) and
    asd.ap_pty_role_typ = cl.pty_role_typ(+) and
    asd.acct_id = cl.acct_id(+) and
    asd.ap_acct_sta_typ = cl.acct_sta_typ(+) and
    asd.ap_acct_pty_sta_typ = cl.acct_pty_sta_typ(+) and
    cl.acct_grp_sta_typ(+) = 'PROCESSED' and
    cl.acct_grp_sta_typ = cag.acct_grp_sta_typ(+) and

    cl.acct_grp_id = cag.acct_grp_id(+) and

    t1.tran_no = tp1.tran_no(+) and
    tp1.tpty_typ(+) = 'BENE' and

    t1.tran_no = tp4.tran_no(+) and
    tp4.tpty_typ(+) = 'ORDPTY' and

    t1.tran_no = tp2.tran_no(+) and
    tp2.ptcp_bank_rte_sys_cod(+) = 'FW' and

    t1.tran_no = tp3.tran_no(+) and
    tp3.ptcp_bank_rte_sys_cod(+) = 'CH' and

    t1.prod_typ = tvm_1.typ_val(+) and
    tvm_1.typ_def(+) = 'PROD_TYP' and
    t1.prod_typ = tve_1.typ_val(+) and
    tve_1.typ_def(+) = 'PROD_TYP' and
    tve_1.lang_id(+) = 'en_US' and

    t1.swift_tran_cod = tvm_2.typ_val(+) and
    tvm_2.typ_def(+) = 'SWIFTTRAN' and
    t1.swift_tran_cod = tve_2.typ_val(+) and
    tve_2.typ_def(+) = 'SWIFTTRAN' and
    tve_2.lang_id(+) = 'en_US' and


    asd.brn_id = ate.pty_id(+) and
    ate.pty_sta_typ(+) = 'PROCESSED' and
    asd.acct_typ = ate.acct_typ(+) and
    ate.lang_id(+) = 'en_US' and

    asd.brn_id = atm.pty_id(+) and
    atm.pty_sta_typ(+) = 'PROCESSED' and
    asd.acct_typ = atm.acct_typ(+) and
    atm.lang_id(+) = SYSLANGUAGE and

    tc.iso_ccy_cod(+) = t1.iso_ccy_cod and
    ac.iso_ccy_cod(+) = asd.acct_ccy and

    e1.iso_ccy_cod(+) = t1.iso_ccy_cod and
    ec.iso_ccy_cod(+) = SYSBASECURRENCY and

    e1.bas_ccy_cod(+) = 'USD' and
    e2.bas_ccy_cod(+) = 'USD' and
    e1.prod_src_typ(+) = 'MCP' and
    e2.prod_src_typ(+) = 'MCP' and
    e1.actv_ind(+) = 'Y' and
    e2.actv_ind(+) = 'Y' and
    e2.iso_ccy_cod(+) = ec.iso_ccy_cod and

    e3.bas_ccy_cod(+) = 'EUR' and
    e3.prod_src_typ(+) = 'EMU' and
    e3.actv_ind(+) = 'Y' and
    e3.iso_ccy_cod(+) = ec.iso_ccy_cod and

    e4.bas_ccy_cod(+) = 'EUR' and
    e4.prod_src_typ(+) = 'EMU' and
    e4.actv_ind(+) = 'Y' and
    e4.iso_ccy_cod(+) = t1.iso_ccy_cod and

    -- CHECK NYRS IF MCP MISSING 6/27/01 CHANGE T. HINES

    e5.iso_ccy_cod(+) = t1.iso_ccy_cod and
    e5.bas_ccy_cod(+) = 'USD' and
    e5.prod_src_typ(+) = 'NYRS' and
    e5.exch_rt_typ(+) = 'SPOT' and
    e5.actv_ind(+) = 'Y' and

    e6.bas_ccy_cod(+) = 'USD' and
    e6.prod_src_typ(+) = 'NYRS' and
    e6.exch_rt_typ(+) = 'SPOT' and
    e6.actv_ind(+) = 'Y' and
    e6.iso_ccy_cod(+) = ec.iso_ccy_cod and


    (asd.clnt_id = SYSCLIENTID) and


    (pbranch = '*' or
    (asd.br_cod in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = pbranch and
    rp.user_id = sysuserid and
    rp.field_name = 'PBRANCH'))) and


    (vc_pcustomer ='*' or
    (asd.gfcid_id in(Select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_pcustomer and
    rp.user_id = sysuserid and
    rp.field_name = 'PCUSTOMER')) or
    (asd.cust_bas_no_id in(Select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_pcustomer and
    rp.user_id = sysuserid and
    rp.field_name = 'PCUSTOMER'))) and


    (vc_paccount = '*' or
    (asd.acct_id in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = vc_paccount and
    rp.user_id = sysuserid and
    rp.field_name = 'PACCOUNT'))) and

    (pccy_acct = '*' or

    (asd.acct_ccy in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = pccy_acct and
    rp.user_id = sysuserid and
    rp.field_name = 'PCCY_ACCT'))) and

    (pccy_tran = '*' or
    (t1.iso_ccy_cod in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = pccy_tran and
    rp.user_id = sysuserid and
    rp.field_name = 'PCCY_TRAN'))) and


    ((ENTRY_DATE_RANGE ='*' ) or
    ((t1.tran_post_dt) between Entry_FromDate and Entry_ToDate )) and


    ((pvdaterange ='*' ) or
    ((t1.tran_val_dt) between Value_FromDate and Value_ToDate)) and



    ((PCHQONLY = 'NO_CHQ' AND T1.SWIFT_TRAN_COD <> 'CHK') OR
    (PCHQONLY = 'ONLY_CHQ' AND T1.SWIFT_TRAN_COD = 'CHK') OR
    (PCHQONLY = 'INC_CHQ' ) ) and


    ((PAMTRANGE = '*') or
    (DECODE(t1.cr_dr_mrk, 'D ' , -1.0*t1.tran_amt,
    'RC', -1.0*t1.tran_amt,
    t1.tran_amt) between Amt_From and Amt_To)) and

    ((PDEBITCREDIT = '*') OR
    (PDEBITCREDIT = 'B' AND (t1.CR_DR_MRK = 'C' OR t1.CR_DR_MRK = 'RC' OR t1.CR_DR_MRK = 'D' OR t1.CR_DR_MRK = 'RD')) OR
    (PDEBITCREDIT = 'C' AND (t1.CR_DR_MRK = 'C' OR t1.CR_DR_MRK = 'RD')) OR
    (PDEBITCREDIT = 'D' AND (t1.CR_DR_MRK = 'D' OR t1.CR_DR_MRK = 'RC'))) AND



    (PTRANDESC = '*' OR
    (nvl(decode(t1.tran_cod,'MSC',NULL,convert((btr_util.get_tran_desc(t1.tran_cod,SYSLANGUAGE,asd.brn_i d)),SYSORACHARSET)),
    nvl(t1.fthr_ref_data,nvl(convert(tvm_2.typ_val_des,SYSORACHARSET),tve_2.typ_val_des))) = ptrandesc) ) and

    (prodtype = '*' or
    (t1.prod_typ in (select field_value_item
    from RPT_PARAM rp
    where rp.rpt_nam = prodtype and
    rp.user_id = sysuserid and
    rp.field_name = 'PRODTYPE'))) and


    (pacctgrp = '*' or
    (asd.acct_id in (select cl.acct_id from acct_static_details asd1,client_group_accounts cl
    where asd1.acct_id = cl.acct_id and cl.pty_id = SYSCLIENTID and cl.acct_grp_id in
    (select field_value_item from RPT_PARAM rp
    where rp.rpt_nam = pacctgrp and
    rp.user_id = sysuserid and
    rp.field_name = 'PACCTGRP')))) and


    /*
    ((pacctgrp = '*' and
    cl.acct_grp_id = c2_acct_grp_id or
    cl.acct_grp_id is null) or
    (pacctgrp <> '*')) and


    (pacctgrp = '*' or
    (cl.acct_grp_id in
    (select field_value_item from RPT_PARAM rp
    where rp.rpt_nam = PACCTGRP and
    rp.user_id = sysuserid and
    rp.field_name = 'PACCTGRP'))) and

    -- combined both below

    */

    ((pacctgrp = '*' and
    cl.acct_grp_id = c2_acct_grp_id or
    cl.acct_grp_id is null) or
    ( cl.acct_grp_id in
    (select field_value_item from RPT_PARAM rp
    where rp.rpt_nam = PACCTGRP and
    rp.user_id = sysuserid and
    rp.field_name = 'PACCTGRP')) ) and

    tvm_1.lang_id(+) = SYSLANGUAGE and
    tvm_2.lang_id(+) = SYSLANGUAGE ;


    end if;
    END SP_TRAN_SUMMARY;





  4. #4
    Join Date
    Feb 2000
    Posts
    142

    URGENT!!!!

    Can somebody pleaaaaaaaaaaaase help me? It's urgent!!

    Thanks.

  5. #5
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    Not sure if I can help much, but you may want to trace the session that's running the procedure to see where the hang issue is occurring. I beleive the CU is an enqueue wait lock. Here is an article I found on Metalink that may be able to help you out. Good luck.


    Note:102925.1
    Subject: Tracing sessions: waiting on an enqueue
    Type: BULLETIN
    Status: PUBLISHED
    Content Type: TEXT/PLAIN
    Creation Date: 21-MAR-2000
    Last Revision Date: 25-APR-2001


    PURPOSE
    -------

    How to monitor and trace locking problems (enqueues).


    SCOPE
    -----

    DBA's and people working with technical support.


    APPLICATION
    -----------

    This bulletin will assist you when:

    1. Requiring to figure out if an instance has had locking problems with
    enqueues. See section "Has locking been a problem".

    2. Are there sessions just now in the instance that are waiting on
    enqueues and who are the blockers. See "Is locking a problem just now".


    Abstract
    --------

    What are locks, enqueues and latches?
    What are the common types of enqueues?
    Has locking been a problem?
    Is locking a problem just now?

    All those questions will be answered in this document.

    To get better performance (get a better flow), one approach is to
    eliminate the time a session is waiting to get a resource (for example
    an enqueue). You donít want sessions that are waiting on other sessions,
    because those waiting sessions may also hold enqueues and resources, which
    will make other sessions wait for them - which can end up with a deadlock.

    ===============================================================================

    What are locks, enqueues and latches
    -----------------------------------

    Often the word "lock" is synonymous to enqueue. In this document, "enqueue"
    will be used for the locking mechanism that you can find in V$LOCK. "locking"
    will be used when talking about requesting an enqueue, waiting on an enqueue
    and blocking others while holding the enqueue.

    A lock is a resource that you need to hold to get access to the resource.
    Oracle has two kind of locks: enqueues and latches. This bulletin will not
    talk about latches. A latch is a fast, inexpensive and non-sophisticated
    lock. A latch is used when you need serial access to operations/functions
    in Oracle. There is no ordered queue for the waiters on a latch - first to
    grab it gets it.

    Enqueues are sophisticated locks for managing access to shared resources
    (like tables, rows, jobs, redo threads). An enqueue can be requested in
    different levels/mode: null, row share, row exclusive, share, share row
    exclusive or exclusive. If a session holds an enqueue in share mode, other
    sessions can then also take the enqueue in share mode (for the same resource).
    If a session holds an enqueue in exclusive mode, other sessions that wants to
    get it - independently in which level - they have to wait.

    When access is required by a session, a lock structure is obtained and a
    request is made to acquire access to the resource at a specific level (mode)
    is made. The lock structure is placed on one of three linked lists which hang
    off of the resource, called the OWNER (if the enqueue could be acquired),
    WAITER (if the session is waiting to acquiring the enqueue) and CONVERTER
    (the session is holding the enqueue in one level but wants to convert it to
    another) lists.

    An enqueue is uniquely defined by it's TYPE, ID1 and ID2 (which are columns
    in the V$LOCK view). For example can there only be one enqueue for user
    SCOTT's EMP table (identified by TYPE=TM, ID1=, ID2=0).


    Common types of enqueues
    ------------------------

    ∑ JQ - Job Queue. When a job (submitted by DBMS_JOB.SUBMIT) is running,
    it is protected by a JQ enqueue (which means that only one SNP-process
    can run the job).

    ∑ ST - Space management Transaction. The ST enqueue is need to be held
    every time the session is allocating/deallocating extents (which means
    wants to change the UET$ and FET$ dictionary tables), like coalescing,
    drop/truncate segments and disk -sorting. If the session gets a timeout
    when requesting the ST enqueue, "ORA-1575 timeout waiting for space
    management" is returned.

    ∑ TM - DML (Table) enqueue. Every time a session wants to lock a table,
    a TM enqueue is requested. If a session deletes a row in the parent-table
    (DEPT) and a referential constraint (foreign key) is created without
    an index on the child-table (EMP), or if the session is updating the
    column(s) that the foreign key references to then a share lock (level 4)
    is taken on the child table. If another session tries to do changes to
    the child-table they have to wait (because they want the enqueue in row
    exclusive mode, and that is not compatible with the share mode). If an
    index is created on the child-tableís foreign key-column, then no share-
    lock is required on the child-table.

    ∑ TX - Transaction. As soon as a transaction is started a TX enqueue is
    needed. A transaction is uniquely defined by the rollback segment number,
    the slot number in the rollback segmentís transaction table and the slot
    numberís sequence number. A session can be waiting on a TX enqueue for
    several reasons: 1) Another session is locking the requested row.
    2) When two sessions tries to insert the same unique key into a table
    (none of them has done a COMMIT), then the last session is waiting for
    the first one to COMMIT or ROLLBACK. 3) There are no free ITL (Interested
    Transaction List) in the block header (increase INI_TRANS och PCT_FREE
    for the segment).

    ∑ UL - User Lock. A session has taken a lock with the DBMS_LOCK.REQUEST
    function.

    ===============================================================================

    Has locking been a problem
    --------------------------

    To see if the instance has had locking problems since startup, execute the
    following SELECT:

    SELECT *
    FROM v$sysstat
    WHERE class=4;

    STATISTIC# NAME CLASS VALUE
    22 enqueue timeouts 4 0
    23 enqueue waits 4 2
    24 enqueue deadlocks 4 0
    25 enqueue requests 4 213
    26 enqueue conversions 4 0
    27 enqueue releases 4 204

    ∑ "enqueue timeouts" shows the total number of enqueue operations (get
    and convert) that timed out before they could complete.

    ∑ "enqueue waits" shows how many times a session had to wait for an
    enqueue. This column is updated first after the wait is finished.

    ∑ "enqueue deadlocks" is how many times a deadlock situation has
    occured (every time the client receives an ORA-60 and a trace file
    will be created). This value should be zero, else an investigation
    should be made and the trace files should be checked.

    ∑ "enqueue requests" minus "enqueue releases" shows how many locks that
    are held just now (which is equal to the number of rows in V$LOCK).

    ∑ "enqueue conversions" is how many times an session is holding a lock
    in one mode and then wants to change the mode (for example, first the
    session is doing an SELECT * FROM emp FOR UPDATE and then a UPDATE emp
    SET sal=9876 WHERE empno=7839).

    ∑ "enqueue releases" shows how many times an enqueue was released (freed).

    Maybe the session(s) that created these waits is still connected. If
    they are, you find them in V$SESSTAT (check first with V$STATNAME or
    V$SYSSTAT that statistic# 23 is "enqueue waits"):

    SELECT *
    FROM v$sesstat
    WHERE statistic# = 23 AND value > 0;

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

    If a session has been waiting for an enqueue, then a row in V$SYSTEM_EVENT is
    created. If the instance is running with TIMED_STATISTICS = TRUE, you can see
    for how long time the session had to wait for the enqueue:

    SELECT *
    FROM v$system_event
    WHERE event = 'enqueue';

    EVENT TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
    enqueue 15 13 4193 279.53333

    ∑ TOTAL_WAITS and AVERAGE_WAIT are not showing what you are expecting.
    The real number of waits are "enqueue waits" in V$SYSSTAT, or by taking
    TOTAL_WAITS minus TOTAL_TIMEOUTS. To get the correct AVERAGE_WAIT you
    should take TIME_WAITED (4193) divided by the real number of waits (2).
    So the real average wait time is not 2.79 seconds - it is 20.96 seconds.

    With the following SELECT you can see for how long time the connected
    sessions had to wait for an enqueue and how long the maximum wait time was:

    SELECT COUNT( * ), SUM( time_waited ), MAX( max_wait )
    FROM v$session_event
    WHERE event = 'enqueue';

    *) Remember that in V$SYSTEM_EVENT you see the total values for all sessions
    that has been connected to the instance and those that are still connected.
    In V$SESSION_EVENT you will only see values for those sessions that are
    connected to the instance just now.
    -------------------------------------------------------------------------------

    To see the gets and waits that had been on the different types of enqueues,
    check in the fixed internal table X$KSQST:

    SELECT *
    FROM x$ksqst
    WHERE ksqstget > 0;

    ADDR INDX INST_ID KS KSQSTGET KSQSTWAT
    01ECBE30 69 1 CF 9 0
    01ECBE48 72 1 CI 12 0
    01ECBEA8 84 1 CU 57 0
    01ECBF60 107 1 DL 28 0
    01ECBF90 113 1 DR 1 0
    01ECBFB0 117 1 DV 12 0
    01ECC498 274 1 IS 5 0
    01ECC890 401 1 MR 16 0
    01ECCB30 485 1 PF 1 0
    01ECCDA0 563 1 RT 1 0
    01ECCE88 592 1 SQ 1 0
    01ECCEA0 595 1 ST 1 0
    01ECCF68 620 1 TM 14 0
    01ECCF98 626 1 TS 1 0
    01ECCFC0 631 1 TX 13 2
    01ECD098 658 1 US 59 0

    ∑ The sum of KSQSTWAT is equal to "enqueue waits" in V$SYSSTAT, so here you
    can see what type of locks that the sessions and background processes
    had been waiting on.

    ===============================================================================

    Is locking a problem just now
    -----------------------------

    This is an example of how you can trace locking problems.

    Background: User SCOTT has updated the salary for all employees in table EMP.
    Then user SYSTEM tries to update the JOB-description for employee KING in the
    same table.


    The SELECTs
    -----------

    Which session is the blocker and which sessions are waiting to get the lock?
    This SELECT will return no rows if there are no waiters and blockers. You can
    never find situations when there is only blocker(s) or waiter(s). One session
    can block many others.

    SELECT sid, DECODE( block, 0, 'NO', 'YES' ) BLOCKER,
    DECODE( request, 0, 'NO', 'YES' ) WAITER
    FROM v$lock
    WHERE request > 0 OR block > 0
    ORDER BY block DESC;

    SID BLOCKER WAITER
    8 YES NO
    7 NO YES

    ∑ We can see situations where a session is both a Blocker and a Waiter.
    ∑ If there are only two sessions and both are Blockers and Waiters then we
    got a deadlock situation (which Oracle will solve automatically).
    ∑ A fast way to check blocking/waiting situations is to run:
    SELECT * FROM v$lock WHERE block > 0 OR request > 0;

    ===============================================================================

    Get some common information about these sessions from V$SESSION:

    SELECT sid, taddr, lockwait, status, sql_address,
    row_wait_obj# RW_OBJ#, row_wait_file# RW_FILE#, row_wait_block#
    RW_BLOCK#, row_wait_row# RW_ROW#
    FROM v$session
    WHERE sid IN( 7, 8 )
    ORDER BY sid;

    SID TADDR LOCKWAIT STATUS SQL_ADDRESS RW_OBJ# RW_FILE# RW_BLOCK# RW_ROW#
    7 01F2D014 01EC9858 ACTIVE 0224C9A8 2494 1 15631 8
    8 01F2CCBC INACTIVE 02258D80 -1 0 0 0

    ∑ The column TADDR could be joined with ADDR in V$TRANSACTION.

    ∑ The sessions that are waiting to get a lock has a value (IS NOT NULL)
    in LOCKWAIT.

    ∑ Status for a waiting session is ACTIVE. There are a lot of SQL:s around,
    that counts the number of active sessions by looking at this column.

    ∑ SQL_ADDR is the address for the last SQL statement that the session executed.
    Could be joined with V$SQL.ADDRESS and V$OPEN_CURSOR.ADDRESS. For the Waiter
    this is the SQL-command that made him wait. For the blocker this could be the
    SQL that makes the block but it could also be any later SQL.

    ∑ If ROW_WAIT_OBJ# has not the value -1, then it means that the session is
    waiting on a lock for this object. Could be joined with
    DBA_OBJECTS.OBJECT_ID or DBA_OBJECTS.DATA_OBJECT_ID.

    ∑ The other ROW_WAIT* columns shows where the row is located that the
    waiting session wants to get a lock on.

    ∑ From DBA_OBJECTS you can see who is the owner to the table and the table
    name, then you can with the information from the ROW_WAIT* columns build
    the following SELECT (the first argument is 1 for the Oracle8 extended ROWID,
    the second is the object ID, and then the file#, block#, slot#):

    SELECT * FROM scott.emp
    WHERE rowid = DBMS_ROWID.ROWID_CREATE( 1, 2494, 1, 15631, 8 );

    ===============================================================================

    Get information about the enqueues the sessions are holding and waiting on:

    SELECT *
    FROM v$lock
    WHERE sid IN( 7, 8 )
    ORDER BY sid, type;

    ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
    01F27D0C 01F27D20 7 TM 2494 0 3 0 1008 0
    01EC9848 01EC9858 7 TX 65546 5989 0 6 1008 0
    01F27C98 01F27CAC 8 TM 2494 0 3 0 1032 0
    01CD8820 01CD88EC 8 TX 65546 5989 6 0 1032 1

    ∑ If a session is waiting to get a lock, then column KADDR contains the
    same value as V$SESSION.LOCKWAIT.

    ∑ The type column shows what type of lock the session is holding or waiting on.

    ∑ For the lock type TM (table lock), column ID1 is the object_id (or the
    data_object_id) for that table (could be joined with DBA_OBJECTS). For a
    lock of type TX (transaction lock), TRUNC( ID1 / 65536 ) shows which
    rollback segment that is used for the transaction and ID1 -
    ( rollbacksegment# * 65536 ) shows which slot number in the transaction
    table that this sessions is using (or want to used).

    ∑ LMODE shows in which mode the session is holding the lock.
    0=None, 1=NULL, 2=Row share (SS), 3=Row exclusive (SX), 4=Share (S),
    5=Share row exclusive (SSX), 6=Exclusive (X)

    ∑ REQUEST is the mode for which the session wants the lock.

    ∑ If a session has values greater than 0 in both LMODE and REQUEST, then the
    session is a Converter.

    ∑ CTIME is how many seconds the session has either hold the lock or waited
    for the lock.

    ∑ BLOCK is greater than 0 if the lock is blocking other sessions (if there
    are other sessions waiting for this lock to be released).

    ===============================================================================

    What are the sessions doing (which event are the sessions waiting on just
    now or last waited on):

    SELECT sid, seq#, event, p1raw, p2raw, p3raw, state, wait_time WAIT,
    seconds_in_wait SECS
    FROM v$session_wait
    WHERE sid IN( 7, 8 )
    ORDER BY sid;

    SID SEQ# EVENT P1RAW P2RAW P3RAW STATE WAIT SECS
    7 488 enqueue 54580006 0001000A 00001765 WAITING 0 1011
    8 147 SQL*Net message from client 28444553 00000001 00 WAITING 0 1035

    ∑ SEQ# is increasing by 1 for each wait.

    ∑ EVENT is the name of the event (or resource) that the session is waiting on.

    ∑ From this you can draw the conclusion that session 8 has locked a row that
    session 7 wants, but session 8 has not done a COMMIT or ROLLBACK (and
    thatís why session 7 is waiting). Session 8 has been idle in 1035 seconds
    (typically the user has gone for a cup of coffee).

    ===============================================================================

    Get some information about the Blockerís transaction:

    SELECT t.addr, t.xidusn USN, t.xidslot SLOT, t.xidsqn SQL, t.status,
    t.start_time, t.used_ublk UBLK, t.used_urec UREC, t.log_io LOG,
    t.phy_io PHY, t.cr_get, t.cr_change CR_CHA
    FROM v$transaction t, v$session s
    WHERE s.sid IN( 7, 8 ) AND t.addr = s.taddr;

    ADDR USN SLOT SQN STATUS START_TIME UBLK UREC LOG PHY CR_GET CR_CHA
    01F2CCBC 1 10 5989 ACTIVE 01/22/00 11:11:28 2 15 54 34 32 0

    ∑ ADDR can be joined with V$SESSION.TADDR.

    ∑ USN is the Undo Segment Number. Can be joined with V$ROLLNAME.USN and
    V$ROLLSTAT.USN.

    ∑ SLOT is the slot number in the rollback segmentís transaction table.

    ∑ SQN is the sequence number for the transaction.

    ∑ USN+SLOT+SQN are the three values that uniquely identifies a transaction.

    ∑ UBLK is how many undo blocks that the transaction has used.

    ∑ UREC is how many table- and index-entries the transaction has inserted,
    updated or deleted. If you are doing an INSERT or DELETE, then you will
    see that UREC is set to + how many rows
    you inserts/deletes. If you UPDATE a column then UREC will be set to
    * 2 + number of updated
    rows (so if the column belongs to no index, then UREC is set to the number
    of rows that was updated).

    ∑ If USED_UBLK and USED_UREC is decreasing for a transaction every time you
    look, it means that the transaction is rolling back. When USED_UREC is down
    to zero the rollback is finished. So here you can see when the rollback
    phase is finished after an instance/crash recovery.

    ===============================================================================

    Which SQL statements are the sessions currently executing (or last executed):

    SELECT s.sid, q.users_executing, q.sql_text
    FROM v$session s, v$sql q
    WHERE s.sid IN( 7, 8 ) AND q.address = s.sql_address
    ORDER BY s.sid;

    SID USERS_EXECUTING SQL_TEXT
    7 1 update scott.emp set job=job where empno=7839
    8 0 SELECT * FROM DUAL

    ∑ The SQL statement for the Waiter is the SQL that the user is hanging on.
    ∑ The SQL statement for the Blocker may not be the SQL that is holding the
    lock.
    ∑ If USERS_EXECUTING greater than 0 then this SQL is executing just now.

    ===============================================================================

    Get all open cursors for the two session:

    SELECT sid, user_name, address, sql_text
    FROM v$open_cursor
    WHERE sid IN( 7, 8 )
    ORDER BY sid;

    SID USER_NAME ADDRESS SQL_TEXT
    7 SYSTEM 0224C9A8 update scott.emp set job=job where empno=7839
    8 SCOTT 02258D80 update emp set sal=sal
    8 SCOTT 02263154 SELECT * FROM DUAL

    ∑ Here you can see the SQL that is blocking others (if the session has not
    closed the cursor yet, tools like SQL*Plus closes the cursor automatically
    after execution).

    ===============================================================================

    Get some information about the Blockerís session so you can call him and tell
    him to COMMIT/ROLLBACK his transaction.

    SELECT sid, serial#, username, osuser, program, machine
    FROM v$session
    WHERE sid = 8;

    SID SERIAL# USERNAME OSUSER PROGRAM MACHINE
    8 147 SCOTT jtreugut c:\orawin95\bin\plus80 JTREUGUT2

    ∑ If you need to kill the session, only do it with ALTER SYSTEM KILL
    SESSION ', ' command. The session will then have status
    KILLED or MARKED FOR KILLED (if PMON couldnít terminate the session directly).
    But the locks on the resources can be held for a long time (until PMON has
    cleaned up everything).


    RELATED MANUALS
    ---------------

    Oracle8i Reference Chapter Dynamic Performance (V$) Views:
    Appendix A Wait Events - enqueue
    Appendix B Enqueue and Lock Names
    Appendix C Statistics Descriptions
    Oracle 8i Application Developer's Guide

    Doug

  6. #6
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    If you didn't get the entire article, not displaying for me, post an -email address and I will send it to you.
    Doug

  7. #7
    Join Date
    Feb 2000
    Posts
    142
    I am not sure what I have to do. Please help.

    Thanks.


  8. #8
    Join Date
    Feb 2000
    Posts
    142
    What is a CU lock? Please explain.


    Thanks.

  9. #9
    Join Date
    Dec 2000
    Posts
    40

    subprogram or cursor reference is out of scope

    Hi,

    In one of my PL/SQL blocks, I declared a cursor as
    CURSOR CUR_MS_SITE IS
    SELECT *
    FROM CSS_DBA.CSS_MS_SITE
    WHERE MS_SITE_ID = v_MS_SITE_ID
    FOR UPDATE;

    Where in MS_SITE_ID is the primary key for the table and v_MS_SITE_ID is a pre defined variable.

    When I compile this PL/SQL Block, I get an error:

    ORA-06550: line 135, column 24:
    PLS-00225: subprogram or cursor 'CUR_MS_SITE' reference is out of scope.

    Any help Please..

    MS Reddy

  10. Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •  



Click Here to Expand Forum to Full Width