如何在执行sql loader命令时发生错误时回滚？ [英] How to rollback when an error occurs while executing sql loader command?

问题描述

如果在载入此文件时

$ cat employee.txt
100,Thomas,Sales,5000
200,Jason,Technology,5500
300,Mayla,Technology,7000
400,Nisha,Marketing,9500
500,Randy,Technology,6000
501,Ritu,Accounting,5400

使用控制文件sqlldr-add-new.ctl所有的记录都是有故障的，所以我想要保留该表中的以前加载的记录（昨天加载的），如果今天有任何错误。如何处理此异常。

using the control file (say) sqlldr-add-new.ctl I came to know all the records are faulty so I want the previously loaded records in that table (those that were loaded yesterday) to be retained if today's had any error. How to handle this exception.

这是我的示例ctl文件

This is my sample ctl file

$ cat sqlldr-add-new.ctl
load data
infile '/home/ramesh/employee.txt'
into table employee
fields terminated by ","
( id, name, dept, salary )

推荐答案

您不能从SQL * Loader回滚，它会自动提交。这在 错误中提及参数描述：

You can't roll back from SQL*Loader, it commits automatically. This is mentioned in the errors parameter description:

在单表加载时，SQL * Loader终止加载错误超过此错误限制。

On a single-table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.

还有中断货物部分。

您可以尝试将数据加载到暂存表，如果成功将数据移动到实际表中（ delete / insert into .. select .. ），或者如果您有大量的数据，使用分区交换）。或者你可以使用外部表，并做同样的事情，但你需要一个方法来确定表是否有任何丢弃或拒绝的记录。

You could attempt to load the data to a staging table, and if it is successful move the data into the real table (with delete/insert into .. select .., or with a partition swap if you have a large amount of data). Or you could use an external table and do the same thing, but you'd need a way to determine if the table had any discarded or rejected records.

这篇关于如何在执行sql loader命令时发生错误时回滚？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！