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,yer,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_LBBH LEFT OUTER JOIN CodeItems OfTradeItem ON LSWLDW.OfTrade=OfTradeItem.Code ANDOfTradeItem.SetID='A003'LEFT OUTER JOIN CodeItems OwnerType ON LSWLDW.OwnerType=OwnerType.Code ANDOwnerType.SetID='A004'LEFT OUTER JOIN LSDQZD ON LSWLDW.LSWLDW_DQBH=LSDQZD.LSDQZD_DQBH where1=1 and 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语句3fvcnc7ngu0gp3908895221通过查询v$sqltext显示完整的sql语句select sql_text from v$sqltextwhere hash_value='3908895221'order by piece;或者from v$sqltextwhere sql_id='3fvcnc7ngu0gp'order by piece;查询出来的结果到文本编辑器中整理格式就可以了.第二种方法：使用sqlplus在V$sql中有sql_fulltext字段,它存储这完整的sql,字段类型是clob首先设置sqlplusset heading offset long40000其次输入查询语句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,LSWLDW.LSWLDW_JC as ShortName,nvl(LSWLDW.LSWLDW_SH,'')as SH,LSWLDW.IsDetail as Detail,yer,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 where1=1and LSWLDW_TYB Z='0'and LSWLDW.LSWLDW_WLDWBH in(select LSWLDW_WLDWBH from(selectRownumrn,LSWLDW_WLDWBH from(select LSWLDW_WLDWBH from lswldw where1=1and LSWLDW_TYBZ='0'order by lswldw_wldwbh)where Rownum<=12)where rn>=1)ORDER BY LSWLDW.LSWLDW_WLDWBH。
如果还有好的方法，请赐教。
谢谢！v$sqlarea,v$sql,v$sqltext提供的sql语句区别？来源：本站整理作者：佚名时间：2006-08-0220:46:33v$sqltext存储的是完整的SQL,SQL被分割SQL>desc v$sqltextName Null?Type-----------------------------------------------------------------------------ADDRESS RAW(4)---------HASH_VALUE NUMBER---------和address一起唯一标志一条sqlCOMMAND_TYPE NUMBERPIECE NUMBER----------分片之后的顺序编号SQL_TEXT VARCHAR2(64)--------------注意长度v$sqlarea---------存储的SQL和一些相关的信息，比如累计的执行次数，逻辑读，物理读等统计信息SQL>desc v$sqlareaName Null?Type-----------------------------------------------------------------------------SQL_TEXT VARCHAR2(1000)SHARABLE_MEM NUMBERPERSISTENT_MEM NUMBERRUNTIME_MEM NUMBERSORTS NUMBERVERSION_COUNT NUMBERLOADED_VERSIONS NUMBEROPEN_VERSIONS NUMBERUSERS_OPENING NUMBERFETCHES NUMBEREXECUTIONS NUMBERUSERS_EXECUTING NUMBERLOADS NUMBERFIRST_LOAD_TIME VARCHAR2(38)INVALIDATIONS NUMBERPARSE_CALLS NUMBERDISK_READS NUMBERBUFFER_GETS NUMBERROWS_PROCESSED NUMBERCOMMAND_TYPE NUMBEROPTIMIZER_MODE VARCHAR2(25)PARSING_USER_ID NUMBERPARSING_SCHEMA_ID NUMBERKEPT_VERSIONS NUMBERADDRESS RAW(4)HASH_VALUE NUMBERMODULE VARCHAR2(64)MODULE_HASH NUMBERACTION VARCHAR2(64)ACTION_HASH NUMBERSERIALIZABLE_ABORTS NUMBERCPU_TIME NUMBERELAPSED_TIME NUMBERIS_OBSOLETE VARCHAR2(1)CHILD_LATCH NUMBERv$sql----------存储的是具体的SQL和执行计划相关信息，实际上，v$sqlarea可以看做v$sql根据sqltext等做了group by之后的信息SQL>desc v$sqlName Null?Type-----------------------------------------------------------------------------SQL_TEXT VARCHAR2(1000)SHARABLE_MEM NUMBERPERSISTENT_MEM NUMBERRUNTIME_MEM NUMBERSORTS NUMBERLOADED_VERSIONS NUMBEROPEN_VERSIONS NUMBERUSERS_OPENING NUMBERFETCHES NUMBEREXECUTIONS NUMBERUSERS_EXECUTING NUMBERLOADS NUMBERFIRST_LOAD_TIME VARCHAR2(38)INVALIDATIONS NUMBERPARSE_CALLS NUMBERDISK_READS NUMBERBUFFER_GETS NUMBERROWS_PROCESSED NUMBERCOMMAND_TYPE NUMBEROPTIMIZER_MODE VARCHAR2(10)OPTIMIZER_COST NUMBERPARSING_USER_ID NUMBERPARSING_SCHEMA_ID NUMBERKEPT_VERSIONS NUMBERADDRESS RAW(4)TYPE_CHK_HEAP RAW(4)HASH_VALUE NUMBERPLAN_HASH_VALUE NUMBERCHILD_NUMBER NUMBER----------注意这个MODULE VARCHAR2(64)MODULE_HASH NUMBERACTION VARCHAR2(64)ACTION_HASH NUMBERSERIALIZABLE_ABORTS NUMBEROUTLINE_CATEGORY VARCHAR2(64)CPU_TIME NUMBERELAPSED_TIME NUMBEROUTLINE_SID NUMBER--------------注意这里跟outline有关CHILD_ADDRESS RAW(4)SQLTYPE NUMBERREMOTE VARCHAR2(1)OBJECT_STATUS VARCHAR2(19)LITERAL_HASH_VALUE NUMBERLAST_LOAD_TIME VARCHAR2(38)IS_OBSOLETE VARCHAR2(1)CHILD_LATCH NUMBER另外注意这个QL>desc v$sql_planName Null?Type-----------------------------------------------------------------------------ADDRESS RAW(4)HASH_VALUE NUMBERCHILD_NUMBER NUMBER------------注意这个和v$sql里面的相同字段OPERATION VARCHAR2(60)OPTIONS VARCHAR2(60)OBJECT_NODE VARCHAR2(20)OBJECT#NUMBEROBJECT_OWNER VARCHAR2(30)OBJECT_NAME VARCHAR2(64)OPTIMIZER VARCHAR2(40)ID NUMBERPARENT_ID NUMBERDEPTH NUMBERPOSITION NUMBERSEARCH_COLUMNS NUMBERCOST NUMBERCARDINALITY NUMBERBYTES NUMBEROTHER_TAG VARCHAR2(70)PARTITION_START VARCHAR2(10)PARTITION_STOP VARCHAR2(10)PARTITION_ID NUMBEROTHER VARCHAR2(4000)DISTRIBUTION VARCHAR2(40)CPU_COST NUMBERIO_COST NUMBERTEMP_SPACE NUMBERACCESS_PREDICATES VARCHAR2(4000)FILTER_PREDICATES VARCHAR2(4000)实际上，看起来同样的一句SQL，往往具有不同的执行计划如果是不同的数据库用户，那么相应的涉及的对象可能都不一样，注意v$sql中OBJECT#NUMBEROBJECT_OWNER VARCHAR2(30)OBJECT_NAME VARCHAR2(64)OPTIMIZER VARCHAR2(40)即使是相同的数据库用户，若session的优化模式、session级的参数等不一样，执行计划也能不同。