缺少where条件也不会只在10.2上执行不成功，在11.1上执行成功，我个人觉得可能10.2的sql格式上和11.1有区别

检查一下SQL的括号对应，你的SQL这个位置括号多了吧？

抱歉我传错了sql

问题中的sql传错了，下面这个是原sql
SELECT
INST_ID AS INST_ID,
EVENT AS EVENT,
NVL(MS1,0) AS MS1,
NVL(MS2,0) AS MS2,
NVL(MS4,0) AS MS4,
NVL(MS8,0) AS MS8,
NVL(MS16,0) AS MS16,
NVL(MS32,0) AS MS32,
NVL(MS64,0) AS MS64,
NVL(MS128,0) AS MS128,
NVL(MS256,0) AS MS256,
NVL(MS512,0) AS MS512,
NVL(MS1024,0) AS MS1024,
NVL(MS2048,0) AS MS2048,
NVL(MS4096,0) AS MS4096,
NVL(MS8192,0) AS MS8192,
NVL(MS16384,0) AS MS16384,
NVL(MS32768,0) AS MS32768,
NVL(MS65536,0) AS MS65536,
NVL(MS131072,0) AS MS131072,
NVL(MS262144,0) AS MS262144,
NVL(MS524288,0) AS MS524288,
NVL(MS1048576,0) AS MS1048576,
NVL(MS2097152,0) AS MS2097152,
NVL(MS4194304,0) AS MS4194304
FROM
(
SELECT
*
FROM
(
SELECT
EH.INST_ID AS INST_ID,
EH.EVENT AS EVENT,
EH.WAIT_TIME_MILLI AS WAIT_TIME_MILLI,
EH.WAIT_COUNT AS WAIT_COUNT
FROM
GV$EVENT_NAME EN, GV$EVENT_HISTOGRAM EH
WHERE
EN.EVENT#=EH.EVENT#
AND EN.NAME=EH.EVENT
AND EN.INST_ID=EH.INST_ID
AND EN.WAIT_CLASS <> ‘Idle’
AND EH.WAIT_COUNT > 0) PIVOT (MAX(WAIT_COUNT) FOR WAIT_TIME_MILLI IN (1 AS MS1,
2 AS MS2,
4 AS MS4,
8 AS MS8,
16 AS MS16,
32 AS MS32,
64 AS MS64,
128 AS MS128,
256 AS MS256,
512 AS MS512,
1024 AS
MS1024,
2048 AS
MS2048,
4096 AS
MS4096,
8192 AS
MS8192,
16384 AS
MS16384,
32768 AS
MS32768,
65536 AS
MS65536,
131072 AS
MS131072,
262144 AS
MS262144,
524288 AS
MS524288,
1048576 AS
MS1048576,
2097152 AS
MS2097152,
4194304 AS
MS4194304)))

您在10g的数据库中无法用PIVOT和UNPIVOT。在11g才推出这个函数。
可以参考：https://stackoverflow.com/questions/13410464/pivot-on-oracle-10g

Prior to 11g we could accomplish a similar result using the DECODE function combined with aggregate functions.

解决办法改写SQL，把pivot改成其他方式实现。
例如用case when等写法：
select usr,
T1 + T2 + T3 as Total,
T1,
T2,
T3
from
(
select usr,
sum(case when tp =‘T1’ then cnt else 0 end) T1,
sum(case when tp =‘T2’ then cnt else 0 end) T2,
sum(case when tp =‘T3’ then cnt else 0 end) T3
from temp
group by usr
) src;

谢谢！