本帖最后由 Ashic 于 2016-6-4 12:17 编辑
今日发现今天凌晨跑的job出现了报错
Log ID, Log date, Status, Error, Requested start date, Actual start date, Run duration, Instance ID, Session ID, Slave PID, CPU Used, Additional info
6719, 2016/6/3 5:00:37, FAILED, 904, 2016/6/3 5:00:01, 2016/6/3 5:00:01, 0 00:00:37, 1, 262,12451, 31017, 0 00:00:30.98, "ORA-00904: ""K"".""USER_NAME"": 标识符无效
ORA-06512: 在 ""CHANNEL.ADD_INVEST_DATA_DAY"", line 110
ORA-06512: 在 ""CHANNEL.PR_EXECUTE_JOB_DAY"", line 21
该job是运行一个procedure，这个procedure有调用了n个procedure。根据报错信息找到出错的 过程 ADD_INVEST_DATA_DAY
create or replace procedure add_invest_data_day(p_strday varchar2 default to_char(sysdate-1,'yyyymmdd'),
p_type varchar2,
pn_jobno number,
Pn_Day Number default to_number(to_char(sysdate,'yyyymmdd'))) Is
Start_time varchar2(30);
End_time varchar2(30);
lv_procname varchar2(32) := lower('add_invest_data_day');
ln_rowid rowid;
v_start varchar2(8);
ln_cnt number := 0;
err_info varchar2(200);
pn_code number :=0;
pv_info varchar2(200);
begin
if (p_type = '1') then
v_start := to_char(to_date(p_strday,'yyyymmdd') - 6,'yyyymmdd');
elsif (p_type = '2') then
v_start := to_char(to_date(p_strday,'yyyymmdd') - 30,'yyyymmdd');
end if;
Start_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => Start_time, pv_info => '开始执行 add_invest_data_day ');
p_ymstat_run_check(pn_job => pn_jobno,
pn_statedate => Pn_Day,
po_code => pn_code,
po_info => pv_info);
if pn_code0 then
P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => Pn_Day, pv_info => '任务前置检查没通过'||pv_info);
return;
end if;
--任务统计开始，写初始进度日志
insert into t_job_log
(seqno, jobno, statdate, status, ctime, uptime)
values
(seq_job_log.nextval, pn_jobno, Pn_Day, 1, sysdate, sysdate)
returning rowid into ln_rowid;
commit;
insert into rpt_invest_data_day
(str_day,
day_type,
channel_id,
channel_name,
city,
county,
platform,
agent_id,
invest_users_all,
invest_amount_all,
invest_count,
invest_users_new,
invest_amount_new)
select p_strday str_day, p_type,
a.channel_id, a.channel_name, a.city, a.county, a.platform, c.agent_id,
count(distinct a.invest_id) invest_users_all,
sum(a.order_amount)/100 invest_amount_all,
count(order_id) invest_count,
count(distinct case when a.reg_day = v_start then a.invest_id else null end) invest_users_new,
sum(case when a.reg_day = v_start then a.order_amount else 0 end)/100 invest_amount_new
from agent.base_data_invest_info a
left join (SELECT b.user_name,a.agent_id FROM act.tb_user_agent_relat a,act.tb_user_info b WHERE a.user_id=b.user_id) c on a.user_name=c.user_name
where a.str_day = v_start
group by a.channel_id, a.channel_name, a.city, a.county, a.platform, c.agent_id;
ln_cnt := sql%rowcount;
MERGE INTO RPT_INVEST_DATA_DAY M
USING (
SELECT BONUS_DAY,CHANNEL_ID,CHANNEL_NAME,CITY,COUNTY,PLATFORM,AGENT_ID,SML_USERS_ALL,SML_AMOUNT_ALL,BIG_USERS_ALL,BIG_AMOUNT_ALL FROM (
SELECT K.BONUS_DAY,
K.CHANNEL_ID,
K.CHANNEL_NAME,
K.CITY,
K.COUNTY,
K.PLATFORM,
D.AGENT_ID,
COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 0 THEN K.INVEST_ID ELSE NULL END) SML_USERS_ALL,
SUM(CASE WHEN K.BIG_FLAG = 0 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 SML_AMOUNT_ALL,
COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 1 THEN K.INVEST_ID ELSE NULL END) BIG_USERS_ALL,
SUM(CASE WHEN K.BIG_FLAG = 1 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 BIG_AMOUNT_ALL
FROM AGENT.BASE_DATA_INVEST_INFO K
LEFT JOIN (SELECT B.USER_NAME,A.AGENT_ID
FROM ACT.TB_USER_AGENT_RELAT A, ACT.TB_USER_INFO B
WHERE A.USER_ID = B.USER_ID) D
ON K.USER_NAME=D.USER_NAME
WHERE BONUS_DAY = V_START
GROUP BY K.CHANNEL_ID,
K.CHANNEL_NAME,
K.CITY,
K.COUNTY,
K.PLATFORM,
D.AGENT_ID,
K.BONUS_DAY) )C
ON (M.CHANNEL_ID = C.CHANNEL_ID AND M.CHANNEL_NAME = C.CHANNEL_NAME AND M.CITY = C.CITY AND M.PLATFORM = C.PLATFORM AND M.COUNTY = C.COUNTY AND M.AGENT_ID = C.AGENT_ID AND M.STR_DAY = C.BONUS_DAY)
WHEN MATCHED THEN
UPDATE
SET M.SML_USERS_ALL= C.SML_USERS_ALL,
M.SML_AMOUNT_ALL = C.SML_AMOUNT_ALL,
M.BIG_USERS_ALL= C.BIG_USERS_ALL,
M.BIG_AMOUNT_ALL = C.BIG_AMOUNT_ALL
WHEN NOT MATCHED THEN
INSERT
(STR_DAY,CHANNEL_ID,CHANNEL_NAME,CITY,COUNTY,PLATFORM,AGENT_ID,SML_USERS_ALL,SML_AMOUNT_ALL,BIG_USERS_ALL,BIG_AMOUNT_ALL)
VALUES
(C.BONUS_DAY,C.CHANNEL_ID,C.CHANNEL_NAME,C.CITY,C.COUNTY,C.PLATFORM,C.AGENT_ID,C.SML_USERS_ALL,C.SML_AMOUNT_ALL,C.BIG_USERS_ALL,C.BIG_AMOUNT_ALL);
update rpt_invest_data_day a
set a.arpu = trunc(a.invest_amount_all/a.invest_users_all, 2)
where a.str_day = p_strday and a.invest_users_all > 0;
End_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => End_time, pv_info => '完成 add_invest_data_day ,插入了: '|| ln_cnt ||'行');
commit;
--更新进度状态表 为已完成
update t_job_log set status = 2, uptime = sysdate where rowid = ln_rowid;
commit;
exception
when others then
--写错误日志
err_info := substr(sqlerrm, 0, 200);
End_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
P_CHANNEL_JOB_ERROR_INFO(pv_procname => lv_procname, pn_stattime => End_time, pv_err => err_info);
raise;
end;
复制代码出错的就是标红的地方。首先AGENT.BASE_DATA_INVEST_INFO 是有user_name列的
SQL> desc agent.BASE_DATA_INVEST_INFO
名称
是否为空? 类型
----------------------------------------- -------- ----------------------------
PLATFORM
VARCHAR2(32)
INVEST_ID
VARCHAR2(32)
PHONE
VARCHAR2(32)
USER_NAME
VARCHAR2(32)
REAL_NAME
VARCHAR2(32)
AGENT_ID
VARCHAR2(32)
CHANNEL_ID
VARCHAR2(32)
CHANNEL_NAME
VARCHAR2(64)
ORDER_ID
VARCHAR2(64)
LOTTERY_ID
VARCHAR2(32)
ISSUE
VARCHAR2(32)
ORDER_AMOUNT
NUMBER
BONUS_AMOUNT
NUMBER
BIG_FLAG
NUMBER
REG_DAY
VARCHAR2(8)
STR_DAY
VARCHAR2(8)
BONUS_DAY
VARCHAR2(8)
CITY
VARCHAR2(32)
COUNTY
VARCHAR2(32)
其次，这个过程没有改动过，之前都没有报错
我尝试单独把sql拿出来跑
DROP TABLE RPT_INVEST_DATA_DAY_2
CREATE TABLE RPT_INVEST_DATA_DAY_2 AS SELECT * FROM RPT_INVEST_DATA_DAY
执行merge
MERGE INTO channel.RPT_INVEST_DATA_DAY_2 M
USING (
SELECT K.BONUS_DAY,
K.CHANNEL_ID,
K.CHANNEL_NAME,
K.CITY,
K.COUNTY,
K.PLATFORM,
D.AGENT_ID,
COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 0 THEN K.INVEST_ID ELSE NULL END) SML_USERS_ALL,
SUM(CASE WHEN K.BIG_FLAG = 0 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 SML_AMOUNT_ALL,
COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 1 THEN K.INVEST_ID ELSE NULL END) BIG_USERS_ALL,
SUM(CASE WHEN K.BIG_FLAG = 1 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 BIG_AMOUNT_ALL
FROM AGENT.BASE_DATA_INVEST_INFO K LEFTJOIN
(SELECT B.USER_NAME,A.AGENT_ID
FROM ACT.TB_USER_AGENT_RELAT A, ACT.TB_USER_INFO B
WHERE A.USER_ID = B.USER_ID) D
on K.USER_NAME=D.USER_NAME
where BONUS_DAY = '20160527'
GROUP BY K.CHANNEL_ID,
K.CHANNEL_NAME,
K.CITY,
K.COUNTY,
K.PLATFORM,
D.AGENT_ID,
K.BONUS_DAY ORDER BY K.BONUS_DAY DESC)/*)*/ C
ON (M.CHANNEL_ID = C.CHANNEL_ID AND M.CHANNEL_NAME = C.CHANNEL_NAME AND M.CITY = C.CITY AND M.PLATFORM = C.PLATFORM AND M.COUNTY = C.COUNTY AND M.AGENT_ID = C.AGENT_ID AND M.STR_DAY = C.BONUS_DAY)
WHEN MATCHED THEN
UPDATE
SET M.SML_USERS_ALL= C.SML_USERS_ALL,
M.SML_AMOUNT_ALL = C.SML_AMOUNT_ALL,
M.BIG_USERS_ALL= C.BIG_USERS_ALL,
M.BIG_AMOUNT_ALL = C.BIG_AMOUNT_ALL
WHEN NOT MATCHED THEN
INSERT
(STR_DAY,CHANNEL_ID,CHANNEL_NAME,CITY,COUNTY,PLATFORM,AGENT_ID,SML_USERS_ALL,SML_AMOUNT_ALL,BIG_USERS_ALL,BIG_AMOUNT_ALL)
VALUES
(C.BONUS_DAY,C.CHANNEL_ID,C.CHANNEL_NAME,C.CITY,C.COUNTY,C.PLATFORM,C.AGENT_ID,C.SML_USERS_ALL,C.SML_AMOUNT_ALL,C.BIG_USERS_ALL,C.BIG_AMOUNT_ALL);复制代码可以执行成功
但是RPT_INVEST_DATA_DAY实际上是一个分区表，于是我又创建了一个结构一样的分区表
create table RPT_INVEST_DATA_DAY_1
(
str_day VARCHAR2(8),
day_type
VARCHAR2(8),
channel_idVARCHAR2(50),
channel_nameVARCHAR2(200),
city
VARCHAR2(32),
county
VARCHAR2(32),
platform
VARCHAR2(32),
agent_id
VARCHAR2(32),
invest_users_allNUMBER,
invest_amount_all NUMBER,
invest_countNUMBER,
invest_users_newNUMBER,
invest_amount_new NUMBER,
sml_users_all NUMBER,
sml_amount_allNUMBER,
big_users_all NUMBER,
big_amount_allNUMBER,
arpu
NUMBER,
back_amount_all NUMBER
)
PARTITION BY RANGE(str_day)
(
partition p201603 values less than ('20160401'),
partition p201604 values less than ('20160501'),
partition p201605 values less than ('20160601'),
partition p201606 values less than ('20160701'),
partition p201607 values less than ('20160801'),
partition p201608 values less than ('20160901'),
partition p201609 values less than ('20161001'),
partition p201610 values less than ('20161101'),
partition p201611 values less than ('20161201'),
partition p201612 values less than ('20170101'),
partition p201701 values less than ('20170201'),
partition p201702 values less than ('20170301'),
partition p201703 values less than ('20170401'),
partition p201704 values less than ('20170501'),
partition p201705 values less than ('20170601'),
partition p201706 values less than ('20170701'),
partition p201707 values less than ('20170801'),
partition p201708 values less than ('20170901'),
partition p201709 values less than ('20171001'),
partition p201710 values less than ('20171101'),
partition p201711 values less than ('20171201'),
partition p201712 values less than ('20180101'),
partition p201801 values less than ('20180201'),
partition p201802 values less than ('20180301'),
partition p201803 values less than ('20180401'),
partition p201804 values less than ('20180501'),
partition p201805 values less than ('20180601'),
partition p201806 values less than ('20180701'),
partition p201807 values less than ('20180801'),
partition p201808 values less than ('20180901'),
partition p201809 values less than ('20181001'),
partition p201810 values less than ('20181101'),
partition p201811 values less than ('20181201'),
partition p201812 values less than (maxvalue)
)
INSERT INTO RPT_INVEST_DATA_DAY_1 SELECT * FROM RPT_INVEST_DATA_DAY复制代码再次执行merge
MERGE INTO channel.RPT_INVEST_DATA_DAY_1 M
USING (
SELECT K.BONUS_DAY,
K.CHANNEL_ID,
K.CHANNEL_NAME,
K.CITY,
K.COUNTY,
K.PLATFORM,
D.AGENT_ID,
COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 0 THEN K.INVEST_ID ELSE NULL END) SML_USERS_ALL,
SUM(CASE WHEN K.BIG_FLAG = 0 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 SML_AMOUNT_ALL,
COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 1 THEN K.INVEST_ID ELSE NULL END) BIG_USERS_ALL,
SUM(CASE WHEN K.BIG_FLAG = 1 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 BIG_AMOUNT_ALL
FROM AGENT.BASE_DATA_INVEST_INFO K LEFT JOIN
(SELECT B.USER_NAME,A.AGENT_ID
FROM ACT.TB_USER_AGENT_RELAT A, ACT.TB_USER_INFO B
WHERE A.USER_ID = B.USER_ID) D
on K.USER_NAME=D.USER_NAME
where BONUS_DAY = '20160527'
GROUP BY K.CHANNEL_ID,
K.CHANNEL_NAME,
K.CITY,
K.COUNTY,
K.PLATFORM,
D.AGENT_ID,
K.BONUS_DAY ORDER BY K.BONUS_DAY DESC) /*)*/ C
ON (M.CHANNEL_ID = C.CHANNEL_ID AND M.CHANNEL_NAME = C.CHANNEL_NAME AND M.CITY = C.CITY AND M.PLATFORM = C.PLATFORM AND M.COUNTY = C.COUNTY AND M.AGENT_ID = C.AGENT_ID AND M.STR_DAY = C.BONUS_DAY)
WHEN MATCHED THEN
UPDATE
SET M.SML_USERS_ALL = C.SML_USERS_ALL,
M.SML_AMOUNT_ALL = C.SML_AMOUNT_ALL,
M.BIG_USERS_ALL = C.BIG_USERS_ALL,
M.BIG_AMOUNT_ALL = C.BIG_AMOUNT_ALL
WHEN NOT MATCHED THEN
INSERT
(STR_DAY,CHANNEL_ID,CHANNEL_NAME,CITY,COUNTY,PLATFORM,AGENT_ID,SML_USERS_ALL,SML_AMOUNT_ALL,BIG_USERS_ALL,BIG_AMOUNT_ALL)
VALUES
(C.BONUS_DAY,C.CHANNEL_ID,C.CHANNEL_NAME,C.CITY,C.COUNTY,C.PLATFORM,C.AGENT_ID,C.SML_USERS_ALL,C.SML_AMOUNT_ALL,C.BIG_USERS_ALL,C.BIG_AMOUNT_ALL);复制代码第 27 行出现错误:
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-00904: "K"."USER_NAME": 标识符无效
报错K.USERNAME 无效的标识符ORA-00904。为什么分区表就报错非分区表就不报错呢？感到很奇怪
继续试验
创建临时表
create global temporary table tmp_invest_data_day(BONUS_DAY varchar2(8),CHANNEL_ID varchar2(32),CHANNEL_NAME varchar2(64),CITY varchar2(32),COUNTY varchar2(32),PLATFORM varchar2(32),AGENT_ID varchar2(32),SML_USERS_ALL number,SML_AMOUNT_ALL number,BIG_USERS_ALL number,BIG_AMOUNT_ALL number) on commit delete rows;
create or replace procedure add_invest_data_day(p_strday varchar2 default to_char(sysdate-1,'yyyymmdd'),
p_type varchar2,
pn_jobno number,
Pn_Day Number default to_number(to_char(sysdate,'yyyymmdd'))) Is
Start_time varchar2(30);
End_time varchar2(30);
lv_procname varchar2(32) := lower('add_invest_data_day');
ln_rowid rowid;
v_start varchar2(8);
ln_cnt number := 0;
err_info varchar2(200);
pn_code number :=0;
pv_info varchar2(200);
begin
if (p_type = '1') then
v_start := to_char(to_date(p_strday,'yyyymmdd') - 6,'yyyymmdd');
elsif (p_type = '2') then
v_start := to_char(to_date(p_strday,'yyyymmdd') - 30,'yyyymmdd');
end if;
Start_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => Start_time, pv_info => '开始执行 add_invest_data_day ');
p_ymstat_run_check(pn_job => pn_jobno,
pn_statedate => Pn_Day,
po_code => pn_code,
po_info => pv_info);
if pn_code0 then
P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => Pn_Day, pv_info => '任务前置检查没通过'||pv_info);
return;
end if;
--任务统计开始，写初始进度日志
insert into t_job_log
(seqno, jobno, statdate, status, ctime, uptime)
values
(seq_job_log.nextval, pn_jobno, Pn_Day, 1, sysdate, sysdate)
returning rowid into ln_rowid;
commit;
insert into rpt_invest_data_day
(str_day,
day_type,
channel_id,
channel_name,
city,
county,
platform,
agent_id,
invest_users_all,
invest_amount_all,
invest_count,
invest_users_new,
invest_amount_new)
select p_strday str_day, p_type,
a.channel_id, a.channel_name, a.city, a.county, a.platform, c.agent_id,
count(distinct a.invest_id) invest_users_all,
sum(a.order_amount)/100 invest_amount_all,
count(order_id) invest_count,
count(distinct case when a.reg_day = v_start then a.invest_id else null end) invest_users_new,
sum(case when a.reg_day = v_start then a.order_amount else 0 end)/100 invest_amount_new
from agent.base_data_invest_info a
left join (SELECT b.user_name,a.agent_id FROM act.tb_user_agent_relat a,act.tb_user_info b WHERE a.user_id=b.user_id) c on a.user_name=c.user_name
where a.str_day = v_start
group by a.channel_id, a.channel_name, a.city, a.county, a.platform, c.agent_id;
ln_cnt := sql%rowcount;
insert into tmp_invest_data_day as SELECT K.BONUS_DAY,
K.CHANNEL_ID,
K.CHANNEL_NAME,
K.CITY,
K.COUNTY,
K.PLATFORM,
D.AGENT_ID,
COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 0 THEN K.INVEST_ID ELSE NULL END) SML_USERS_ALL,
SUM(CASE WHEN K.BIG_FLAG = 0 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 SML_AMOUNT_ALL,
COUNT(DISTINCT CASE WHEN K.BIG_FLAG = 1 THEN K.INVEST_ID ELSE NULL END) BIG_USERS_ALL,
SUM(CASE WHEN K.BIG_FLAG = 1 THEN K.BONUS_AMOUNT ELSE 0 END) / 100 BIG_AMOUNT_ALL
FROM AGENT.BASE_DATA_INVEST_INFO K
,(SELECT B.USER_NAME,A.AGENT_ID
FROM ACT.TB_USER_AGENT_RELAT A, ACT.TB_USER_INFO B
WHERE A.USER_ID = B.USER_ID) D
WHERE K.USER_NAME=D.USER_NAME(+)
AND BONUS_DAY = V_START
GROUP BY K.CHANNEL_ID,
K.CHANNEL_NAME,
K.CITY,
K.COUNTY,
K.PLATFORM,
D.AGENT_ID,
K.BONUS_DAY;
MERGE INTO RPT_INVEST_DATA_DAY M
USING tmp_invest_data_day C
ON (M.CHANNEL_ID = C.CHANNEL_ID AND M.CHANNEL_NAME = C.CHANNEL_NAME AND M.CITY = C.CITY AND M.PLATFORM = C.PLATFORM AND M.COUNTY = C.COUNTY AND M.AGENT_ID = C.AGENT_ID AND M.STR_DAY = C.BONUS_DAY)
WHEN MATCHED THEN
UPDATE
SET M.SML_USERS_ALL= C.SML_USERS_ALL,
M.SML_AMOUNT_ALL = C.SML_AMOUNT_ALL,
M.BIG_USERS_ALL= C.BIG_USERS_ALL,
M.BIG_AMOUNT_ALL = C.BIG_AMOUNT_ALL
WHEN NOT MATCHED THEN
INSERT
(STR_DAY,CHANNEL_ID,CHANNEL_NAME,CITY,COUNTY,PLATFORM,AGENT_ID,SML_USERS_ALL,SML_AMOUNT_ALL,BIG_USERS_ALL,BIG_AMOUNT_ALL)
VALUES
(C.BONUS_DAY,C.CHANNEL_ID,C.CHANNEL_NAME,C.CITY,C.COUNTY,C.PLATFORM,C.AGENT_ID,C.SML_USERS_ALL,C.SML_AMOUNT_ALL,C.BIG_USERS_ALL,C.BIG_AMOUNT_ALL);
update rpt_invest_data_day a
set a.arpu = trunc(a.invest_amount_all/a.invest_users_all, 2)
where a.str_day = p_strday and a.invest_users_all > 0;
End_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
P_CHANNEL_JOB_LOG_INFO(pv_procname => lv_procname, pn_stattime => End_time, pv_info => '完成 add_invest_data_day ,插入了: '|| ln_cnt ||'行');
commit;
--更新进度状态表 为已完成
update t_job_log set status = 2, uptime = sysdate where rowid = ln_rowid;
commit;
exception
when others then
--写错误日志
err_info := substr(sqlerrm, 0, 200);
End_time := to_char(sysdate,'yyyy-mm-dd hh24:mi:ss');
P_CHANNEL_JOB_ERROR_INFO(pv_procname => lv_procname, pn_stattime => End_time, pv_err => err_info);
raise;
end;复制代码再次执行过程，可以成功不报错
在实验中还发现，时间条件改为 >='20160626'
sdypt3_ora_5518_10046.txt(28.54 KB, 下载次数: 9)2016-6-3 17:47 上传点击文件名下载附件
|
