Re: Can I create temp table in Oracle.

jmodic_at_src.si (Jurij Modic) wrote:

[Automatically dropping some tables when session ends...]

>Although I agree with Billy Verreynne that the reasons for using such
>"temporary" tables is highly questionable, there is a way to perform a
>kind of "clean up" of those temp tables whenever a user disconnects
>from a database. For this to work, you must enable auditing and
>execute a trigger on AUD$ whenever a disconnect event occures.
>
>As AUD$ is again owned by SYS, you must duplicate this table under
>some other schema (SYSTEM for example), drop the original SYS.AUD$ and
>create a synonym SYS.AUD$ pointing to SYSTEM.AUD$. Now you can create
>a trigger on table AUD$. Remember hovewer that the owner of the
>trigger must be SYS, not SYSTEM, othervise no one will be able to
>connect to the database (ORA-3113....)!

Hi Jurij,

although this sounds interesting enough to try it as far as the trigger for end of session is concerned, dropping of a table this way surely won't work, because any DDL statement will do an implicit commit, and thus end the current transaction. However ending the transaction from a trigger is not possible.

cu,
Peter

--
Peter Schneider
peter.schneider_at_okay.net Received on Sun Oct 25 1998 - 15:14:05 CST