oracle Microsoft Access 的临时空间不足（错误 3183）

Microsoft Access running out of temp space (Error 3183)

提问by Steve Thorne

I inherited this Frankenstein application at work that utilizes an Access 2007 DB frontend with linked tables to an Oracle 10g DB.

我在工作中继承了这个 Frankenstein 应用程序，它利用 Access 2007 DB 前端和链接表到 Oracle 10g DB。

Users have started reporting that a lot of the queries are failing with an error 3183 out of temporary space. These same queries have run fine a few weeks back.

用户已经开始报告许多查询失败，错误 3183 的临时空间不足。几周前，这些相同的查询运行良好。

I verified that Access is creating a 2GB temp file which is the maximum amount allowed in the system temp directory.

我确认 Access 正在创建一个 2GB 的临时文件，这是系统临时目录中允许的最大数量。

Even if I run the query using parameters that would not return data, I still get the error. Doing a SQL trace shows that some of the queries are trying to run a queryset that would return about 14 million rows. I know some of the queries are poorly designed, but that is not an easy thing to change.

即使我使用不会返回数据的参数运行查询，我仍然收到错误消息。执行 SQL 跟踪表明，某些查询正在尝试运行将返回大约 1400 万行的查询集。我知道有些查询设计得很差，但这不是一件容易改变的事情。

I had the Oracle DBA double the temp space, but still didn't fix the issue.

我让 Oracle DBA 将临时空间加倍，但仍然没有解决问题。

I guess my question is does an Access DB use the temp space available on the linked ODBC database first then use the temp file if no Oracle temp tablespace is available? Any ideas why this would start happening or is it just that the amount of the data in the Oracle database has exceeded the ability of Access? The way the application was created had been working up until a few weeks back or so.

我想我的问题是，如果没有可用的 Oracle 临时表空间，Access DB 是否首先使用链接的 ODBC 数据库上可用的临时空间然后使用临时文件？任何想法为什么会开始发生这种情况，或者只是因为 Oracle 数据库中的数据量已经超出了 Access 的能力？应用程序的创建方式一直持续到几周前。

Understand redesigning this app is not an easy option. I am more looking for an idea as to why this would have worked before and started happening recently.

了解重新设计此应用程序并非易事。我更想知道为什么这会在以前起作用并且最近开始发生。

This is the Access RAW SQL.. Understand I didn't write this.. The Oracle tables at least didn't have space and ampersands. :)

这是 Access RAW SQL.. 明白我没有写这个.. Oracle 表至少没有空间和符号。:)

    SELECT DISTINCT [Event: Overhaul & Repair].BASE_PART_NUMBER,
  [Event: Overhaul & Repair].PERIOD,
  [Event: Overhaul & Repair].RECEIVED_BY_FACILITY_DATE,
  [Event: Overhaul & Repair].PART_NUMBER,
  [Event: Overhaul & Repair].CONFIGURATION_RECEIVED,
  [Event: Overhaul & Repair].PART_SERIAL_NBR,
  [Findings: Feature Findings Detail].SUBASSEMBLY_NAME,
  [Findings: Feature Findings Detail].COMPONENT_NAME,
  [Findings: Feature Findings Detail].FEATURE_NAME,
  [Findings: Feature Findings Detail].FAILURE_DESCRIPTION,
  [Findings: Feature Findings Detail].PRIMARY_FAILURE_IND,
  [Event: Overhaul & Repair].REMOVAL_JUSTIFIED_FLAG_ON_OR,
  [Event: Overhaul & Repair].CUSTOMER_NAME_SUBMITTING,
  [Findings: Feature Findings Detail].AIRCRAFT_TYPE,
  [Event: Overhaul & Repair].AIRCRAFT_REG_NUMBER,
  [Event: Overhaul & Repair Text].NOTE_TEXT,
  [Event: Overhaul & Repair].TIME_SINCE_NEW_ON_OR,
  [Event: Overhaul & Repair].TIME_SINCE_INSTALL,
  [Event: Overhaul & Repair].TIME_SINCE_OVERHAUL_ON_OR,
  [Event: Overhaul & Repair].FACILITY_NAME,
  [Event: Overhaul & Repair].EVENT_SEQNO
FROM ([Event: Overhaul & REPAIR]
LEFT JOIN [Event: Overhaul & REPAIR Text]
ON [Event: Overhaul & REPAIR].EVENT_SEQNO=[Event: Overhaul & REPAIR Text].EVENT_SEQNO)
LEFT JOIN [Findings: Feature Findings Detail]
ON [Event: Overhaul & REPAIR].EVENT_SEQNO=[Findings: Feature Findings Detail].EVENT_SEQUENCE_NUMBER
WHERE ((([Event: Overhaul & Repair].BASE_PART_NUMBER)=[PART NUMBER])
AND (([Event: Overhaul & Repair].PERIOD) BETWEEN [START DATE YYYYMM] AND [END DATE YYYYMM])
AND (([Findings: Feature Findings Detail].PRIMARY_FAILURE_IND)="PF"
OR ([Findings: Feature Findings Detail].PRIMARY_FAILURE_IND) IS NULL)
AND (([Event: Overhaul & Repair Text].NOTE_TEXT)="R"));

回答by HansUp

This is the error description for AccessError(3183):

这是错误描述AccessError(3183)：

The query cannot be completed. Either the size of the query result is larger than the maximum size of a database (2 GB), or there is not enough temporary storage space on the disk to store the query result.

无法完成查询。要么查询结果的大小大于数据库的最大大小（2GB），要么磁盘上没有足够的临时存储空间来存储查询结果。

When the Access db engine needs space for its work set, it uses a temporary disk file. That file is size-restricted to a max of 2 GB, the same as a regular Access db file.

当 Access db 引擎需要为其工作集提供空间时，它会使用临时磁盘文件。该文件的大小限制为最大 2 GB，与常规 Access db 文件相同。

So apparently the Access db engine must pull down so much data from Oracle that its work set space requirement exceeds 2 GB. My hunch is that happens even when you adjust the query constraints so that no rows match because Access must first pull so much data for the intermediate set before it can even determine none of the candidate rows satisfy the constraint.

很明显，Access 数据库引擎必须从 Oracle 中提取如此多的数据，以至于其工作集空间要求超过 2 GB。我的预感是，即使您调整查询约束使没有行匹配，也会发生这种情况，因为 Access 必须首先为中间集提取如此多的数据，然后才能确定没有任何候选行满足约束。

You need to find some way to limit the amount of data Access must process. Since the Oracle view you attempted created a different problem, I don't know what else to suggest. If the final result set from the Oracle query is reasonably-sized, and if you can use the result set as read-only from the Access side, use an Access pass-through query. If that's not satisfactory, show us the Access SQL from a problem query and see whether we can fix it.

您需要找到某种方法来限制 Access 必须处理的数据量。由于您尝试的 Oracle 视图创建了一个不同的问题，我不知道还有什么建议。如果 Oracle 查询的最终结果集大小合理，并且您可以从 Access 端将结果集用作只读，请使用 Access pass-through 查询。如果这不令人满意，请向我们展示问题查询中的 Access SQL，看看我们是否可以修复它。

Regarding "Any ideas why this would start happening ...", most likely the volume of the Oracle data has grown over time.

关于“为什么这会开始发生的任何想法......”，很可能 Oracle 数据量随着时间的推移而增长。