Help: Implementing temporary tables in Oracle.

My department recently inherited a C/S application which uses Oracle/UNIX for the data layer. All database operations are performed by a commercial middleware library that places the actual calls to Oracle. This library provides "begin_work()", "commit_work()", and "abort_work()" to manage database transactions.

The developers implemented a "temporary" table functionality for the clients in the following way. They created a permanent table in Oracle and programmed the client data access operations to use that table. The table is "temporary" in the sense that the client routines never commit changes to the table. Several clients can store data in the table simultaneously, but each client never sees the records being stored by the others. When a client operation ends, all insertions to the "temporary" table are rolled back.

The problem we are facing is that even though a client operation only ever inserts 10Kbytes worth of data in one of these tables before rolling back, the table in question has required extension 120 times now and has reached its MAXEXTENTS. This table is now over 520Kbytes in size but has never had any transactions committed to it. When queried outside of the client operations, this table will always show count(*) = 0. At any given time, no more than 2 or 3 clients at most could ever have been accessing this table.

Does what I am describing ring a bell to anyone? What are considered proper methods for providing "temporary" tables to each of several simultaneous database clients?

Thank you for any help.

Matt Marek
WI Dept. of Corrections
mmarek_at_mail.state.wi.us Received on Thu May 16 1996 - 00:00:00 CEST