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!
OS Error 11, EAGAIN, means you are out of process space at the OS level, not the Oracle level.
You need to either increase the amount of memory available to Oracle or the number of processes the Oracle account can run simultaneously. Probably the number of processes.
It must be a pretty large sort.
By the way, Oracle will try to use memory to sort in SORT_AREA_SIZE chunks, using the TEMP tablespace when the sort set is too large to fit in one chunk, and possibly data files if too large for temp space (I am vague on this, but I seem to remember the 'files' thing from the documentation.)
Joseph R.P. Maloney, CSP,CDP,CCP
'The answer is 42'