daily check query (mysql & oracle)

•  mySQL

select str_to_date(A.start_time,'%H%i') utc, date_add(str_to_date(A.start_time,'%H%i'),INTERVAL 9 HOUR) kr, A.svc_act_flag, A.* from tb_ic_contents_set_job_gr A order by 5;
select serial_no, contents_set_id,job_type,gen_date,complt_date,schdr_prcs_stat flag,TIMEDIFF(COMPLT_DATE, GEN_DATE) time, 
	CONVERT_TZ(gen_date,'+00:00','+09:00') kr_start, CONVERT_TZ(complt_date,'+00:00','+09:00') kr_end from TB_IC_DMON_SCHD_HIST A 
	where contents_set_id like 'xumo%'
	ORDER BY 4 DESC;
select * from TB_IC_ERROR_HIST where error_code like 'DM%' order by error_occur_date desc;
select DATE(strt_time),IC_CONTENTS_SET_ID,COUNT(*) from tb_ic_n_svc_bas GROUP BY DATE(strt_time),IC_CONTENTS_SET_ID;
select strt_date,IC_CONTENTS_SET_ID,COUNT(*) from vi_ic_n_brdcst_schd GROUP BY strt_date,IC_CONTENTS_SET_ID;

select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no, use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
	from TB_IC_N_CRL_BRDCST_PGM_HIST ORDER BY 4 DESC;
select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no,use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
	from TB_IC_N_CRL_BRDCST_SCHD_HIST A ORDER BY 4 DESC;
select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no,use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
	from TB_IC_N_CRL_PERSON_DETAIL_HIST A ORDER BY 4 DESC;
select * from tb_ic_n_crl_brdcst_pgm where crawling_hist_map_seq='63731';
select * from tb_ic_n_crl_brdcst_schd where crawling_hist_map_seq='63731';
select * from tb_ic_n_crl_person_detail where crawling_hist_map_seq='63731';
select * from tb_ic_n_svc_bas where IC_CONTENTS_SET_ID='';
select * from tb_ic_n_brdcst_pgm_mir where IC_CONTENTS_SET_ID='';
select * from tb_ic_n_brdcst_schd_org where IC_CONTENTS_SET_ID='';
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_BRDCST_PGM group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_BRDCST_schd group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_PERSON_DETAIL group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;

-- ipchannels

select A.ip_url, A.* from tb_ic_mso_bas A where mso_code like '30%'; 
select mso_code,mso_prdt_itm_code,prdt_ver,chan_ver,call_ver,chan_logo_ver,use_flag,interact_url, A.* from tb_ic_mso_prdt_itm_bas A where dvc_src_idx='7'; 
select A.mso_prdt_itm_code,A.use_flag, count(*) from tb_ic_mso_chan_map A, tb_ic_chan_bas B where A.mso_prdt_itm_code like '300%' and A.mso_prdt_itm_code!='300'
	and A.ic_contents_set_id=B.ic_contents_set_id and A.chan_code=B.chan_code and B.use_flag='Y' group by A.mso_prdt_itm_code,A.use_flag;
select chan_code from tb_ic_mso_chan_map where mso_prdt_itm_code='3000' order by chan_code;
select conf_code, stat_code, count(*) from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.conf_no=B.conf_no group by conf_code, stat_code;
select conf_code, stat_code, chan_no, count(*) from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.conf_no=B.conf_no group by conf_code, stat_code, chan_no;
select chan_code from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.CONF_NO=B.conf_no and stat_code='IB172' and conf_code='3000' order by chan_code;
select cntry_code,svc_set_cntry_id,use_flag,count(*) from tb_ic_chan_svc_cntry group by cntry_code,svc_set_cntry_id,use_flag; 
-- select IC_CONTENTS_SET_ID,src_id,use_flag, count(*) from tb_ic_chan_bas group by IC_CONTENTS_SET_ID,src_id,use_flag;
-- select IC_CONTENTS_SET_ID,chan_gr_id,use_flag, count(*) from tb_ic_chan_bas group by IC_CONTENTS_SET_ID,chan_gr_id,use_flag;
-- select IC_CONTENTS_SET_ID,phys_brdcst_type,use_flag, count(*) from tb_ic_chan_bas where phys_brdcst_type='IP' group by IC_CONTENTS_SET_ID,phys_brdcst_type,use_flag;
select IC_CONTENTS_SET_ID,cp_id,use_flag, count(*) from tb_ic_dmost_chan_bas group by IC_CONTENTS_SET_ID,cp_id,use_flag;

select str_to_date(A.start_time,'%H%i') utc, date_add(str_to_date(A.start_time,'%H%i'),INTERVAL 9 HOUR) kr, A.svc_act_flag, A.* from tb_ic_contents_set_job_gr A order by 5;
select serial_no, contents_set_id,job_type,gen_date,complt_date,schdr_prcs_stat flag,TIMEDIFF(COMPLT_DATE, GEN_DATE) time, 
    CONVERT_TZ(gen_date,'+00:00','+09:00') kr_start, CONVERT_TZ(complt_date,'+00:00','+09:00') kr_end from TB_IC_DMON_SCHD_HIST A 
    where contents_set_id like 'xumo%'
    ORDER BY 4 DESC;
