数据稽核问题处理脚本汇总

智能数据总量：根据智能接口规范，统计所有属性的量。例如100个机智能缺失量：就是智能需求的属性缺失的量，以100个机房，10个属性智能数据总量变化量：跟前一天比较省份前置库或统一库数据量的差值数据核查select*fromcheck_tableawherea.缺失数据量<>0;--select*fromzn_check_tableawherea.缺失值<>0anda.是否必填!='O';--智能selectsum(a.数据总量)数据总量总和,sum(a.缺失数据量)和,to_char((sum(a.缺失数据量sum(a.数据总量))*100,'90.99')||'%'fromcheck_tableselectsum(a.数据总量)数据总量总和,sum(a.缺失值)缺失值总和,to_char((sum(a.缺失值)/sum(a.数据总量))*100,'90.99')||'%'缺失比例fromzn_check_tableawherea.是否必填!='O'现状：目前只有在同步人员和部计划后续会先把数据同步到基准库然后往各个省数据库同（新增人员，目前的解决方案：手工同步（全量createtabletemp_staffasselect*fromcreatetabletemp_departmentasselect*fromtruncatetabledepartment;truncatetablestaff;altertableSTAFFmodifyNAMEaltertableSTAFFmodifyDEPARTMENTNUMBER(9);altertableSTAFFmodifyWORKGROUP_IDNUMBER(8);altertableSTAFFmodifyLOGIN_NAMEVARCHAR2(40);insertintodepartmentaselect*fromresltjt_t.department@db33where 895';insertintodepartmentaselect*fromresltjt_t.department@db33wheredept_idininsertintostaff(staff_id,name,title,station,password,department,workgroup_id,site_id,dept_level_id,eff_date,exp_date,state,state_date,ip_limit,ip_subnet,ip_netmask,ip_hostname,pc_limit,pc_id,_serial_nbr,last_date,,phone_no,delete_state,delete_time,remark,staff_alias,login_name,pwd_mod_date,lan_id,loginuid,staff_code,staff_state,last_login_date,erro_times,captcha,validate_time,his_pwd,safe_strategy,identity_id,hr_code)selectstaff_id,name,title,station,password,department,workgroup_id,site_id,dept_level_id,eff_date,exp_date,state,state_date,ip_limit,ip_subnet,ip_netmask,ip_hostname,pc_limit,pc_id,_serial_nbr,last_date,,phone_no,delete_state,delete_time,remark,staff_alias,login_name,pwd_mod_date,lan_id,loginuid,staff_code,staff_state,last_login_date,erro_times,captcha,validate_time,his_pwd,safe_strategy,identity_id,hr_codefromresltjt_t.staff@db33aexists(select1fromresltjt_t.department@db33bwherea.site_id=b.dept_idand 895');insertintostaff(staff_id,name,title,station,password,department,workgroup_id,site_id,dept_level_id,eff_date,exp_date,state,state_date,ip_limit,ip_subnet,ip_netmask,ip_hostname,pc_limit,pc_id,_serial_nbr,last_date,,phone_no,delete_state,delete_time,remark,staff_alias,login_name,pwd_mod_date,lan_id,loginuid,staff_code,staff_state,last_login_date,erro_times,captcha,validate_time,his_pwd,safe_strategy,identity_id,hr_code)selectstaff_id,name,title,station,password,department,workgroup_id,site_id,dept_level_id,eff_date,exp_date,state,state_date,ip_limit,ip_subnet,ip_netmask,ip_hostname,pc_limit,pc_id,_serial_nbr,last_date,,phone_no,delete_state,delete_time,remark,staff_alias,login_name,pwd_mod_date,lan_id,loginuid,staff_code,staff_state,last_login_date,erro_times,captcha,validate_time,his_pwd,safe_strategy,identity_id,hr_codefromresltjt_t.staff@db33awhere staff_idin(11,12,2302);droptablecreatetabletemp_d_rme_portselectmax(port_id)port_id,port_namefromrme_port groupbyport_name,super_res_idhavingcount(*)>1;createindextemp_d_rme_portontemp_d_rme_portdeletefromrme_portawherenotexists(select1fromtemp_d_rme_portbwhere解决方法：根据智能找出问题数据将sysid修改SELECTa.sys_id,a.*FROMtrs_segaWHERE(notexists(SELECT1 trs_sysb WHEREnvl(b.delete_state,0)='0' anda.sys_id=b.sys_id)ora.sys_idisnull)andnvl(a.delete_state,0)='0'移动专业机房类型和id不一解决方法：2080001是机房2080004是放置点根据id中的第五位到第八位确定是机房还是设备放置点来进行更新机房是0205放置点是0208SELECTa.posit_type_id,a.posit_id,a.res_type_id/* intov_qsum16*/FROMrme_eqpaWHEREa.posit_type_idnotin(' ',' ')andselect*frompub_res_typetwhereupdaterme_eqpaseta.posit_type_id=' 'wherea.posit_type_idnotin ',' 控制器与关系缺失于控制器关联的多选关系表insertintoselectfn_sub_key(''),51832prop_spec_id,from(select*fromrme_eqprewherere.res_type_id='6755')re1,sz_check_hubei.chk_rme_eqp_btscreb,(select*fromrme_eqprewherere.res_type_id='2513')re2wherere1.eqp_name=creb.eqp_nameandcreb.bsc_eqp=select*fromRME_EQP_PROPERTY_MULTI_VALUEtwhere ,a1.groupname,a1.role_group_idfromgroup_rolea3,rolegrpa1,rolesa2wherea3.group_id=a1.role_group_idanda3.role_id=a2.role_idanda1.delete_state='0'anda2.delete_state='0'orderbya1.role_group_idselecta4.speciality_id,a4.speciality,a2.role_id,fromrolesa2,pub_specialitya4wherea2.speciality_id=a4.speciality_idanda2.delete_state='0'anda4.delete_state='0'orderbya4.speciality_idselecta4.speciality_id,a4.speciality,a2.role_id,fromrolesa2,pub_specialitya4wherea2.speciality_id=a4.speciality_idanda2.delete_state='0'anda4.delete_state='0'orderbya4.speciality_idupdatetrs_segasetseg_name=(SELECTne_nameFROMtrs_trs_neWHEREtrs_ne_id=a.a_trs_ne_id)'-'(SELECTne_nameFROMtrs_trs_neWHEREtrs_ne_id=创建传输设备和传输关联关--droptablecreatetabletrans_eqp_nm_relaselecttn.nm_id,ttn.trs_ne_id,0fromsz_check_hubei.chk_trans_equip_infoctei,trs_trs_nettn,trs_nmtnwherectei.equip_name=ttn.ne_nameandselectcount(1)fromselectcount(1)frominsertintotrs_nmne(nm_id,trs_ne_id,delete_state)select*fromtrans_eqp_nm_rela;insertintoselectsubstr(tn.nm_id,1,8)||9999999999999999NM_ID, 'NM_NO, ' 'ALIAS,substr(tn.nm_id,1,8)||9999999999999999NMS_ORIG_RES_ID,fromtrs_nmtnwhererownum=1;insertintoselect(selectsubstr(t.nm_id,1,8)||9999999999999999fromtrs_nmtwhere=1),ttn.trs_ne_id,0fromtrs_trs_newherenotexists(selectfromtrans_eqp_nm_relawheretenr.trs_ne_id=创建设备(RME_EQP)和关联关createtableeqp_nm_relationselectpln.nm_id,re.eqp_name,re.res_type_id,pln.nm_name,re.eqp_idfromrme_eqpre,pub_log_nmplnwhereinsertintopub_log_nm_union(nm_id,res_type,res_id,nm_eqp,delete_state)selectenr.nm_id,enr.res_type_id,enr.eqp_id,1,0fromeqp_nm_relationenr;----建立虚拟，并且插入到--建立交换网虚拟insertintoselectsubstr(tn.nm_id,1,8)||9999999999999999

'交换网设备临时统一','交换网设备临时统一','交换网设备临时统一',substr(tn.nm_id,1,8)||9999999999999999frompub_log_nmplnwhere--建立接入网虚拟insertintoselectsubstr(tn.nm_id,18||9999999999999998NM_ID,'接入网设备临时统一','接入网设备临时统一'接入网设备临时统一',substr(tn.nm_id,1,8)||9999999999999998frompub_log_nmplnwhere--建立数据网虚拟insertintoselectsubstr(tn.nm_id,18||9999999999999997NM_ID,'数据网设备临时统一','数据网设备临时统一'数据网设备临时统一',substr(tn.nm_id,1,8)||9999999999999997frompub_log_nmplnwhereinsertintoselectsubstr(tn.nm_id,1,8)||9999999999999996NM_ID, 网设备临时统 '移动网设备临时统一',substr(tn.nm_id,1,8)||9999999999999996frompub_log_nmplnwhere--建立移动无线网虚拟insertintoselectsubstr(tn.nm_id,18||9999999999999995NM_ID,'移动无线网设备临时统一','移动无线网设备临时统一'移动无线网设备临时统一',substr(tn.nm_id,1,8)||9999999999999995frompub_log_nmplnwhere----插入无关联的交换网数据进入设备与关联关系insertintopub_log_nm_union(nm_id,res_type,res_id,nm_eqp,delete_state)select(selectsubstr(tn.nm_id,1,8)||9999999999999999frompub_log_nm),0fromrme_eqpwherenotexists(select1fromeqp_nm_relationenrwhereenr.eqp_id=re.eqp_id)andre.res_type_idin('2800','7684','7690','7689');----插入无关联的接入网数据进入设备与关联关系insertintopub_log_nm_union(nm_id,res_type,res_id,nm_eqp,delete_state)select(selectsubstr(tn.nm_id,1,8)||9999999999999998frompub_log_nm),0fromrme_eqpwherenotexists(select1fromeqp_nm_relationenrwhereenr.eqp_id=re.eqp_id)andre.res_type_idin('2510','2511');----插入无关联的数据网数据进入设备与关联关系insertintopub_log_nm_union(nm_id,res_type,res_id,nm_eqp,delete_state)select(selectsubstr(tn.nm_id,1,8)||9999999999999997frompub_log_nm),0fromrme_eqpwherenotexists(select1fromeqp_nm_relationenrwhereenr.eqp_id=re.eqp_id)andre.res_type_idin('2400','2401','2406','2423','2402','2407');----插入无关联的移动网数据进入设备与关联关系表insertintopub_log_nm_union(nm_id,res_type,res_id,nm_eqp,delete_state)selectselectsubstr(tn.nm_id,1,8)||9999999999999996frompub_log_nm),0fromrme_eqpwherenotexists(select1fromeqp_nm_relationenrwhereenr.eqp_id=re.eqp_id)andre.res_type_idin----插入无关联的移动无线网数据进入设备与关联关系insertintopub_log_nm_union(nm_id,res_type,res_id,nm_eqp,delete_state)select(selectsubstr(tn.nm_id,1,8)||9999999999999995frompub_log_nm),0fromrme_eqpwherenotexists(select1fromeqp_nm_relationenrwhereenr.eqp_id=re.eqp_id)andre.res_type_idin('6714','6729','6713','6755','2513');安置类型与安点不对应问updaterme_eqpaseta.posit_type_id=' 'wheresubstr(a.posit_id,5,4)='0205'anda.res_ty