[20181120]奇怪的insert語句.txt

[20181120]奇怪的insert語句.txt

--//上午檢查SQL*Net break/reset to client時,發現一條insert語句很特殊.分析做一個記錄.

1.環境:

SYSTEM@192.168.31.8:1521/hrp430> @ ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SELECT DISTINCT kglnaobj c120 , kglobt03 sql_id

  FROM x$kglob

 WHERE kglobt03 IN (  SELECT sql_id

                        FROM DBA_HIST_ACTIVE_SESS_HISTORY

                       WHERE event = 'SQL*Net break/reset to client'

                    GROUP BY sql_id);

C120                                                                                                                     SQL_ID

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

SELECT PBE_NAME,PBE_EDIT,PBE_TYPE,PBE_CNTR,PBE_WORK,PBE_SEQN,PBE_FLAG FROM SYSTEM.PBCATEDT ORDER BY PBE_NAME,PBE_SEQN    8gvfr81z8nfs7

Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 )                                               gftx8vhbhujf3

select count ( :"SYS_B_0" ) from yk_gnt where ypxh =:1                                                                   ddd4xgabw2tct

Select csz2 From gy_xtcs where xtxh =:"SYS_B_0" And csmc =:"SYS_B_1"                                                     c21vhszr9gbdq

SYSTEM@zzzzzz > select sql_id,sql_text,executions,rows_processed from v$sqlarea where sql_id='gftx8vhbhujf3';

SQL_ID        SQL_TEXT                                                                         EXECUTIONS ROWS_PROCESSED

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

gftx8vhbhujf3 Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 )            13919              0

--//EXECUTIONS=13919,ROWS_PROCESSED=0,為什麼?

2.分析:

--//要分析為什麼沒有插入,要麼建立觸發器跟蹤插入語句的執行或者選擇審計表插入操作.主要是獲得繫結變數的值.

--//執行如下:

audit insert on portal_his.gy_xtcs by access whenever not successful;

select * from DBA_AUDIT_TRAIL where obj_name='GY_XTCS' and owner='PORTAL_HIS';

--//奇怪審計看不到插入的繫結變數值.如何才能看到呢?難道不成功看不到繫結變數值嗎?

3.採用跟蹤特定sql語句方式:

ALTER SYSTEM SET EVENTS 'sql_trace [sql: sql_id=gftx8vhbhujf3] bind=true, wait=true';

--//...等insert語句執行.

ALTER SYSTEM SET EVENTS 'sql_trace off';

$ cd /u01/app/oracle/diag/rdbms/hrp430/hrp430/trace

$ grep -i gftx8vhbhujf3 *.trc

hrp430_ora_12427.trc:PARSING IN CURSOR #47617027301968 len=75 dep=0 uid=94 oct=2 lid=94 tim=1542700449294504 hv=386745795 ad='aebb74d0' sqlid='gftx8vhbhujf3'

hrp430_ora_24947.trc:PARSING IN CURSOR #47018732013064 len=75 dep=0 uid=94 oct=2 lid=94 tim=1542700444830519 hv=386745795 ad='aebb74d0' sqlid='gftx8vhbhujf3'

hrp430_ora_28526.trc:PARSING IN CURSOR #47763681011784 len=75 dep=0 uid=94 oct=2 lid=94 tim=1542700476876606 hv=386745795 ad='aebb74d0' sqlid='gftx8vhbhujf3'

hrp430_ora_29024.trc:PARSING IN CURSOR #47501418697344 len=75 dep=0 uid=94 oct=2 lid=94 tim=1542700436091555 hv=386745795 ad='aebb74d0' sqlid='gftx8vhbhujf3'

$ grep -i -l gftx8vhbhujf3 *.trc | xargs -I{} sed -n '/gftx8vhbhujf3/,/=====================/p' {}

PARSING IN CURSOR #47617027301968 len=75 dep=0 uid=94 oct=2 lid=94 tim=1542700449294504 hv=386745795 ad='aebb74d0' sqlid='gftx8vhbhujf3'

Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 )

END OF STMT

