[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错误不报错吗?