Our customer has an event and a stored procedure sp1 which has only 2 temporary tables declared (declare local temorary table...). After running for some hours it throws an error: Too many temporary tables in connection (SQLCODE -817).
The procedure sp1 calls another procedure sp2 many times (inside a cursor in cursor) and sp2 has 1 temp. table declared itself, but I doubt it can be the reason. From the docs:

Declared local temporary tables within compound statements exist within the compound statement.

There are no triggers in the table which is updated in the procedure.
What reasons may cause this error? What should we check/consider? Thanks.

Version: 11.0.1.2467.

asked 02 Apr '14, 09:36

Arthoor's gravatar image

Arthoor
1.1k264056
accept rate: 0%

edited 02 Apr '14, 10:02


There is a upper limit of 1,048,576 temporary tables that can be created per transaction. Dropping a temporary table will not allow you to create more than this number. Temporary tables in a transaction are assigned auto-incrementing IDs and currently the server cannot go back and 're-use' IDs of dropped temporary tables.

Is there a place where you could execute a COMMIT or reuse the same temporary table? For example, after each iteration of the cursor you could clear the temporary table and reuse it for the next iteration.

Edit: This is enhanced in version 17. The server can now go back and re-use IDs of dropped temporary tables.

permanent link

answered 02 Apr '14, 11:13

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 30%

edited 22 Jul '15, 10:18

Time to switch from DEFAULT AUTOINCREMENT to a SEQUENCE ... CYCLE in the SA server itself?

(02 Apr '14, 11:30) Volker Barth

Thank You for the explanation. The upper limit is too small in this case. :)
Well, we'll try to find a workaround. The last pick (if commits won't be applicable) will be to move temp. table declaration from sp2 to sp1 and clear it everytime in sp2.

(03 Apr '14, 01:06) Arthoor
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×137
×85
×19

question asked: 02 Apr '14, 09:36

question was seen: 970 times

last updated: 22 Jul '15, 10:18