Oracle Database Discussions

Hi Everyone,

Today, we met with a rather unusual situation where a few users complainted about query hanging. But the cause
of this hang is not the size of table (the size were rather small; 32000 rows). We proceeded to check the locks
and found the oracle process SMON was blocking as BLOCK column for V$LOCK showed 1 and with LOCK MODE 4. Further,
LOGFILE SWITCH command was executed and the command did not return the prompt. There was no space issue
either with TEMP tablespace or LOGARCHIVE destination. May be because of SMON locking, the transactions are
getting locked. I also fired some random queries on some other table, but for me, the query returned results
in usual time. Now, to prevent this situation from cropping up again, I wish to find the root-cause. What
may be the possibilities that caused this SMON to block. Oracle was bounced and operation commenced normally.
Alert log file showed "MMNL absent for 1203 seconds, foregrounds taking over".

Details: Oracle 10g on Linux Itanium on HP rx3600

Regards,
...