Oracle 亿级数据 更新 实战方案

sql
-- 表空间检查（需至少1.5倍更新数据量的空闲空间）
SELECT tablespace_name, 
       ROUND(SUM(bytes)/1024/1024/1024,2) "Free(GB)" 
FROM dba_free_space 
WHERE tablespace_name IN ('USERS','UNDOTBS1')
GROUP BY tablespace_name;
-- UNDO表空间监控（建议保留时间≥6小时）
SELECT tablespace_name, status, ROUND(sum_bytes/1024/1024,2) "MB" 
FROM dba_undo_extents 
GROUP BY tablespace_name, status;

sql
-- 创建临时表（与目标表同结构）
CREATE TABLE temp_update_table 
PARALLEL 16 
NOLOGGING 
AS SELECT * FROM target_table WHERE 1=0;
-- 禁用非关键约束
BEGIN
  FOR c IN (SELECT constraint_name FROM user_constraints 
            WHERE table_name='TARGET_TABLE' AND constraint_type='R') LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE target_table DISABLE CONSTRAINT '||c.constraint_name;
  END LOOP;
END;
/
-- 设置优化参数
ALTER SESSION SET "_hash_join_enabled"=FALSE;
ALTER SYSTEM SET "_parallel_cluster_cache_policy"=ADAPTIVE SCOPE=MEMORY;

sql
-- 启用并行DML（需SYSDBA权限）
ALTER SESSION ENABLE PARALLEL DML;
-- 分批更新（每批100万条）
DECLARE
  CURSOR c_data IS 
    SELECT rowid as row_id FROM target_table 
    WHERE [your_condition] 
    ORDER BY rowid;
  TYPE t_rowids IS TABLE OF ROWID;
  v_rowids t_rowids;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data BULK COLLECT INTO v_rowids LIMIT 1000000;
    EXIT WHEN v_rowids.COUNT = 0;
    
    FORALL i IN 1..v_rowids.COUNT
      UPDATE target_table 
      SET column1=new_value1, 
          column2=new_value2
      WHERE rowid = v_rowids(i);
    
    COMMIT;
    DBMS_LOCK.SLEEP(5); -- 每批间隔5秒
  END LOOP;
  CLOSE c_data;
END;
/

sql
-- 步骤1：更新临时表数据
INSERT /*+ APPEND PARALLEL(16) */ INTO temp_update_table
SELECT [columns] FROM source_data WHERE [conditions];
-- 步骤2：交换分区（秒级操作）
ALTER TABLE target_table 
EXCHANGE PARTITION p_2025 
WITH TABLE temp_update_table 
INCLUDING INDEXES 
UPDATE GLOBAL INDEXES;

sql
-- 增加LMS进程数量
ALTER SYSTEM SET "_gc_lms_processes"=8 SCOPE=SPFILE;
-- 禁用细粒度缓存控制
ALTER SYSTEM SET "_gc_policy_time"=0 SCOPE=SPFILE;

bash
# 创建专用更新服务
srvctl add service -d racdb -s UPDATE_SVC \
-r rac1,rac2 -P BASIC -j SHORT -z 30 -B SERVICE_TIME

bash
# 调整RAC私网参数（需root权限）
ifconfig bond0 mtu 9000 txqueuelen 10000
echo "net.core.rmem_max=4194304" >> /etc/sysctl.conf
sysctl -p

sql
-- 集群等待事件监控
SELECT inst_id, event, count(*) 
FROM gv$session_wait 
WHERE wait_class != 'Idle' 
GROUP BY inst_id, event 
ORDER BY 3 DESC;
-- 资源争用监控
SELECT inst_id, block_type, status, count(*) 
FROM gv$gc_element 
GROUP BY inst_id, block_type, status;

sql
ALTER TABLESPACE undotbs1 ADD DATAFILE '+DATA' SIZE 50G AUTOEXTEND ON;

sql
-- 重建索引（在线操作）
ALTER INDEX idx_name REBUILD ONLINE PARALLEL 8;
-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname=>'SCHEMA', 
  tabname=>'TARGET_TABLE', 
  degree=>16,
  cascade=>TRUE);

sql
CREATE RESTORE POINT pre_update GUARANTEE FLASHBACK DATABASE;