在使用nohup进行循环插入的过程中，如果发现在即插入的数据不对想要重新执行的时候，仅仅kill掉nohup进程是不行的，还需要通过
v$session,v$process,v$sql查询到其发出的SQL命令，查出spid，并kill掉这个进程才可以，否则这个sqlplus的循环会一直跑下去，如果这时再起一个nohup的循环插入
进程脚本的话，就会出现重复数据，因为两个插入进程都在同时对数据库的表进行操作，这样也容易造成锁堵塞的情况。

类似nohup执行脚本：

export ORACLE_SID=DWTH
sqlplus / as sysdba <<EOF
set feedback off
set serveroutput on
declare
v_sql varchar2(4000);
v_sqlerrm varchar2(4000);
cursor cur_tab is
select dt.owner,dt.table_name from dba_tables dt
where dt.owner in ('ODS_BUFF_KKX','SGDM','ZS_OUTPUT','NOVELL_IAS','ODS_BUFF_GJHZ','EIC','ODS_UNV','SGDW','ERPPSUSER','ODS_BUFF_JJFL','ODS_ZCQSM','EPBI_SG','ODS_BUFF_JRGK','ODS_BUFF_ZDC','ODS_BUFF_TGYERP','ODS_ZHTJ_VIEW','SGODSESA','EIC_SJZX','ODS_BUFF_CWGK','ODS_BUFF','SGDC','OWB_OWNER','SGODS_JY','SGDC_DXP','NOVELL','PSDSS_DCHK','PSDSS_YXYWGKPT','PSDSS_JLSCDDPT','DATAVIEWER','YXYQ','PSDSS_MID','OMAC','EPBI','EPBI_DXP','PSDSS_SGDC','PSDSS_BUFF_SG','PSDSS_BUFF','PSDSS_SJTS','PSDSS_BUSI','SG_EESMP')
minus
(
select owner,table_name from crpt_check.crpt_check_err_log
union all
select owner,table_name from crpt_check.crpt_check_log
);
begin
dbms_output.enable(buffer_size=>null);
for cur_1 in cur_tab loop
begin
dbms_output.put_line(cur_1.owner||'.'||cur_1.table_name);
v_sql:='analyze table '||cur_1.owner||'.'||cur_1.table_name||' validate structure cascade';
dbms_output.put_line(v_sql);
execute immediate v_sql;
dbms_output.put_line(cur_1.owner||'.'||cur_1.table_name||' analyzed');
insert into crpt_check.crpt_check_log(correct_log,exec_date,owner,table_name) values (cur_1.owner||'.'||cur_1.table_name||' analyzed',sysdate,cur_1.owner,cur_1.table_name);
commit;
exception
when others then
v_sqlerrm:=sqlerrm;
dbms_output.put_line('error:'||cur_1.owner||'.'||cur_1.table_name||' not analyzed,'||sqlerrm);
insert into crpt_check.crpt_check_err_log(error_log,exec_date,owner,table_name) values ('error:'||cur_1.owner||'.'||cur_1.table_name||' not analyzed,'||v_sqlerrm,sysdate,cur_1.owner,cur_1.table_name);
commit;
end;
end loop;
end;
/
set feedback on
set serveroutput off
quit;
EOF
echo "over"

ITPub博客

使用nohup运行循环脚本插入发现数据重复的问题

最新文章