Session hangs waiting for sort segment
When a statement needs the temporary tablespace it hangs. In v$session_wait I get Sort Segment Request. Then I get other session in v$session_wait waiting for SS enqueue, Is there any place to see what the P1RAW field means. I thougt SS was a wait for a (Row-S) lock in exclusive mode but this time I guess it is sort segment wait.
If I truss the process waiting for a sort segment I get the following output:
/1: semtimedop(3342339, 0xFFBE6B34, 1, 0xFFBE6B2C) Err#11 EAGAIN
/1: semtimedop(3342339, 0xFFBE6B34, 1, 0xFFBE6B2C) (sleeping...)
I have tried to create a new Temporary tablespace and the same problem occur. Why isn't the temp tablespace used?
When I select from v$sort_segment I get no rows!