从客户端到oracle的sql语句跟踪笔记

oracle端要用到的四个视图为:V$sessionoracle端要用到的四个视图为:V$session：V$transactionV$processV$SQL：这四个图的关系为：oracle从客户端到sql语句追踪这两天看小布老师的视频学习了一下从客户端到oracle数据库发送执行的SQL语句的跟踪，整理一下笔记。需要用到的命令：netstat当前有多少个用户连接到数据库服务器上事务信息进程信息当前正在运行的一些SQL的情况下面进行探索:第一步：首先在数据库端以sysdba身份登录oracle，查询v%transaction视图SQL>norowsselected显示当前没事务在处理

第二步：windows端使用SQL/PLUS以HR用户连接到远程oracle,执行语句insertintotvalues(3,'lisi');再次查询事务视图:图2再次查询事务视图:图2SqL>selectADDR,SES_ADDRfromv$transaction;ADDRSES_ADDR00000000830EE24800000000853C67F8Elapsed:00:00:00.00这里显示已经有了一个事务。ADDR第三步：查询v$session视图sql>selectSADDR,SID,PADDR,SQL_ADDRESS,PREV_SQL_ADDR,USERNAME,STATUSfromv$session;SADDRSIDPADDRSQL_ADDRESSPREV_SQL_ADDRUSERNAMESTATUS0000000085292D9810000000085079BB00000ACTIVE000000008528FF282000000008507BC300000ACTIVE000000008528D0B83000000008507DCB00000ACTIVE000000008528A2484000000008507FD300000ACTIVE00000000852873D850000000085081DB00000ACTIVE000000008528456860000000085083E300000ACTIVE00000000852816F870000000085085EB00000ACTIVE000000008527E88880000000085087F300000ACTIVE000000008527BA189000000008508C0300000ACTIVE0000000085278BA810000000008508E0B00000ACTIVE0000000085272EC81200000000850901300000ACTIVE00000000852675081600000000850921B000000000007F66DAF8ACTIVE00000000852618281800000000850942300000ACTIVE0000000085255E682200000000850962B00000ACTIVE000000008525018824000000008509A3B00000ACTIVE000000008524A4A82600000000850983300000ACTIVE00000000853FABD8125000000008508AFF0000000007D8D2848000000007F5CC218SYSACTIVE

