When Dropping or Truncating Table, When Creating or Rebuilding Index

ORA-0054: When Dropping or Truncating Table, When Creating or Rebuilding Index
Doc ID: Note:117316.1 Type: TROUBLESHOOTING
Last Revision Date: 20-OCT-2005 Status: PUBLISHED


Problem Description
-------------------

When trying to drop or truncate a table, or create or alter an index
of a table, you receive the following error message:

ORA-00054: resource busy and acquire with NOWAIT specified
Cause: Resource interested is busy
Action: Retry if necessary


Solution Description
--------------------

You need to analyze who has a lock on the table being dropped/truncated,
or on the table whose index is being created/alter-ed, and if it is valid.
Do the following:

1. Go to svrmgrl and connect internal.

2. select object_id from dba_objects where object_name='';

3. select * from v$locked_object where object_id=;

Note the "oracle_username" and "session_id".

4. Or you can query v$access

select sid from v$access where owner='' and
object='';

Note the session id number or "sid".

5. select sid, serial#, command, taddr from v$session
where sid=;

6. Now you have identified the user and what they are doing.

Investigation into the validity of this session needs to be
made. Many times it may be a job that ran before or a
hanging query. If it is determined that this session
needs to be terminated, go to step 7, or else wait until the user
has completed the action. To find out what they are doing,
look at the command number in the COMMAND column and match it
with the Command Number Values Table in either the Oracle 7 or
8 Server Reference manual listed under the entry for "V$SESSION".

7. To terminate the session:

alter system kill session '';

8. The session should now be killed and the lock SHOULD release.
Rechecking "v$locked_object" will tell you this. If the lock does
not immediately release, there may be a rollback occuring. To
check this, goto step nine, else dropping the table should
work now.

9. To check for rollback:

select used_ublk from v$transaction where ADDR= TADDR in v$session>;

If there is a value there, this is the number of undo blocks used by
the transaction. Wait one minute and again select "used_ublk" from
"v$transaction" where ADDR=; .
Note the value. If it is decreasing, a rollback is occuring and based
on the difference between these values, you can "guesstimate" the time
required to complete the rollback. For example, if the first query
returns a value of 80000 and the second one returns 70000, it
took 1 minute to rollback 10000 blocks. Based on this number,
you can guestimate the time to complete the rollback. In this
case, it would be 7 minutes.

10. In some cases, you might need to kill the session at the OS
level as well. Some 3rd party database monitoring software and
certain unexplained situations will cause the lock to remain
despite not showing up in "v$locked_object" after killing the
session.


Explanation
-----------

Dropping or truncating a table requires you to acquire an exclusive lock
on the table. A table is a "busy" resource if there are other sessions
modifying or holding a lock on the same table.

Doing a CREATE INDEX, DROP INDEX and ALTER INDEX REBUILD will lock the table
with a Share Table Lock (S). No DML operations are permitted on the base table.
Note, this is true for pre-8.1. For 8.1, if the ONLINE key word is
included as part of the CREATE INDEX or ALTER INDEX....REBUILD commands,
the table is locked with a Row Share Table Lock (RS). Other users will be
able to continue to access the table as normal except for any DDL operations.
DML operations are permitted.