‘ADMIN’ 카테고리의 보관물

좋은 자료 찾았다.
ORA00054 발생시 참고하면 좋은 문서.

 

출처 :  http://arjudba.blogspot.kr/2009/01/ora-00054-resource-busy-and-acquire.html

 

Problem Description
In my production database Oracle 10.2g while I was adding column to one of my transaction table it fails with ORA-54 error as below.

SQL> alter table student add b number;
alter table student add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Description of the Problem
Let’s try to produce the problem in our development environment. I have opened two session that connected to database under a schema. In one session, I have created a table and inserted data into it.
SQL> create table a (a number);

Table created.

SQL> insert into a values(1);

1 row created.

I did not yet committed data in session 1. Now in another session whenever I try to any ddl like (alter table, drop table) ORA-00054 will produce.

In another session,
SQL> alter table a add b number;
alter table a add b number
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> drop table a;
drop table a
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> lock table a in exclusive mode nowait;
lock table a in exclusive mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Cause of the Problem
Whenever you try to do any structural changes on a table oracle try to lock the table exclusively with NOWAIT option(this is in 10.2g while in 11g you can change the wait timeout). If oracle fails to lock the table exclusively then ORA-00054 will occur.

Solution of the Problem
In 10.2g you are limited to several choices to solve the problem. To avoid it,

-Re run the DDL at a later time when the database become idle.
or,

-Kill the sessions that are preventing the exclusive lock.
or,

-Prevent end user to connect to the database and then run the DDL.

You have different views to see locking information about the table.
1)DBA_BLOCKERS: Displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting. In our scenario this view will not help.

2)DBA_DDL_LOCKS: It lists all DDL locks held in the database and all outstanding requests for a DDL lock.

3)DBA_DML_LOCKS: It lists all DML locks held in the database and all outstanding requests for a DML lock.
If you query from it in the mode_held field you will see ‘row exclusive lock’.
SQL> select mode_held from dba_dml_locks where owner=’MAXIMSG’;

MODE_HELD
————-
Row-X (SX)

4)DBA_LOCK: It lists all locks or latches held in the database, and all outstanding requests for a lock or latch.

5)DBA_LOCK_INTERNAL: It displays a row for each lock or latch that is being held, and one row for each outstanding request for a lock or latch.

6)DBA_LOCKS is a synonym for DBA_LOCK.

7)DBA_WAITERS: Shows all the sessions that are waiting for a lock.

8)V$LOCK: It lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.

9)V$LOCK_ACTIVITY: This view is deprecated.

10)V$LOCKED_OBJECT: This view lists all locks acquired by every transaction on the system.

In order to see locked object query,

SQL> set linesize 130
SQL> set pages 100
SQL> col username       format a20
SQL> col sess_id        format a10
SQL> col object format a25
SQL> col mode_held      format a10
SQL> select     oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id
,  owner || '.' || object_name object
,  object_type
,  decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
,  decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id
/

USERNAME             SESS_ID    OBJECT                    OBJECT_TYPE         STATUS       MODE_HELD
-------------------- ---------- ------------------------- ------------------- ------------ ----------
MAXIMSG (A)          142,232    MAXIMSG.A                 TABLE               Not Blocking Row-X (SX)
OMS (DBA2\ershad)    143,280    OMS.T                     TABLE               Not Blocking Row-X (SX)
OMS (DBA2\ershad)    143,280    OMS.T1                    TABLE               Not Blocking Row-X (SX)

Here we see there is 3 types of locking. In our case the object is A. And we see the sid=142 and serial#=232 is preventing us from locking the table in exclusive mode. So to achieve our task we can kill the session as below.

오라클 데이터베이스를 운영하다보면 종종 cursor: pin S wait on X 이벤트가 계속 발생하는 경우가 있다. 그저 그때 대처할 수 있는 거라곤 해당 대기 이벤트들을 KILL 해주는게 전부이지만 근본적으로 해당 mutex 를 가지고 놓아주지 않는 세션을 잡아서 KILL 해 주는게 제일 좋다.

(더 보기…)

TOP n SQL Ordered by CPUTIME

게시 날짜: 2012/04/05, 카테고리: ADMIN, ORACLE

특정 기간동안 CPU TIME 을 많이 가져가는 TOP 20 SQL 을 알려달라는 요청을 받았다. 지금 일하는 이 사업장에서 기존 정보를 저장해 두고 각 STAT 을 뽑아볼 수 있는 툴이 제공되기는 하지만 무언가 잘못되었는지 해당 기간동안의 TOP SQL 을 확인할 수 없었다. AWR 레포트를 통해서 확인을 해 보아도 TOP 10 은 나오지만 TOP 20 은 보여주질 못해 한번 직접 해당 정보를 뽑아볼 수 있는 쿼리를 작성해 보았다.

이 쿼리는 AWR 이 지원되는 10g 이상의 버전에서 사용할 수 있다.

(더 보기…)

Table Recovery using Clone DB

게시 날짜: 2011/11/15, 카테고리: ADMIN, ORACLE
태그:, ,

Table 을 purge 를 이용하여 드롭하였을 경우 플래시백 테이블 명령을 통해 복구할 수 없다. 우연찮게도 필요 없을 줄 알고 깨끗하게 지우기 위해 drop table tablename purge 를 사용하였는데, 갑자기 현업에서 그 테이블의 데이터가 필요하다고 요청이 오면 적지 않이 당황할 수 있다.

(더 보기…)

Extended SQL Trace

게시 날짜: 2011/11/03, 카테고리: ADMIN, ORACLE

SQL을 튜닝하거나 현재 돌아가는 SQL이 어떤 상태인지 확인해 보고 싶을때 SQL을 추적한다. 이 추적하는 방법은 전에도 포스팅한 autotrace 를 사용하면 별다른 복잡한 과정 없이 쉽게 SQL을 트레이스 할 수 있다. 하지만 SQL을 트레이스 하는 방법은 그 외에도 여러가지가 있는데 이번에 그 방법들에 대해서 포스팅 해 볼 생각이다.

(더 보기…)

Transaction Monitoring SQL

게시 날짜: 2011/10/11, 카테고리: ADMIN, ORACLE
태그:, ,

트렌젝션 모니터링 스크립트를 작성해 보았다. 예전 오라클용 오렌지 툴을 사용할때 편하게 사용했었던 트랜젝션 모니터링 기능을 조금 따라해 보기 위해 작성해 보았다. 꽤 긴 시간동안 돌아가는 트랜젝션을 발생시켰을 때 이 트랜젝션 모니터링 기능은 어떻게 트랜젝션이 잘 돌아가는지 확인하는데 꽤 유용했었던 것으로 기억한다.

(더 보기…)

Range Partition Table Split (MAXVALUE)

게시 날짜: 2011/09/22, 카테고리: ADMIN, ORACLE
태그:, , ,

1. 테스트 파티션 테이블 생성

  1  CREATE TABLE SPLIT_TEST (EMPNO NUMBER(10), NAME VARCHAR2(30))
  2  PARTITION BY RANGE(EMPNO)
  3  (PARTITION SPLIT_TEST_P1 VALUES LESS THAN (10),
  4   PARTITION SPLIT_TEST_P2 VALUES LESS THAN (20),
  5   PARTITION SPLIT_TEST_P3 VALUES LESS THAN (30),
  6*  PARTITION SPLIT_TEST_MX VALUES LESS THAN (MAXVALUE))
SQL> /

Table created.

(더 보기…)