台灣最大程式設計社群網站
線上人數
528
 
會員總數:245465
討論主題:189222
歡迎您免費加入會員
討論區列表 >> Oracle >> 執行後會發生ORA-02020:過多的數據庫連接使用中問題,若拆開語法執行 OK
[]  
[我要回覆]
1
回應主題 加入我的關注話題 檢舉此篇討論 將提問者加入個人黑名單
執行後會發生ORA-02020:過多的數據庫連接使用中問題,若拆開語法執行 OK
價值 : 10 QP  點閱數:2126 回應數:1

樓主

jinan
門外漢
0 3
180 0
發送站內信

select '2',a.user_workcod,to_char(sysdate,'yyyymmdd') as data_ym,

0,0,0,0,

0,0,0,0,

sysdate,b.EXCHANGE_DIFF as d_EXCHANGE_DIFF,c.EXCHANGE_DIFF as m_EXCHANGE_DIFF,

d.d_fidy_fee,e.m_fidy_fee

from

(select user_cod,user_workcod from users@richdb a where substr(user_workcod,1,1) <> '9' and ao_type in ('g01','g02')) a

-- 當日

left join

(select a.user_cod,a.user_workcod,nvl(f.CHANGE_DIFF,0) as EXCHANGE_DIFF

from

(select user_cod,user_workcod from users@richdb a where substr(user_workcod,1,1) <> '9' and ao_type in ('g01','g02')) a

-- 匯兌損益

left join

(select user_cod,sum(EXCHANGE_DIFF) as CHANGE_DIFF

from

(SELECT B.BRANCH_COD ,b.user_cod,B.USER_WORKCOD , B.USER_CHI, B.AO_CODE , A.COUNTER_PARTY , A.TXNT_DATE, A.CCY ,

A.AMT , A.DR_CR , ROUND(A.EXRATE,3) AS EXRATE, ROUND(A.EXCOST,3) AS EXCOST,

ROUND(A.EXCHANGE_DIFF,3) as EXCHANGE_DIFF

FROM (

SELECT TXNT_DATE , COUNTER_PARTY ,CCY, DR_CR , AMT , EXRATE , EXCOST,

CASE WHEN DR_CR = 'D'

THEN AMT * (EXRATE - EXCOST)

WHEN DR_CR = 'C'

THEN AMT * (EXCOST - EXRATE)

ELSE NULL

END AS EXCHANGE_DIFF

FROM TRX_FD_ACCT_DETAIL@dws.fddm

WHERE trunc(TXNT_DATE) = trunc(sysdate)

AND SUBSTR(GLNO,1,5) = '19695'

AND ((BRANCH_CLASS = 'DBU' AND CCY <> 'TWD' ) OR (BRANCH_CLASS = 'OBU' AND CCY <> 'USD' ))

) A

LEFT JOIN (

SELECT B.BRANCH_COD , a.AGENT_ID_NO , B.USER_WORKCOD , B.user_cod ,

B.USER_CHI , B.AO_CODE , a.CUST_ID_NO,b.ao_type

FROM REF_AGENT_CUSTOMER@dws.wmdm A

LEFT JOIN

(select * from REF_WM_AGENT_USERS@dws.wmdm where ao_type in ('g01','g02') and substr(user_workcod,1,1)<>'9') B on TRIM(a.AGENT_ID_NO) = TRIM(B.USER_COD)

) B ON TRIM(A.COUNTER_PARTY) = TRIM(B.CUST_ID_NO)

WHERE B.USER_WORKCOD IS NOT NULL)

group by user_cod) f on a.user_cod=f.user_cod) b on a.user_cod=b.user_cod

-- 當月累計

left join

