sqlldr直接路径导入遇到ORA-4030

 

15亿+条的记录，用sqlldr导入，9个多小时后遇到ORA-4030。

调整了一些参数后再进行，同样也是在9个多小时以ORA-4030结束。

 

Sweep [inc2][132427]: completed
Trace dumping is performing id=[cdmp_20110810190110]
Wed Aug 10 19:01:11 2011
Errors in file /u01/app/oracle/diag/rdbms/ddns_fs/peanut/incident/incdir_132426/peanut_ora_20743_i132426.trc:
ORA-04030: out of process memory when trying to allocate 82456 bytes (pga heap,control file i/o buffer)
ORA-04030: out of process memory when trying to allocate 65560 bytes (klcalh:ld_hds,kdblc memory)
Trace dumping is performing id=[cdmp_20110810190112]
Wed Aug 10 19:02:08 2011
Sweep [inc2][132428]: completed
Sweep [inc2][132426]: completed

 

调整的部分参数

ALTER SYSTEM SET parallel_servers_target=32 SCOPE=BOTH;
Wed Aug 10 19:22:54 2011
ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH;
Wed Aug 10 19:24:31 2011
ALTER SYSTEM SET processes=300 SCOPE=SPFILE;
Wed Aug 10 19:24:46 2011
ALTER SYSTEM SET session_cached_cursors=100 SCOPE=SPFILE;
Wed Aug 10 19:25:25 2011
ALTER SYSTEM SET sga_max_size='1G' SCOPE=SPFILE;
Wed Aug 10 19:25:52 2011
ALTER SYSTEM SET sort_area_size=0 SCOPE=SPFILE;

[root@localhost ~]# cat /etc/sysctl.conf | grep shmmax
kernel.shmmax = 68719476736
#kernel.shmmax = 536870912

 

[root@oray ~]# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 137216
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 137216
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited