kill session 后一直在回滚中的处理 os kill

Do not kill the sessions at the OS level.

Use ALTER SYSTEM KILL SESSION 'sid, serial#';   

This acts in the following manner:

1. It terminates a session, rolls back ongoing transactions, releases all session locks, frees all session resources.

2. If the session is performing some activity that must be completed (eg waiting for a reply from a remote database or rolling back a transaction), Oracle waits for this activity to complete, kills the session then returns control.

3).If the wait lasts for 60 seconds then Oracle marks the session to be killed, and returns control with a message that the session is marked to be killed. It then gets killed when the activity is complete.

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

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.