oracle v$sql中语句截断的解决方法
有时候想看看软件后台数据库到底执行了什么语句。比如大的erp系统，我们在查询的时候，后台执行了
什么语句或者过程都存储在v$sql表中。但是当sql语句很长的时候就出现了截断显现。
比如
select sql_text
from 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 OwnerType
From LSWLDW LEFT OUTER JOIN LSDWLB ON LSWLDW.LSWLDW_DWLB = LSDWLB.LSDWLB_LBBH
LEFT OUTER JOIN CodeItems OfTradeItem ON LSWLDW.OfTrade=OfTradeItem.Code AND
OfTradeItem.SetID='A003'
LEFT OUTER JOIN CodeItems OwnerType ON LSWLDW.OwnerType=OwnerType.Code AND
OwnerType.SetID='A004'
LEFT OUTER JOIN LSDQZD ON LSWLDW.LSWLDW_DQBH = LSDQZD.LSDQZD_DQBH where 1=1
and LSWLDW_TYBZ='0' and LSWLDW.LSWLDW_WLDWBH in
(select LSWLDW_WLDWBH from
(select Rownum rn,LSWLDW_WLDWBH from
(select LS
select LS下面的语句就被截断了.
第一种解决方法：通过sql语句实现
我们查找该语句的sql_id或者hash_value
select sql_text,sql_id,hash_value
from v$sql
结果为:
sql语句 3fvcnc7ngu0gp3908895221
通过查询v$sqltext显示完整的sql语句
select sql_text from v$sqltext
where hash_value='3908895221'
order by piece;
或者
from v$sqltext
where sql_id='3fvcnc7ngu0gp'
order by piece;
查询出来的结果到文本编辑器中整理格式就可以了.
第二种方法：使用sqlplus
在V$sql中有sql_fulltext字段,它存储这完整的sql,字段类型是clob
首先设置sqlplus
set heading off
set 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 CustomersNa
me,LSWLDW.LSWLDW_DWLB as CustomerSorts,LSDWLB_LBMC AS CustomerSortName,LSWLDW.LS
WLDW_DQBH as CustomerAreas,LSDQZD_DQMC AS CustomerAreaName, LSWLDW.HelpTag, LS
WLDW.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(Own
erType."NAME",' ') AS OwnerType From LSWLDW LEFT OUTER JOIN LSDWLB ON LSWLDW.LSW
LDW_DWLB = LSDWLB.LSDWLB_LBBH LEFT OUTER JOIN CodeItems OfTradeItem ON LSWLDW.Of
Trade=OfTradeItem.Code AND OfTradeItem.SetID='A003'LEFT OUTER JOIN CodeItems Own
erType ON LSWLDW.OwnerType=OwnerType.Code AND OwnerType.SetID='A004' LEFT OUTER
JOIN LSDQZD ON LSWLDW.LSWLDW_DQBH = LSDQZD.LSDQZD_DQBH where 1=1 and LSWLDW_TYB
Z='0' and LSWLDW.LSWLDW_WLDWBH in (select LSWLDW_WLDWBH from (select Rownum
rn,LSWLDW_WLDWBH from (select LSWLDW_WLDWBH from lswldw where 1=1 and L
SWLDW_TYBZ='0' order by lswldw_wldwbh ) where Rownum <= 12 ) where rn >= 1 )
ORDER BY LSWLDW.LSWLDW_WLDWBH。
如果还有好的方法，请赐教。谢谢！
