通过微软报表服务器访问Oracle做好张报表为了偷懒直接在数据库建立了个视图然后显示就行了结果测试时报ORA-600 [qks3tinit]登陆到本机运行没问题啊怀疑是微软报表服务器连接Oracle接口有问题但是其他几个类似视图又都没问题于是检测SQL:

SELECT (CASE
WHEN fjname IS NULL
THEN \'全局\'
ELSE fjname
END) fjname,
(CASE
WHEN stat_type IS NULL
THEN \'总计\'
ELSE stat_type
END) stat_type,
(CASE
WHEN cust_type_old IS NULL
THEN \'总计\'
ELSE cust_type_old
END
) cust_type_old,
cnt, curr_user
FROM (SELECT fjname, stat_type, cust_type_old, COUNT (*) cnt, SUM(CASE state WHEN \'正常\' THEN 1 ELSE 0 END) curr_user
FROM tmp_tj0507001
WHERE fjname IS NOT NULL
GROUP BY grouping s(rollup(fjname, stat_type, cust_type_old), rollup(stat_type, cust_type_old)))
ORDER BY fjname, stat_type, cust_type_old;

执行计划:

SELECT STATEMENT, GOAL = HINT: ALL_ROWSCost=10Cardinality=15Bytes=780
SORT ORDER BYCost=10Cardinality=15Bytes=780
VIEWObject owner=YAOYPCost=8Cardinality=15Bytes=780
TEMP TABLE TRANSFORMATION
VIEWObject owner=YAOYPCost=8Cardinality=4Bytes=208
VIEWObject owner=YAOYPCost=8Cardinality=4Bytes=208
UNION-ALL
TABLE ACCESS FULLObject owner=SYSObject name=SYS_TEMP_0FD9D668F_9A864B59Cost=2Cardinality=1Bytes=52
TABLE ACCESS FULLObject owner=SYSObject name=SYS_TEMP_0FD9D6690_9A864B59Cost=2Cardinality=1Bytes=52
TABLE ACCESS FULLObject owner=SYSObject name=SYS_TEMP_0FD9D6691_9A864B59Cost=2Cardinality=1Bytes=52
TABLE ACCESS FULLObject owner=SYSObject name=SYS_TEMP_0FD9D6691_9A864B59Cost=2Cardinality=1Bytes=52
RECURSIVE EXECUTIONObject name=SYS_LE_7_0
RECURSIVE EXECUTIONObject name=SYS_LE_7_1
RECURSIVE EXECUTIONObject name=SYS_LE_7_2
RECURSIVE EXECUTIONObject name=SYS_LE_7_3

就看到个TEMP TABLE TRANSFORMATION这个比较少见哦另外个类似SQL:

SELECT (CASE
WHEN fjname IS NULL
THEN \'全局\'
ELSE fjname
END) fjname,
(CASE
WHEN cust_type IS NULL
THEN \'总计\'


ELSE cust_type
END
) cust_type,
curr_user
FROM (SELECT fjname, (CASE WHEN cust_type IS NULL THEN \'公免等\' ELSE cust_type END) cust_type,
SUM(CASE state WHEN \'正常\' THEN 1 ELSE 0 END) curr_user
FROM tmp_tj0507001
WHERE fjname IS NOT NULL AND state = \'正常\'
GROUP BY ROLLUP (fjname, (CASE WHEN cust_type IS NULL THEN \'公免等\' ELSE cust_type END)))
ORDER BY fjname, cust_type;

执行计划:

SELECT STATEMENT, GOAL = CHOOSECost=3021Cardinality=43Bytes=1376
SORT ORDER BYCost=3021Cardinality=43Bytes=1376
VIEWObject owner=YAOYPCost=3020Cardinality=43Bytes=1376
SORT GROUP BY ROLLUPCost=3020Cardinality=43Bytes=989
INDEX FAST FULL SCANObject owner=YAOYPObject name=SYS_IOT_TOP_62350Cost=367Cardinality=1132418Bytes=26045614


没有TEMP TABLE TRANSFORMATION了看来要避免这个执行路径想了半天也没办法开始还怀疑是参数STAR_TRANSFORMATION_ENABLED问题后来看是FALSE只能到Metalink上找了结果又是个Bug最近简直是出问题就是Bug以前如何就不觉得呢？

文档 ID: 注释:2836690.8
主题: Support Description of Bug 2836690
类型: PATCH
状态: PUBLISHED

内容类型: TEXT/X-HTML
创建日期: 08-AUG-2003
上次修订日期: 14-AUG-2003
Click here for details of sections in this note.
Bug 2836690 ROLLUP over DBLINK may fail with OERI:qks3tinit
This note gives a brief overview of bug 2836690.
Affects:

Product (Component)Oracle Server (RDBMS)
Range of versions believed to be affectedVersions >= 9 but < 10G
Versions confirmed as being affected

* 9.2.0.3

Platforms affectedGeneric (all / most platforms affected)

Fixed:

This issue is fixed in

* 9.2.0.4 (Server Patch Set)
* 10G Production Base Release

Symptoms:

* Internal Error may occur (ORA-600)
* ORA-600 [qks3tinit]

Related To:

* DB-Link / Distributed
* Analytic SQL (Windowing etc..)

Description

ORA-600 [qks3tinit] possible from queries using ROLLUP which reference
remote objects.

The full bug text ( published) can be seen at <Bug:2836690>
This link will not work for UNPUBLISHED bugs.

类似还有个Bug:

文档 ID: 注释:2419335.8
主题: Support Description of Bug 2419335
类型: PATCH
状态: PUBLISHED

内容类型: TEXT/X-HTML
创建日期: 08-AUG-2003
上次修订日期: 14-AUG-2003
Click here for details of sections in this note.
Bug 2419335 OERI:[qks3tInit] possible from STAR transformation
This note gives a brief overview of bug 2419335.
Affects:

Product (Component)Oracle Server (RDBMS)
Range of versions believed to be affectedVersions >= 9.2 but < 10G
Versions confirmed as being affected

* 9.2.0.3

Platforms affectedGeneric (all / most platforms affected)

Fixed:

This issue is fixed in

* 9.2.0.4 (Server Patch Set)
* 10G Production Base Release

Symptoms:

* Internal Error may occur (ORA-600)
* ORA-600 [qks3tInit]

Related To:

* DB-Link / Distributed
* Star Transformation

Description

ORA-600 [qks3tInit] possible from star transformation query
where all the tables are remote (ie: over a dblink)

Workaround:
Set star_transformation_enabled = false at the remote DB.

The full bug text ( published) can be seen at <Bug:2419335>


This link will not work for UNPUBLISHED bugs.

不过我还是觉得是TEMP TABLE TRANSFORMATION问题没理由第2个SQL里ROLLUP就没问题哦唉放弃ROLLUP咯有关上面这个执行路径资料好少有办法避免么？