在公司的一台linux服务器上的oracle 10g数据库上运行一个存储过程,每次运行1,2小时的样子开始报错,ORA-3113 "end of file on communication channel" 。开始用pl sql developer上执行,后来直接在服务器上用sqlplus 执行,还是报一样的错误。查看数据库alert日志,并没有报错。这个存储过程的工作大致就是从另外两台linux服务器上数据库上的表,通过数据库同义词查询更新后,然后将数据插入到自己数据库上的一个表,执行起来比较费时间。搞不懂什么原因,请高手指点一下。
解决方案 »
- 新手请教:Oracle服务器隔几天Down一次,怎么分析?
- oracle定时删除表中的某条记录问题???
- oracle启动时,open,mount,nomount有啥区别
- 表最重要的特征是什么?以及表、数据库、约束的定义
- 如何在pro*c中调用存储过程,且名字未知
- sql server中的存储过程在oracle中怎么写?
- 祝大家国庆快乐
- 请教:change archivelog all validate的作用以及含义
- what is the different between sql*plus and plus/sql ?
- 子查询可以无限制地嵌套下去吗?有没有限制?限制是多少?
- oracel9i 安装过程关于unzip.exe写入失败的问题
- ”全局数据库名称“与”数据库SID“
ALTER SYSTEM DISCONNECT SESSION ... IMMEDIATE
SHUTDOWN ABORT/IMMEDIATE/TRANSACTIONAL
Action: If this message occurs during a connection attempt, check the setup files for the appropriate Net8 driver and confirm Net8 software is correctly installed on the server. If the message occurs after a connection is well established, and the error is not due to a physical failure, check if a trace file was generated on the server at failure time. Existence of a trace file may suggest an Oracle internal error that requires the assistance of Oracle Support Services.
尝试把SQL进行分开执行,也可以用SQL_TRACE来进行跟踪,找到导致问题的SQL语句。在SQLPlus下:
ALTER SESSION SET sql_trace=TRUE;SQL语句中的非法字符和不合理的处理结果,甚至一些不可解释的原因偶尔会带来问题.
SQL问题举例:
SELECT *
FROM (SELECT ROWNUM AS num, k.*
FROM (SELECT a.cp_code, c.cp_cha_name, a.service_code,
a.service_name, a.content_name,
SUBSTR (a.access_time, 1, 8) thedate,
COUNT (*) AS hit_count
FROM sm_wap_log_daily_tab a, t_cp_info c
WHERE (SUBSTR (a.access_time, 1, 8) BETWEEN '20040301'
AND '20040304'
)
AND c.cp_code LIKE '%%'
AND a.cp_code = c.cp_code
AND a.service_code LIKE '%%'
GROUP BY a.cp_code,
c.cp_cha_name,
a.service_code,
a.service_name,
a.content_name,
SUBSTR (a.access_time, 1, 8)
ORDER BY a.cp_code,
a.service_code,
a.content_name,
SUBSTR (a.access_time, 1, 8) DESC) k) n;
上面这条语句在9204/Linux 系统上始终出现03113 的错误。对语句进行细化,分成小一点的子语句逐步执行,最后判定问题出现在
ORDER BY a.cp_code,
a.service_code,
a.content_name,
SUBSTR (a.access_time, 1, 8) DESC) k) n;
中的 SUBSTR (a.access_time, 1, 8) 这里。去掉SUBSTR (a.access_time, 1, 8)则问题不再出现。尝试调整SUBSTR (a.access_time, 1, 8) 的位置,语句得到通过。之后,顺便优化一下该语句。:)
SELECT *
FROM (SELECT ROWNUM AS num, k.*
FROM (SELECT a.cp_code, c.cp_cha_name, a.service_code,
a.service_name, a.content_name,
SUBSTR (a.access_time, 1, 8) thedate,
COUNT (*) AS hit_count
FROM sm_wap_log_daily_tab a, t_cp_info c
WHERE (SUBSTR (a.access_time, 1, 8) BETWEEN '20040301'
AND '20040304'
)
AND c.cp_code LIKE '%%'
AND c.cp_code = a.cp_code
AND a.service_code LIKE '%%'
GROUP BY a.cp_code,
c.cp_cha_name,
a.service_code,
a.service_name,
a.content_name,
SUBSTR (a.access_time, 1, 8)
ORDER BY (SUBSTR (a.access_time, 1, 8)),
a.cp_code,
a.service_code,
a.content_name DESC) k) n;