[Shell] owner의 전체 table을 truncate 시키는 문장 생성 shell

티베로 Data 작업시 특정 owner 에 대한 모든 table에 대해서 truncate table 문장을 작성해주는 스크립트

참고로 HP-UX, Linux 에서 테스트하였음

먼저 2단계로 진행하는 걸로 구성했다.

※ Truncate Table SQL 문장 생성하기 (generate)

▶ 스크립트명 : gen_truncate_sql.sh     ( 다운로드 : gen_truncate_sql.sh.txt

▶ 사용방법 : sh gen_truncate_sql.sh [OWNER명]

▶ 출력파일 : sql/truncate_sql_[OWNER명].sql

                  list/table_list_[OWNER명].lst

▶ 사용예 : ./gen_truncate_sql.sh TIBERO

▶ Shell Script 소스 ( gen_truncate_sql.sh )

#!/bin/sh

### TODO Modify -> Tibero Connection Information

TB_USER=sys

TB_PASSWORD=tibero

TB_SID_NAME=tibero6

### Local Variable

DIR_LIST=list

DIR_LOG=log

DIR_SQL=sql

OWNER_STR=$1

LOG_FILE="${DIR_LOG}/truncate_sql_${OWNER_STR}.log"

LOG_LEVEL=DEBUG   #DEBUG, NONE

### 입력받은 db owner의 table 전체 목록을 생성함 : list/table_list_[onwer명].lst

TABLE_LIST_FILE="${DIR_LIST}/table_list_${OWNER_STR}.lst"

### 새성된 list/table_list_[owner명].lst 파일을 이용해서 truncate table 문장을 생성함

### 향후 list/table_list_[owner명].lst 파일이 미리 만들어 놓은게 있으면 입력받아 아래만 실행해도 될 것같음

GEN_SQL_FILE="${DIR_SQL}/truncate_sql_${OWNER_STR}.sql"

function _print_usage() {

    echo 

    echo "Error : No arguments supplited!!!"

    echo 

    echo "Usage : sh $0 [Owner Name] " 

    echo 

}

function _check_argument() {

  ### Check 1st argument -> db owner name

  if [ -z "$1" ]; then 

    _print_usage

    exit 0

  fi

}

function _check_directory() {

  ### Working Directory Make

  if [ ! -d "${DIR_LIST}" ]; then

    mkdir ${DIR_LIST}

  fi

  

  if [ ! -d "${DIR_LOG}" ]; then

    mkdir ${DIR_LOG}

  fi

  

  if [ ! -d "${DIR_SQL}" ]; then

    mkdir ${DIR_SQL}

  fi

}

function _log() {

  LOG_DATE=`date '+%Y.%m.%d %H:%M:%S'`

  if [ -z "$1" ]; then

    echo

    #echo "[${LOG_DATE}]"

  else

    echo $1

    #echo "[${LOG_DATE}] $1"

  fi

  if [ "DEBUG" = "${LOG_LEVEL}" ]; then

    echo "[$LOG_DATE] $1" >> ${LOG_FILE}

  fi

}

_check_argument ${OWNER_STR}

_check_directory

_log

_log "##############################################################"

_log "###   OWNER = ${OWNER_STR}"

_log "###   SQL FILE NAME = ${GEN_SQL_FILE}"

_log "##############################################################"

_log

tbsql -s ${TB_USER}/${TB_PASSWORD}@${TB_SID_NAME} <<EOF

set pagesize 0

set linesize 200

set pagesize 0 feedback off ver off heading off echo off

set pages 0 trimspool on lines 30000 termout off

spool ${TABLE_LIST_FILE}

select '"' || owner || '"."' || table_name || '"' tblname 

from dba_tables 

where owner in ('${OWNER_STR}')

order by owner, table_name;

spool off

exit

EOF

touch ${GEN_SQL_FILE}

cp /dev/null ${GEN_SQL_FILE}

for tbl_name_str in `cat ${TABLE_LIST_FILE}`

do

  if [ -n "${tbl_name_str}" ]; then

    echo "TRUNCATE TABLE ${tbl_name_str};" >> ${GEN_SQL_FILE}

  fi

done

# 아래는 table list 목록 없이 바로 sql문을 구하는 스크립트임

#tbsql -s ${TB_USER}/${TB_PASSWORD}@${TB_SID_NAME} <<EOF

#set pagesize 0

#set linesize 200

#set pagesize 0 feedback off ver off heading off echo off

#set pages 0 trimspool on lines 30000 termout off

#spool trun_sql_${OWNER_STR}.sql

#select 'truncate table "' || owner || '"."' || table_name || '";' command from dba_tables where owner = '${OWNER_STR}';

#spool off

#exit

#EOF

_log 

_log "##############################################################"

_log " Please Check Below Files..."

_log "  - LOG FILE => cat ${LOG_FILE}"

_log "  - TABLE LIST FILE BELONG OWNER[${OWNER_STR}] => cat ${TABLE_LIST_FILE}"

_log "  - GENERATED SQL FILE [TRUNCATE TABLE] => cat ${GEN_SQL_FILE}"

_log "  - If NECESSARY, RUN TRUNCATE => sh run_truncate_sql.sh ${OWNER_STR}"

_log 

_log " Annyung~~~~ *^_^*"

_log "##############################################################"

_log

▶ 실행 결과

- Owner 명을 입력하지 않고 수행한 경우 수행하지 않고 사용법을 Logging해줌

$ sh gen_truncate_sql.sh

Error : No arguments supplited!!!

Usage : sh gen_truncate_sql.sh [Owner Name]

- Owner명을 입력한 경우

$ sh gen_truncate_sql.sh TIBERO

##############################################################

### OWNER = TIBERO

### SQL FILE NAME = sql/truncate_sql_TIBERO.sql

##############################################################

Spooling is started.

truncate table "TIBERO"."BOARD";

truncate table "TIBERO"."MEMBER";

truncate table "TIBERO"."TB001";

Spooling is stopped: list/table_list_TIBERO.lst

##############################################################

 Please Check Below Files...

  - TABLE LIST FILE BELONG OWNER[TIBERO] => cat list/table_list_TIBERO.lst

  - GENERATED SQL FILE [TRUNCATE TABLE] => cat sql/trun_sql_TIBERO.sql

  - IF Necessary, RUN SQL FILE => sh run_truncate_sql.sh TIBERO

 Annyung~~~~ *^__^*

##############################################################

<<< 생성된 sql 파일 확인하기 >>>

$ cat sql/trun_sql_TIBERO.sql

TRUNCATE TABLE "TIBERO"."BOARD";

TRUNCATE TABLE "TIBERO"."MEMBER";

TRUNCATE TABLE "TIBERO"."TB001";

$

※ Truncate Table SQL 문장 실행하기 (run)

▶ 스크립트명 : run_truncate_sql.sh  ( 다운로드 : run_truncate_sql.sh.txt

▶ 사용방법 : ./run_truncate_sql.sh [OWNER명]

▶ 로그파일 : log/trun_sql_[OWNER명].log 

▶ 사용예 : ./run_truncate_sql.sh TIBERO

비교적 간단하게 tbsql 에 접속해서 @file_name을 이용애서 실행한다. 이 때 spool을 이용해서 실행 결과를 출력해줌

▶ Shell Script 소스 : run_truncate_sql.sh 

#!/bin/sh

### TODO Modify -> Tibero Connection Information

TB_USER=sys

TB_PASSWORD=tibero

TB_SID_NAME=tibero6

### Local Variable

DIR_LIST=list

DIR_LOG=log

DIR_SQL=sql

OWNER_STR=$1

LOG_FILE="${DIR_LOG}/truncate_sql_${OWNER_STR}.log"

LOG_LEVEL=DEBUG   #DEBUG, NONE

GEN_SQL_FILE="${DIR_SQL}/truncate_sql_${OWNER_STR}.sql"

function _print_usage() {

    echo 

    echo "Error : No arguments supplited!!!"

    echo 

    echo "Usage : sh $0 [Owner Name] " 

    echo 

}

function _check_argument() {

  ### Check 1st argument -> db owner name

  if [ -z "$1" ]; then 

    _print_usage

    exit 0

  fi

}

function _check_directory() {

  ### Working Directory Make

  if [ ! -d "${DIR_LIST}" ]; then

    mkdir ${DIR_LIST}

  fi

  

  if [ ! -d "${DIR_LOG}" ]; then

    mkdir ${DIR_LOG}

  fi

  

  if [ ! -d "${DIR_SQL}" ]; then

    mkdir ${DIR_SQL}

  fi

}

function _log() {

  LOG_DATE=`date '+%Y.%m.%d %H:%M:%S'`

  if [ -z "$1" ]; then

    echo

    #echo "[${LOG_DATE}]"

  else

    echo $1

    #echo "[${LOG_DATE}] $1"

  fi

  if [ "DEBUG" = "${LOG_LEVEL}" ]; then

    echo "[$LOG_DATE] $1" >> ${LOG_FILE}

  fi

}

_check_argument ${OWNER_STR}

_check_directory

_log

_log "##############################################################"

_log "###   OWNER = ${OWNER_STR}"

_log "###   SQL FILE NAME = ${GEN_SQL_FILE}"

_log "##############################################################"

_log

tbsql -s ${TB_USER}/${TB_PASSWORD}@${TB_SID_NAME} >> ${LOG_FILE} <<EOF

set timing on

set pagesize 0

set linesize 200

@${GEN_SQL_FILE}

exit

EOF

_log

_log "##############################################################"

_log " Please Check Below Files..."

_log "  - SQL FILE [TRUNCATE TABLE] => cat ${GEN_SQL_FILE}"

_log "  - RESULT LOG FILE => cat ${LOG_FILE}"

_log 

_log " Annyung~~~~ *^__^*"

_log "##############################################################"

_log

truncate table 실행 결과

$ sh run_truncate_sql.sh TIBERO

##############################################################

### OWNER = TIBERO

### SQL FILE NAME = sql/truncate_sql_TIBERO.sql

##############################################################

##############################################################

 Please Check Below Files...

  - SQL FILE [TRUNCATE TABLE] => cat sql/truncate_sql_TIBERO.sql

  - RESULT LOG FILE => cat log/run_truncate_sql_TIBERO.log

  Annyung~~~~ *^__^*

##############################################################$

<<< log 확인하기 >>>

$ cat log/run_truncate_sql_TIBERO.log

Table 'TIBERO.BOARD' truncated.

Total elapsed time 00:00:00.151937

Table 'TIBERO.MEMBER' truncated.

Total elapsed time 00:00:00.048663

Table 'TIBERO.TB001' truncated.

Total elapsed time 00:00:00.024917

$

- 작성일 : 2018년 8월 14일