Oracle DB Tablespace Monitoring

회사에서 CC인증을 하며 DB의 헬스체크 및 임계치에 대한 모니터링 툴을 개발해야 했다.

여기서 나의 역할은 임계치 모니터링에 필요한 쿼리를 조사하는 것이었다.

먼저 임계치 모니터링의 프로세스를 다음과 같이 정리해봤다.

1. bat 파일을 하나 생성

: 여기서는 MFC로 만들어진 exe를 실행하고 실행 결과를 리턴받아 로그를 남기도록 되어있다.

2. exe 의 내부 실행 순서

먼저 현재의 여유 공간을 체크하고 여유 공간이 10% 이면 -1을 리턴하며 exe를 종료.

 

- 현재 여유공간 체크

SELECT    A.TABLESPACE_NAME "테이블스페이스명",

          A.FILE_NAME "파일경로",

           (A.BYTES - B.FREE)    "사용공간",

            B.FREE                 "여유 공간",

            A.BYTES                "총크기",

            TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"

      FROM

       (

         SELECT FILE_ID,

                TABLESPACE_NAME,

                FILE_NAME,

                SUBSTR(FILE_NAME,1,200) FILE_NM,

                SUM(BYTES) BYTES

           FROM DBA_DATA_FILES

         GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)

       ) A,

       (

         SELECT TABLESPACE_NAME,

                FILE_ID,

                SUM(NVL(BYTES,0)) FREE

           FROM DBA_FREE_SPACE

        GROUP BY TABLESPACE_NAME,FILE_ID

       ) B

      WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME

         AND A.FILE_ID = B.FILE_ID

         AND A.TABLESPACE_NAME = 'xxxxxxxxxxx';

다음으로는 여유 공간이 5% 이하이면 특정 테이블의 row를 100개 삭제하고 다시 현재의 여유 공간을 체크한다. 

이 것을 for문으로 반복하여 여유 공간이 15%가 될 때까지 진행하고 여유 공간이 15% 이상이 되면 for문을 마치고 -2를 리턴하며 exe를 종료.

- log_table이라는 테이블의 row를 오래된 순서부터 100개 삭제한고 테이블 스페이스 공간을 줄이는 쿼리

delete from log_table where rownum <= 100;

alter table log_table shrink space;

alter table log_table shrink space cascade;

commit;

아참! 위의 쿼리들을 실행하기 전에 해당 계정이 DBA 권한이 있는지 확인을 먼저 해야한다. DBA 권한이 없을 경우 위의 쿼리를 실행하면 테이블 또는 뷰가 없다고 에러가 난다. (GRANT DBA TO 계정명;)

그리고 아래의 쿼리도 반드시 선행되어야 한다.

ALTER TABLE adt_adm ENABLE ROW MOVEMENT;