數(shù)據(jù)稽核問題處理腳本匯總

智能數(shù)據(jù)總量：根據(jù)智能接口規(guī)范，統(tǒng)計所有屬性的量。例如100個機(jī)智能缺失量：就是智能需求的屬性缺失的量，以100個機(jī)房，10個屬性智能數(shù)據(jù)總量變化量：跟前一天比較省份前置庫或統(tǒng)一庫數(shù)據(jù)量的差值數(shù)據(jù)核查select*fromcheck_tableawherea.缺失數(shù)據(jù)量<>0;--select*fromzn_check_tableawherea.缺失值<>0anda.是否必填!='O';--智能selectsum(a.數(shù)據(jù)總量)數(shù)據(jù)總量總和,sum(a.缺失數(shù)據(jù)量)和,to_char((sum(a.缺失數(shù)據(jù)量sum(a.數(shù)據(jù)總量))*100,'90.99')||'%'fromcheck_tableselectsum(a.數(shù)據(jù)總量)數(shù)據(jù)總量總和,sum(a.缺失值)缺失值總和,to_char((sum(a.缺失值)/sum(a.數(shù)據(jù)總量))*100,'90.99')||'%'缺失比例fromzn_check_tableawherea.是否必填!='O'現(xiàn)狀：目前只有在同步人員和部計劃后續(xù)會先把數(shù)據(jù)同步到基準(zhǔn)庫然后往各個省數(shù)據(jù)庫同（新增人員，目前的解決方案：手工同步（全量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解決方法：根據(jù)智能找出問題數(shù)據(jù)將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'移動專業(yè)機(jī)房類型和id不一解決方法：2080001是機(jī)房2080004是放置點(diǎn)根據(jù)id中的第五位到第八位確定是機(jī)房還是設(shè)備放置點(diǎn)來進(jìn)行更新機(jī)房是0205放置點(diǎn)是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 ',' 控制器與關(guān)系缺失于控制器關(guān)聯(lián)的多選關(guān)系表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=創(chuàng)建傳輸設(shè)備和傳輸關(guān)聯(lián)關(guān)--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=創(chuàng)建設(shè)備(RME_EQP)和關(guān)聯(lián)關(guān)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;----建立虛擬，并且插入到--建立交換網(wǎng)虛擬insertintoselectsubstr(tn.nm_id,1,8)||9999999999999999

'交換網(wǎng)設(shè)備臨時統(tǒng)一','交換網(wǎng)設(shè)備臨時統(tǒng)一','交換網(wǎng)設(shè)備臨時統(tǒng)一',substr(tn.nm_id,1,8)||9999999999999999frompub_log_nmplnwhere--建立接入網(wǎng)虛擬insertintoselectsubstr(tn.nm_id,18||9999999999999998NM_ID,'接入網(wǎng)設(shè)備臨時統(tǒng)一','接入網(wǎng)設(shè)備臨時統(tǒng)一'接入網(wǎng)設(shè)備臨時統(tǒng)一',substr(tn.nm_id,1,8)||9999999999999998frompub_log_nmplnwhere--建立數(shù)據(jù)網(wǎng)虛擬insertintoselectsubstr(tn.nm_id,18||9999999999999997NM_ID,'數(shù)據(jù)網(wǎng)設(shè)備臨時統(tǒng)一','數(shù)據(jù)網(wǎng)設(shè)備臨時統(tǒng)一'數(shù)據(jù)網(wǎng)設(shè)備臨時統(tǒng)一',substr(tn.nm_id,1,8)||9999999999999997frompub_log_nmplnwhereinsertintoselectsubstr(tn.nm_id,1,8)||9999999999999996NM_ID, 網(wǎng)設(shè)備臨時統(tǒng) '移動網(wǎng)設(shè)備臨時統(tǒng)一',substr(tn.nm_id,1,8)||9999999999999996frompub_log_nmplnwhere--建立移動無線網(wǎng)虛擬insertintoselectsubstr(tn.nm_id,18||9999999999999995NM_ID,'移動無線網(wǎng)設(shè)備臨時統(tǒng)一','移動無線網(wǎng)設(shè)備臨時統(tǒng)一'移動無線網(wǎng)設(shè)備臨時統(tǒng)一',substr(tn.nm_id,1,8)||9999999999999995frompub_log_nmplnwhere----插入無關(guān)聯(lián)的交換網(wǎng)數(shù)據(jù)進(jìn)入設(shè)備與關(guān)聯(lián)關(guān)系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');----插入無關(guān)聯(lián)的接入網(wǎng)數(shù)據(jù)進(jìn)入設(shè)備與關(guān)聯(lián)關(guān)系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');----插入無關(guān)聯(lián)的數(shù)據(jù)網(wǎng)數(shù)據(jù)進(jìn)入設(shè)備與關(guān)聯(lián)關(guān)系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');----插入無關(guān)聯(lián)的移動網(wǎng)數(shù)據(jù)進(jìn)入設(shè)備與關(guān)聯(lián)關(guān)系表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----插入無關(guān)聯(lián)的移動無線網(wǎng)數(shù)據(jù)進(jìn)入設(shè)備與關(guān)聯(lián)關(guān)系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');安置類型與安點(diǎn)不對應(yīng)問updaterme_eqpaseta.posit_type_id=' 'wheresubstr(a.posit_id,5,4)='0205'anda.res_ty