shell脚本 20101102(二)

来源：互联网发布：远程控制软件破解版编辑：程序博客网时间：2024/04/30 14:24

for和case的混合运用
set -A region hz hy mz gz yf qy zq sg jm mm zj yj st sw jy cz fs zs zh sz dg
for loop in ${region[*]}
         do

          print "select "123$loop," count(*) from "$loop"zw.sp_hlr_result where to_char(CREATTIME,'yyyymmdd')>='20071101' and to_ch
ar(CREATTIME,'yyyymmdd')<='20071130' and NETCELLIDERRCODE='000';" >/home/devis/bosskz.sql
        case $loop in
              jm|mz|qy|yf|zh|zs) dbtool cxquery@szdb2 -f[/home/devis/bosskz.sql] </tmp/cxn.txt>>/home/devis/cxnhlr.txt;;
              sz)                dbtool cxquery@szdb1 -f[/home/devis/bosskz.sql] </tmp/cxn.txt>>/home/devis/cxnhlr.txt;;
              sg|hy|cz|sw|fs|mm) dbtool cxquery@dgdb2 -f[/home/devis/bosskz.sql] < /tmp/cxn.txt1>>/home/devis/cxnhlr.txt;;
              yj|dg|zj)          dbtool cxquery@dgdb1 -f[/home/devis/bosskz.sql] < /tmp/cxn.txt1>>/home/devis/cxnhlr.txt;;
              hz|st|jy|zq)       dbtool cxquery@gzdb2 -f[/home/devis/bosskz.sql] < /tmp/cxn.txt2>>/home/devis/cxnhlr.txt;;
              gz)                dbtool cxquery@gzdb1 -f[/home/devis/bosskz.sql] < /tmp/cxn.txt2>>/home/devis/cxnhlr.txt;;
               *)      echo "error!" ;;
        esac
        echo $loop
        done

表空间报警
SELECT UPPER(d.TABLESPACE_NAME), D.TOT_GROOTTE_MB, D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0), TO_CHAR(ROUND((D.TOT_GROOTTE_MB - nvl(F.
TOTAL_BYTES,0)) / D.TOT_GROOTTE_MB * 100, 2), '990.99'), nvl(F.TOTAL_BYTES,0), TO_CHAR(ROUND((D.TOT_GROOTTE_MB - nvl(F.TOTAL_BYTES,0
) - nvl(e.xxxx,0)) / D.TOT_GROOTTE_MB * 100, 2), '990.99'), nvl(e.xxxx,0)
FROM
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
where DD.BYTES is not null
GROUP BY DD.TABLESPACE_NAME
) D,
(select TABLESPACE_NAME,sum(BYTES/1024/1024) as xxxx from dba_segments where segment_name like 'BIN$%'
group by TABLESPACE_NAME) E
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME (+)
and d.TABLESPACE_NAME = E.TABLESPACE_NAME (+)
and (d.TABLESPACE_NAME like '%_MI%' or d.TABLESPACE_NAME like '%_HIS%' or d.TABLESPACE_NAME like '%ZWO%');　


select to_char('100') from dual;

select to_char('100','0.0000') from dual;
计算每月的最后一天
startday=$1
days=6
yy=`echo $startday|cut -c 1-4`
mm=`echo $startday|cut -c 5-6`
dd=`echo $startday|cut -c 7-8`
days=`expr $days + $dd`
while [ $days -gt 0 ]
do
        aaa=`cal $mm $yy`
        bbb=`echo $aaa|awk '{print $NF}'`
        sav_dd=$days
        sav_mm=$mm
        sav_yy=$yy
        days=`expr $days - $bbb`
        [ $days -gt 0 ] && mm=`expr $mm + 1`
        if [ $mm -gt 12 ]
        then
          mm=1
          yy=`expr $yy + 1`
        fi
done

#! /usr/bin/ksh
############################################################
# 文件名: brand_duizhang_all.sh
# Copyright (c) 2006-2099 从兴公司维护部部
# 脚本名称：查询回割动感、神大-计费品牌资料冲销脚本V1.0(Final)
# 创建人: 卢斌
# 创建日期: 2009-05-12
# 修改人: 卢斌
# 修改日期: 2009-05-12
# 描述: 该脚本的主要目的是：由于回割动感、神大查询计费
#         计费品牌资料生成冲销文件脚本
# 版本: 1.0
# 联系方式:lubin@revenco.com
############################################################
. /home/cxljx/.profile

