oracle SQL语句的监控.docVIP

oracle SQL语句的监控 有时候想看看软件后台数据库到底执行了什么语句。比如大的erp系统，我们在查询的时候，后台执行了什么语句或者过程都存储在v$sql表中。但是当sql语句很长的时候就出现了截断显现。比如select sql_textfrom v$sql；其中有一条不完整的sql语句:SELECT LSWLDW.LSWLDW_WLDWBH as CustomersCode,LSWLDW.LSWLDW_DWMC as CustomersName,LSWLDW.LSWLDW_DWLB as CustomerSorts,LSDWLB_LBMC AS CustomerSortName,LSWLDW.LSWLDW_DQBH as CustomerAreas,LSDQZD_DQMC AS CustomerAreaName,? ?LSWLDW.HelpTag, LSWLDW.LSWLDW_JC as ShortName,nvl(LSWLDW.LSWLDW_SH, ) as SH, LSWLDW.IsDetail as Detail,LSWLDW.Layer, LSWLDW.OfTrade,nvl(LSWLDW.LSWLDW_CJDW, ) as LSWLDW_CJDW, LSWLDW.OfTrade AS OfTradeCode,nvl(OfTradeItem.NAME, ) AS OfTradeName,nvl(OwnerType.NAME, ) AS OwnerTypeFrom LSWLDW LEFT OUTER JOIN LSDWLB ON LSWLDW.LSWLDW_DWLB = LSDWLB.LSDWLB_LBBHLEFT OUTER JOIN CodeItems OfTradeItem ON LSWLDW.OfTrade=OfTradeItem.Code ANDOfTradeItem.SetID=A003LEFT OUTER JOIN CodeItems OwnerType ON LSWLDW.OwnerType=OwnerType.Code ANDOwnerType.SetID=A004LEFT OUTER JOIN LSDQZD ON LSWLDW.LSWLDW_DQBH = LSDQZD.LSDQZD_DQBH??where 1=1and LSWLDW_TYBZ=0 and LSWLDW.LSWLDW_WLDWBH in(select LSWLDW_WLDWBH from? ?(select Rownum rn,LSWLDW_WLDWBH from ? ?? ?(select LSselect LS下面的语句就被截断了.第一种解决方法：通过sql语句实现我们查找该语句的sql_id或者hash_valueselect sql_text,sql_id,hash_valuefrom v$sql结果为:? ?? ?sql语句? ? 3fvcnc7ngu0gp 3908895221通过查询v$sqltext显示完整的sql语句select sql_text from v$sqltextwhere hash_value=3908895221order by piece;或者from v$sqltextwhere sql_id=3fvcnc7ngu0gporder by piece;查询出来的结果到文本编辑器中整理格式就可以了.第二种方法：使用sqlplus在V$sql中有sql_fulltext字段,它存储这完整的sql,字段类型是clob首先设置sqlplusset heading offset long 40000其次输入查询语句select sql_fulltext from v$sql where sql_id=3fvcnc7ngu0gp；或者使用语句select dbms_lob.substr(sql_fulltext) from v$sql where sql_id=3fvcnc7ngu0gp；就能够得出完整的sql。第三种解决方法：使用第三方工具在V$sql中有sql_fulltext字段,它存储这完整的sql,字段类型是clob.使用pl/sql dev 直接打开就能看到完整的代码.一般的第三方oracle工具够有次功能。下面是完整的sql语句：SELECT LSWLDW.LSWLDW_WLDWBH as CustomersCode, LSWLDW.LSWLDW_DWMC as CustomersName,LSWLDW.LSWLDW_DWLB as C