oracle日常检查脚本

最新推荐文章于 2025-05-17 13:40:39 发布

思考v

最新推荐文章于 2025-05-17 13:40:39 发布

阅读量1.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： shell

本文链接：https://blog.csdn.net/xiegh2014/article/details/43054849

shell 专栏收录该内容

11 篇文章

订阅专栏

--检查表空间脚本

dg1-> cat check_database.sh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=oatestdb1
sqlplus -s '/ as sysdba' <<EOF
set feed off
set linesize 150
set pagesize 200
column "SUM_SPACE(M)" format a15
column " SUM_BLOCKS" format a20
column "USED_SPACE(M)" format a15
column "USED_RATE(%)" format a15
column "FREE_SPACE(M)" format a15
spool tablespace.alert
SELECT D.TABLESPACE_NAME,
       SPACE || 'M' "SUM_SPACE(M)",
       BLOCKS "SUM_BLOCKS",
       SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",
       FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --如果有临时表空间
SELECT D.TABLESPACE_NAME,
       SPACE || 'M' "SUM_SPACE(M)",
       BLOCKS SUM_BLOCKS,
       USED_SPACE || 'M' "USED_SPACE(M)",
       ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",
       NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
               SUM(BLOCKS) BLOCKS
          FROM DBA_TEMP_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
               ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
          FROM V\$TEMP_SPACE_HEADER
         GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
spool off
exit

EOF

--检查失效对象

dg1-> cat check_test1.sh
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=oatestdb1
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.log
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS = 'INVALID' ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
spool off
exit
EOF

--执行脚本

dg1-> cat test.sh
#!/bin/sh
select ch in "database" "invalid_object" "exit"
do
case $ch in
"database")
    sh /home/oracle/script/check_database.sh
    ;;
"invalid_object")
    sh /home/oracle/script/check_test1.sh
    ;;
"exit")
    echo "exit"
    break;
    ;;
*)
    echo "请正确输入"
    ;;
esac
done;