有多少条记录可以包含 GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS？
我想运行一个涉及 80,000,000 条记录的 PL/SQL 过程。
此 PL/SQL 过程删除大约 80,000,000 条记录，并将它们备份到使用 ON COMMIT PRESERVE ROWS 子句创建的 GLOBAL TEMPORARY TABLE 中。
我如何知道有多少记录可以包含此 GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS？
仅在 PL/SQL 过程末尾使用 COMMIT 的情况下，这些表的大小限制是多少？
I would like to run a PL/SQL procedure involving 80,000,000 records.
This PL/SQL procedure deletes about 80,000,000 records, backupping them in a GLOBAL TEMPORARY TABLE created with the clause ON COMMIT PRESERVE ROWS.
How can I know how many records can contain this GLOBAL TEMPORARY TABLE ON COMMIT PRESERVE ROWS?
What is the size limit for these tables, with a COMMIT only at the end of the PL/SQL procedure?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
有两个因素会限制您可以插入的行数：临时空间和撤消空间。
您可以在临时表空间中放入尽可能多的数据。如果允许临时表空间增长（使用自动扩展数据文件和表空间），则只会受到磁盘空间的限制。现在您想要估计行的大小并留出一些额外的开销空间。这将为您提供临时表空间所需大小的粗略估计。
单个事务需要完全适合撤消表空间。插入的撤消数据比其他 DML 小，但 80M 行仍会产生大量撤消。如果您还从其他表中删除这些行，则撤消操作将占用与原始行大致相同的空间。您可能正在使用自动撤消管理，只需将表空间及其数据文件设置为自动扩展即可。
如果这是一次性的，您可能希望在完成后减少临时表空间和撤消表空间的大小。如果您要定期执行此操作，只需让表空间增长并随后将其留在那里即可。
80M 行事务的唯一真正问题是，如果出现问题，您可能会经历很长的回滚时间。特别是删除的行将使您的回滚时间比实际删除的时间长得多。
虽然 Oracle 和大型事务没有什么根本性的问题（Oracle将会扩展），但是将总工作划分为较小的工作单元将允许您更快地重新启动流程并在较小的数据子集上，以防万一失败的。
Two factors will limit the number of rows you can insert: temporary space and undo space.
You can put as much data in a temporary table as there is space in the temporary tablespace. If the temporary tablespace is allowed to grow (with autoextend datafiles and tablespace), you will only be limited by the disk space. Now you want to estimate the size of your rows and allow some extra room for overhead. This will give you a rough estimate of the size needed by the temporary tablespace.
A single transaction needs to fit entirely in the undo tablespace. Undo data for insert is smaller than other DMLs, still 80M rows will produce a LOT of undo. If you're also deleting these rows from some other table, the undo will take roughly the same space as the original rows. You're probably using automatic undo management, just set the tablespace and its datafiles to autoextend and you're good.
If this is a one-shot you may want to reduce the size of both temporary and undo tablespaces once you're done. If you're going to perform this operation on a regular basis, just let the tablespaces grow and leave them there afterwards.
The only real problem with a 80M row transaction is the looooooong rollback time you may experience if something goes wrong. Deleted rows in particular will make your rollback a lot longer than the actual deletion.
While there is nothing fundamentally wrong with Oracle and a large transaction (Oracle will scale), dividing the total work into smaller work units will allow you to restart the process faster and on a smaller subset of data in case of failure.
如果唯一的提交是在过程的末尾，那么 on commit 子句就有点无关紧要，除非这个过程只是更大过程的一部分。当您的会话结束时，无论
on commit设置如何，GTT 数据都将消失，因此您的“备份”数据仅在执行该过程的会话中可用。从给出的上下文来看，不清楚您是否意识到您的“备份”是暂时的。on commit keep rows子句的作用是允许您在会话期间提交非 GTT 数据，而不会丢失 GTT 中的内容。假设您想分块删除数据，一次可能删除一百万行。因此，您确定一百万行，将它们复制到 GTT，从原始表中删除它们，然后提交。如果您的on commit设置是delete rows那么此时您的GTT又是空的，所以您没有备份。但是，如果您的on commit是preserve rows，那么您的GTT将保留您插入的百万行。重复 80 次...最后，
删除行时，GTT 为空，并且一次从未保存超过一百万行；使用preserve rows，它每次都会增长，并且将拥有全部 8000 万条记录。但仍然仅限于会议结束。使用
保留行，如果您在任何时候遇到问题，您都可以将“备份”GTT 中的所有数据重新插入到原始表中。使用删除行，您只能重新插入自上次提交以来删除的所有内容 - 但您也可以在此时回滚。If the only commit is at the end of the procedure then the
on commitclause is a bit irrelevant, unless this procedure is just a part of a larger process. When your session ends the GTT data will be gone regardless of theon commitsetting, so your 'backup' data is only available within the session that's executing the procedure. From the context given it's not clear if you realise that your 'backup' is temporary.What the
on commit preserve rowsclause does is allow you to commit non-GTT data during the session without losing what's in the GTT. Say you want to delete your data in chunks, maybe a million rows at a time. So you identify your million rows, copy them to the GTT, delete them from the original table, and commit. If youron commitsetting isdelete rowsthen at this point your GTT is empty again, so you have no backup. But if youron commitispreserve rowsthen your GTT keeps the million rows you inserted.Repeat 80 times... and at the end with
delete rowsthe GTT is empty and never held more than a million rows at a time; withpreserve rowsit will have grown each time and will have all 80 million records. But still only until the session ends.With
preserve rowsif you encounter a problem at any point you can reinsert all the data from your 'backup' GTT into your original table. Withdelete rowsyou could only reinsert whatever has been deleted since the last commit - but then you might as well just rollback at that point.