Re: Drop user causes session to freeze with no error message returned?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 6 May 2005 06:42:14 -0700
Message-ID: <1115386934.016434.176290@o13g2000cwo.googlegroups.com>

Turn on trace from another session for the session performing the drop and after a few minutes turn it off and look in the trace file to see what Oracle is doing.

I have seen drops take hours due usually when the user being dropped was an object owner and thousands of object level grants had to be cleaned up.

But find out what the session is doing. If the session is performing base table cleanup then instead of cancelling the session try waiting it out.

In case you have never had to trace an already running session:

How do I switch on sql trace in another session that is already running?
http://www.jlcomp.demon.co.uk/faq/alien_trace.html

PS Check for statistics on the SYS owned based tables. If you find then either remove them or update them. I believe that some pieces of Oracle Applications actually creates and requires statistics on the base tables. Depending on your setup this may or may not be desirable and might be part of the problem.

HTH -- Mark D Powell -- Received on Fri May 06 2005 - 08:42:14 CDT