Re: Help: Implementing temporary tables in Oracle.

Quick and dirty suggestion to alleviate your extent problem: Create a cron job that runs (at some oportune time, with proper permission) a script that TRUNCATES the table in question. This will reset the high water mark for the table and free all but the initial extent.

This is a really fascinating problem!

In article <319BB9D4.6333_at_mail.state.wi.us>, Matthew Marek <mmarek_at_mail.state.wi.us> wrote:

> 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

David Scott                        ...how can I keep from singing?
                bumper snicker: My other guitar is a Martin (D16H)