[ORACLE] 테이블스페이스 모니터

----------------------- sh 파일 ----------------------- (작업중)

#!/bin/sh

TEMP_FILE=~/ts.dat

get_msg() {
sqlplus "/as sysdba" <<EOFotpo
set tab off
set space 1 pagesize 0
set heading off feedback off echo off verify off
set term off
set colsep ""
set linesize 80

spool ${TEMP_FILE}
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
@ts.sql
spool off

exit
EOFotpo
}

cd ~
while true ; do
        clear
        get_msg >> /dev/null
        PRINT_MSG=`cat ${TEMP_FILE}`
        echo "${PRINT_MSG}"
        sleep 2
done




----------------------- sql 파일 -----------------------
SELECT  A.TABLESPACE_NAME AS TS_NM
        , ROUND(SPACE_SIZE/1024,0) AS TOT_MB
        , ROUND(BLOCKS_KB/1024,0) AS USED_MB
        , ROUND((SPACE_SIZE-BLOCKS_KB)/1024,0) AS FREE_MB
FROM    (
        SELECT  TABLESPACE_NAME
                , SUM(BLOCKS*8) AS BLOCKS_KB
        FROM    DBA_SEGMENTS
        WHERE   1=1
        GROUP BY TABLESPACE_NAME
        ) A
        ,(
        SELECT  TABLESPACE_NAME
                , SUM(BYTES/1024) AS SPACE_SIZE
        FROM    DBA_DATA_FILES
        GROUP BY TABLESPACE_NAME
        ) B
WHERE   A.TABLESPACE_NAME = B.TABLESPACE_NAME
UNION ALL
SELECT  'TEMP' AS TS_NM
        , ROUND(B.BYTES/1024/1024,0) AS TOT_MB
        , ROUND(A.BYTES_USED/1024/1024,0) AS USED_MB
        , ROUND((B.BYTES-A.BYTES_USED)/1024/1024,0) AS FREE_MB
FROM    V$TEMP_SPACE_HEADER A, DBA_TEMP_FILES B
;