一条语句总是执行不完，大家帮忙分析~谢谢！
存储过程中有这么一条语句
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;
tablename 是传入的变量 ，yjxxb_temp是临时表，是空的，gd_yjxxb是个有4千万数据的表。
执行到这条语句时的锁表情况为
1 WEBDB 31 10193 zxj_db2 oracle@zxj_db2 (P003) oracle
2 WEBDB 32 5533 zxj_db2 oracle@zxj_db2 (P000) oracle
3 WEBDB 59 5754 zxj_db2 sqlplus@zxj_db2 (TNS V1-V3) oracle
4 WEBDB ROW LOCK 59 5754 zxj_db2 sqlplus@zxj_db2 (TNS V1-V3) oracle
5 WEBDB TABLE LOCK WEBDB YJXXB_TEMP TABLE 59 5754 zxj_db2 sqlplus@zxj_db2 (TNS V1-V3) oracle
6 WEBDB 59 5754 zxj_db2 sqlplus@zxj_db2 (TNS V1-V3) oracle
7 WEBDB 59 5754 zxj_db2 sqlplus@zxj_db2 (TNS V1-V3) oracle
8 WEBDB 59 5754 zxj_db2 sqlplus@zxj_db2 (TNS V1-V3) oracle
9 WEBDB 59 5754 zxj_db2 sqlplus@zxj_db2 (TNS V1-V3) oracle
10 WEBDB 92 1659 zxj_db2 oracle@zxj_db2 (P004) oracle
11 WEBDB 100 4203 zxj_db2 oracle@zxj_db2 (P002) oracle
12 WEBDB 148 2763 zxj_db2 oracle@zxj_db2 (P001) oracle
总是执行不完，不知道是什么原因，大家帮忙分析分析！~谢谢！
问题点数：20、回复次数：5Top
1 楼zouxin2000（惨绿青春）回复于 2005-08-03 18:24:37 得分 0
整个过程
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;
/
Top
2 楼liuyi8903（不让疑问伴随）回复于 2005-08-03 20:25:15 得分 0
查询是否有锁等待！
Top
3 楼zouxin2000（惨绿青春）回复于 2005-08-04 09:04:56 得分 0
能发一下查询的语句吗？Top
4 楼bobfang（匆匆过客）回复于 2005-08-04 09:59:37 得分 0
把那个动态SQL拿出来用explain plan看看ORACLE执行策略是怎样的。大表，有IN，还有or，不知道oracle是怎样优化的。Top
5 楼liuyi8903（不让疑问伴随）回复于 2005-08-05 08:57:49 得分 20
回复人： zouxin2000(惨绿青春) ( ) 信誉：100 2005-08-04 09:04:00 得分: 0
能发一下查询的语句吗？
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#'Top
