시스템 유지보수(SM)에는
디스크의 증가량을 주별 또는 월별로 모니터링하여 미리 할당할 수도 있고
보고용으로도 사용할 수 있도록 DB에 관리용 테이블을 만들어
자동으로 저장할 수 있도록 해 놓으면 시간이 많이 지난 다음에도
추이를 알 수 있어 유용합니다.
테이블스페이스 사용율(exec_tbs_usage.sh)을
매일 야간에 Crontab에서 실행할 수 있도록 등록하여 사용하는 샘플입니다.
% vi exec_tbs_usage.sh
#---------------------------------------------------------
# ORACLE 환경변수
#---------------------------------------------------------
export ORACLE_SID=TESTDB
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=$ORACLE_BASE/product/10.2
:
#-------------------------------------------------
# 실행할 쉘이 존재하는 절대경로
#-------------------------------------------------
cd /oracle/app/product/10.2/DBA
echo "DB Tablespace Usage" > ./log/exec_tbs_usage.log
date >> ./log/exec_tbs_usage.log
TABLESPACE_USAGE.sh >> ./log/exec_tbs_usage.log
% vi TABLESPACE_USAGE.sh
#!/usr/bin/ksh
###############################################################################
#
# 일별 테이블스페이스(TABLESPACE) 사용량 저장
#
# *---------------------------------------------------------------------------
# * 일별 테이블 건수
# *---------------------------------------------------------------------------
# * Program : TABLESPACE_USAGE.sh
# * Object Name : 없음
# * Object Type : PL/SQL
# * OutPut Table : DBMANAGER.TABLESPACE_USAGE
# * Create Date : 2010-06-10
# *-------------------------------------------------------------------------*/
# *
# * Execution Sample : exec_tablespace_usage.sh (cron job)
# * TABLESPACE_USAGE.sh
# * SQL Exec. : sqlplus / as sysdba
# *
# *-------------------------------------------------------------------------*/
#
###############################################################################
#----------------------------------------------
#-- DB수집정보를 저장할 오라클 계정 생성 및 Grant
#----------------------------------------------
#-- DROP USER DBMANAGER CASCADE;
#-- CREATE USER DBMANAGER IDENTIFIED BY DBMANAGER
#-- DEFAULT TABLESPACE TOOLS TEMPORARY TABLESPACE TEMP;
#--
#-- GRANT SELECT_CATALOG_ROLE, SELECT ANY TABLE, SELECT ANY DICTIONARY TO DBMANAGER;
#-- GRANT UNLIMITED TABLESPACE TO DBMANAGER;
#-- GRANT BECOME USER TO DBMANAGER;
#-- GRANT CREATE VIEW TO DBMANAGER;
#-- GRANT CREATE TABLE TO DBMANAGER;
#-- GRANT ALTER SESSION TO DBMANAGER;
#-- GRANT CREATE SESSION TO DBMANAGER;
#-- GRANT CREATE SYNONYM TO DBMANAGER;
#-- GRANT SELECT ANY TABLE TO DBMANAGER;
#-- GRANT SELECT ANY SEQUENCE TO DBMANAGER;
#-- GRANT SELECT ANY DICTIONARY TO DBMANAGER;
#--
#--
#----------------------------------------------
#-- DB수집정보를 저장할TABLE 생성 및 Grant
#----------------------------------------------
#--
#-- DROP TABLE DBMANAGER.TABLESPACE_USAGE PURGE;
#-- CREATE TABLE DBMANAGER.TABLESPACE_USAGE
#-- ( GATHER_DATE CHAR(8) DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD') NOT NULL,
#-- TABLESPACE_NAME VARCHAR2(30) NOT NULL,
#-- ALLOC_KBYTES NUMBER DEFAULT 0 NOT NULL,
#-- USED_KBYTES NUMBER DEFAULT 0 NOT NULL,
#-- EXEC_DATETIME DATE DEFAULT SYSDATE NOT NULL
#-- ) TABLESPACE TOOLS;
#--
#-- CREATE UNIQUE INDEX
#-- DBMANAGER.UK_TABLESPACE_USAGE_01 ON DBMANAGER.TABLESPACE_USAGE(GATHER_DATE, TABLESPACE_NAME)
#-- TABLESPACE TOOLS;
#-- COMMENT ON TABLE DBMANAGER.TABLESPACE_USAGE IS '테이블스페이스 크기 및 사용량';
#--
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.GATHER_DATE IS '수집 일시';
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.TABLESPACE_NAME IS '테이블스페이스 명';
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.ALLOC_KBYTES IS '할당된 크기(KB)';
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.USED_KBYTES IS '사용된 크기(KB)';
#-- COMMENT ON COLUMN DBMANAGER.TABLESPACE_USAGE.EXEC_DATETIME IS '수행 일시';
#---------------------------------------------------------
# ORACLE 환경변수
#---------------------------------------------------------
export ORACLE_SID=TESTDB
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=$ORACLE_BASE/product/10.2
:
#-------------------------------------------------
# 실행할 쉘이 존재하는 절대경로
#-------------------------------------------------
cd /sw/oracle/app/product/10.2/DBA
if [ $# != 0 ]
then
echo "\n-----------------------------------------"
echo "\n인수(Argument) 갯수가 틀립니다."
echo "\n실행 예) : TABLESPACE_USAGE.sh"
echo "\n-----------------------------------------"
exit
fi
#---------------------------------------------------------
# 입력 값 없음
#---------------------------------------------------------
##################################
##### [ 오라클 PL/SQL Start] #####
##################################
#--------------------
#-- SQL*PLUS 접속
#--------------------
sqlplus -s /nolog<<EOF
SET SERVEROUTPUT ON
SET PAGESIZE 0 SQLPROMPT "" ECHO OFF HEADING OFF FEEDBACK OFF TIMING OFF VERIFY OFF TERMOUT ON TRIMSPOOL ON
---------------------
-- SYS 계정으로 접속
---------------------
connect / as sysdba
DECLARE
-------------------------------------------------------
-- 커서 변수 선언
-------------------------------------------------------
cursor_insert INTEGER;
cursor_delete INTEGER;
-------------------------------------------------------
-- 데이터 값을 저장할 변수 선언
-------------------------------------------------------
v_dummy NUMBER;
v_gather_date VARCHAR2(08) := TO_CHAR(SYSDATE,'YYYYMMDD');
-------------------------------------------------------
-- SQL 문장을 저장할 변수 선언
-------------------------------------------------------
insertStmt VARCHAR2(2000);
deleteStmt VARCHAR2(2000);
BEGIN
-------------------------------------------------------
-- CURSOR 열기
-------------------------------------------------------
cursor_insert := DBMS_SQL.OPEN_CURSOR;
cursor_delete := DBMS_SQL.OPEN_CURSOR;
-----------------------------------------------------------------
-- 중복 데이터에 의한 오류를 방지하기 위해
-- DBMANAGER.TABLESPACE_USAGE에 있는 해당일자(TODAY)의 정보 삭제
-----------------------------------------------------------------
deleteStmt := 'DELETE /*+ parallel(A 8) */ FROM DBMANAGER.TABLESPACE_USAGE A WHERE A.GATHER_DATE = :gather_date';
DBMS_SQL.PARSE( cursor_delete, deleteStmt, DBMS_SQL.NATIVE );
DBMS_SQL.BIND_VARIABLE( cursor_delete, 'gather_date', v_gather_date );
v_dummy := DBMS_SQL.EXECUTE( cursor_delete );
-------------------------------------------------------
-- DBMANAGER.TABLESPACE_USAGE에 입력(INSERT)
-------------------------------------------------------
insertStmt := ' INSERT INTO DBMANAGER.TABLESPACE_USAGE '||
' ( GATHER_DATE, TABLESPACE_NAME, ALLOC_KBYTES, USED_KBYTES ) '||
' SELECT :gather_date, C.TABLESPACE_NAME, '||
' NVL(A.TOTAL,0) ALLOC_KBYTES, '||
' NVL(A.TOTAL,0) - NVL(B.FREE,0) USED_KBYTES '||
' FROM (SELECT TABLESPACE_NAME, '||
' NVL(ROUND((SUM(BYTES)/1024),2),0) TOTAL '||
' FROM SYS.DBA_DATA_FILES '||
' GROUP BY TABLESPACE_NAME) A, '||
' (SELECT TABLESPACE_NAME, '||
' NVL(ROUND((SUM(BYTES)/1024),2),0) FREE '||
' FROM SYS.DBA_FREE_SPACE '||
' GROUP BY TABLESPACE_NAME) B, '||
' DBA_TABLESPACES C '||
' WHERE C.TABLESPACE_NAME = A.TABLESPACE_NAME(+) '||
' AND C.TABLESPACE_NAME = B.TABLESPACE_NAME(+) '||
' AND C.TABLESPACE_NAME NOT LIKE '||'''TEMP%''' ;
DBMS_SQL.PARSE( cursor_insert, insertStmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE( cursor_insert, 'gather_date', v_gather_date );
v_dummy := DBMS_SQL.EXECUTE( cursor_insert );
-------------------------------------------------------
-- CURSOR 닫기
-------------------------------------------------------
DBMS_SQL.CLOSE_CURSOR( cursor_insert );
DBMS_SQL.CLOSE_CURSOR( cursor_delete );
COMMIT;
EXCEPTION
-- WHEN DUP_VAL_ON_INDEX THEN
-- DBMS_OUTPUT.PUT_LINE('DUP_VAL_ON_INDEX 에러-중복되는 데이터가 존재');
-- WHEN TOO_MANY_ROWS THEN
-- DBMS_OUTPUT.PUT_LINE('TOO_MANY_ROWS 에러-리턴해야 하는 SELECT문이 하나 이상의 행을 반환');
-- WHEN NO_DATA_FOUND THEN
-- DBMS_OUTPUT.PUT_LINE('NO_DATA_FOUND 에러-SELECT문이 아무런 데이터 행을 반환하지 못함');
-- WHEN INVALID_CURSOR THEN
-- DBMS_OUTPUT.PUT_LINE('INVALID_CURSOR 에러-잘못된 커서 연산');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR CODE : ' || TO_CHAR(SQLCODE));
DBMS_OUTPUT.PUT_LINE('ERR MESSAGE : ' || SQLERRM);
END;
/
exit
EOF
환경 : Unix, Aix5L, Oracle10gR2
CERTVIEW.pdf