Oracle Ora-00054:resource busy and acquire with nowait specified resolution _oracle

Errors such as Ora-00054:resource busy and acquire with nowait specified often occur when a database user inserts, updates, deletes a table's data in a database, or adds a table's primary key or a table's index.

The main reason is that a transaction is being executed (or the transaction has been locked), all resulting in an unsuccessful execution.

1, users with DBA authority to see what locks the database has

Select T2.username,t2.sid,t2.serial#,t2.logon_time from
v$locked_object t1,v$session T2
where t1.session_id =t2.sid ORDER by T2.logon_time;

such as: TestUser 339 13545 2009-3-5 17:40:05
The user who knows the lock is testuser,sid for 339,serial# 13545

2, according to the SID view specific SQL statements, if the SQL is not important, you can kill

Select Sql_text from v$session a,v$sqltext_with_newlines b
 where DECODE (a.sql_hash_value, 0, Prev_hash_value, sql_ Hash_value) =b.hash_value and A.sid=&sid order by
 piece;

Detected SQL, such as: begin:id: = sys.dbms_transaction.local_transaction_id; End

3. Kill the transaction

Alter system kill session ' 339,13545 ';

4, so you can execute other transaction SQL statements

For example, add a primary key to a table:

ALTER TABLE test
Add constraint Pk_test primary key (TEST_NO);

If prompted: Ora-00030:user session ID does not exist
Alter session SET Events ' immediate trace name Flush_cache Level 1 ';

Consequences Unknown

Ora-00031:session marked for Kill

Some Oracle processes are killed, the state is set to "killed", but the locked resources are not released for a long time, sometimes there is no way, had to restart the database. Now provides a way to solve this problem, which cannot be killed in Oracle, and then killed at OS level.

1. The following statements are used to query which objects are locked:

Select object_name,machine,s.sid,s.serial# 
from V$locked_object l,dba_objects o, v$session s
where l.object_ id = o.object_id and l.session_id=s.sid;

2. The following statement is used to kill a process:

Alter system kill session ' 24,111 '; (24,111 of which are the above query sid,serial#)

The above two steps can be performed through Oracle's management console.

3. If you use the above command to kill a process, the process state is set to "killed", but the locked resource is not released for a long time, then you can kill the corresponding process (thread) at the OS level, first execute the following statement to obtain the process (thread) Number:

Select spid, Osuser, s.program from 
v$session s,v$process p
where s.paddr=p.addr and s.sid=24 (24 is the above SID)

4. Kill this process (thread) on the OS:

1 on UNIX, execute the command with root:
#kill-9 12345 (that is, the SPID in step 3rd)
2 to kill a thread with Orakill in Windows (Unix also applies), Orakill is an executable command provided by Oracle, syntax:
Orakill SID Thread
which
Sid: Represents the instance name of the process to kill
Thread: Is the number of threads to kill, that is, the 3rd step of the SPID query.
Example: C:>orakill ORCL 12345

Ora-00031:session marked for Kill

Cause:the session specified in a ALTER SYSTEM KILL session command cannot are killed immediately (because it is rolling b Ack or blocked on a network operation) but the it has been for kill. This means it'll be killed as soon as possible on its current uninterruptible operation.
The Action:no action is required for the session to being killed, but further executions of the ALTER SYSTEM KILL session Comman D on this session may cause of the session to be killed sooner.

Kill-9 12345