전체 shell script

#!/bin/bash
export ORACLE_BASE=/box/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0
export PS1='[$ORACLE_SID] $PWD> '
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/OPatch:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/network/lib:/lib:/usr/lib:/usr/local/lib
export NLS_LANG=AMERICAN_AMERICA.UTF8

PATH=$PATH:/usr/local/bin:/usr/bin:/bin:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:.
DATE=`date +%Y%m%d`
CATALOG_INSTANCE="V\$INSTANCE"
USER_ID=`cat /home/oracle/dba/base_info/ora_ID.txt`
USER_MEGA_ID=`cat /home/oracle/dba/base_info/ora_mega_ID.txt`
USER_PW=`cat /home/oracle/dba/base_info/ora_PW.txt`
tns_list=`cat /home/oracle/dba/base_info/ts2.txt`



function ORACLE_SEGMENT() {   
	sqlplus -silent $2/$3@$1 <<EOF
		column SaveDt format a8
		column host_name format a15
		column instance_name format a20
		column schema format a20
		column tablespace_name format a30
		column segment_name format a100
		column segment_type format a30
		column partition_name format a100
		column table_name format a100
		column SIZE_MB format 999999
		column SIZE_Percent format 999999
		SPOOL /home/oracle/dba/CRON/result/DB_Space/t_spool.txt APPEND
		SET TERM off
		SET PAGESIZE 0
		SET LINESIZE 500
		SET FEEDBACK OFF
		SET VERIFY OFF
		SET HEADING OFF
		SET ECHO OFF
		set colsep ','

		select TO_CHAR(TO_DATE(sysdate), 'YYYYMMDD') AS SaveDt,
		       (SELECT HOST_NAME FROM ${CATALOG_INSTANCE} ) AS host_name,
		       (SELECT INSTANCE_NAME FROM ${CATALOG_INSTANCE} ) AS instance_name,
		       A.* ,
		       nvl(round(A.Size_MB/B.Size_MB * 100, 2), 0) Size_p
			       from (select owner schema,
					       tablespace_name ,
					       segment_name, 
					       segment_type,
					       partition_name,
					       case 
					       when segment_type = 'LOB'OR segment_type = 'LOB PARTITION' then 
					       (select table_name from dba_lobs where segment_name = A.segment_name and owner = A.owner )
					       when segment_type = 'INDEX' OR segment_type = 'INDEX PARTITION' then 
					       (select table_name from dba_indexes where index_name = A.segment_name and owner = A.owner )
					       when segment_type = 'TABLE' OR segment_type = 'TABLE PARTITION' then 
					       ( A.segment_name )
					       END AS table_name,
					       nvl(round(bytes/1024/1024, 2), 0) Size_MB
					       from dba_segments A
					       where owner not like'SYS%'
					       --and owner not like 'P_%'
					       and owner not in ('OUTLN',
						       'SYSGIS',
						       'MAXGAUG')
					       order by Size_MB desc ) A,
					       (select tablespace_name ,
						round(sum(bytes)/1024/1024, 2) Size_MB
						from dba_segments
						group by tablespace_name
						order by Size_MB desc ) B
						       where A.tablespace_name = B.tablespace_name
						       order by A.tablespace_name;

	 exit;
EOF
}


function sql_load() {
   sqlldr $1/$2@noc control='/home/oracle/dba/CRON/script/DB_Space/DB_Space_Oracle_Segment.ctl' log='/home/oracle/dba/CRON/script/DB_Space/DB_Space_Oracle_Segment.log'
}


for line in $tns_list
do
 if [ $line = 'MEGA_BIZDB1' ] || [ $line = 'MEGA_INFO1' ] || [ $line = 'DOMEGACR' ]
  then
        USER_ID=`cat /home/oracle/dba/base_info/ora_mega_ID.txt`
        ORACLE_SEGMENT $line $USER_ID $USER_PW
 # elif [ $line = 'AP1' ]
 # then
 #       USER_ID=`cat /home/oracle/dba/base_info/ora_ID.txt`
 #       ORACLE_SP $line $USER_ID $USER_PW
 else
        USER_ID=`cat /home/oracle/dba/base_info/ora_ID.txt`
        ORACLE_SEGMENT $line $USER_ID $USER_PW
 
 fi
done

chmod 777 /home/oracle/dba/CRON/result/DB_Space/t_spool.txt


USER_ID=`cat /home/oracle/dba/base_info/ora_ID.txt`

