Oracle Database ora-00054: resource busy and acquire with NOWAIT s

Source: Internet
Author: User


Address oracle Database ora-00054: resource busy and acquire with NOWAIT specified error I found a midway card and then forcibly terminated pl/SQL developer after using the pl/SQL developer client to call the Stored Procedure for operations, oracle has always encountered lock-related errors. According to the analysis of www.2cto.com, when the stored procedure is called, DDL statements are performed on the table, resulting in a pessimistic blocking. Oracle has not released the lock since the client is stuck and will not release it, leading to an error. This session needs to be forcibly killed: 1. query the currently locked object SELECT * from v $ LOCKED_OBJECT WHERE ORACLE_USERNAME = 'athena'; SQL> SELECT * FROM V $ LOCKED_OBJECT WHERE ORACLE_USERNAME = 'athena '; xidusn xidslot xidsqn OBJECT_ID SESSION_ID ORACLE_USERNAME OS _USER_NAME PROCESS LOCKED_MODE ---------- ------------ too many ---------- --------------- 19 11 79 134 76618 436 ATHENA Administrator 3512: 1272 3 11 28 74487 76617 574 ATHENA Administrator 2412: 3928 3 20 25 75512 76620 594 ATHENA Administrator 4064: 4060 3 2. query session information SQL> SELECT v. sid, v. SERIAL #, v. USERNAME, v. action from v $ session v WHERE v. sid IN (SELECT session_id FROM v $ locked_object WHERE ORACLE_USERNAME = 'hena') and v. MACHINE = 'nbcb \ KJB-080701N '; sid serial # username action ------------ -------- Automated testing 436 62365 ATHENA test window-procedure PROC_HIS_AR 574 60824 ATHENA test window-procedure PROC_HIS_AR 594 15217 ATHENA test window-procedure PROC_HIS_AR www.2cto.com 3. kill session Syntax: alter system kill session 'sid, SERIAL # '; for example: alter system kill session '000000'; Return: ora-00031: SESSION marked for KILL. this session is not killed, but is marked as killed. Query SELECT spid, osuser, s. program from v $ session s, v $ process p WHERE s. PADDR = p. addr and s. SID = 436; then log on to the operating system and run the following command to execute the kill process syntax at the operating system level: orakill sid thread or # kill-9 spid for example: orakill database instance name $ {spid}; the database cannot be restarted.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.