Select *
From (Select A.*, ROWNUM RN
From (Select caseinfotable.ID,
caseinfotable.CASEID,
caseinfotable.INSURNO,
XZCLASS,
Insuredpeople,
ISREN,
ISWU,
Compensation,
Replacementsum,
MaintenanceTime,
PEOPLESUM,
ITEMSSUM,
RescueSUM,
otherSUM,
CASESUM,
TJDATE,
ISFZ,
ISJC,
basorganization.chiname as Commitmentdept,
COMPENSATIONCASENUMBER,
othernumber,
(Operators || basemployee.employeename) as operators,
operdept,
reportdate,
outcasedate,
(case
when casetype = 0 then
'待查勘'
when casetype = 1 then
'查勘完成'
when casetype = 2 then
'复勘完成'
when casetype = 20 then
'申请复勘'
when casetype = 3 then
'定损完成'
when casetype = 30 then
'打回定损'
when casetype = 4 then
'核损完成'
when casetype = 40 then
'核损上报'
when casetype = 5 then
'核价完成'
when casetype = 50 then
'核价上报'
when casetype = 6 then
'稽查完成'
when casetype = 7 then
'理算完成'
when casetype = 8 then
'核赔完成'
when casetype = 80 then
'核赔上报'
when casetype = 9 then
'核批完成'
when casetype = 10 then
'拒赔完成'
when casetype = 11 then
'销案完成'
when casetype = 12 then
'结案完成'
when casetype = 13 then
'撤案完成'
when casetype = 14 then
'赔案批改'
when casetype = 15 then
'不立案'
end) casetype,
(case
when peoplecasetype = 0 then
'待查勘'
when peoplecasetype = 1 then
'查勘完成'
when peoplecasetype = 2 then
'复勘完成'
when peoplecasetype = 20 then
'申请复勘'
when peoplecasetype = 31 then
'定损完成'
when peoplecasetype = 310 then
'打回定损'
when peoplecasetype = 41 then
'核价完成'
when peoplecasetype = 410 then
'核价上报'
end) peoplecasetype,
(case
when wucasetype = 0 then
'待查勘'
when wucasetype = 1 then
'查勘完成'
when wucasetype = 2 then
'复勘完成'
when wucasetype = 20 then
'申请复勘'
when wucasetype = 32 then
'定损完成'
when wucasetype = 320 then
'打回定损'
when wucasetype = 42 then
'核价完成'
when wucasetype = 420 then
'核价上报'
end) wucasetype,
casetimetable.A1,
casetimetable.A3,
casetimetable.A4,
casetimetable.A5,
casetimetable.A7,
casetimetable.A8
From CASEINFOTABLE,
basorganization,
basemployee,
casetimetable
where caseinfotable.commitmentdept =
basorganization.organizationcode
and caseinfotable.operators = basemployee.engname
And caseinfotable.caseid = casetimetable.caseid
And CaseType >= 1
and CaseType < 3
and caseinfotable.iscar = 1
And (COMMITMENTDEPT Like 'AEAAAC' or
OPERDEPT Like 'AEAAAC%' or SANGBAODEPT Like 'AEAAAC%' or
soulidept Like 'AEAAAC%' OR
COMMITMENTDEPT Like 'AEAAAF' or
OPERDEPT Like 'AEAAAF%' or SANGBAODEPT Like 'AEAAAF%' or
soulidept Like 'AEAAAF%')
Order by CaseinfoTable.TjDate Desc) A
Where ROWNUM <= 25)
Where RN >= 1执行这个SQL,会报两个错。提示:ORA-03113:通信通道的文件结束。ORA-03114:未连接到ORACLE。
同样是这个SQL,以前都不会报错,就是最近才报错,不能运行了,请高手指点一二。如果把上面这个SQL,加上这一个条件,就可以正常执行了。and caseinfotable.tjdate >= date'2004-1-1'
create table t_wh_tmpbasemployee as select * from basemployee;然后,在SQL中,把用到basemployee表的地方,全部替换成t_wh_tmpbasemployee 就不报那两个错了。不过,还是没有找到原因。
1. 如果是客户机访问,确保网络通信状况一直OK,或者在服务机上执行一把看看。2. 检查session相关的trc文件的和日志文件,检查有没有一些异常的信息。根据这些信息再进一步排查。
后来检查是网络的问题,你可以确认下。
另外,不过是什么错误,你都可以看下trc文件来进行确定详细的。
From (Select caseinfotable.ID,
caseinfotable.CASEID,
caseinfotable.INSURNO,
XZCLASS,
Insuredpeople,
ISREN,
ISWU,
Compensation,
Replacementsum,
MaintenanceTime,
PEOPLESUM,
ITEMSSUM,
RescueSUM,
otherSUM,
CASESUM,
TJDATE,
ISFZ,
ISJC,
basorganization.chiname as Commitmentdept,
COMPENSATIONCASENUMBER,
othernumber,
(Operators || basemployee.employeename) as operators,
operdept,
reportdate,
outcasedate,
(case
when casetype = 0 then
'待查勘'
when casetype = 1 then
'查勘完成'
when casetype = 2 then
'复勘完成'
when casetype = 20 then
'申请复勘'
when casetype = 3 then
'定损完成'
when casetype = 30 then
'打回定损'
when casetype = 4 then
'核损完成'
when casetype = 40 then
'核损上报'
when casetype = 5 then
'核价完成'
when casetype = 50 then
'核价上报'
when casetype = 6 then
'稽查完成'
when casetype = 7 then
'理算完成'
when casetype = 8 then
'核赔完成'
when casetype = 80 then
'核赔上报'
when casetype = 9 then
'核批完成'
when casetype = 10 then
'拒赔完成'
when casetype = 11 then
'销案完成'
when casetype = 12 then
'结案完成'
when casetype = 13 then
'撤案完成'
when casetype = 14 then
'赔案批改'
when casetype = 15 then
'不立案'
end) casetype,
(case
when peoplecasetype = 0 then
'待查勘'
when peoplecasetype = 1 then
'查勘完成'
when peoplecasetype = 2 then
'复勘完成'
when peoplecasetype = 20 then
'申请复勘'
when peoplecasetype = 31 then
'定损完成'
when peoplecasetype = 310 then
'打回定损'
when peoplecasetype = 41 then
'核价完成'
when peoplecasetype = 410 then
'核价上报'
end) peoplecasetype,
(case
when wucasetype = 0 then
'待查勘'
when wucasetype = 1 then
'查勘完成'
when wucasetype = 2 then
'复勘完成'
when wucasetype = 20 then
'申请复勘'
when wucasetype = 32 then
'定损完成'
when wucasetype = 320 then
'打回定损'
when wucasetype = 42 then
'核价完成'
when wucasetype = 420 then
'核价上报'
end) wucasetype,
casetimetable.A1,
casetimetable.A3,
casetimetable.A4,
casetimetable.A5,
casetimetable.A7,
casetimetable.A8
From CASEINFOTABLE,
basorganization,
basemployee,
casetimetable
where caseinfotable.commitmentdept =
basorganization.organizationcode
and caseinfotable.operators = basemployee.engname
And caseinfotable.caseid = casetimetable.caseid
And CaseType >= 1
and CaseType < 3
and caseinfotable.iscar = 1
And (COMMITMENTDEPT Like 'AEANOO' or
OPERDEPT Like 'AEANOO%' or SANGBAODEPT Like 'AEANOO%' or
soulidept Like 'AEANOO%' OR
COMMITMENTDEPT Like 'AEAAAL' or
OPERDEPT Like 'AEAAAL%' or SANGBAODEPT Like 'AEAAAL%' or
soulidept Like 'AEAAAL%' OR
COMMITMENTDEPT Like 'AEAAAH' or
OPERDEPT Like 'AEAAAH%' or SANGBAODEPT Like 'AEAAAH%' or
soulidept Like 'AEAAAH%' OR
COMMITMENTDEPT Like 'AEAC00' or
OPERDEPT Like 'AEAC00%' or SANGBAODEPT Like 'AEAC00%' or
soulidept Like 'AEAC00%' OR
COMMITMENTDEPT Like 'AEAD00' or
OPERDEPT Like 'AEAD00%' or SANGBAODEPT Like 'AEAD00%' or
soulidept Like 'AEAD00%' )
Order by CaseinfoTable.TjDate Desc) A
http://www.hy11.com.cn/uploadfile/yai_ora_3448.txt
有这样的信息
*** 2009-09-30 13:48:22.484
ksedmp: internal or fatal error
ORA-07445: 出现异常错误: 核心转储 [ACCESS_VIOLATION] [kkfipbr+7] [PC:0x1D8E519] [ADDR:0x0] [UNABLE_TO_READ] []一个7445的信息,这里也是一个类似600的棘手错误号,我这里搜集了2中7445的解决方法,不过不见得适合你的错误,你试试先,
http://www.inthirties.com/thread-220-1-1.html
http://www.inthirties.com/thread-221-1-1.html不行的话,需要查找一下相关的metalink。
感谢inthirties,热心有实力的老鸟!!
如果我把 case 的那几列去掉,问题依旧,还是要报错
select * from abc 这个句语很顺利,也是是0.0001ms的时间
但执行
select * from abc where ruwnum <=25 ,则至少要等上2秒以上的时间才能出结果
把sql语句进行分解排查,一定能找到原因、、
SQL> select * from scott.emp where rownum<=14; EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7788 SCOTT ANALYST 7566 19-APR-87 3000
20 7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100
20 7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20 7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.Elapsed: 00:00:00.10
SQL> select * from scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10 7788 SCOTT ANALYST 7566 19-APR-87 3000
20 7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100
20 7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM---------- ---------- --------- ---------- --------------- ---------- ---------- DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20 7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows selected.Elapsed: 00:00:00.10
另外 你的rownum写错了 呵呵