昨天在测试的时候发现，开发人员写的一段程序放在开发库中是好的，但是放在测试库中就会有问题。开发人员一直找不到问题的原因在哪里。于是就花了点时间协助开发人员来找问题的根本因为。
通过一些技术手段，定位到问题是由一个SQL语句引起的。
SQL语句如下：
(SELECT A.DISTRIBUTE_DATE,
A.APP_NO,
A.TASK_ID,
A.TASK_NO,
A.ACTUAL_DISTRIBUTE_DATE,
A.ACTUAL_RETURN_DATE,
A.RELA_ID RELAID,
A.VEHICLE_SERIAL,
A.STATUS_CODE,
A.MONTH,
A.MADE_DPET_NO,
A.DIST_TYPE_CODE,
A.TASK_TYPE,
A.RETURN_DATE,
B.SOURCE_NODE_ID,
B.RCV_NODE_NAME,
B.RCV_NODE_ID,
B.RCV_ID,
C.DET_ID,
C.EQUIP_CATEG,
C.TASK_NUM,
D.RELA_ID
FROM D_DISTRIBUTE_TASK A,
D_RCV_TASK B,
D_DIST_TASK_DET C,
ECMS.D_VEHICLE_TASK_RELA D
WHERE A.TASK_ID = B.TASK_ID(+)
AND B.RCV_ID = C.RCV_ID(+)
AND A.TASK_ID = D.TASK_ID(+)),
PXQ_TASK_CATEG AS
(SELECT DISTINCT TASK_ID, RCV_ID, DET_ID, EQUIP_CATEG, TASK_NUM
FROM PXQ_TASK_TEMP),
PXQ_TASK_SUM AS
(SELECT V.NAME || ':' || SUM(TASK_NUM) TASK_SUM, MAX(PXQ.TASK_ID) TASK_ID
FROM V_SP_CODE V, PXQ_TASK_CATEG PXQ
WHERE CODE_TYPE LIKE 'equipCateg'
AND VALUE = EQUIP_CATEG
GROUP BY PXQ.TASK_ID, V.NAME)
SELECT DISTINCT AA.DISTRIBUTE_DATE PLAN_DATE,
AA.APP_NO,
AA.MONTH,
AA.TASK_ID,
AA.TASK_NO,
AA.ACTUAL_DISTRIBUTE_DATE,
AA.ACTUAL_RETURN_DATE,
AA.RELAID,
AA.VEHICLE_SERIAL EV_NO,
AA.STATUS_CODE,
AA.DIST_TYPE_CODE,
AA.TASK_TYPE,
AA.RETURN_DATE,
(SELECT REPLACE(WMSYS.WM_CONCAT(NODE_NAME), ',', ';')
FROM ECMS.D_DISTRIBUTE_NODE
WHERE NODE_ID IN (SELECT DISTINCT SOURCE_NODE_ID
FROM PXQ_TASK_TEMP
WHERE TASK_ID = AA.TASK_ID)) SOURCE_NODE,
(SELECT REPLACE(WMSYS.WM_CONCAT(NODE_NAME), ',', ';')
FROM ECMS.D_DISTRIBUTE_NODE
WHERE NODE_ID IN (SELECT DISTINCT RCV_NODE_ID
FROM PXQ_TASK_TEMP
WHERE TASK_ID = AA.TASK_ID)) RCV_NODE,
(SELECT REPLACE(WMSYS.WM_CONCAT(TASK_SUM), ',', '/')
FROM PXQ_TASK_SUM
WHERE TASK_ID = AA.TASK_ID) PLAN_NUM,
'' EV_NUM,
'' SETTED_EV
FROM PXQ_TASK_TEMP AA
WHERE 1 = 1;
把这个SQL语句放在开发库可以正常执行，但是放在测试库就报错了。报错信息为：ORA-00932:数据类型不一致:应为 -，但却获得CLOB。当时想到的就是两个库中可能有些表的数据库数据结构不一致（如：字段的数据类型），经比较这个SQL语句中涉及到的表在两个环境中的数据结构完全一致。这时想到的就是可能是某个函数导致了结果出现了LOB类型。经诊断是由oracle函数WMSYS.WM_CONCAT引起的。那么为什么会引起这样的问题呢？两套环境同样是使用的oracle，程序也完全一致。首先想到的是可能oracle的WMSYS.WM_CONCAT函数存在版本差异。
下面就进行了WMSYS.WM_CONCAT函数在oracle版本差异的验证。在开发库中执行SELECT * FROM v$version;显示结果为：Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi；再在测试库中执行SELECT * FROM v$version;显示结果为：Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi。说明oracle版本差异的猜测是对的。
接下来看WMSYS.WM_CONCAT函数在不同oracle版本中到底存在什么差异。
编写测试SQL：
FROM ECMS.D_DISTRIBUTE_NODE A
WHERE NODE_ID IN (SELECT DISTINCT SOURCE_NODE_ID FROM D_RCV_TASK B);
上篇介绍了从linux服务器上把数据库导出的过程。
接下来在一台新安装的windows 2003 64bit的机子上面部署同样的库。
于是就有了从安装oracle->建实例->导入数据库的全部流程。
一、安装oracle11g（第二版11.2.0.1.0）这里是服务端和客户端都带全了的。
官网下载（中文版网页）：http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/index.html
选择win64，http://www.oracle.com/technetwork/cn/database/enterprise-edition/downloads/112010-win64soft-086667-zhs.html
把1和2两部分都下载下来，解压到同一个目录。
安装流程图形界面的，可参考链接：http://www.doc88.com/p-801578348336.html
二、创建实例
DBCA：数据库配置助手 打开db configure assistant 来创建实例。
三、导入的过程（sqlplus里执行的）
create user drag identified bydragtest;#创建用户
GRANT DBA TO drag;
create directory dump_dir as 'D:\oracle_dump';#要在系统里面手动创建该目录
grant read,write on directory dump_dir to drag;
create tablespace drag datafile 'd:\oracle_dbf\DragSpace.dbf' size 100m reuse #创建原来库里面用到的表空间。
autoextend on maxsize unlimited
default storage(initial 320k
next 320k
minextents 1
maxextents unlimited
pctincrease 0);
切换到系统下执行以下 导入：
C:>impdp drag/dragtest DIRECTORY=dump_dir DUMPFILE=drag130.dmp SCHEMAS=drag REUSE_DATAFILES=y;#要复用数据文件，上面创建的数据文件名要和原来的库里一样。
本文链接
故障现象：临时表空间不足的问题已经报错过3次，客户也烦了，前两次都是同事添加5G的数据文件，目前已经达到40G，占用临时表空间主要是distinct 和group by 以及Union all 表数据量在200W左右，也不至于把40G的临时表空间撑爆。
原因分析：既然排序用不了这么多临时表空间应该是别的原因造成。
从包含故障时间段的AWR报告中可以看出这一阶段DBtime蛮高的，并且sql execute elapsed time 竟然占到了99.43%，可以断定是SQL语句引起的。
通过TOP SQL定位到出问题的SQL
确认是以下SQL引起：
select 'A',
d.explanation, --金融机构标识码
c.account_no, --交易账号
to_date(a.batchentrydate, 'yyyy-mm-dd'), --发生日期
c.currencycode, --币种
SUM(decode(A.Creditdebit, 'C', a.transactionamount, 0)), --当日贷方发生额
SUM(decode(A.Creditdebit, 'D', a.transactionamount, 0)), --当日借方发生额
case
when C.Currencycode = 'JPY' Then
Round(c.Ccyledgerbalance, 0)
else
c.ccyledgerbalance
End Balance, --账户余额
--b.instcode instcode, --系统虚拟机构代号
1 datastatus, --前台对应的数据状态
c.account_no || c.currencycode || '2013-01-04',
to_date('2013-01-04', 'yyyy-mm-dd')
from df_cust C
left join (select distinct ACCOUNTBRANCH,
DESCRIPTION,
MASTERNO,
CURRENCYCODE,
ACCOUNT_NUMBER,
SEQNO,
ACCT_CLASS_CODE,
PRODUCTCODE,
VALUEDT_YYYY,
VALUEDT_MM,
VALUEDT_DD,
BATCHENTRYDATE,
VALUEDT_YYYYMMDD,
NARRATIONPOST,
TRANSACTIONAMOUNT,
CREDITDEBIT,
ACCOUNTBRANCH1,
SEGMENTCODE,
REFERENCENUMBER,
NARRATIONTRAN,
BATCHNUMBER,
GLDEPTID,
ARMCODE,
EXTREFNO,
MAKERID,
CHECKERID,
CHANNELID,
TRANSACTION_AMT_IN_USD,
ACCSHORTNAME,
ARMNAME,
SEGNAME,
TXNCODE,
REVERSALFLAG,
EBBSREFERENCE,
TRANSTYPECODE,
CUSTOMERRATE,
ADVTREASURYFLAG,
VA_FLAG
from df_acmov_today
where Creditdebit in ('C', 'D')) a on a.account_number =
c.account_no
Left Join Da_Mid_Acc_Gl_Dic D On D.Source = A.Accountbranch
Where exists (select 1
from acc.t_base_account b
where b.account = c.account_no
and b.currence_code = c.currencycode)
and a.account_number is not null
and c.account_no like '0%'
group by d.explanation, --金融机构标识码
c.account_no, --交易账号
a.batchentrydate, --发生日期
c.currencycode, --币种
C.Ccyledgerbalance--系统机构代号
观察并分析其执行计划，貌似也没有什么问题，因为df_acmov_today（200W左右数据）是每天都清空的，没有索引，全表扫描，nestloops也正常。
但是在执行SQL语句时通过脚本监控临时表空间的使用情况，发现临时表空间使用率很快就达到了40G左右。又要临时表空间不足了…
使用dbms_stats.gather_table_stats 分析了下表，然后再去执行语句，发现很快。这下问题清楚了，SQL执行计划错误导致的问题。
在对比下先前的SQL执行计划，发现在执行计划中基数不对，竟然为1 ，估算的差距太大了。
为什么每天做分析的表（crontab job)最后执行计划却不对？
最后竟然是这样：使用crontab 在凌晨2:30对表做分析，但是早上6点。其他任务对表做了，truncate 和Insert into 从而导致该原因。
最终调整计划任务时间问题完全解决。
本文链接