I have come across a problem with Global Temporary Tables: If a process crashes, pmon may not clean up properly - you will see this because a TT lock will be left in V$LOCK long after the session disappears. The side-effects of this are that space in the temporary tablespace is locked out, and sessions which subsequently use the same session ID can go into a spin when they try to use the temporary tablespaces. In fact, this problem extends to an 'disorderly' termination of a process - including using alter system kill session 'sid,serial#';
A further problem with Global Temporary Tables, passed to me by David Kurtz of Go-Faster is an anomaly if you use the option to truncate table reuse storage on a GTT that has been defined with on commit preserve rows. Obviously this is not an option you need to consider when designing from scratch, but if you have changed some scratch tables in an existing system, such code might be lurking waiting to spring at you when you least expect it.
The problem is that the statement appears to work. However the table is not truncated, and the data is NOT eliminated. This is recorded as Oracle Bug 1396741.
A question: If you have defined two global temporary tables, and make use of both of them at the same time you can see two data segments assigned to you in v$sort_usage. How do you tell which segment is associated with which global temporary table ?
None so far.
Back to Book Index