ORA-00054 : 자원이 바쁘고 NOWAIT를 지정해 취득 또는 타임 아웃이 만료되었습니다

ORA-00054 : 자원이 바쁘고 NOWAIT를 지정해 취득 또는 타임 아웃이 만료되었습니다

---

테이블을 업데이트 할 때이 데이터베이스 오류가 발생하는 이유는 무엇입니까?

1 행의 오류 : ORA-00054 : 자원이 사용 중이며 NOWAIT를 지정하여 획득하거나 시간 종료가 만료되었습니다.

---

테이블이 이미 일부 쿼리에 의해 잠겨 있습니다. 예를 들어, "업데이트를 위해 선택"을 실행했지만 아직 다른 선택 쿼리를 커밋 / 롤백하지 않고 실행했을 수 있습니다. 쿼리를 실행하기 전에 커밋 / 롤백을 수행하십시오.

---

여기에서 ORA-00054 : 자원이 바쁘고 NOWAIT를 지정하여 획득

또한 SQL, 사용자 이름, 컴퓨터, 포트 정보를 조회하고 연결을 유지하는 실제 프로세스를 얻을 수 있습니다

SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,S.USERNAME,
S.MACHINE,S.PORT , S.LOGON_TIME,SQ.SQL_FULLTEXT 
FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, 
V$PROCESS P, V$SQL SQ 
WHERE L.OBJECT_ID = O.OBJECT_ID 
AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR 
AND S.SQL_ADDRESS = SQ.ADDRESS;

---

Oracle 세션을 종료하십시오

아래 쿼리를 사용하여 활성 세션 정보 확인

SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;

처럼 죽이다

alter system kill session 'SID,SERIAL#';

(예 : alter system kill session '13,36543';)

참조 http://abeytom.blogspot.com/2012/08/finding-and-fixing-ora-00054-resource.html

---

이 문제에 대한 매우 쉬운 해결 방법이 있습니다.

세션에서 10046 추적을 실행하면 (Google이 설명하기에는 너무 많습니다). DDL 작업 전에 Oracle이 다음을 수행함을 알 수 있습니다.

LOCK TABLE 'TABLE_NAME'NO WAIT

따라서 다른 세션에 열린 트랜잭션이 있으면 오류가 발생합니다. 그래서 수정은 ... 드럼 롤주세요. DDL 전에 자신의 잠금 장치를 발행하고 'NO WAIT'을 ​​생략하십시오.

특별 참고 사항 :

파티션 분할 / 삭제를 수행하는 경우 oracle은 파티션을 잠급니다. 따라서 파티션 하위 파티션을 잠글 수 있습니다.

그래서 ... 다음 단계는 문제를 해결합니다.

1. 잠금 테이블 '표 이름'; -당신은 '기다릴 것입니다 (개발자들은 이것을 교수형이라고 부릅니다). 열린 트랜잭션 세션이 커밋 될 때까지 이것은 대기열입니다. 그래서 몇 번의 세션이있을 수 있습니다. 그러나 오류가 발생하지 않습니다.
2. DDL을 실행하십시오. 그런 다음 DDL은 NO WAIT로 잠금을 실행합니다. 그러나 세션이 잠금을 요청했습니다. 그래서 당신은 좋습니다.
3. DDL 자동 커밋 잠금이 해제됩니다.

테이블이 잠겨있는 동안 DML 문은 '대기'하거나 개발자가이를 '멈춤'이라고합니다.

작업에서 실행되어 파티션을 삭제하는 코드에서 이것을 사용합니다. 잘 작동합니다. 초당 수백 번의 삽입 속도로 지속적으로 삽입되는 데이터베이스에 있습니다. 오류가 없습니다.

궁금하다면 11g에서이 작업을 수행합니다. 나는 과거에도 10g 전에 이것을했습니다.

---

This error happens when the resource is busy. Check if you have any referential constraints in the query. Or even the tables that you have mentioned in the query may be busy. They might be engaged with some other job which will be definitely listed in the following query results:

SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE'

Find the SID,

SELECT * FROM V$OPEN_CURSOR WHERE SID = --the id

---