BINDS #47617027301968:

 Bind#0

  oacdty=96 mxl=32(11) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=128 off=0

  kxsbbbfp=2b4eb4342d48  bln=32  avl=11  flg=05

  value="MS_JZGH_BLB"

 Bind#1

  oacdty=96 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=32

  kxsbbbfp=2b4eb4342d68  bln=32  avl=00  flg=01

 Bind#2

  oacdty=96 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=64

  kxsbbbfp=2b4eb4342d88  bln=32  avl=00  flg=01

 Bind#3

  oacdty=96 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=96

  kxsbbbfp=2b4eb4342da8  bln=32  avl=10  flg=01

  value="急診掛號費"

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

PARSING IN CURSOR #47018732013064 len=75 dep=0 uid=94 oct=2 lid=94 tim=1542700444830519 hv=386745795 ad='aebb74d0' sqlid='gftx8vhbhujf3'

Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 )

END OF STMT

BINDS #47018732013064:

 Bind#0

  oacdty=96 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=128 off=0

  kxsbbbfp=2ac366d6a4b0  bln=32  avl=12  flg=05

  value="MS_LSTD_YSDM"

 Bind#1

  oacdty=96 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=32

  kxsbbbfp=2ac366d6a4d0  bln=32  avl=00  flg=01

 Bind#2

  oacdty=96 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=64

  kxsbbbfp=2ac366d6a4f0  bln=32  avl=00  flg=01

 Bind#3

  oacdty=96 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=96

  kxsbbbfp=2ac366d6a510  bln=32  avl=20  flg=01

  value="綠色通道開通醫生列表"

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

PARSING IN CURSOR #47763681011784 len=75 dep=0 uid=94 oct=2 lid=94 tim=1542700476876606 hv=386745795 ad='aebb74d0' sqlid='gftx8vhbhujf3'

Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 )

END OF STMT

BINDS #47763681011784:

 Bind#0

  oacdty=96 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=128 off=0

  kxsbbbfp=2b70d935c610  bln=32  avl=12  flg=05

  value="MS_LSTD_YSDM"

 Bind#1

  oacdty=96 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=32

  kxsbbbfp=2b70d935c630  bln=32  avl=00  flg=01

 Bind#2

  oacdty=96 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=64

  kxsbbbfp=2b70d935c650  bln=32  avl=00  flg=01

 Bind#3

  oacdty=96 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=96

  kxsbbbfp=2b70d935c670  bln=32  avl=20  flg=01

  value="綠色通道開通醫生列表"

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

PARSING IN CURSOR #47501418697344 len=75 dep=0 uid=94 oct=2 lid=94 tim=1542700436091555 hv=386745795 ad='aebb74d0' sqlid='gftx8vhbhujf3'

Insert Into GY_XTCS ( CSMC , CSZ , MRZ , BZ ) Values ( :1 , :2 , :3 , :4 )

END OF STMT

BINDS #47501418697344:

 Bind#0

  oacdty=96 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=128 off=0

  kxsbbbfp=2b33c971da48  bln=32  avl=12  flg=05

  value="MS_LSTD_YSDM"

 Bind#1

  oacdty=96 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=32

  kxsbbbfp=2b33c971da68  bln=32  avl=00  flg=01

 Bind#2

  oacdty=96 mxl=32(00) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=64

  kxsbbbfp=2b33c971da88  bln=32  avl=00  flg=01

 Bind#3

  oacdty=96 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00

  oacflg=01 fl2=1000000 frm=01 csi=852 siz=0 off=96

  kxsbbbfp=2b33c971daa8  bln=32  avl=20  flg=01

  value="綠色通道開通醫生列表"

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

--//很明顯主鍵衝突.

SYSTEM@zzzzzz > column csz format a20

SYSTEM@zzzzzz > column MRZ format a20

SYSTEM@zzzzzz > select * from GY_XTCS where CSMC in ('MS_LSTD_YSDM','MS_JZGH_BLB');

CSMC                 CSZ                  MRZ                  BZ

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

MS_JZGH_BLB                                                    急診掛號費

MS_LSTD_YSDM                                                   綠色通道開通醫生列表

--//這樣的開發團隊,真心的無語.. 欄位CSMC是主鍵.難道程式不做判斷嗎?出現ora-00001錯誤不報錯嗎?