SQL ORA删除/截断
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15878297/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA delete / truncate
提问by 4est
I'm using SQL loader to load my data into database.
我正在使用 SQL 加载器将我的数据加载到数据库中。
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(
.
.
.
.)
But I got error like:
但我得到了如下错误：
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'
SQL*LOADER-926：对表 IMPORT_ABC 执行删除/截断时发生 OCI 错误 ORA-30036：无法在撤消表空间“undo1”中将段扩展 8
How can I delete data for example by 10000 rows? I know that I have some limit on my DB.
如何删除数据，例如 10000 行？我知道我的数据库有一些限制。
采纳答案by Alex Poole
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 UNDOspace to accommodate the work you need to do.
批量删除记录可以在 PL/SQL 循环中完成，但通常被认为是不好的做法，因为整个删除通常应视为单个事务；这不能从 SQL*Loader 控制文件中完成。您的 DBA 应调整UNDO空间大小以适应您需要完成的工作。
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
...
Or as a separate truncatestatement in SQL*Plus/SQL Developer/some other client before you start the load:
或者truncate在开始加载之前作为SQL*Plus/SQL Developer/其他客户端中的单独语句：
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.
缺点是当加载新行时，您的表对其他用户来说是空的，但如果它是专用的导入区域（从名称中猜测），无论如何都可能无关紧要。
If your UNDOis 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 truncatein the control file for the first one (or use the separate truncatestatement), and have appendinstead in subsequent control files as you noted in comments.
如果你UNDO真的那么小，那么你可能必须运行多个加载，在这种情况下 - 可能很明显 - 你需要确保你只有truncate第一个的控制文件（或使用单独的truncate语句），而append不是在您在评论中指出的后续控制文件中。
You might also want to consider external tablesif you're using this data as a base to populate something else, as there is no UNDOoverhead 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.
如果您使用此数据作为填充其他数据的基础，您可能还需要考虑外部表，因为UNDO替换外部数据源没有开销。您可能需要与您的 DBA 讨论设置并授予您必要的目录权限。
回答by Sibster
Your undo tablespace is to small to hold all the undo information and it seems it cannot be extended.
您的撤消表空间太小，无法容纳所有撤消信息，而且似乎无法扩展。
You can split the import into smaller batched and issue a commit after each batch or get your DBA to increase the tablespace for undo1
您可以将导入拆分为更小的批处理并在每个批处理后发出提交或让您的 DBA 增加表空间以进行 undo1
And use truncate in stead of replace before you start the immports
并在开始导入之前使用 truncate 而不是替换
