Bookmark and Share
Locking Issues
(Publish Date: 2008-8-29 12:48am, Total Visits: 798, Today: 1, This Week: 1, This Month: 4)

If a major table or tables are locked unbeknownst to you, the database could slow down dramatically in very short order.

If a major table or tables are locked unbeknownst to you, the database could slow down dramatically in very short order. Try running a command such as select * from persons, for example, where persons is your largest table and is part of just about every SQL statement. If you aren't sure which tables (if any) might be locked, you can run the following statement to identify the table or index that's being locked, leading to a slow database:

SQL> select l.object_id,l.session_id,
  2  l.oracle_username,l.locked_mode,
  3  o.object_name
  4  from v$locked_object l,
  5  dba_objects o
  6* where o.object_id=l.object_id;
OBJECT_ID  SESSION_ID ORACLE_USERNAME  LOCKED_MODE  OBJECT_NAME
 6699        22         NICHOLAS           6        EMPLOYEES
SQL>

As the preceding query and its output show, user Nicholas has locked up the Employees table. If this is preventing other users from accessing the table, you have to quickly remove the lock by killing the locking user's session. You can get the session_id from the preceding output and the V$SESSION view will give you the serial# that goes with it. Using the alter system kill … command, you can kill the offending session. The same analysis applies to a locked index, which will prevent users from using the base table. For example, an attempt to create an index or rebuild it when users are accessing the table can end up inadvertently locking up the table.

If there is a table or index corruption, that could cause a problem with accessing that object(s). You can quickly check for corruption by running the following statement:

SQL> analyze table employees validate structure cascade;
Table analyzed.
SQL>