Skip to Main Content
  • Questions

  • ORA-04030 when using sqlldr direct path load

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hamish.

Asked: February 28, 2018 - 9:36 am UTC

Last updated: February 28, 2018 - 11:12 am UTC

Version: 11.1g

Viewed 1000+ times

You Asked

I am using sqlldr to load two hundred million data, but after a few minutes I get a
"out of process memory when trying to allocate 64544 bytes (sort subheap,sort key)" error , and trace file shows that `sort subheap` used up to 22G memory.

Following are sqlldr command parameter:
`sqlldr ${user}/${passwd}@${db} control=${ctl_file} rows=50000 bindsize=15728640 readsize=15728640 log=log_${ctl_file}.log bad=bad_${ctl_file}.bad direct=y`

and Chris said...

You're out of memory!

It's possible you're hitting a bug. If so the ORA-04030 troubleshooter (MOS note 521926.1) will help here.

But more likely you don't have enough memory to process 200M rows at once. Which gives two basic options:

- Allocate more memory
- Process less data, e.g. split into chunks of 10M or smaller.

For more on ORA-04030, read:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1363891100346045112

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database