select * from TB_IC_ERROR_HIST where error_code like 'DM%' order by error_occur_date desc;
select DATE(strt_time),IC_CONTENTS_SET_ID,COUNT(*) from tb_ic_n_svc_bas GROUP BY DATE(strt_time),IC_CONTENTS_SET_ID;
select strt_date,IC_CONTENTS_SET_ID,COUNT(*) from vi_ic_n_brdcst_schd GROUP BY strt_date,IC_CONTENTS_SET_ID;

select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no, use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
    from TB_IC_N_CRL_BRDCST_PGM_HIST ORDER BY 4 DESC;
select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no,use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
    from TB_IC_N_CRL_BRDCST_SCHD_HIST A ORDER BY 4 DESC;
select IC_CONTENTS_SET_ID,CRAWLING_SEQ,DB_DAEMON_JOB_TYPE type,CRT_DATE, LAST_CHG_DATE,gen_usr_no,use_flag,CONVERT_TZ(CRT_DATE,'+00:00','+09:00') kr_crt, CONVERT_TZ(LAST_CHG_DATE,'+00:00','+09:00') kr_last
    from TB_IC_N_CRL_PERSON_DETAIL_HIST A ORDER BY 4 DESC;
select * from tb_ic_n_crl_brdcst_pgm where crawling_hist_map_seq='63731';
select * from tb_ic_n_crl_brdcst_schd where crawling_hist_map_seq='63731';
select * from tb_ic_n_crl_person_detail where crawling_hist_map_seq='63731';
select * from tb_ic_n_svc_bas where IC_CONTENTS_SET_ID='';
select * from tb_ic_n_brdcst_pgm_mir where IC_CONTENTS_SET_ID='';
select * from tb_ic_n_brdcst_schd_org where IC_CONTENTS_SET_ID='';
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_BRDCST_PGM group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_BRDCST_schd group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;
select ic_contents_set_id,crawling_hist_map_seq, count(*) from TB_IC_N_CRL_PERSON_DETAIL group by ic_contents_set_id, crawling_hist_map_seq order by 3 desc;

-- ipchannels
select A.ip_url, A.* from tb_ic_mso_bas A where mso_code like '30%'; 
select mso_code,mso_prdt_itm_code,prdt_ver,chan_ver,call_ver,chan_logo_ver,use_flag,interact_url, A.* from tb_ic_mso_prdt_itm_bas A where dvc_src_idx='7'; 
select A.mso_prdt_itm_code,A.use_flag, count(*) from tb_ic_mso_chan_map A, tb_ic_chan_bas B where A.mso_prdt_itm_code like '300%' and A.mso_prdt_itm_code!='300'
    and A.ic_contents_set_id=B.ic_contents_set_id and A.chan_code=B.chan_code and B.use_flag='Y' group by A.mso_prdt_itm_code,A.use_flag;
select * from tb_ic_mso_chan_map where mso_prdt_itm_code like '3000%';
select conf_code, stat_code, count(*) from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.conf_no=B.conf_no group by conf_code, stat_code;
select conf_code, stat_code, chan_no, count(*) from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.conf_no=B.conf_no group by conf_code, stat_code, chan_no;
select A.stat_code, B.conf_code, A.chan_no, A.chan_code, A.chan_name 
    from tb_ib_conf_file A, tb_ib_conf_mgmt B where A.CONF_NO=B.conf_no and stat_code='IB171' and A.use_flag='Y' and B.use_flag='Y' and conf_code like '3000%';
select cntry_code,svc_set_cntry_id,use_flag,count(*) from tb_ic_chan_svc_cntry group by cntry_code,svc_set_cntry_id,use_flag; 
-- select IC_CONTENTS_SET_ID,src_id,use_flag, count(*) from tb_ic_chan_bas group by IC_CONTENTS_SET_ID,src_id,use_flag;
-- select IC_CONTENTS_SET_ID,chan_gr_id,use_flag, count(*) from tb_ic_chan_bas group by IC_CONTENTS_SET_ID,chan_gr_id,use_flag;
-- select IC_CONTENTS_SET_ID,phys_brdcst_type,use_flag, count(*) from tb_ic_chan_bas where phys_brdcst_type='IP' group by IC_CONTENTS_SET_ID,phys_brdcst_type,use_flag;
select IC_CONTENTS_SET_ID,cp_id,use_flag, count(*) from tb_ic_dmost_chan_bas group by IC_CONTENTS_SET_ID,cp_id,use_flag;

• oracle

SELECT A.IC_CONTENTS_SET_ID,A.CRAWLING_SEQ seq,A.DB_DAEMON_JOB_TYPE type,gen_usr_no NO,TO_CHAR(CRT_DATE, 'YYYY/MM/DD hh24:mi') crt, TO_CHAR(LAST_CHG_DATE, 'YYYY/MM/DD hh24:mi') last_Chg, A.use_flag, 
TO_CHAR(CRT_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_crt, TO_CHAR(LAST_CHG_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_last
FROM hesdp_app.TB_IC_N_CRL_BRDCST_PGM_HIST A ORDER BY crt_date DESC;
SELECT A.IC_CONTENTS_SET_ID,A.CRAWLING_SEQ seq,A.DB_DAEMON_JOB_TYPE type,gen_usr_no NO,TO_CHAR(CRT_DATE, 'YYYY/MM/DD hh24:mi') crt, TO_CHAR(LAST_CHG_DATE, 'YYYY/MM/DD hh24:mi') last_Chg, A.use_flag, 
TO_CHAR(CRT_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_crt, TO_CHAR(LAST_CHG_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_last
FROM hesdp_app.TB_IC_N_CRL_BRDCST_SCHD_HIST A ORDER BY crt_date DESC;
SELECT A.IC_CONTENTS_SET_ID,A.CRAWLING_SEQ seq,A.DB_DAEMON_JOB_TYPE type,gen_usr_no NO,TO_CHAR(CRT_DATE, 'YYYY/MM/DD hh24:mi') crt, TO_CHAR(LAST_CHG_DATE, 'YYYY/MM/DD hh24:mi') last_Chg, A.use_flag, 
TO_CHAR(CRT_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_crt, TO_CHAR(LAST_CHG_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_last
FROM hesdp_app.TB_IC_N_CRL_PERSON_DETAIL_HIST A ORDER BY crt_date DESC; 

SELECT SERIAL_NO seq,CONTENTS_SET_ID,TO_CHAR(GEN_DATE, 'YYYY/MM/DD hh24:mi') GEN, TO_CHAR(COMPLT_DATE, 'YYYY/MM/DD hh24:mi') COMPLT, SCHDR_PRCS_STAT F,JOB_TYPE,
TO_CHAR(GEN_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_gen, TO_CHAR(COMPLT_DATE + 9/24, 'YYYY/MM/DD hh24:mi') kr_complt, ROUND((COMPLT_DATE - GEN_DATE) * 24 * 60, 0) time
FROM hesdp_app.TB_IC_DMON_SCHD_HIST A ORDER BY 1 DESC; 

SELECT TO_CHAR(TO_DATE(A.start_time,'hh24mi')+9/24,'hh24:mi') KST, 'UTC'||TO_CHAR(TO_DATE(A.start_time,'hh24mi'),'hh24:mi') GMT,A.INTVL_TIME intvl , A.SVC_ACT_FLAG F, A.* 
FROM hesdp_app.tb_ic_contents_set_job_gr A ORDER BY 6,1; 

SELECT IC_CONTENTS_SET_ID, TO_CHAR(STRT_TIME,'YYYY-MM-DD'), COUNT(*) FROM hesdp_app.TB_IC_N_SVC_BAS GROUP BY IC_CONTENTS_SET_ID, TO_CHAR(STRT_TIME,'YYYY-MM-DD') ORDER BY 1,2; 
SELECT IC_CONTENTS_SET_ID, TO_CHAR(STRT_DATE,'YYYY-MM-DD'), COUNT(*) FROM hesdp_app.VI_IC_N_BRDCST_SCHD WHERE use_flag='Y' GROUP BY IC_CONTENTS_SET_ID, TO_CHAR(STRT_DATE,'YYYY-MM-DD') ORDER BY 1,2; 
SELECT chan_code, COUNT(*) FROM TB_IC_N_SVC_BAS WHERE hesdp_app.chan_code LIKE '9999%' GROUP BY chan_code; 
SELECT chan_code, COUNT(*) FROM VI_IC_N_BRDCST_SCHD WHERE hesdp_app.chan_code LIKE '9999%' GROUP BY chan_code; 
SELECT TO_CHAR(STRT_TIME,'YYYY-MM-DD'), COUNT(*) FROM hesdp_app.TB_IC_N_SVC_BAS WHERE chan_code LIKE '9999%' GROUP BY TO_CHAR(STRT_TIME,'YYYY-MM-DD');
SELECT TO_CHAR(STRT_DATE,'YYYY-MM-DD'), COUNT(*) FROM hesdp_app.VI_IC_N_BRDCST_SCHD WHERE chan_code LIKE '9999%' GROUP BY TO_CHAR(STRT_DATE,'YYYY-MM-DD');

SELECT * FROM hesdp_app.tb_ic_error_hist WHERE ERROR_CODE LIKE 'DM%' ORDER BY error_occur_date DESC;
SELECT * FROM hesdp_app.tb_ic_last_upd_time_bas;