Oracle Data Pump Export (expdp) 锁定表（或类似的东西）
我必须从具有全局索引的分区表中导出数据，该索引必须始终在线，但我在执行此操作时遇到了麻烦。 对于数据导出，我使用数据泵导出 - expdp，并且仅导出一个分区。最古老的一位，而不是活跃的一位。
我的 expdp 命令导出正确的数据，如下所示：
expdp user/pass@SID DIRECTORY=EXP_DIR
DUMPFILE=part23.dmp TABLES=SCHEMA_NAME.TABLE_NAME:TABLE_PARTITION_23`
使用数据库的应用程序的连接超时为 10 秒。该参数无法更改。如果 INSERT 查询未在 10 秒内完成，数据将写入备份文件。
我的问题是，在持续几分钟的导出过程中，一些数据最终出现在备份文件中，而不是数据库中。我想知道为什么，并避免它。
分区每周组织一次，我保持 4 个分区处于活动状态（过去 4 周）。每个分区最大为 3 GB。
我使用的是Oracle 11.2
I must export data from a partitioned table with global index that must be online all the time, but I am having troubles in doing that.
For data export I am using Data Pump Export - expdp and I am exporting only one partition. The oldest one, not the active one.
My expdp command exports correct data and it looks like this:
expdp user/pass@SID DIRECTORY=EXP_DIR
DUMPFILE=part23.dmp TABLES=SCHEMA_NAME.TABLE_NAME:TABLE_PARTITION_23`
Application that uses database has a connection timeout of 10 seconds. This parameter can't be changed. If INSERT queries are not finished within 10 seconds, data is written to a backup file.
My problem is that, during the export process that lasts few minutes, some data ends up in the backup file, and not in the database. I want to know why, and avoid it.
Partitions are organized weekly, and I am keeping 4 partitions active (last 4 weeks). Every partition is up to 3 GB.
I am using Oracle 11.2
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您是否获得使用 AWR 的许可？如果是这样，您是否有发生超时时快照的 AWR 报告？
Oracle 读取器不会阻止写入器，并且导出过程没有理由锁定任何会影响新插入的内容。
这是一个超时为 10 秒的单个 INSERT 操作（即您在单个 INSERT 语句中插入大量行）吗？或者这是一批单独的插入，其中一些插入可以在 10 秒窗口内成功，而另一些则可能失败？您说“某些数据最终出现在备份文件中”，但我不确定这些场景中哪一个更准确。
在正常操作期间，距离 10 秒超时还有多远？
系统是否可能受 I/O 限制，并且执行导出会增加 I/O 系统的负载，从而导致所有操作变慢？如果您遇到 I/O 瓶颈，并且添加了一个必须读取 3 GB 分区并将数据写入磁盘（可能也在数据库服务器上）的导出进程，那么这肯定会导致总体速度下降。如果您已经相当接近 10 秒暂停，那肯定会将您推向边缘。
Are you licensed to use the AWR? If so, do you have an AWR report for the snapshot when the timeouts occurred?
Oracle readers don't block writers and there would be no reason for an export process to lock anything that would impact new inserts.
Is this a single INSERT operation that has a timeout of 10 seconds (i.e. you are inserting a large number of rows in a single INSERT statement)? Or is this a batch of individual inserts such that some of the inserts can succeed in the 10 second window and some can fail? You say that "some data ends up in the backup file" but I'm not sure which of these scenarios are more accurate.
During normal operations, how close are you to the 10 second time-out?
Is it possible that the system is I/O bound and that doing the export increases the load on the I/O system causing all operations to be slower? If you've got an I/O bottleneck and you add an export process that has to read a 3 GB partition and write that data to disk (presumably also on the database server), that could certainly cause a general slowdown. If you're reasonably close to the 10 second time-out already, that could certainly push you over the edge.