(select a.user_cod,a.user_workcod,nvl(f.CHANGE_DIFF,0) as EXCHANGE_DIFF

from

(select user_cod,user_workcod from users@richdb a where substr(user_workcod,1,1) <> '9' and ao_type in ('g01','g02')) a

-- 匯兌損益

left join

(select user_cod,sum(EXCHANGE_DIFF) as CHANGE_DIFF

from

(SELECT B.BRANCH_COD ,b.user_cod,B.USER_WORKCOD , B.USER_CHI, B.AO_CODE , A.COUNTER_PARTY , A.TXNT_DATE, A.CCY ,

A.AMT , A.DR_CR , ROUND(A.EXRATE,3) AS EXRATE, ROUND(A.EXCOST,3) AS EXCOST,

ROUND(A.EXCHANGE_DIFF,3) as EXCHANGE_DIFF

FROM (

SELECT TXNT_DATE , COUNTER_PARTY ,CCY, DR_CR , AMT , EXRATE , EXCOST,

CASE WHEN DR_CR = 'D'

THEN AMT * (EXRATE - EXCOST)

WHEN DR_CR = 'C'

THEN AMT * (EXCOST - EXRATE)

ELSE NULL

END AS EXCHANGE_DIFF

FROM TRX_FD_ACCT_DETAIL@dws.fddm

WHERE trunc(TXNT_DATE) >= trunc(sysdate,'mm') and trunc(TXNT_DATE) <= trunc(sysdate)

AND SUBSTR(GLNO,1,5) = '19695'

AND ((BRANCH_CLASS = 'DBU' AND CCY <> 'TWD' ) OR (BRANCH_CLASS = 'OBU' AND CCY <> 'USD' ))

) A

LEFT JOIN (

SELECT B.BRANCH_COD , a.AGENT_ID_NO , B.USER_WORKCOD , B.user_cod ,

B.USER_CHI , B.AO_CODE , a.CUST_ID_NO,b.ao_type

FROM REF_AGENT_CUSTOMER@dws.wmdm A

LEFT JOIN

(select * from REF_WM_AGENT_USERS@dws.wmdm where ao_type in ('g01','g02') and substr(user_workcod,1,1)<>'9') B on TRIM(a.AGENT_ID_NO) = TRIM(B.USER_COD)

) B ON TRIM(A.COUNTER_PARTY) = TRIM(B.CUST_ID_NO)

WHERE B.USER_WORKCOD IS NOT NULL)

group by user_cod) f on a.user_cod=f.user_cod) c on a.user_cod=c.user_cod

-- 當日,信貸收入

left join

(select a.user_cod,sum(nvl(b.trx_amt,0)+nvl(c.open_fidy_fee,0)) as d_fidy_fee

from

(select a.emp_no,a.name,b.branch_cod,b.ao_type,b.ao_code, b.user_cod, b.user_group_chi

from personnel a left join v_users@richdb b on a.emp_no = b.user_workcod) a

left join

(select b.appl_sykno, sum(a.trx_amt) as trx_amt

from

--撥款手續費

(select a.INIT_VALUE_DATE,a.reckey,a.ORIG_LOAN_AMT,a.CURR_IRATE,b.trx_amt

from lb_lom_basic a

join

(SELECT CONTR_ID,SUM(TRANS_AMT)AS TRX_AMT

FROM

(

SELECT CONTR_ID,DC_CDE,POST_DTE,TRX_AMT,

CASE WHEN DC_CDE='-'THEN TRX_AMT*1

WHEN DC_CDE='0'THEN TRX_AMT*-1

ELSE NULL

END AS TRANS_AMT,TRX_DESC

FROM TRX_LN_FEE_DETAIL@dws.LNDM WHERE ACC_CDE='420210399'AND SUBSTR(CONTR_ID,1,3)='886'

)GROUP BY CONTR_ID) b on a.RECKEY=b.CONTR_ID

where a.status_2 = '00000000' and trunc(a.INIT_VALUE_DATE)=trunc(sysdate)) a

--推廣員編

left join

(select a.p_loan_no,b.appl_sykno from NAS_GM_PLOAN_APPL_ATTACH@DWS.GMDM a

join NAS_GM_PLOAN_APPL_DETAIL_ODS_V@DWS.GMDM b on a.case_no = b.case_no

where substr(a.p_loan_no,1,3)='886') b on substr(a.reckey,1,11)=b.p_loan_no

group by b.appl_sykno) b on a.emp_no = substr(b.appl_sykno,2,6)

left join

(select b.appl_sykno,sum(a.open_fidy_fee) as open_fidy_fee

from

--淨利息收入明細

(select a.INIT_VALUE_DATE,a.reckey,a.ORIG_LOAN_AMT,a.CURR_IRATE,b.rate_d,

a.ORIG_LOAN_AMT * (a.CURR_IRATE - b.rate_d) / 200 as open_fidy_fee

from lb_lom_basic a,

(select * from rat_rate@ods where start_date =

(select max(start_date) from rat_rate )) b

where substr(a.reckey,1,3)='886' and status_2 = '00000000' and trunc(INIT_VALUE_DATE)=trunc(sysdate)) a

--推廣員編

left join

(select a.p_loan_no,b.appl_sykno from NAS_GM_PLOAN_APPL_ATTACH@DWS.GMDM a

join NAS_GM_PLOAN_APPL_DETAIL_ODS_V@DWS.GMDM b on a.case_no = b.case_no

where substr(a.p_loan_no,1,3)='886') b on substr(a.reckey,1,11)=b.p_loan_no

group by b.appl_sykno) c on a.emp_no = substr(c.appl_sykno,2,6)

where a.user_cod is not null and (b.appl_sykno is not null or c.appl_sykno is not null)

group by a.user_cod) d on a.user_cod=d.user_cod

