分享整理的sql脚本
1. 表空间使用率
SQL> select a.tablespace_name,
2 round(a.total_size) "total_size M",
3 round(a.total_size)-round(b.free_size,3) "used M",
4 round(b.free_size ,3) "free_size M",
5 round(b.free_size/total_size *100,2) ||'%' free_rate
6 from (select tablespace_name,sum(bytes) /1024/1024 total_size
7 from dba_data_files group by tablespace_name) a,
8 (select tablespace_name,sum(bytes)/1024/1024 free_size
9 from dba_free_space group by tablespace_name) b
10 where a.tablespace_name=b.tablespace_name(+);
TABLESPACE_NAME total_size M used M free_size M FREE_RATE
------------------------------ ------------ ---------- ----------- -----------------------------------------
SYSAUX 530 494.312 35.688 6.73%
UNDOTBS1 195 67.625 127.375 65.32%
USERS 114 108.562 5.438 4.78%
SYSTEM 1060 1002.187 57.813 5.45%
EXAMPLE 127 103.562 23.438 18.47%
TEST01 300 1 299 99.67%
2. 检查监听程序的配置
#!/bin/bash
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1;
export ORACLE_SID=prod
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH; export PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
lsn=`netstat -an |grep :1521 |head -1 |awk '{print $4}'|cut -c9-12`
if [ "$lsn" = '1521' ] ;then
echo "Current Listener Port is : $lsn"
echo 'listener is up !'
else
echo "This `hostname` listener is down !"
echo "Starting listener ...."
lsnrctl start
fi
3. 检查失效对象
#####################################################################
##invalid_object_alert.sh
#####################################################################
#!/bin/ksh
EDITOR=vi; export EDITOR
ORACLE_SID=prod; export ORACLE_SID
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN
NLS_LANG=american; export NLS_LANG
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMAT
ORATAB=/etc/oratab;export ORATAB
PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; export PATH
DBALIST="oracle,root";export DBALIST
sqlplus -s '/ as sysdba' <<EOF
set feed off
set heading off
column OWNER format a10
column OBJECT_NAME format a35
column OBJECT_TYPE format a10
column STATUS format a10
spool invalid_object.alert
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
spool off
exit
EOF
if [ `cat invalid_object.alert | wc -l` -gt 0 ]
then
mailx -s "INVALID OBJECTS for prod" $DBALIST < invalid_object.alert
fi
4. 检查实例是否启动
#!/bin/bash
#chkinst.sh
###################################################################
## ckinstance.ksh ##
###################################################################
ORATAB=/etc/oratab
echo "`date` "
echo "Oracle Database(s) Status `hostname`"
db=`egrep -i ":Y|:N" $ORATAB | cut -d":" -f1 | grep -v "^#" | grep -v "^*"`
pslist="`ps -ef | grep pmon`"
for i in $db
do
echo "$pslist" | grep "ora_pmon_$i" > /dev/null 2>&1
if (( $? )); then
echo "Oracle Instance - $i: Down"
else
echo "Oracle Instance - $i: Up"
fi
done
5.检查死锁
###################################################################
## deadlock_alert.sh ##
###################################################################
#!/bin/ksh
EDITOR=vi; export EDITOR
ORACLE_SID=prod; export ORACLE_SID
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
LD_LIBRARY_PATH=$ORACLE_HOME/lib; export LD_LIBRARY_PATH
TNS_ADMIN=/var/opt/oracle;export TNS_ADMIN
NLS_LANG=american; export NLS_LANG
NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'; export NLS_DATE_FORMAT
ORATAB=/etc/oratab;export ORATAB
PATH=$PATH:$ORACLE_HOME:$ORACLE_HOME/bin:/usr/ccs/bin:/bin:/usr/bin:/usr/sbin:/sbin:/usr/openwin/bin:/opt/bin:.; export PATH
DBALIST="oracle,root";export DBALIST
sqlplus -s '/ as sysdba' <<EOF
set feed off
set heading off
spool deadlock.alert
SELECT SID, DECODE(BLOCK, 0, 'NO', 'YES' ) BLOCKER,
DECODE(REQUEST, 0, 'NO','YES' ) WAITER
FROM V\$LOCK
WHERE REQUEST > 0 OR BLOCK > 0
ORDER BY block DESC;
spool off
exit
EOF
if [ `cat deadlock.alert | wc -l` -gt 0 ]
then
mailx -s "DEADLOCK ALERT for prod" $DBALIST < deadlock.alert
fi
5.检查表空间使用率的 shell脚本
#!/bin/bash
sqlplus -s '/as sysdba' <<EOF
set feedback off
set echo on
set linesize 100
set pagesize 200
column "USED (MB)" format a10
column "FREE (MB)" format a10
column "TOTAL (MB)" format a10
column PER_FREE format a10
spool /home/oracle/tablespace.more append
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') "TIME:" from dual;
select a.name "DB_NAME",b.instance_name "INSTANCE_NAME" from v\$database a,v\$instance b ;
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE - F.FREE_SPACE),'999,999') "USED (MB)",
TO_CHAR (F.FREE_SPACE, '999,999') "FREE (MB)",
TO_CHAR (T.TOTAL_SPACE, '999,999') "TOTAL (MB)",
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),'999')||' %' PER_FREE
FROM (
SELECT TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = 'db_block_size')/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 80;
spool off
exit;
EOF
6.取样分析对象
set heading off
set feedback off
set pagesize 200
set linesize 100
set echo off
set time off
set timing off
set term off
set trimspool on
spool analyze_table.sql
select 'ANALYZE TABLE ' || owner || '.' || segment_name || ' ESTIMATE STATISTICS SAMPLE 10 PERCENT;' from dba_segments where segment_type = 'TABLE' and owner not in ('SYS', 'SYSTEM');
spool off
最新文章
- PHP 文件与文件夹的创建和删除操作
- 如何解决虚拟机Mac OS X 不支持二进制编译问题()
- PHP用Array模拟枚举
- ListView单击单元格 产生其他控件
- Science:给青年科研工作者的忠告
- 约束的DEFERRABLE, NOT DEFERRABLE, INITIALLY IMMEDIATE 和 INITIALLY DEFERRED
- protel DXP的类矢量图功能
- 8.Java 加解密技术系列之 PBE
- 如何编写一个稳定的网络程序(TCP)
- 纯css3打造瀑布流布局
- windows下QT打包
- pytorch dataloader num_workers
- 爬起点小说 day02
- 快速切题 sgu115. Calendar 模拟 难度:0
- [转]Oracle 中计算时间间隔的SQL 语句
- eclipse JAVA反编译
- 算法题:整形数组找a和b使得a+b=n
- PHP isset和empty 详细比较
- HDUOJ-------2844Coins
- 【LG5018】[NOIP2018pj]对称的二叉树
热门文章
- Solr -- Solr Facet 2
- 【canvas】基于坐标的碰撞检测 / 基本的动画 / 多物体动画
- 传智播客C语言视频第一季(有效下载期为10.1-10.7,10.8关闭)
- Java---网络蜘蛛-网页邮箱抓取器~源码
- 杭州(含嘉兴,绍兴,金华,湖州,义乌)Uber优步司机奖励政策(1月25日~1月31日)
- JuliaSet&;MandelBulb @ Maya&;KK —— 4亿粒子的测试
- Web移动端Fixed布局的解决方案
- java工具类--数据库操作封装类
- hdu4348 - To the moon 可持久化线段树 区间修改 离线处理
- [转]Android UI:看看Google官方自定义带旋转动画的ImageView-----RotateImageView怎么写(附 图片淡入淡出效果)