oracle報表相關指令碼

今天查詢之前的資料，突然發現一些有用的指令碼，在上一家公司，工作當中需要調取相關報表的一些指令碼，感覺還是蠻有用的，就放到blog上面來，命令多數是作業系統層面的(linux,hp_unix,aix)，具體如下：

AUTHSTATE=compat
export SHELL=/usr/local/bin/bash
export NLS_LANG=American_America.ZHS16GBK
export PATH=/opt/softbench/bin:/usr/bin:/usr/ccs/bin:/usr/contrib/bin:/usr/contrib/Q4/bin:/opt/perl/bin:/opt/ipf/bin:/opt/hparray/bin:/opt/nettladm/bin:/opt/fcms/bin:/opt/sas/bin:/opt/wbem/bin:/opt/wbem/sbin:/usr/bin/X11:/opt/resmon/bin:/opt/perf/bin:/usr/contrib/kwdb/bin:/opt/graphics/common/bin:/opt/prm/bin:/opt/sfm/bin:/opt/hpsmh/bin:/opt/upgrade/bin:/opt/wlm/bin:/opt/gvsd/bin:/opt/sec_mgmt/bastille/bin:/opt/drd/bin:/opt/dsau/bin:/opt/dsau/sbin:/opt/firefox:/opt/gnome/bin:/opt/mozilla:/opt/perl_32/bin:/opt/perl_64/bin:/opt/sec_mgmt/spc/bin:/opt/ssh/bin:/opt/swa/bin:/opt/thunderbird:/opt/gwlm/bin:/usr/contrib/bin/X11:/opt/aCC/bin:/opt/caliper/bin:/opt/cadvise/bin:/opt/sentinel/bin:/opt/langtools/bin:/usr/sbin:/usr/local/sbin:/sbin:/oramem/oracle/product/10.2.0/bin:/usr/local/bin:/home/oracle/bin:/usr/sbin:/usr/local/sbin:/sbin
export BASH_ENV=/home/kasaur/.bashrc
export ORACLE_HOME=/oramem/oracle/product/10.2.0
export NLS_DATE_FORMAT=YYYYMMDDHH24MISS
export NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat
export LD_LIBRARY_PATH=/oramem/oracle/product/10.2.0/lib:/oramem/oracle/product/10.2.0/sqlplus/lib:/usr/lib:/lib:
cd /tmp/kpi_sh
export pwd=`cat /tmp/kpi_sh/pwd.txt `
TMP_VAL=`sqlplus -S kasaur/$pwd@M_ORAQUE << !
set heading off feedback off pagesize 0 verify off echo off numwidth 4
select to_char(sysdate-1,'YYYYMM') from dual;
exit;
!`
TMP_VAL2=`sqlplus -S kasaur/$pwd@M_ORAQUE << !
set heading off feedback off pagesize 0 verify off echo off numwidth 4
select to_char(sysdate-1,'YYYYMMDD') from dual;
exit;
!`

sqlplus -S kasaur/$pwd@M_ORAMDB  << !
truncate table tmp_sun_ip;
insert into tmp_sun_ip select active_ip,uuid from njxlzxtf.memb_active_game where active_ip in
(select active_ip from (
select active_ip,count(*) as cnt from njxlzxtf.memb_active_game where trunc(active_ts)=trunc(sysdate-1) and site_cd='SUN'
group by active_ip having count(*)>=10)
) and trunc(active_ts)=trunc(sysdate-1) and site_cd='SUN';
commit;
exit;
!

sqlplus -S kasaur/$pwd@oraque > sun_ipban_$TMP_VAL2.txt << !
set echo off
set feedback off
set newpage none
set linesize 2000
set verify off
set pagesize 0
set term off
set trims on
set heading  off
set trimspool on
set trimout on
set timing off
select substr(sysdate-1,1,8)||'|'||site_id||'|'||active_ip||'|'||loginname from
(select a.site_id as site_id,b.active_ip as active_ip,a.loginname as loginname from njxlzxtf.memb_active_queue_sun a,tmp_sun_ip b where a.create_ts>=substr(sysdate-1,1,8) and a.create_ts=50
union all
select b.active_ts as active_ts,a.site_id as site_id,b.active_ip as active_ip,a.loginname as loginname from njxlzxtf.memb_active_queue_sun_$TMP_VAL a,tmp_sun_ip b where a.create_ts>=substr(sysdate-1,1,8) and a.create_ts=50)
order by site_id,active_ip,loginname;
commit;
exit;
!

sqlplus -S kasaur/$pwd@oraque > sun_ipunban_$TMP_VAL2.txt << !
set echo off
set feedback off
set newpage none
set linesize 2000
set verify off
set pagesize 0
set term off
set trims on
set heading  off
set trimspool on
set trimout on
set timing off
select active_ts||'|'||site_id||'|'||active_ip||'|'||loginname from
(select b.active_ts as active_ts,a.site_id as site_id,b.active_ip as active_ip,a.loginname as loginname from njxlzxtf.memb_active_queue_sun a,tmp_sun_ip b where a.create_ts>=substr(sysdate-1,1,8) and a.create_ts=10 and b.no<50
union all
select b.active_ts as active_ts,a.site_id as site_id,b.active_ip as active_ip,a.loginname as loginname from njxlzxtf.memb_active_queue_sun_$TMP_VAL a,tmp_sun_ip b where a.create_ts>=substr(sysdate-1,1,8) and a.create_ts=10 and b.no<50)
order by site_id,active_ip,loginname;
commit;
exit;
!

然後把這段指令碼放到crontab裡面，每天自動執行就ok了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25618347/viewspace-713821/，如需轉載，請註明出處，否則將追究法律責任。