| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Can I create temp table in Oracle.
On Sun, 25 Oct 1998 07:51:45 GMT, Douglas Dunyan
<dm^noise_for_spammers^dunyan_at_hotmail.com> wrote:
>But you may be able to use a trigger, on v$session....
>When a session ends, it can attenpt to drop the specific table form your
>specific tablespace. In theory, it sounds plausible. I have never
>tried it.
A few problems with this idea....
First, v$session is a view and you can't stick a trigger on a view. This view is based on a single fixed table, namely SYS.X$KSUSE, so the next idea would be to attach a trigger on this table. But here comes the next problem: you can't create a trigger on any table owned by SYS! 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....)!
You should use a consistent naming convention when creating such temporary tables so that trigger will be able to drop all of those temporary tables (using dynamic SQL) before ending a session.
>Douglas Dunyan
>--dm^noise_for_spammers^dunyan_at_hotmail.com
>OCP ORACLE V7
>MASTER OF TECHNOLOGY V8
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |