create or replace directory data_dir as '/oracle/oracle9i/admin/boss/bdump/'
create table alert_log ( text_line varchar2(255))
organization external(
type oracle_loader
default directory data_dir
access parameters(
records delimited by newline
fields
reject rows with all null fields
)
location (
'alert_boss.log'
)
)reject limit unlimited
select to_char(last_time, 'yyyy-mm-dd hh24:mi:ss') shutdown,
to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') startup,
round((start_time - last_time) * 24 * 60, 2) mins_down,
round((last_time - lag(start_time) over(order by r)), 2) days_up,
case when (lead(r) over(order by r) is null) then
round((sysdate - start_time), 2)
end days_still_up
from (select r,
to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
to_date(start_time, 'Dy Mon DD HH24:MI:SS YYYY') start_time --select *
from (select r,
text_line,
lag(text_line, 1) over(order by r) start_time,
lag(text_line, 2) over(order by r) last_time
from (select rownum r, text_line
from alert_log
where text_line like '___ ___ __ __:__:__ 20__'
or text_line like 'Starting ORACLE instance %'))
where text_line like 'Starting ORACLE instance %')
执行如上语句时出现:Ora-01846: not a valid day of the week 错误
从最内层开始检查(绿色部分),没有问题;
检查第二层(黑色部分),没有问题;
第三层(红色部分),这个问题出现了。检查具体的数据,发现“Mon Jan 21 13:25:47 2008”
是符合to_date()的格式转换要求的,但是为什么这里就报错了呢。
尝试以单个转换的方式来检查:
select to_date('Mon Jan 21 13:25:47 2008', 'dy mon dd hh24:mi:ss yyyy') from dual
结果还是报错,不大明白是什么原因了。
于是检查当前环境:
select to_date(sysdate, 'dy mon dd hh24:mi:ss yyyy') from dual
结果出现的是中文的字符日期,如下:星期五 12月 19 13:53:15 2008
难怪他老是报错呢,原来是字符集的问题,于是:
alter session set nls_date_language = 'American'
再次执行前面的SQL语句,果然搞定。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12932950/viewspace-517455/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12932950/viewspace-517455/