ORA-0054: When Dropping or Truncating Table, When Creating or Rebuilding Index

刘泽宇

目标：
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

处理方法：
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='<tablename>';

3. select * from v$locked_object where object_id=<id number);
Note the "oracle_username" and "session_id".

4. Or you can query v$access

select sid from v$access where owner='<table owner>' and object='<table name>';
Note the session id number or "sid".

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

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 '<sid, serial#>';

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=<value from 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=<value from TADDR in v$session>;.
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. Furthermore, drop user cascade can hit this error
as it is implicitly dropping objects that might be referenced by other users.

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.