当对某数据量巨大的表(如上亿行)进行update更新时,如果直接update很可能会导致undo表空间被写满的情况:
ORA-30036: unable to extend segment by 8 in undo tablespace "UMDOTBSI'
这时就需要去分页更新, 如果只是一个表,可以参考以下语句
UPDATE 【待更新表名】 t1
SET 【列明】 = 【值】
WHERE EXISTS (SELECT *
FROM (SELECT A.* ,ROWNUM RN
FROM (SELECT * FROM 【待更新表名】) A
WHERE ROWNUM <= 280) t2
WHERE RN >= 270 AND 【t1.主键列=t2.主键列】)
或者先查出主键列条件,
SELECT LISTAGG(
'T1.'||col.column_name||'='||'T2.'||col.column_name,' AND ') within GROUP (order by col.table_name) as WH
FROM DBA_Constraints con, Dba_Cons_Columns col
WHERE col.owner = con.owner
AND col.constraint_name = con.constraint_name
AND col.owner = UPPER('【待更新表所属schema名称】')
AND col.table_name = UPPER('【待更新表名】')
AND con.constraint_type='P'
AND col.table_name NOT LIKE 'BIN$%'
或者使用rowid
UPDATE 【待更新表名】 t1
SET 【列明】 = 【值】
WHERE EXISTS (SELECT t2.rid
FROM (SELECT A.rowid rid,ROWNUM RN
FROM (SELECT rowid FROM 【待更新表名】 ) A
WHERE ROWNUM <= 100) t2
WHERE RN >= 1 AND t1.rowid=t2.rid)