Oracle 亿级数据 插入 实战方案

SELECT tablespace_name, 
       (bytes/1024/1024) "Free(MB)" 
FROM dba_free_space 
WHERE tablespace_name = 'TARGET_TBS';

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

-- 禁用非关键约束
ALTER TABLE billion_table DISABLE CONSTRAINT fk_constraint_name;

-- 创建中间表（与目标表同结构）
CREATE TABLE interim_table 
NOLOGGING 
PARALLEL 32 
AS SELECT * FROM billion_table WHERE 1=0;

-- 设置临时高水位标记
ALTER SESSION SET "_highpriority_parallelism"=TRUE;

# 使用SQL*Loader直接路径加载
sqlldr userid=system/pwd@racdb \
control=load.ctl \
data=data.csv \
direct=true \
parallel=true \
errors=100000 \
log=load_$(date +%Y%m%d).log

OPTIONS (SKIP=1,ROWS=100000)
LOAD DATA
INFILE 'data.csv'
APPEND INTO TABLE interim_table
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(col1, col2 DATE "YYYY-MM-DD HH24:MI:SS", col3)

-- 分区交换（秒级操作）
ALTER TABLE billion_table 
EXCHANGE PARTITION p_new 
WITH TABLE interim_table 
INCLUDING INDEXES 
UPDATE GLOBAL INDEXES;

ALTER SYSTEM SET "_gc_policy_time"=0 SCOPE=SPFILE;  -- 禁用对象级策略
ALTER SYSTEM SET "_gc_lms_processes"=32 SCOPE=SPFILE;  -- 增加LMS进程

-- 创建专用服务
srvctl add service -d racdb -s LOAD_SVC \
-r rac1,rac2 -P BASIC -j SHORT -B SERVICE_TIME

# 调整RAC私网参数
ifconfig bond0 mtu 9000 txqueuelen 10000
echo "net.core.rmem_max=4194304" >> /etc/sysctl.conf

-- 全局等待事件
SELECT event, count(*) FROM gv$session_wait 
WHERE wait_class != 'Idle' 
GROUP BY event ORDER BY 2 DESC;

-- RAC资源争用
SELECT inst_id, block_type, status, count(*) 
FROM gv$gc_element 
GROUP BY inst_id, block_type, status;

ALTER TABLESPACE target_tbs ADD DATAFILE '+DATA' SIZE 100G AUTOEXTEND ON;

-- 数据一致性验证
SELECT COUNT(*) FROM billion_table PARTITION(p_new);

-- 性能基准对比
SELECT * FROM table(dbms_xplan.compare_plans(
  cursor(select * from plan_table where id=1),
  cursor(select * from plan_table where id=2)));

CREATE RESTORE POINT pre_load GUARANTEE FLASHBACK DATABASE;

EXEC dbms_stats.gather_table_stats('SCHEMA','BILLION_TABLE',degree=>32);