1.
#! /bin/ksh
#set -x
SID=$1
ORACLE_SID=stat10g
ORACLE_HOME=/oracle10g/product/10.2
PATH=$PATH:/usr/bin:/usr/sbin:$ORACLE_HOME/bin:.:/usr/local/bin
export ORACLE_SID ORACLE_HOME PATH
DATE=`date +%Y%m%d`
BASEDIR=/home/oracle/monitor/segment_size
LOGDIR=/database/log/segment_size
#Window DBs
DATABASE=”boeprod newsdb”
#LOGG=/${BASEDIR}/win.log
#>LOGG
for i in ${DATABASE}
do
LOG=/${LOGDIR}/log/${i}_`date ‘+%Y%m%d’`.log
sqlplus oper/oper123<< EOF > ${LOG}
set echo off
–heading on feedback on head on serveroutput on
prompt “tbs utilization:”
set line 142;
set pagesize 1000;
col tablespace_name for a30;
set num 10;
col instance_name for a15
col used_ for 999,990.90;
col free_ for a15;
col free_percentage for a15;
select a.tablespace_name,all_sum “all_sum(M)”,
to_char(nvl(free_sum,0),’9,999,990.00′)||’M’ free_,
to_char(100*nvl(free_sum,0)/all_sum,’900.00′)||’%’ free_percentage,instance_name
from
(select tablespace_name,sum(bytes)/1024/1024 all_sum
from dba_data_files@${i}
group by tablespace_name) a,
(select tablespace_name,sum(bytes)/1024/1024 free_sum
from dba_free_space@${i}
group by tablespace_name) c, v\$instance@${i}
where
a.tablespace_name = c.tablespace_name(+)
order by 4
/
prompt “tbs percentage utilization:”
–tbs percentage utilization
select tablespace_name, to_char(sysdate,’YYYY-MM’) “Date”, org_mb total, free_mb free, pct_free pct_free,
–CASE WHEN ( (((0.8*org_mb)-free_mb)/(1-0.8)) < 1) THEN 0
— ELSE (((0.8*org_mb)-free_mb)/(1-0.8))
— END as “Free80%-Add”,
CASE WHEN ( (((0.2*org_mb)-free_mb)/(1-0.2)) < 1) THEN 0
ELSE (((0.2*org_mb)-free_mb)/(1-0.2))
END as “Free20%-Add”,
CASE WHEN ( (((0.17*org_mb)-free_mb)/(1-0.17)) < 1) THEN 0
ELSE (((0.17*org_mb)-free_mb)/(1-0.17))
END as “Free17%-Add”,
CASE WHEN ( (((0.16*org_mb)-free_mb)/(1-0.16)) < 1) THEN 0
ELSE (((0.16*org_mb)-free_mb)/(1-0.16))
END as “Free16%-Add”,
CASE WHEN ( (((0.15*org_mb)-free_mb)/(1-0.15)) < 1) THEN 0
ELSE (((0.15*org_mb)-free_mb)/(1-0.15))
END as “Free15%-Add”
from
( select a.tablespace_name
,((sum(a.bytes)/1024/1024)/max(b.Org_Mb))*100 Pct_free
,max(b.Org_Mb) Org_Mb
,max(b.Org_Mb) – sum(a.bytes)/1024/1024 Used_Mb
,sum(a.bytes)/1024/1024 Free_Mb
,max(a.bytes)/1024/1024 Max_Mb
from dba_free_space@${i} a,
(select tablespace_name ,sum(bytes)/1024/1024 Org_Mb from dba_data_files@${i} group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name
order by 1 desc
)
/
col file_name for a55
col tablespace_name for a30
select tablespace_name,file_name,autoextensible,sum(maxbytes)/1024/1024 totalbytes,sum(bytes)/1024/1024 bytes from dba_data_Files@${i} group by tablespace_name,file_name,autoextensible order by tablespace_name,file_name
/
prompt”top20seg:”
–top20seg.sql
–def run_dt = sysdate – 6 months
def incr_percent = 0
def lower_bound = 65536
col owner for a12
col seg_type for a12
col segment_name for a35
col “%Growth+” for ‘999’
select to_char(s1.run_date,’YYYYMMDD HH24:MI:SS’) origin_date,s1.owner, s1.segment_type seg_type,s1.segment_name, s1.extents “last_exts”, s2.extents “cur_exts”, s1.bytes “last_bytes”, s2.bytes “cur_bytes”, round(((s2.bytes – s1.bytes)/s1.bytes)*100,2) “%Growth+”
from ${i}_segmon_statistics s1, dba_segments@${i} s2
where s1.segment_name = s2.segment_name
and s1.owner = s2.owner
and s1.segment_type = s1.segment_type
and nvl(s1.partition_name,’*’) = nvl(s2.partition_name,’*’)
and ( ((s2.extents – s1.extents)/s1.extents) * 100 >= &incr_percent or ((s2.bytes – s1.bytes)/s1.bytes)*100 >= &incr_percent)
and s2.bytes > &lower_bound
and s1.run_date = (select min(run_date) from ${i}_segmon_statistics)
order by 9,2,3,4
/
column capture_date format A11
column segment_name format A35
column segment_type format A15
column owner format A20
column MB format 99,999.99
select to_char(sysdate,’YYYY-MON-DD’) capture_date,owner,segment_name,segment_type,tablespace_name,sum(bytes)/1024/1024 MB
from dba_segments@${i}
where owner not in (‘OUTLN’,’SCOTT’,’SYSTEM’)
group by owner,segment_name,segment_type,tablespace_name having (sum(bytes)/1024/1024 >5)
order by MB desc
/
exit;
EOF
cat ${LOG}
#/bin/mailx -s “Temporary monitor ${i} tablespace” ts-dba@aa.com.cn < ${LOG};
#cat ${LOG}>>${LOGG}
done
2.
PWD=/database/log/segment_size
LOG=${PWD}/tbs_windows_rep.log
echo “TABLESPACE_NAME all_sum(M) FREE_ FREE_PERCENTAGE INSTANCE_NAME” >${LOG}
echo “—————————— ———- ————— ————— —————-“>>${LOG}
grep ‘%’ ${PWD}/tbs_win_all.log|grep -v ‘%G’ |grep -v ‘%-‘ |sed ‘s/.*://g’ |sort -uk 4 >> ${LOG}
#cat ${LOG}
#(echo “Windows DB tablespace’s size monitor data:”;/usr/bin/uuencode ${LOG} ${LOG}) | /bin/mailx -s “Windows DB tablespace’s size monitor `date ‘+%Y-%m-%d’ `” ts-dba@aa.com.cn
echo “Windows DB tablespace’s size monitor data:” | /bin/mailx -s “Windows DB tablespace’s size monitor `date ‘+%Y-%m-%d’ `” ts-dba@aa.com.cn <${LOG}
3.fixed:
因为使用到了db_link.所以新增加数据库，也需要新增加db_link.
for pdb
conn oper/oper123
create database link pcwspprod connect to oper identified by oper123 using ‘pcwspprod’;
for cdb
conn c##oper/oper123
create database link cwspprod connect to c##oper identified by oper123 using ‘cwspprod’;
