Re: Lock problem

Tom Grenier wrote: > Last weekend I needed to drop one index and add another on a table. > We > are a 24X7 shop, so I just wait until the wee hours of the morning and > > try to grab the table. It kept giving me a 'non-exclusive access' > error. So... > > Instead of continuing to try to run the drop and create commands, I > added a 'lock table xxx in exclusive mode;' line. I got the lock -- > but > then it still errored on the drop and create statements with the same > non-exclusive error. I'm confused -- if I got an exclusive lock on > the > table -- why was it telling me I couldn't get exclusive access when I > tried to do the index commands? > You can acquire an EXCLUSIVE MODE lock as long as noone else has an exclusivelock. However there may be shared locks on the table. In order to build an index you must be the ONLY locker. Any prepared statements hold a shared lock on the table's tablespace. Art S. Kagel