使用SQL Developer进行Oracle批量插入 [英] Oracle Bulk Insert Using SQL Developer

问题描述

我最近从Oracle数据库进行了数据转储. 它们中的许多都很大(〜5GB).我试图通过在SQL Developer中执行以下SQL将转储的数据插入另一个Oracle数据库中

I have recently taken data dumps from an Oracle database. Many of them are large in size(~5GB). I am trying to insert the dumped data into another Oracle database by executing the following SQL in SQL Developer

@C:\path\to\table_dump1.sql;
@C:\path\to\table_dump2.sql;
@C:\path\to\table_dump3.sql;
             :

但是要完成一个单独的SQL文件也要花费超过一天的时间.
有没有更好的方法可以更快地完成此任务?

but it is taking a long time like more than a day to complete even a single SQL file.
Is there any better way to get this done faster?

推荐答案

SQL * Loader是我最喜欢的将大数据量批量加载到Oracle中的方法.使用直接路径插入选项可最大程度地提高速度，但可以理解直接路径负载的影响(例如，所有数据都插入高水位线之后，如果您截断了表格，这很好).它甚至可以容忍错误的行，因此，如果您的数据有一些"错误，它仍然可以工作.

SQL*Loader is my favorite way to bulk load large data volumes into Oracle. Use the direct path insert option for max speed but understand impacts of direct-path loads (for example, all data is inserted past the high water mark, which is fine if you truncate your table). It even has a tolerance for bad rows, so if your data has "some" mistakes it can still work.

SQL * Loader可以挂起索引并在最后建立所有索引，这使得批量插入非常快.

SQL*Loader can suspend indexes and build them all at the end, which makes bulk inserting very fast.

SQL * Loader调用示例:

Example of a SQL*Loader call:

$SQLDIR/sqlldr /@MyDatabase direct=false silent=feedback \
    control=mydata.ctl log=/apps/logs/mydata.log bad=/apps/logs/mydata.bad \
    rows=200000

mydata.ctl看起来像这样:

And the mydata.ctl would look something like this:

LOAD DATA
INFILE '/apps/load_files/mytable.dat'
INTO TABLE my_schema.my_able
FIELDS TERMINATED BY "|"
 (ORDER_ID,
  ORDER_DATE,
  PART_NUMBER,
  QUANTITY)

或者...如果您只是跨数据库将一个表的全部内容复制到另一个表，那么如果您的DBA设置了DBlink(一个30秒的过程)，而前提是您的DB设置了数据库链接，则可以执行此操作.重做空间以完成此操作.

Alternatively... if you are just copying the entire contents of one table to another, across databases, you can do this if your DBA sets up a DBlink (a 30 second process), presupposing your DB is set up with the redo space to accomplish this.

truncate table my_schema.my_table;

insert into my_schema.my_table
select * from my_schema.my_table@my_remote_db;

使用/* +append */提示仍然可以使用直接路径插入.

The use of the /* +append */ hint can still make use of direct path insert.

这篇关于使用SQL Developer进行Oracle批量插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！