sql_load $USER_ID $USER_PW


cp /home/oracle/dba/CRON/result/DB_Space/t_spool.txt /home/oracle/dba/CRON/result/DB_Space/result_Oracle_Segment_$DATE.txt
rm  /home/oracle/dba/CRON/result/DB_Space/t_spool.txt
/bin/find /home/oracle/dba/CRON/result/DB_Space -name 'result_Oracle*.txt' -type f -mtime +5 | xargs rm

세그먼트 조회 쿼리

select TO_CHAR(TO_DATE(sysdate), 'YYYYMMDD') AS SaveDt,
       (SELECT HOST_NAME
          FROM ${CATALOG_INSTANCE} ) AS host_name,
       (SELECT INSTANCE_NAME
          FROM ${CATALOG_INSTANCE} ) AS instance_name,
       A.* ,
       nvl(round(A.Size_MB/B.Size_MB * 100, 2), 0) Size_p
  from (select owner schema,
               tablespace_name ,
               segment_name,
               segment_type,
               partition_name,
               case
                                   when segment_type = 'LOB'
                                OR segment_type = 'LOB PARTITION' then (select table_name
                  from dba_lobs
                 where segment_name = A.segment_name
                   and owner = A.owner )
                                   when segment_type = 'INDEX'
                                OR segment_type = 'INDEX PARTITION' then (select table_name
                  from dba_indexes
                 where index_name = A.segment_name
                   and owner = A.owner )
                                   when segment_type = 'TABLE'
                                OR segment_type = 'TABLE PARTITION' then ( A.segment_name )
                               END AS table_name,
               nvl(round(bytes/1024/1024, 2), 0) Size_MB
          from dba_segments A
         where owner not like'SYS%'
--and owner not like 'P_%'
                            and owner not in ('OUTLN',
                       'SYSGIS',
                       'MAXGAUG')
         order by Size_MB desc ) A,
       (select tablespace_name ,
               round(sum(bytes)/1024/1024, 2) Size_MB
          from dba_segments
         group by tablespace_name
         order by Size_MB desc ) B
 where A.tablespace_name = B.tablespace_name
 order by A.tablespace_name;
 

해당 쿼리를 통해 오라클의 세그먼트 정보를 수집한다.

sqlldr을 통해 서버별로 수집된 정보를 저장한다

DB_Space_Oracle_Segment.ctl

load data
infile '/home/oracle/dba/CRON/result/DB_Space/t_spool.txt'
append
into table DB_SPACE_ORACLE_SEGMENT
fields terminated by ','
(SAVEDT,HOST_NAME "trim(:HOST_NAME)",INSTANCE_NAME "trim(:INSTANCE_NAME)",SCHEMA "trim(:SCHEMA)",TABLESPACE_NAME "trim(:TABLESPACE_NAME)",SEGMENT_NAME "trim(:SEGMENT_NAME)",SEGMENT_TYPE "trim(:SEGMENT_TYPE)",
 PARTITION_NAME "trim(:PARTITION_NAME)", TABLE_NAME "trim(:TABLE_NAME)", SIZE_MB "trim(:SIZE_MB)", SIZE_PERCENT "trim(:SIZE_PERCENT)")

해당 정보를 저장하기 위한 테이블 생성

CREATE TABLE DBACOM.DB_SPACE_ORACLE_SEGMENT
(
    SAVEDT              CHAR(8),
    HOST_NAME           VARCHAR2(20),
    INSTANCE_NAME       VARCHAR2(50),
    SCHEMA              VARCHAR2(50),
    TABLESPACE_NAME     VARCHAR2(100),
    SEGMENT_NAME        VARCHAR2(100),
    SEGMENT_TYPE        VARCHAR2(50),
    PARTITION_NAME      VARCHAR2(100),
    SIZE_MB             NUMBER,
    SIZE_PERCENT        NUMBER,
    TABLE_NAME      VARCHAR2(100)
)
TABLESPACE USERS

--drop index DBALKH.UK_DB_SPACE_ORACLE_SEGMENT

CREATE UNIQUE INDEX DBALKH.UK_DB_SPACE_ORACLE_SEGMENT
ON DBACOM.DB_SPACE_ORACLE_SEGMENT
(
    SAVEDT,
    HOST_NAME,
    INSTANCE_NAME,
    SCHEMA,
    TABLESPACE_NAME,
    SEGMENT_TYPE,
    SEGMENT_NAME,
    PARTITION_NAME
) 
TABLESPACE USERS