-- 當月累計,信貸收入

left join

(select a.user_cod,sum(nvl(b.trx_amt,0)+nvl(c.open_fidy_fee,0)) as m_fidy_fee

from

(select a.emp_no,a.name,b.branch_cod,b.ao_type,b.ao_code, b.user_cod, b.user_group_chi

from personnel a left join v_users@richdb b on a.emp_no = b.user_workcod) a

left join

(select b.appl_sykno, sum(a.trx_amt) as trx_amt

from

--撥款手續費

(select a.INIT_VALUE_DATE,a.reckey,a.ORIG_LOAN_AMT,a.CURR_IRATE,b.trx_amt

from lb_lom_basic a

join

(SELECT CONTR_ID,SUM(TRANS_AMT)AS TRX_AMT

FROM

(

SELECT CONTR_ID,DC_CDE,POST_DTE,TRX_AMT,

CASE WHEN DC_CDE='-'THEN TRX_AMT*1

WHEN DC_CDE='0'THEN TRX_AMT*-1

ELSE NULL

END AS TRANS_AMT,TRX_DESC

FROM TRX_LN_FEE_DETAIL@dws.LNDM WHERE ACC_CDE='420210399'AND SUBSTR(CONTR_ID,1,3)='886'

)GROUP BY CONTR_ID) b on a.RECKEY=b.CONTR_ID

where a.status_2 = '00000000' and trunc(a.INIT_VALUE_DATE)>=trunc(sysdate,'mm') and trunc(a.INIT_VALUE_DATE)<=trunc(sysdate)) a

--推廣員編

left join

(select a.p_loan_no,b.appl_sykno from NAS_GM_PLOAN_APPL_ATTACH@DWS.GMDM a

join NAS_GM_PLOAN_APPL_DETAIL_ODS_V@DWS.GMDM b on a.case_no = b.case_no

where substr(a.p_loan_no,1,3)='886') b on substr(a.reckey,1,11)=b.p_loan_no

group by b.appl_sykno) b on a.emp_no = substr(b.appl_sykno,2,6)

left join

(select b.appl_sykno,sum(a.open_fidy_fee) as open_fidy_fee

from

--淨利息收入明細

(select a.INIT_VALUE_DATE,a.reckey,a.ORIG_LOAN_AMT,a.CURR_IRATE,b.rate_d,

a.ORIG_LOAN_AMT * (a.CURR_IRATE - b.rate_d) / 200 as open_fidy_fee

from lb_lom_basic a,

(select * from rat_rate@ods where start_date =

(select max(start_date) from rat_rate )) b

where substr(a.reckey,1,3)='886' and status_2 = '00000000' and trunc(a.INIT_VALUE_DATE)>=trunc(sysdate,'mm') and trunc(INIT_VALUE_DATE)<=trunc(sysdate)) a

--推廣員編

left join

(select a.p_loan_no,b.appl_sykno from NAS_GM_PLOAN_APPL_ATTACH@DWS.GMDM a

join NAS_GM_PLOAN_APPL_DETAIL_ODS_V@DWS.GMDM b on a.case_no = b.case_no

where substr(a.p_loan_no,1,3)='886') b on substr(a.reckey,1,11)=b.p_loan_no

group by b.appl_sykno) c on a.emp_no = substr(c.appl_sykno,2,6)

where a.user_cod is not null and (b.appl_sykno is not null or c.appl_sykno is not null)

group by a.user_cod) e on a.user_cod=e.user_cod


本篇文章發表於2012-10-17 16:52
別忘捐VP感謝幫助你的人 新手會員瞧一瞧
1樓
回應

花旗蔘
檢舉此回應
ORA-02020: too many database links in use
Cause: The current session has exceeded the INIT.ORA open_links maximum.

Action: Increase the open_links limit, or free up some open links by committing or rolling back the transaction and canceling open cursors that reference remote databases.
本篇文章回覆於2012-10-17 22:45
== 簽名檔 ==
--未登入的會員無法查看對方簽名檔--
   
1

回覆
如要回應,請先登入.