ORA删除/截断 [英] ORA delete / truncate

问题描述

我正在使用 SQL 加载器将我的数据加载到数据库中.

I'm using SQL loader to load my data into database.

在插入数据之前，我需要删除表中的现有数据:

Before I insert the data I need to remove existing data in the table:

options(skip=1,load=250000,errors=0,ROWS=30000,BINDSIZE=10485760)
load data
infile 'G:1.csv' "str '^_^'"
replace
into table IMPORT_ABC
fields terminated by "," OPTIONALLY ENCLOSED BY '"'
trailing nullcols(
.
.
.
.)

但我得到了如下错误:

SQL*LOADER-926:对表 IMPORT_ABC 执行删除/截断时发生 OCI 错误ORA-30036: 无法在撤消表空间undo1"中将段扩展 8

SQL*LOADER-926: OCI error while executing delete/truncate for table IMPORT_ABC ORA-30036: unable to extend segment by 8 in undo tablespace 'undo1'

如何删除数据，例如 10000 行?我知道我的数据库有一些限制.

How can I delete data for example by 10000 rows? I know that I have some limit on my DB.

推荐答案

批量删除记录可以在 PL/SQL 循环中完成，但通常被认为是不好的做法，因为整个删除通常应该被视为单个事务；这不能从 SQL*Loader 控制文件中完成.您的 DBA 应调整 UNDO 空间的大小以适应您需要完成的工作.

Deleting records in batches can be done in a PL/SQL loop, but is generally considered bad practice as the entire delete should normally be considered as a single transaction; and that can't be done from within the SQL*Loader control file. Your DBA should size the UNDO space to accommodate the work you need to do.

如果您要删除整个表，那么无论如何截断都会更好，在控制文件中:

If you're deleting the entire table you'll almost certainly be better off truncating anyway, either in the control file:

options(skip=1,load=250000,errors=0,ROWS=30000,BINDSIZE=10485760)
load data
infile 'G:1.csv' "str '^_^'"
truncate
into table IMPORT_ABC
...

或者作为单独的truncate 开始加载之前 SQL*Plus/SQL Developer/其他客户端中的语句:

Or as a separate truncate statement in SQL*Plus/SQL Developer/some other client before you start the load:

truncate table import_abc;

缺点是当加载新行时，您的表对其他用户来说是空的，但如果它是一个专用的导入区域(从名称中猜测)，则无论如何都可能无关紧要.

The disadvantage is that your table will appear empty to other users while the new rows are being loaded, but if it's a dedicated import area (guessing from the name) that may not matter anyway.

如果您的 UNDO 真的那么小，那么您可能需要运行多次加载，在这种情况下 - 很明显 - 您需要确保只有 truncate在第一个的控制文件中(或使用单独的 truncate 语句)，并有 append 代替您在评论中指出的后续控制文件.

If your UNDO is really that small then you may have to run multiple loads, in which case - probably obviously - you need to make sure you only have the truncate in the control file for the first one (or use the separate truncate statement), and have append instead in subsequent control files as you noted in comments.

您可能还想考虑外部表如果您将此数据用作填充其他内容的基础，则替换外部数据源不会产生 UNDO 开销.您可能需要与您的 DBA 讨论设置并授予您必要的目录权限.

You might also want to consider external tables if you're using this data as a base to populate something else, as there is no UNDO overhead on replacing the external data source. You'll probably need to talk to your DBA about setting that up and giving you the necessary directory permissions.

这篇关于ORA删除/截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！