This happens when a session other than the one used to alter a table is holding a lock likely because of a DML (update/delete/insert). If you are developing a new system, it is likely that you or someone in your team issues the update statement and you could kill the session without much consequence. Or you could commit from that session once you know who has the session open.

If you have access to a SQL admin system use it to find the offending session. And perhaps kill it.

You could use v$session and v$lock and others but I suggest you google how to find that session and then how to kill it.

In a production system, it really depends. For oracle 10g and older, you could execute

LOCK TABLE mytable in exclusive mode;
alter table mytable modify mycolumn varchar2(5);

In a separate session but have the following ready in case it takes too long.

alter system kill session '....

It depends on what system do you have, older systems are more likely to not commit every single time. That is a problem since there may be long standing locks. So your lock would prevent any new locks and wait for a lock that who knows when will be released. That is why you have the other statement ready. Or you could look for PLSQL scripts out there that do similar things automatically.

In version 11g there is a new environment variable that sets a wait time. I think it likely does something similar to what I described. Mind you that locking issues don't go away.

ALTER SYSTEM SET ddl_lock_timeout=20;
alter table mytable modify mycolumn varchar2(5);

Finally it may be best to wait until there are few users in the system to do this kind of maintenance.

---

In my case, I was quite sure it was one of my own sessions which was blocking. Therefore, it was safe to do the following:

• I found the offending session with:

  SELECT * FROM V$SESSION WHERE OSUSER='my_local_username';

  The session was inactive, but it still held the lock somehow. Note, that you may need to use some other WHERE condition in your case (e.g. try USERNAME or MACHINE fields).

• Killed the session using the ID and SERIAL# acquired above:

  alter system kill session '<id>, <serial#>';

Edited by @thermz: If none of the previous open-session queries work try this one. This query can help you to avoid syntax errors while killing sessions:

• SELECT 'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||''' immediate;' FROM V$SESSION WHERE OSUSER='my_local_username_on_OS'

---

Your problem looks like you are mixing DML & DDL operations. See this URL which explains this issue:

http://www.orafaq.com/forum/t/54714/2/

---

Just check for process holding the session and Kill it. Its back to normal.

Below SQL will find your process

SELECT s.inst_id,
   s.sid,
   s.serial#,
   p.spid,
   s.username,
   s.program FROM   gv$session s
   JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id;

Then kill it

ALTER SYSTEM KILL SESSION 'sid,serial#'

OR

some example I found online seems to need the instance id as well alter system kill session '130,620,@1';

---

I managed to hit this error when simply creating a table! There was obviously no contention problem on a table that didn't yet exist. The CREATE TABLE statement contained a CONSTRAINT fk_name FOREIGN KEY clause referencing a well-populated table. I had to:

• Remove the FOREIGN KEY clause from the CREATE TABLE statement
• Create an INDEX on the FK column
• Create the FK

---

I had this error happen when I had 2 scripts I was running. I had:

• A SQL*Plus session connected directly using a schema user account (account #1)
• Another SQL*Plus session connected using a different schema user account (account #2), but connecting across a database link as the first account

I ran a table drop, then table creation as account #1. I ran a table update on account #2's session. Did not commit changes. Re-ran table drop/creation script as account #1. Got error on the drop table x command.

I solved it by running COMMIT; in the SQL*Plus session of account #2.

---

select
   c.owner,
   c.object_name,
   c.object_type,
   b.sid,
   b.serial#,
   b.status,
   b.osuser,
   b.machine
from
   v$locked_object a,
   v$session b,
   dba_objects c
where
   b.sid = a.session_id
and
   a.object_id = c.object_id;
   
   ALTER SYSTEM KILL SESSION 'sid,serial#';

---

I also face the similar Issue. Nothing programmer has to do to resolve this error. I informed to my oracle DBA team. They kill the session and worked like a charm.

---

Solution given by Shashi's link is the best... no needs to contact dba or someone else

make a backup

create table xxxx_backup as select * from xxxx;

delete all rows

delete from xxxx;
commit;

insert your backup.

insert into xxxx (select * from xxxx_backup);
commit;

참고URL : https://stackoverflow.com/questions/4842765/ora-00054-resource-busy-and-acquire-with-nowait-specified-or-timeout-expired