From: Mike Krolewski Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc,comp.databases.oracle.tools Subject: Re: Please Help: table locking Date: Tue, 12 Dec 2000 02:54:59 GMT Organization: Deja.com - Before you buy. Lines: 53 Message-ID: <914421$fmm$1@nnrp1.deja.com> References: <913s5g$9dm$1@nnrp1.deja.com> <913tjl$aks$1@nnrp1.deja.com> <9142a6$ebk$1@nnrp1.deja.com> <91431g$es9$1@nnrp1.deja.com> NNTP-Posting-Host: 63.75.139.178 X-Article-Creation-Date: Tue Dec 12 02:54:59 2000 GMT X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt) X-Http-Proxy: 1.1 x67.deja.com:80 (Squid/1.1.22) for client 63.75.139.178 X-MyDeja-Info: XMYDJUIDmikkrolewski In article <91431g$es9$1@nnrp1.deja.com>, Mike Krolewski 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@rosetta.org Ususual disclaimers Sent via Deja.com http://www.deja.com/ Before you buy.