整个过程
create or replace procedure CHANGE_JGDM_PROC_PARA(tablename varchar2)
is
BEGIN
DBMS_OUTPUT.put_line('GO GO GO! ');
DBMS_OUTPUT.put_line('BEGIN GD_CKLDZB!');
LOOP
execute immediate'update gd_ckldzb set zzf=(select newjgdm from '|| tablename||' where oldjgdm=zzf)
where zzf IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
LOOP
execute immediate'update gd_ckldzb set jsf=(select newjgdm from '|| tablename ||' where oldjgdm=jsf)
where jsf IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP; DBMS_OUTPUT.put_line('BEGIN GD_CKLDMXB!');
LOOP
execute immediate'update gd_ckldmxb set xjz=(select newjgdm from '|| tablename ||' where oldjgdm=xjz)
WHERE xjz IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
DBMS_OUTPUT.put_line('BEGIN GD_JKLDZB');
LOOP
execute immediate'update gd_jkldzb set zzf=(select newjgdm from '|| tablename ||' where oldjgdm=zzf)
where zzf IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
LOOP
execute immediate'update gd_jkldzb set jsf=(select newjgdm from '|| tablename ||' where oldjgdm=jsf)
where jsf IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
DBMS_OUTPUT.put_line('BEGIN GD_JKLDMXB!');
LOOP
execute immediate'update gd_jkldmxb set fcz=(select newjgdm from '|| tablename ||' where oldjgdm=fcz)
WHERE fcz IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
DBMS_OUTPUT.put_line('BEGIN GD_YJXXB');/*
LOOP
update gd_yjxxb set yjtm=yjhm|| sjjdm ||substr(yjtm,13,1)
WHERE sjjdm IN (SELECT NEWJGDM FROM JGDM_TeMP)
and rownum<5000;
exit when sql%notfound;
commit work;
END LOOP;
*/
EXECUTE IMMEDIATE'TRUNCATE TABLE yjxxb_temp';
commit work;
EXECUTE IMMEDIATE'insert into yjxxb_temp(oldrowid,sjjdm,jdjdm)
select rowid as oldrowid, sjjdm as sjjdm, jdjdm as jdjdm
from gd_yjxxb where sjjdm in (select oldjgdm from '||tablename||')
or jdjdm in (select oldjgdm from '||tablename||')';
commit work; LOOP
update gd_yjxxb a set (sjjdm,jdjdm) = (select sjjdm,jdjdm from yjxxb_temp where oldrowid=a.rowid)
where a.rowid in (select oldrowid from yjxxb_temp
where bj<>'1'
and rownum<50000);
commit work; update yjxxb_temp set bj='1' where oldrowid in (select oldrowid from yjxxb_temp
where bj<>'1'
and rownum<50000);
exit when sql%notfound;
commit work;
END LOOP; DBMS_OUTPUT.put_line('BEGIN GD_ZBFJXX!');
LOOP
EXECUTE IMMEDIATE'update gd_zbfjxx set xjz=(select newjgdm from '||tablename||' where oldjgdm=xjz)
WHERE xjz IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
--......
DBMS_OUTPUT.put_line('BEGIN GD_ZBJBXX');
LOOP
EXECUTE IMMEDIATE'update gd_zbjbxx set yjjdm=(select newjgdm from '||tablename||' where oldjgdm=yjjdm)
WHERE yjjdm IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
LOOP
EXECUTE IMMEDIATE'update gd_zbjbxx set jdjdm=(select newjgdm from '||tablename||' where oldjgdm=jdjdm)
where jdjdm IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
EXECUTE IMMEDIATE'TRUNCATE TABLE zbjbxx_temp';
commit work;
EXECUTE IMMEDIATE'insert into zbjbxx_temp select rowid as oldrowid, zbtm as zbtm, yjjdm as yjjdm, jdjdm as jdjdm
from gd_zbjbxx where yjjdm IN (SELECT OLDJGDM FROM '||tablename||')
or jdjdm IN (SELECT OLDJGDM FROM '||tablename||')';
commit work; update zbjbxx_temp set zbtm= yjjdm || jdjdm||substr(zbtm,17,14);
commit work; update gd_zbjbxx a set a.zbtm = (select zbtm from zbjbxx_temp where OLDROWID=a.ROWID)
where a.rowid in (select oldrowid from zbjbxx_temp);
commit work;
DBMS_OUTPUT.put_line('BEGIN GD_FFQDZB!');
LOOP
EXECUTE IMMEDIATE'update gd_ffqdzb set ffjdm=(select newjgdm from '||tablename||' where oldjgdm=ffjdm)
WHERE ffjdm IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
LOOP
EXECUTE IMMEDIATE'update gd_ffqdzb set jdjdm=(select newjgdm from '||tablename||' where oldjgdm=jdjdm)
where jdjdm IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP; EXECUTE IMMEDIATE'TRUNCATE TABLE ffqdzb_temp';
commit work;
EXECUTE IMMEDIATE'insert into ffqdzb_temp select rowid as oldrowid, zbtm as zbtm, ffjdm as ffjdm, jdjdm as jdjdm
from gd_ffqdzb where ffjdm in (select oldjgdm from '||tablename||')
or jdjdm in (select oldjgdm from '||tablename||')';
commit work;
update ffqdzb_temp set zbtm= ffjdm || jdjdm||substr(zbtm,17,14);
commit work;
update gd_ffqdzb a set a.zbtm = (select zbtm from ffqdzb_temp where OLDROWID=a.ROWID)
where a.rowid in (select oldrowid from ffqdzb_temp);
commit work;
commit work;
DBMS_OUTPUT.put_line('FINISH!');
END change_jgdm_proc_para;
/
create or replace procedure CHANGE_JGDM_PROC_PARA(tablename varchar2)
is
BEGIN
DBMS_OUTPUT.put_line('GO GO GO! ');
DBMS_OUTPUT.put_line('BEGIN GD_CKLDZB!');
LOOP
execute immediate'update gd_ckldzb set zzf=(select newjgdm from '|| tablename||' where oldjgdm=zzf)
where zzf IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
LOOP
execute immediate'update gd_ckldzb set jsf=(select newjgdm from '|| tablename ||' where oldjgdm=jsf)
where jsf IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP; DBMS_OUTPUT.put_line('BEGIN GD_CKLDMXB!');
LOOP
execute immediate'update gd_ckldmxb set xjz=(select newjgdm from '|| tablename ||' where oldjgdm=xjz)
WHERE xjz IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
DBMS_OUTPUT.put_line('BEGIN GD_JKLDZB');
LOOP
execute immediate'update gd_jkldzb set zzf=(select newjgdm from '|| tablename ||' where oldjgdm=zzf)
where zzf IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
LOOP
execute immediate'update gd_jkldzb set jsf=(select newjgdm from '|| tablename ||' where oldjgdm=jsf)
where jsf IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
DBMS_OUTPUT.put_line('BEGIN GD_JKLDMXB!');
LOOP
execute immediate'update gd_jkldmxb set fcz=(select newjgdm from '|| tablename ||' where oldjgdm=fcz)
WHERE fcz IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
DBMS_OUTPUT.put_line('BEGIN GD_YJXXB');/*
LOOP
update gd_yjxxb set yjtm=yjhm|| sjjdm ||substr(yjtm,13,1)
WHERE sjjdm IN (SELECT NEWJGDM FROM JGDM_TeMP)
and rownum<5000;
exit when sql%notfound;
commit work;
END LOOP;
*/
EXECUTE IMMEDIATE'TRUNCATE TABLE yjxxb_temp';
commit work;
EXECUTE IMMEDIATE'insert into yjxxb_temp(oldrowid,sjjdm,jdjdm)
select rowid as oldrowid, sjjdm as sjjdm, jdjdm as jdjdm
from gd_yjxxb where sjjdm in (select oldjgdm from '||tablename||')
or jdjdm in (select oldjgdm from '||tablename||')';
commit work; LOOP
update gd_yjxxb a set (sjjdm,jdjdm) = (select sjjdm,jdjdm from yjxxb_temp where oldrowid=a.rowid)
where a.rowid in (select oldrowid from yjxxb_temp
where bj<>'1'
and rownum<50000);
commit work; update yjxxb_temp set bj='1' where oldrowid in (select oldrowid from yjxxb_temp
where bj<>'1'
and rownum<50000);
exit when sql%notfound;
commit work;
END LOOP; DBMS_OUTPUT.put_line('BEGIN GD_ZBFJXX!');
LOOP
EXECUTE IMMEDIATE'update gd_zbfjxx set xjz=(select newjgdm from '||tablename||' where oldjgdm=xjz)
WHERE xjz IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
--......
DBMS_OUTPUT.put_line('BEGIN GD_ZBJBXX');
LOOP
EXECUTE IMMEDIATE'update gd_zbjbxx set yjjdm=(select newjgdm from '||tablename||' where oldjgdm=yjjdm)
WHERE yjjdm IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
LOOP
EXECUTE IMMEDIATE'update gd_zbjbxx set jdjdm=(select newjgdm from '||tablename||' where oldjgdm=jdjdm)
where jdjdm IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
EXECUTE IMMEDIATE'TRUNCATE TABLE zbjbxx_temp';
commit work;
EXECUTE IMMEDIATE'insert into zbjbxx_temp select rowid as oldrowid, zbtm as zbtm, yjjdm as yjjdm, jdjdm as jdjdm
from gd_zbjbxx where yjjdm IN (SELECT OLDJGDM FROM '||tablename||')
or jdjdm IN (SELECT OLDJGDM FROM '||tablename||')';
commit work; update zbjbxx_temp set zbtm= yjjdm || jdjdm||substr(zbtm,17,14);
commit work; update gd_zbjbxx a set a.zbtm = (select zbtm from zbjbxx_temp where OLDROWID=a.ROWID)
where a.rowid in (select oldrowid from zbjbxx_temp);
commit work;
DBMS_OUTPUT.put_line('BEGIN GD_FFQDZB!');
LOOP
EXECUTE IMMEDIATE'update gd_ffqdzb set ffjdm=(select newjgdm from '||tablename||' where oldjgdm=ffjdm)
WHERE ffjdm IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP;
LOOP
EXECUTE IMMEDIATE'update gd_ffqdzb set jdjdm=(select newjgdm from '||tablename||' where oldjgdm=jdjdm)
where jdjdm IN (SELECT OLDJGDM FROM '||tablename||')
and rownum<500000';
exit when sql%notfound;
commit work;
END LOOP; EXECUTE IMMEDIATE'TRUNCATE TABLE ffqdzb_temp';
commit work;
EXECUTE IMMEDIATE'insert into ffqdzb_temp select rowid as oldrowid, zbtm as zbtm, ffjdm as ffjdm, jdjdm as jdjdm
from gd_ffqdzb where ffjdm in (select oldjgdm from '||tablename||')
or jdjdm in (select oldjgdm from '||tablename||')';
commit work;
update ffqdzb_temp set zbtm= ffjdm || jdjdm||substr(zbtm,17,14);
commit work;
update gd_ffqdzb a set a.zbtm = (select zbtm from ffqdzb_temp where OLDROWID=a.ROWID)
where a.rowid in (select oldrowid from ffqdzb_temp);
commit work;
commit work;
DBMS_OUTPUT.put_line('FINISH!');
END change_jgdm_proc_para;
/
能发一下查询的语句吗?
SELECT S.SID SESSION_ID,s.MACHINE,s.SERIAL#,
S.USERNAME,
DECODE(LMODE,0,'None',
1,'Null',
2,'Row-S (SS)',
3,'Row-X (SX)',
4,'Share',
5,'S/Row-X (SSX)',
6,'Exclusive',
TO_CHAR(LMODE)) MODE_HELD,
DECODE(REQUEST, 0,'None',
1,'Null',
2,'Row-S (SS)',
3,'Row-X (SX)',
4,'Share',
5,'S/Row-X (SSX)',
6,'Exclusive',
TO_CHAR(REQUEST)) MODE_REQUESTED,
O.OWNER || '.' || O.OBJECT_NAME || ' (' || O.OBJECT_TYPE || ')',S.TYPE LOCK_TYPE,
L.ID1 LOCK_ID1,
L.ID2 LOCK_ID2
FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S
WHERE L.SID = S.SID
AND L.ID1 = O.OBJECT_ID
--解锁:alter system kill session 'sid,serial#'