개발자가 되고싶은 개발일기

1. 테이블 조회하기

 - 테이블 목록 조회

     SELECT *
     FROM ALL_ALL_TABLES

     SELECT *
     FROM DBA_TABLES

     SELECT *
     FROM ALL_OBJECTS
     WHERE OBJECT_TYPE = 'TABLE'

- 테이블 목록 조회 (접속한 계정)

     SELECT *
     FROM TABS

     SELECT *
     FROM USER_OBJECTS
     WHERE OBJECT_TYPE = 'TABLE'

     SELECT *
     FROM USER_TABLES

- 테이블 코멘트 조회

     SELECT *
     FROM ALL_TAB_COMMENTS
     WHERE TABLE_NAME = '테이블명'

     SELECT *
     FROM USER_TAB_COMMENTS

2. 컬럼 조회하기

  - 컬럼 조회

     SELECT *
     FROM COLS
     WHERE TABLE_NAME = '테이블명'

     SELECT *
     FROM ALL_TAB_COLUMNS
     WHERE TABLE_NAME = '테이블명'

     SELECT *
     FROM USER_TAB_COLUMNS

  - 컬럼 코멘트 조회

     SELECT *
     FROM USER_COL_COMMENTS

3. PROCEDURE, TRIGGER, FUNCTION, JAVA SOURCE 등 조회

 - TYPE 별 조회

    SELECT *

    FROM USER_SOURCE

    WHERE TYPE = 값 >>>>> (PROCEDURE, TRIGGER, FUNCTION, JAVA SOURCE)

 - TEXT VALUE 값으로 조회

    SELECT *

    FROM USER_SOURCE

    WHERE TEXT LIKE '%값%' >>>>> (찾고싶은 TEXT)

회사에 도착 후 서버 점검을 하는데 오라클 ORA-04031 에러로 인해 개발서버가 실행되지 않고 있었습니다.

이 에러 내용은 nn byte에 공유 메모리를 할당할 수 없다는 내용으로 shared memory를 필요로 하지만 적재를 하지 못해 나는 에러입니다.

발생원인

ORA-04031 에러는 Shared Pool의 관리가 원활히 되지 않아 수행되는 SQL이 적재할 Memory가 없어 error가 발생해서 생기는 게 원인입니다.

구체적인 에러 원인으로 크게 2가지가 있습니다.

• Shared Pool Fragmentation (Shared Pool 과도한 조각화)
• Too many pinned packages (고정된 패키지가 많음)

해결방안

위와 같은 방식으로 오라클을 내렸다 올리면 바로 해결이 가능합니다.

하지만 구체적인 에러 원인에 대해 해결을 해야 에러가 반복되지 않을 것이라 생각하기에 아래와 같은 작업을 하였습니다.

1. Shared Pool Fragmentation (Shared Pool 과도한 조각화)

- 과도한 조각화를 방지하기 위해서는 Literal SQL > Bind Variable SQL로 바꿔주는 작업이 가장 좋은 방법입니다.

 하드코딩으로 박힌 SQL은 각각 마다 Shared Pool을 잡아 먹습니다. 하지만 Bind Variable SQL은 한 번 Shared Pool에 올라가서 Bind만 되는 것이기 때문에 하나의 Shared Pool만 잡아먹기 때문에 Bind Variable SQL로 대부분 바꿔주는 것이 좋습니다.

2. Too many pinned packages (고정된 패키지가 많음)

dbms_shared_pool.keep을 통해 크기가 큰 sql은 패키지를 해두어 빠르게 사용할 수 있도록 해둡니다.

하지만 너무 많은 keep이 일어나게 되면 새 작업을 위한 shared pool 부족 현상으로 ORA-04031의 에러가 발생할 수 있습니다. keep 해둔 것에 대한 unkeep을 통해 조절해야 될 것입니다.

또한 이 외에 예방 방법으로는

shared_pool_size , shared_pool_reserved_size에 RAM을 추가하거나

11g 이상에서는 memory_max_size를 늘려주는 방법이 있습니다.

memory_max_size를 늘리는 방법은 아래 URL에서 확인 가능합니다.

http://haisins.epac.to/wordpress/?p=354(출처:DB의 정석)

오라클을 서버를 구축하고 서버 관리를 하면서

Service는 왜 올리는 것이며, Instance는 왜 올리는 것인지에 대해서 정확한 개념을 알 수가 없었습니다.

일반적으로 로컬에서 사용하는 테스트 환경에서는 한 Instance에 한 Service가 올라가는것이라 잘 이해가 되지 않았습니다. 하지만 클라우드 환경이나 서비스 업체에서는 다양한 Sid를 사용하여 DB를 관리하는 경우가 있습니다. 이럴 때 정확한 개념 이해가 필요하여 이 부분에 있어서 포스팅을 하게 되었습니다.

Instance는 Oracle에서는 sid라고 명명합니다.

예를들어 sid1, sid2라고 명명된 2가지 sid가 있다고 한다면 하나의 Service에서 2개의 Instance를 운영하고 있다고 생각하시면 됩니다.

비교할 수 있는 예시로는, Web서버에서 우리는 여러 대의 Was를 운영하는 것처럼 이해하시면 됩니다.

Web서버가 내려가면 Was가 기동이 안될 것 입니다. 하지만 #1 Was가 Stop 된다 하더라도 #2 Was에서 영향이 가지는 않을 것입니다.

Service = Web Server

Instance = Was

라고 생각하시면 이해하기 편하실 겁니다.

문제 원인

ORA-04021: 객체의 잠금 대기중 시간이 초과됐습니다

