一条语句总是执行不完，大家帮忙分析~谢谢！

小看红尘

存储过程中有这么一条语句
    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


总是执行不完，不知道是什么原因，大家帮忙分析分析！~谢谢！

xamhd

把整个存储过程贴出来！

小看红尘

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;
/