00000000853F7D68126000000008507ABF00000ACTIVE00000000853F4EF8127000000008507CC700000ACTIVE00000000853F2088128000000008507ECF00000ACTIVE00000000853EF2181290000000085080D700000ACTIVE00000000853EC3A81300000000085082DF00000ACTIVE00000000853E95381310000000085084E700000ACTIVE00000000853E66C81320000000085086EF000000000007F719690ACTIVE00000000853DDB78135000000008508D0700000ACTIVE00000000853DAD08136000000008508F0F00000ACTIVE00000000853D21B813900000000850911700000ACTIVE00000000853CF34814000000000850931F00000ACTIVE00000000853CC4D814100000000850952700000ACTIVE00000000853C67F814300000000850972F0000000007F497D500000000085438D78HRINACTIVE00000000853C0B1814500000000850993700000ACTIVE31rowsselected.Elapsed:00:00:00.02如图：172J7.3S.1MXi8*17J.17.33.ISOfl]SQL>selectSADDR,SIC,PfiDDR^SQL-ADDRESS^PREV.SQL-ADDR,USERNAME,STATUSfronv$sesslon：SADDRSIDPADDRSQL_AUUHE粕PftEV_SQL_ADDRUSERNAMESTATUS0000000O85292U驼10000000085079BB00000ACIlVb000000008528FF282000000008507BC300000ACTIVE3000000008507DCBO0000ACTIVE000000008528A2-184000000008507FD300000ACTIVE000000008528730850000000085081DB。0000ACTIVE0000000瞻5网45时60000000085083E3000gMTTVF0000000瞻5KJ1RF910000000085085EBO0000MTTVF000000002歹7Ff?诵S00000000SS0S7F3O0000ACTTVFOO0OO0OgA7M1S9000000008508C0300000MTTVF0000000瞻527丽德10000000008508EOBO0000MTTVF00000000妨272「邙1200000000850901300000(ICTTVrOOOOOOOOH52675C816OOOOOOOO05O921BO00OOOOOOOO7F66DAF0nciTvr0000000085261R2RIE00000000850942300000ACTIVE00000000妨四S「问PPOOQOOQOO骐0962W0000riCTTvrOOOOOOOOH52501能24OOOOOOOO05O9fi3BO0000nciTvr000000008^24祖时2E00000000850983300000ACllVL000000008闵I125OOQOOQQ08508fiFFO000000007D8C2E^8000000007F5CC218SYSACllVL00000000853F7D68126000000008507ABF00000ACllVL00000000853F4LI8127OQOQQ0008507CC700000ACllVL000000008WI12B000000008507EU00000ACllVL0000000O853EFW181290000000085080D700000Mil此0000000O853EC3时1300000000085082DFO0000Mil此0000000D853E953813100000000Hb0H4L/O0000ACIlVt00000000853E66C81320000000085086EFO00000000007F719690Mil此0000000O853DDS8135000000008508D0700。gMil此00000000853DADC8136000000008508FOFO0000ACTIVE00000000853D21B813900000000850911700000ACTIVE00000000853CF3-1814000000000850931FO0000ACTIVE00000000853CC4D814100000000850952700000ACTIVE0000000瞻53C67FSH300000000850972FO000000007F^97D500000000085438D78HRTNMTTVFOOOOOOOOS53COR1S11500000000850993700000ACTTVF21rnur;cict.cd.图3这里我们可以看到已经显示出了所有连接到数据库上的session里面就有我当前的HR的连接。黄色底纹的那一行中SADDR和v$transaction中的SES_ADDR相对应，即：

v$session.SADDR=v$transaction.SES_ADDR第四步：查询v$sql视图，找到正在执行的语句。SQL>selectSQL_TEXT,ADDRESSfromv$sqlwhereADDRESS='0000000085438D78';ADDRESS0000000085438D78SQL_TEXTinsertintotvalues(3,'lisi')Elapsed:00:00:00.03如图:ADDRESS0000000085438D78SQL>selectSQL_TEXT,ADDRESSfromv$sqlwhereADDRESS二’0000000085438D78、SQL_TEXTADDRESSinsertintotvalues(3,"lisi")0000000085438D78Elapsed:00：00：00.03SQL>到此，我们已经找到了正在执行的语句，v$session和v$sql两个视图的关系，从图1也能看出:v$session.PREV_SQL_ADDR=v$sql.ADDRESS其他查询v$process视图SqL>selectADDR,SPIDfromv$process;ADDRSPID0000000085078B700000000085079BB02636000000008507ABF02638000000008507BC302642000000008507CC702644000000008507DCB02646000000008507ECF02648000000008507FD3026500000000085080D7026520000000085081DB026540000000085082DF02656

0000000085083E3026580000000085084E7026600000000085085EB026620000000085086EF026640000000085087F3026660000000085088F7026680000000085089FB02670000000008508AFF03642000000008508C0302683000000008508D0702685000000008508E0B02687000000008508F0F02689000000008509013026910000000085091170269500000000850921B0271000000000850931F02712000000008509423027560000000085095270270800000000850962B0371000000000850972F0368531rowsselected.Elapsed:00:00:00.01SQL>根据图1可以知道：v$session.PADDR=v$process.ADDR对应的行为黄色底纹的行(最后一行)，他的SPID就是进程号。此时我们执行ps-ef|grep3685进程查询[oracle@locahost~]$ps-efgrep3685oracle36851014:43?00:00:00oracleHDWKXT(LOCAL=NO)oracle38132800015:25pts/300:00:00grep3685如图：[oracleOlocahostpsIgrep3685oracle36851014：43?00：00：00oracleHDWKXT(LOCfiL=NO)oracle38132800015=25pts/300：00：00grep3685[oracleQlocahost果然是我们的oracle的session进程。再执行命令：netstat-apn|more

[oracle®1ocahoatnet3tat-aipn1more(Notallprocassescouildbeidenti-Tied.rwn-ownedprocessinfouillnotbeyouuouldhavetoroottoitall.)ActiveInternetconnections(serversandestablished)ProtoR^r：vQSfiiuJQ1ocalAddressForeignAdtJiC!；!；SLni.f：PID/Programnaiwetcp0012/.0.0.1：220^：*L1SIENtcp00:77：w1TENtcp00:1：*IISTENtcp00:63：«LISTENtcp00*：*IISTENtcp00:220：HL1SILNtcp00172.17,38.180:2711380:1521ESTABLISHED2636/orapMonHOHKXtcp00；；；KL1SILN2B78/tnsl3nrtcp00：：：??：：：K1TEN—tcp00：：:侦阳;':::*IISTEN2S6S/ora_d000_HCWKXtcp00::frrr：60=1521::ffK：30：27il3ESTABLISHED2&76/tnslsnrtcp00::rfrr：8O：22:；rrrr=2:59119ESTABlISHED—tcp00::rrrr：i?2.17.38.iso：i52i::ffH：2：51416E5TABLISIILDSG^/oracLellDHKXT馨udp00:76：*udp00；B127：mudp00:^35：wudp00:1：*udp00：63：«udp00o.o.a.o：/ti60.0.so：廿udp00::：14206:i：K2&6^1/orammonHDHKXjjda(Lo：5：：昼图6注：我本机的ip地址为2,远端oracle的ip为80可以看到我本机和远端linux一共有2个连接：tcp00::ffff:80:22::ffff:2:59119ESTABLISHED-tcp00::ffff:80:1521::ffff:2:51416ESTABLISHED3685/oracleHDWKXT其中第一条是我的SecureCRT，另一条PID为3685的进程连接的是就是我的DOS啦（FO四GNAddress指的是客户端的IP和端口。这里有两个客户端端口，分别是59119和51416都是我们正在执行的SQL/PLUS正在连接的进程。）在windows打开DOS窗口，执行netstat-b命令，可以查看到，这两个端口都是PL/SQLDeveloper在使用的。TCP2:51416bogon:1521ESTABLISHED[sqlplus.exe]bogon:sshESTABLISHEDTCP2:59119bogon:sshESTABLISHED[SecureCRT.exe]TCP33-92=51416[sqlplus.exe1TCP33-92=51416[sqlplus.exe1TCP1?2_1?_33_92：55330[AuastSuc_exe1TCP172_17_33_92：59119[GecureCRT_exe1r-054-044-234-0??:httpESTABLISHEDbogfon：sshESTABLISH