oracle update 更新 一千万条数据 更新到三百万条的时候不动了

[寡丶人]

1.向数据库中插入一千万条数据，然后增加一列，定义为blob类型；用UPdate向新增的列中插入数据
update epa_test a set a.zp =(select b.zp from test_zp b ) where a.objectid between 《 1000000；
update epa_test a set a.zp =(select b.zp from test_zp b ) where a.objectid between 1000000 and 2000000；
update epa_test a set a.zp =(select b.zp from test_zp b ) where a.objectid between 2000000 and 3000000；
update epa_test a set a.zp =(select b.zp from test_zp b ) where a.objectid between 3000000 and 4000000；
2.一条一条的执行，执行到3000000的时候，就卡住了
3.在从四百万开始执行都可以，就是三百万到四百万之间都不行
不知道是怎么回事，oracle被锁了，我解不开锁

[IceIsabel]

前面的更新提交了吗？

[小灰狼W]

查一下锁

Select '节点 ' || a.INST_ID || ' session ' || a.sid || ',' || a_s.SERIAL# ||
       ' 阻塞了 节点 ' || b.INST_ID || ' session ' || b.SID || ',' || b_s.SERIAL# blockinfo,
       a.INST_ID,
       a_s.SID,
       a_s.SCHEMANAME,
       a_s.MODULE,
       a_s.STATUS,
       a.type lock_type,
       a.id1,
       a.id2,
       decode(a.lmode,
              0,
              'none',
              1,
              null,
              2,
              'row-S (SS)',
              3,
              'row-X (SX)',
              4,
              'share (S)',
              5,
              'S/Row-X (SSX)',
              6,
              'exclusive (X)') lock_mode,
       
       '后为被阻塞信息' ,
       b.INST_ID blocked_inst_id,
       b_s.SID blocked_sid,
       b.TYPE blocked_lock_type,
       decode(b.request,
              0,
              'none',
              1,
              null,
              2,
              'row-S (SS)',
              3,
              'row-X (SX)',
              4,
              'share (S)',
              5,
              'S/Row-X (SSX)',
              6,
              'exclusive (X)') blocked_lock_request,
       b_s.SCHEMANAME blocked_SCHEMANAME,
       b_s.MODULE blocked_module,
       b_s.STATUS blocked_status,
       b_s.SQL_ID blocked_sql_id,
       obj.owner blocked_owner,
       obj.object_name blocked_object_name,
       obj.OBJECT_TYPE blocked_OBJECT_TYPE,
       case
          when b_s.ROW_WAIT_OBJ# <> -1 then
             dbms_rowid.rowid_create(1,
                                     obj.DATA_OBJECT_ID,
                                     b_s.ROW_WAIT_FILE#,
                                     b_s.ROW_WAIT_BLOCK#,
                                     b_s.ROW_WAIT_ROW#) 
          else 
            '-1' 
          end blocked_rowid, --被阻塞数据的rowid
       decode(obj.object_type,
              'TABLE',
              'select * from ' || obj.owner || '.' || obj.object_name ||
              ' where rowid=''' ||
              dbms_rowid.rowid_create(1,
                                      obj.DATA_OBJECT_ID,
                                      b_s.ROW_WAIT_FILE#,
                                      b_s.ROW_WAIT_BLOCK#,
                                      b_s.ROW_WAIT_ROW#) || '''',
              NULL)  blocked_data_querysql
  from gv$lock     a,
       gv$lock     b,
       gv$session  a_s,
       gv$session  b_s,
       dba_objects obj
 where a.id1 = b.id1
   and a.id2 = b.id2
   and a.BLOCK > 0 --阻塞了其他人
   and b.request > 0
   and ((a.INST_ID = b.INST_ID and a.sid <> b.sid) or
       (a.INST_ID <> b.INST_ID))
   and a.sid = a_s.sid
   and a.INST_ID = a_s.INST_ID
   and b.sid = b_s.sid
   and b.INST_ID = b_s.INST_ID
   and b_s.ROW_WAIT_OBJ# = obj.object_id(+)
 order by a.inst_id,a.sid

[binsweet]

如果数据量大，并且你更新的又是blob列，所以最后每次更新后先commit，然后再更新下一批； 尽量分多个批次，这样可以确保临时表空间够用，比如你现在1百万做一批，可以10万一批一提交，确保成功率。即就是错误，也可以快速定位的错误点

[meichao221]

open v_cursor for 'select rowid rid,zp from epa_test'; end if; loop fetch v_cursor bulk collect into a_rowid,zp limit 1000; if a_rowid.count() > 0 then forall i in a_rowid.first .. a_rowid.last update epa_test a set a.zp =(select b.zp from test_zp b ) where rowid = a_rowid(i); commit; end if; exit when v_cursor%notfound; end loop; close v_cursor;

[卖水果的net]

每执行一次 update ,都应该 commit 一下。

[不写代码的钦]

解锁就找出锁，然后kill掉。 --查看锁表进程 select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid; ---狙掉进程SID和serial# alter system kill session '493,44511'; ---把493和44511 变成你自己的 sid 和SERIAL#

[不写代码的钦]

另外你看看你语句逻辑上有没有错误，找出导致死锁的原因。