Re: Oracle hangs, no errors

Sean Leach <sleachNOSPAM_at_netlojix.com> wrote in news:pan.2001.11.26.17.14.23.905798.6295_at_netlojix.com:

> That is why I would think, but it sometimes will take several minutes
> to do a select count(*) on a table with 0 records. Usually it won't
> give an error, I will kill it after 2-3 minutes

It this is against a table which at one time had many, many records in it all of which have now been deleted, Oracle is forced to scan the block beginning at block 1 up until it reaches the High Water Mark for this table. If there really are zero rows in the table, then do
SQL> TRUNCATE TABLE <now_zero_rows>;

>
> I would patch it up, except it shouldn't be the problem as the system
> worked great for a year before this. We can't think of anything
> changed since this started happening either.
>
> There aren't more connections than before (if there are, it would be
> like 5-10 more), and there is very little swapping (the load on the
> machine is very low as well)
>
> A shutdown immediate does not work when the problem is happening, it
> hangs, only a shutdown abort will do it (we wait for 2-3 minutes for an
> immediate, to make sure all transactions have been rolled back etc.)

If an uncommitted transaction is 30 minutes into its run, it can take around 30 minutes to rollback whatever changes have been made so far. You are way too impatient. Received on Mon Nov 26 2001 - 19:51:44 CST