1

I inherited this Frankenstein application at work that utilizes an Access 2007 DB frontend with linked tables to an 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.

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

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.

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

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.

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. :)

    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"));
Steve Thorne
  • 11
  • 1
  • 1
  • 3
  • Does that error message originate from the Oracle server? If you run the same SQL statement (the modified version which should actually return no rows) from SQLPlus, do you still get the error? – HansUp Oct 08 '13 at 23:34
  • The error being returned is from Access (Error 3183). i modified the SQL code (same joins and parms) and ran it directly in Oracle and it ran immediately with no rows returned. Also tried to export the views that were being used to SQL server and run the query there and got an error from Oracle (ORA-01652 - could not extend temp space). That why believed to be on the Oracle side, would have thought doubling the temp tablespace would have fixed it. Also a web app hitting this same Oracle DB along and SSIS packages that export data and they are running fine. – Steve Thorne Oct 08 '13 at 23:42
  • That SQL is challenging! What do you get if you substitute "`SELECT Count(*) AS row_count`" for everything before the `FROM` and then run that query version with the same parameter values which trigger `AccessError(3183)` with the original query version? – HansUp Oct 09 '13 at 01:14
  • Interesting took your advice Hans and the query ran fine. Tried running the full query again and it timed out. Not sure where that leaves me now. The temp file never went above 0 KB so I would figure that means the query was executed on the Oracle side. – Steve Thorne Oct 09 '13 at 13:00
  • Yes, the goal is to do the processing on the server and feed only the final result set to Access. And it sounds like that happened with `SELECT Count(*)`. However, I have no idea why the original query times out now. – HansUp Oct 09 '13 at 14:35
  • So learned something today in testing. That last parameter of NOTE_TEXT = "R" is invalid and that is a varchar2(4000 BYTE) field in Oracle (Access will treat this as a Memo field). This was causing Access to do a full table scan of a large amount of data. When I corrected the query to be NOTE_CODE = "R" (CHAR(1 BYTE) the query runs fine now. One of the users had apparently tampered with the query a few weeks back. – Steve Thorne Oct 09 '13 at 18:15
  • Excellent! Please submit that explanation as an answer and accept it when the system allows. Thanks. – HansUp Oct 09 '13 at 18:18

1 Answers1

2

This is the error description for 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.

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.

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.

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.

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

HansUp
  • 95,961
  • 11
  • 77
  • 135
  • That is my concern that the volume of data has pretty much reached the breaking point of what Access can handle. The system pulls in nightly feeds from a lot of sources so grows every day. To show the SQL code of the queries wont amount to much mainly because there are many queries starting to fail and honestly the SQL is ugly (being honest there). The problem is this was deployed over time to many users so there is no one Access DB to fix. The views in Oracle could be changed, but they may also be used by parts of the web app. One thought I had was to archive part of the data. – Steve Thorne Oct 09 '13 at 00:11
  • BTW I can upload the SQL as it formatted for Oracle only because it is more legible. The Access SQL is not pretty, but anyway the query is too long to post in a comment. How can I upload it short of answering my own question? – Steve Thorne Oct 09 '13 at 00:14
  • First check the note I added about a pass-through query. If that can give you what you need, you may be in good shape for a long time. – HansUp Oct 09 '13 at 00:16
  • I doubt seeing the Oracle version of the problem Access SQL will help. This is strictly an Access capacity problem so we would need to see the Access SQL which triggers that problem. Without any other information to work with, I would suggest date-restricting the Access queries --- maybe only the last year or few months worth of data. – HansUp Oct 09 '13 at 00:19
  • (strictly as a band-aid (a very big band-aid)), could the O.P. upgrade to a newer version of Access on a new 64 bit machine and get a larger temporary work area? Good luck to all. – shellter Oct 09 '13 at 00:23
  • *"The views in Oracle could be changed, but they may also be used by parts of the web app."* Hold the phone! I didn't suggest you alter existing views. That needn't stop you creating new views which you then link to from Access. – HansUp Oct 09 '13 at 00:24
  • @shellter That size restriction applies to all versions of Access since 2000 (in Access 97, the limit was 1 GB) and is the same for 32 and 64 bit Access. – HansUp Oct 09 '13 at 00:25
  • Yeah I thought about a pass-through query since that will be executed on the Oracle side, but changing all the queries would be a nightmare. Too bad can't change the tables to be pass-through. The query is kind of run by date restriction, it is referred to as a reporting period. Instead of actual dates a they are numeric.. being 201310 would be this month. I tried parms using 201309 to 201310 and still failed. How can I upload the whole Access SQL? – Steve Thorne Oct 09 '13 at 00:27
  • @Shelter yeah 2GB is the limit in at least Access 2010 64bit. Not sure about Access 2013 yet as haven't played with it much. – Steve Thorne Oct 09 '13 at 00:29
  • You can edit your question and paste in the SQL statement text. Use the format button, which looks like `{}`, to format it as code. However, based on your description, it sounds like we would be extra-challenged by ugly Frankenstein SQL. Can you create a simpler query which reproduces `AccessError(3183)`? – HansUp Oct 09 '13 at 00:30
  • I started to paste in the raw SQL and it was nasty. Give me a few moments and will try to at least make it look a little better.. Really appreciate the help – Steve Thorne Oct 09 '13 at 00:37
  • Best hold off on the thanks, Steve. If the SQL is too nasty, I may have to bail out. BTW, I forgot to mention this earlier ... welcome to Stack Overflow. :-) – HansUp Oct 09 '13 at 00:41
  • Thanks Hans.. I had another account but only posted one question. been here many times looking for solutions though and has always helped out. I just appreciate the help and insight from others.. I posted the Access SQL.. I may have to drop soon as well. on the East Coast and spent enough time on this tonight... BTW the nasty part my not only be part of the Access SQL but in the views that are being called.. I have been developing for close to 20 years now and have never seen an app this messed up.. C# .NET, VB6, Access VBA, Oracle procs, views and other pieces make the whole app. – Steve Thorne Oct 09 '13 at 00:59