Table Locking Problem

    I had a situation recently where a user issued the statment select * from <table_name>
for update;

    Without the where clause, this statement proceeded to select all data, return it to standard output and lock the records of one of our largest tables.

    This process was taking too long to wait then issue the rollback statement so I killed the user session assuming that PMON would kick in and tidy up the mess (including removing the table locks). This did not appear to happen so in Server Manager I issued the command:

    ordebug wakeup <pid of PMON>

    This also appeared to do nothing. Time was moving on (or it seems that way when irate staff are hounding you) so I tried to shut down the database using shutdown immediate. All users had logged off by this stage but the database would not shut down. After about 20 minutes (with no disk/cpu activity, AT ALL) I figured the database was not going to shut down so I forced it down (for lack of a better euphemism!).

    I restarted the instance, instance recovery took place and the database opened. The table, however, was still locked. After a few minutes of restarting the instance the lock was released and everything was back to normal.

    Was I just being impatient? Would the locked table have been released? If so why was it taking so long (or did it just seem to take long!!). Is there any other way to release locks, or to halt a user session that is issuing locks midstride and reverse it's action safely?

Thanks and regards,
Graeme Farmer,
AIX System Administrator/Oracle DBA.

The opinions expressed here are my own. Or so I'm told. Received on Tue Apr 18 2000 - 00:00:00 CDT