Re: TEMPORARY TABLE

Mladen:

> I must confess that I didn't react last week because I already knew that
> stuff.
> Basically, doing truncate on a GTT can cause a gorgeous shared pool latch
> deadlock that will effectively hang the whole instance.
 

This is not quite true. GTT truncate has no relation with Shared pool latch and it is not dead lock sensitive. Truncating a temporary table will require a X lock and X pin for the object (to update the object definition in the dictionary cache and library cache) and may be to update the TAB$ entry. Shared pool latch has no role in truncates.

However, this will (acquiring X lock on lib cache object and changing the data_object# after the truncates in the meta data) will invalidate all the dependent cursors in the shared pool. But not to the extent of causing a shared pool latch deadlock. Libarary cache locks are serialized by design and becasue of this there may a long queue if more than one sessions start truncating the same table..

-- 

Best Regards,
K Gopalakrishnan 
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
--
http://www.freelists.org/webpage/oracle-l