dbtool自动登录
dbtool ${DB} sql="${PROC} ; " afljk >> /dev/null 2>&1
自动FTP
echo user "cxph" "${code}" "/n"bin"/n"prompt"/n"cd /appdata/fileftp/c++file/batchbillingday/$area/outfile"/n"lcd /home/cxlf2/rolldis
billday/outfile"/n"get ROUT.$AREA.$DATE.$PIDnum3"/n"get BOUT.$AREA.$DATE.$PIDnum3"/n"by |ftp -n $ADDRESS >> /dev/null 2>&1

自动telnet脚本几秒种后会被自动退出
(sleep 2
        echo "cxhz"
        sleep 2
        echo "GMCC123B"
        sleep 2
        echo "cd ~/tzh"
        sleep 2
        echo "ls"
        sleep 2
        ) | telnet 10.243.208.78

自动生成数据并FTP上传脚本
cat exc.txt|grep -i GSMX|awk '{print $0}'|tr "[A-Z]" "[a-z]"|grep -i fs|tr "[GS*
]" "[SCP]" >/home/cxzy/exc_01.txt
ftp -n 10.243.212.2 << !
user cxyejz Gmcc@0501
cd /home/cxyejz
bi
ls exc_01.txt
put exc_01.txt
quit
!

sql="select /*+ parallel (a 6) */ SERVNUMBER||'|'||STARTDATE||'|'||INVALIDDATE||
'|'||CREATEDATE||'|'||STATUSDATE from mgsj.${warea}_cm_subs_subscriber a"

sqlplus -s $DB <<EOF>/dev/null
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:ss';
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set linesize 1000;
set numwidth 12;
set termout on;
set trimout on;
set trimspool on;
spool /appdata/fileftp/tmp/${warea}_subscriber
$sql;
spool off;
exit;
EOF

. /home/devis/.profile

set -A LOCATE st jy cz sw hz mz zs hy fs zh sg yf zq qy mm yj zj gz dg jm

for AREA in ${LOCATE[*]}
do
case ${AREA} in
      st)
        SOR=汕头;
        DB=user/password@sst;;
      fs)
        SOR=佛山;
        DB=user/password@sfs;;
      zh)
        SOR=珠海;
        DB=user/password@szh;;
      hz)
        SOR=惠州;
        DB=user/password@shz;;
      mz)
        SOR=梅州;
        DB=user/password@smz;;
      sw)
        SOR=汕尾;
        DB=user/password@ssw;;
      cz)
        SOR=潮州;
        DB=user/password@scz;;
      jy)
        SOR=揭阳;
        DB=user/password@sjy;;
      hy)
        SOR=河源;
        DB=user/password@shy;;
      zq)
        SOR=肇庆;
        DB=user/password@szq;;
      yf)
        SOR=云浮;
        DB=user/password@syf;;
      qy)
        SOR=清远;
        DB=user/password@sqy;;
      sg)
        SOR=韶关;
        DB=user/password@ssg;;
      yj)
        SOR=阳江;
        DB=user/password@syj;;
      zj)
        SOR=湛江;
        DB=user/password@szj;;
      mm)
        SOR=茂名;
        DB=user/password@smm;;
      zs)
        SOR=中山;
        DB=user/password@szs;;
      gz)
        SOR=广州;
        DB=user/password@sgz1;;
      dg)
        SOR=东莞;
        DB=user/password@sdg;;
      jm)
        SOR=江门;
        DB=user/password@sjm;;
      *)
      echo 未知区域
esac

Proc01="unload to ${AREA}tmp
select TELNUM,PROC_TIME,NE_DESC from ${AREA}yy.sp_workf_comm_his_k where WORKF_
ID in ('RTU0001','RTU0011','RTU0002') and NE_DESC like '%优惠代码为2805缺少必要?
                                                                                 母郊幼柿?';"
dbtool ${DB} sql="${Proc01};" afljk >> /dev/null 2>&1
if test -e ${AREA}tmp; then    #测试文件是否存在
print "${AREA}地市：";
cat ${AREA}tmp;
else
echo "${AREA}地市查询记录为空";
fi
done