Re: Please Help: table locking

In article <91431g$es9$1_at_nnrp1.deja.com>,   Mike Krolewski <mkrolewski_at_rosetta.org> wrote:

> > There are 800k rows. Tablespace is 20GB. Extents of the 'object'
 table
> > is 2000 extents(i know this is not optimized and I'll fix this
 later).
> > There is a combined PK on object(id,plan). There are no other users
 on
> > line. To update one row should take a second, so I am sure it
 hangs.
> >
> > Thanks!
> >
> > Susan
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> wow. A puzzler. Best guess is that you have corrupted indexes on the
> table. You must be adding data to the system quite regularly, so I am
> assuming that this table has a lot of transactions against it. Are you
> doing any deletes? or other updates? How often? How many? Are there
 any
> other indexes on the table?
>
> I am thinking you could drop all indexes for this table and rebuild
> them. With 800K rows, this could take some time.
>
> Another idea is run explain plan on the query. It should pick up the
> index. If it does not use the index, try analysing the table.
>
> --

Here is a strange idea. You start each of these updates and then the application hung, so you quit the application on PC/desktop. Then you tried this again, etc. Check the users active in you system. You may have a whole bunch of users all logged in still. Often when a user terminates his program in a less than orderly fashion, Oracle maintains the connection for some time. If you find these users, try killing these sessions.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rosetta.org
              Ususual disclaimers


Sent via Deja.com http://www.deja.com/
Before you buy.