ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified

ORA-00054: resource busy and acquire with NOWAIT specified
If you do not use NOWAIT or other items, the default value is to WAIT (WAIT) for other transactions to commit before returning results.

WAIT integer, similar to the NOWAIT function, but can WAIT for the specified number of seconds. For example, after "WAIT 3" waits for 3 seconds, the following results are returned if other transactions are not committed:
ORA-30006: resource busy; acquire with WAIT timeout expired.

When this problem occurs, check V $ LOCKED_OBJECT, or wait until the transaction ends, or kill the session holding the lock (if it is not a key session ):

1. Find the locked database table and related sid, serial # And spid through the previous sentence
Select object_name as object name, s. sid, s. serial #, p. spid as system process number
From v $ locked_object l, dba_objects o, v $ session s, v $ process p
Where l. object_id = o. object_id and l. session_id = s. sid and s. paddr = p. addr;

2. Destroy related sessions in the database
Alter system kill session 'sid, serial # ';-sid and serial # are the data found in step 1.

You can also use the following statement:

1. Users with dba permissions can view database locks

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;

Example: testuser 339 13545 17:40:05
Testuser, whose sid is 339 and serial # Is 13545

2. view the specific SQL statement based on the sid. If the SQL statement is not important, 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;

The SQL statement, for example, begin: id: = sys. dbms_transaction.local_transaction_id; end;

3. kill the transaction
Alter system kill session '00000045 ';
These operations can also be implemented using the TOAD graphical interface!

When a database user inserts, updates, or deletes data from a table in a database, or adds a table's primary key, or the table's index, a ora-00054 often occurs: resource busy and acquire with nowait specified errors.

It is mainly because a transaction is being executed (or the transaction has been locked), which leads to execution failure.

1. Users with dba permissions can view database locks

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;

Example: testuser 339 13545 17:40:05
Testuser, whose sid is 339 and serial # Is 13545

2. view the specific SQL statement based on the sid. If the SQL statement is not important, 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;

The SQL statement, for example, begin: id: = sys. dbms_transaction.local_transaction_id; end;

3. kill the transaction
Alter system kill session '00000045 ';

4. You can execute other transaction SQL statements.

For example, add the table's primary key:
Alter table test
Add constraint PK_test primary key (test_NO );

If the prompt is: ORA-00030: User session ID does not exist

Alter session set events 'immediate trace name flush_cache level 1 ';

Unknown consequences

ORA-00031: session marked for kill

After 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 to restart the database. Now we provide a way to solve this problem, that is, it cannot be killed in ORACLE, and it can be killed again at the OS level.

1. The following statement is 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 '123456'; (24,111 is the sid found above, serial #)

[Note] the preceding two steps can be performed on the Oracle console.

3. if the process state is set to "killed" after a process is killed by using the preceding command, but the locked resources are not released for a long time, then, the corresponding process (thread) can be killed 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 sid above)

4. Killing the process (thread) on the OS ):
1) run the command as root on unix:
# Kill-9 12345 (that is, the spid obtained in step 1)
2) In windows (unix also applies), use orakill to kill the thread. orakill is an executable Command provided by oracle. The syntax is as follows:
Orakill sid thread
Where:
Sid: indicates the Instance name of the process to be killed.
Thread: the ID of the thread to be killed, that is, the spid obtained in step 1.
Example: c:> orakill orcl 12345

ORA-00031: session marked for kill

Cause: The session specified in an alter system kill session command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. this means it will be killed as soon as possible after its current uninterruptible operation is done.

Action: No action is required for the session to be killed, but further executions of the alter system kill session command on this session may cause the session to be killed sooner.

Kill-9 12345