Re: Stuck on insert into a global temporary table

The TT lock is just the temporary table lock. The fact that the SID has two TX locks is probably significant - it suggests that there is a recursive (e.g. space management, or autonomous) transaction going on underneath the actual transaction you are trying to perform.

Check the locks again and see if there is another SID live with its own TX lock that has matching ID1 and ID1 with the TX locks of the stuck SID. Then find out what this other process is doing.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



roman_stangel_at_my-deja.com wrote in message <93h9l2$b7l$1_at_nnrp1.deja.com>...


>Jonathan,

>

>Thank you for your suggestions.

>

>I am not sure if my system supports 32GB files – a good catch – I

>simply used the definition of my default TEMP tablespace and made some

>changes to it.  I tried what you suggested, just with a different file

>size of 64M:

>

>CREATE TABLESPACE "IPWTEMPTS4"

>NOLOGGING

>DATAFILE '/home/oracle/u05/oradata/ORCL/IPWTEMPTS4.dbf'

>SIZE 65536K REUSE

>DEFAULT STORAGE (

> INITIAL 256K NEXT 256K MINEXTENTS 1 MAXEXTENTS 256 PCTINCREASE

>0

>) TEMPORARY

>;

>

>Unfortunately, the problem still remains.

>

>I have examined the v$lock table, and using the SID and CTIME columns

>could find the rows that correspond to my session, but I am not sure

>how I could use this information to find the reason of why my session

>is stuck. I can see two TX (transaction enqueue), one TM (DML enqueue)

>and one TT (temporary table enqueue) lock.  Could the TT lock provide

>any clue?  Do you have any suggestions?

>

>Thank you,

>

>Roman

>

>

>Sent via Deja.com

>http://www.deja.com/