오라클을 사용하시다 보면 해당 에러를 종종 보실 수도 있습니다.

해당 프로시저가 돌아가는 동안에 구조를 변경할수가 없습니다. 이와같은 경우를 방지하기 위해 library lock 과 pin에 의해 lock이 걸립니다.

그러므로 해당하는 프로시저의 실행되고 있는 object 의 lock을 풀어 해결할수가 있습니다.

해결 방법

1. 오류난 해당 PRCEDURE의 SID를 추출합니다.

   SELECT SID
   FROM v$access
   WHERE OBJECT = 'PROCEDURE 명';

2. 해당 SID의 실행중인 SESSION 점검합니다.

    SELECT SID, SERIAL#, USERNAME, STATUS
    FROM  V$SESSION
    WHERE SID = '1번에서 추출 된 SID';

3. 해당 PROCEDURE의 실행중인 SESSION 죽입니다.

    ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

* SESSION 을 죽여도 해결되지 않을 경우

1. SESSION에 걸린 PROCESS 추출

   SELECT A.SPID

   FROM  V$PROCESS A

             , V$SESSION B

   WHERE A.ADDR = B.PADDR

   AND     B.SID    = '1번에서 추출된 SID';

2. 서버 접속 후 실행중인 프로시저 찾기

   ps -ef|grep 1번에서 추출된 SPID

3. 프로시저 죽이기

   KILL -9 1번에서 추출된 SPID

위와 같이 실행해보시면

"ORA-04021: 객체의 잠금 대기중 시간이 초과됐습니다"

이와 같은 에러들은 거의 다 처리 가능할 것으로 생각됩니다.

감사합니다~!

데이터들을 실수로 DELETE, UPDATE 등을 하여 잘못 COMMIT하여 난감하실때가 많으실 겁니다.

저도 이러한 사항을 많이 겪어봤고 많이 혼났을 때가 있었네요.

이럴 때 오라클에서 데이터 복구를 위해 지원해주는 것이 있습니다.

TIMESTAMP를 통한 복구 쿼리입니다.

예시로는 아래와 같습니다.

예시1) 시간 전 조회

이렇게 조회하면 10분 이전의 데이터를 조회할 수 있습니다.

뒤에 단위는 SECOND, MINUTE, HOUR, DAY로 바꿔 쓸 수 있습니다.

예시2) 특정 날짜 조회

이렇게 조회하면 2021년 03월 16일에 테이블 데이터를 조회할 수 있습니다.

또한 삭제된 시간도 적절하게 조절해서 검색가능 합니다.

예시3) INSERT SELECT를 통한 데이터 복구

SELECT INSERT 쿼리로 간단히 테이블 삭제 후 데이터 복구 하시면 됩니다.

데이터변경 하고 COMMIT해서 망했다 생각하실 때 이 쿼리로 당황하지 말고 바로 데이터 복구하시길 바랍니다.

저는 오라클을 쓰면서 많은 프로시저(Procedure)를 사용하고 있습니다.

최근에는 프로시저가 많이 사용하지 않는 추세이긴 합니다.

왜 많이 쓰이는 않는지에 대한 포스팅은 이후에 진행하도록 하겠습니다.

우선 Procedure에 대해서 말씀드리겠습니다.

프로시저(Procedure)

특정 작업을 수행하는 PL/SQL BLOCK입니다.

매개 변수 및 반복적으로 사용할 수 있는 BLOCK이라고 생각하시면 됩니다.

생성

위에 예시는 PROCEDURE를 생성하는 간단한 예제입니다.

LINE별로 설명을 드리면

1 LINE  CREATE OR REPLACE PROCEDURE 프로시저명 (

 - 프로시저명으로 CREATE 한다라고 생각하면 됩니다.

2~5 LINE 매개변수 IN/OUT/INOUT 데이터 타입

 - 매개변수를 통해 IN/OUT/INOUT을 통해 입출입을 정해주고, 데이터 타입(VARCHAR2, NUMBER 등)을 정해준다.

7 LINE 변수 데이터 타입;

 - 프로시저에 맞게 변수랑 사용할 것들을 적어준다.

8 LINE BEGIN

 - 프로시저 시작

9~12 LINE 실행되어야 될 내용

13 LINE EXCEPTION

 - EXCEPTION이 실행되면 THROW 되는 곳

14 LINE WHEN OTHERS THEN

 - OTHERS 예외는, 모든 예외를 포괄하는 예외명이며, 예외 처리의 마지막에 기술해야 한다 아니면 compile error가 된다.

15 LINE ROLLBACK;

 - 프로시저 실행된 부분 전체 ROLLBACK

17 LINE RETURN

 - 프로시저 전체 RETURN

실행

실행은 2가지로 구분이 된다.

     1. 파라미터가 없거나, IN 파라미터만 있을 때 실행

         CALL       프로시저명 (파라미터들);

         EXEC      프로시저명 (파라미터들);

         EXECUTE  프로시저명 (파라미터들);

         예시)

         CALL TESTTESTTEST(1,2,3);

         EXEC TESTTESTTEST(1,2,3);

         EXECUTE TESTTESTTEST(1,2,3);

     2. 프로시저 매개변수가 IN/OUT 두 개 다 존재할 때 실행

       위에 예제로 만들어진 IN/OUT 두 개 다 존재할 때의 프로시저를 실행한다고 할 때

       아래오 같이 실행하면 된다.

        DECLARE

                     A NUMBER;

       BEGIN

               TESTTESTTEST(1,2,3, A);
        END;