同事上班时间居然在Oracle里做这种事？

IT邦德 2025-08-30 10:33:00
作为DBA，你是否每天被困在备份监控、空间告警、性能救火的循环中？本文分享3个生产级Oracle自动化脚本，专治重复性工作，效率提升90%！

 

一、表空间管家

 

以下是一个针对生产环境设计的Oracle ASM磁盘空间监控及自动添加数据文件的脚本方案，结合了ASM空间监控、表空间扩展逻辑及生产环境安全规范，适用于Oracle 11g/12c/19c等版本。

 

  •  
  •  
通过crontab设置每小时执行一次，避免频繁检查：0 */1 * * * /opt/scripts/asm_auto_add_datafile.sh

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
#!/bin/bash# 脚本名称: asm_auto_add_datafile.sh# 功能: 监控ASM磁盘组空间并自动扩展表空间数据文件# 适用环境: Oracle 11g/12c/19c, Linux/Unix
# 配置参数ORACLE_SID=orclTHRESHOLD_PCT=10    # ASM磁盘组剩余空间告警阈值（%）DATAFILE_SIZE=20G    # 新增数据文件大小LOG_FILE=/var/log/oracle/asm_auto_add.log
# 获取ASM磁盘组空间信息get_asm_diskgroup_info() {  sqlplus -S / as sysdba <<EOF  SET FEEDBACK OFF HEADING OFF LINESIZE 200  SELECT name||'|'||ROUND((free_mb/total_mb)*100,2)   FROM v\$asm_diskgroup  WHERE ROUND((free_mb/total_mb)*100,2) < $THRESHOLD_PCT;  EXIT;EOF}
# 获取需要扩展的表空间列表get_tablespace_list() {local dg_name=$1  sqlplus -S / as sysdba <<EOF  SET FEEDBACK OFF HEADING OFF LINESIZE 200  SELECT a.tablespace_name   FROM (    SELECT tablespace_name, SUM(bytes)/1024/1024/1024 used_gb    FROM dba_segments     GROUP BY tablespace_name  ) a, dba_tablespaces b   WHERE a.tablespace_name = b.tablespace_name   AND b.contents = 'PERMANENT'  AND a.used_gb / (SELECT SUM(bytes)/1024/1024/1024                    FROM dba_data_files                    WHERE tablespace_name = a.tablespace_name) > 0.9  AND EXISTS (    SELECT 1     FROM dba_data_files     WHERE tablespace_name = a.tablespace_name     AND file_name LIKE '+${dg_name}%'  ) AND ROWNUM <= 2;  # 限制单次处理表空间数量  EXIT;EOF}
# 主逻辑main() {echo"$(date '+%Y-%m-%d %H:%M:%S') - 开始执行ASM空间监控..." >> $LOG_FILE
# 遍历空间不足的ASM磁盘组  get_asm_diskgroup_info | while IFS='|'read dg_name free_pct; do    echo"$(date) - 检测到磁盘组 ${dg_name} 剩余空间不足: ${free_pct}%" >> $LOG_FILE        # 获取需扩展的表空间    get_tablespace_list "$dg_name" | whileread ts_name; do      datafile_path="+${dg_name}"            # 执行数据文件添加      sqlplus -S / as sysdba <<EOF >> $LOG_FILE 2>&1      WHENEVER SQLERROR EXIT SQL.SQLCODE      ALTER TABLESPACE ${ts_name} ADD DATAFILE '${datafile_path}'        SIZE ${DATAFILE_SIZE} AUTOEXTEND ON NEXT 1G MAXSIZE 32G;      EXIT;EOF      
      # 错误处理      if [ $? -eq 0 ]; then        echo"$(date) - 成功添加数据文件: ${datafile_path} 到表空间 ${ts_name}" >> $LOG_FILE      else        echo"$(date) - 错误: 添加数据文件到 ${ts_name} 失败! 检查ORA错误码." >> $LOG_FILE      fi    donedoneecho"$(date) - 监控执行完成." >> $LOG_FILE}
# 执行主函数main

 

二、自动生成AWR报告

 

每天生成关键数据库的AWR报告，自动分析TOP SQL和性能瓶颈，提前发现慢查询，避免业务高峰期的性能雪崩，脚本具体跳转链接查看。

 

告别人肉运维，AWR/ASH定时自动生成

 

图片

 

三、自动删除分区

 

相信很多小伙伴因为删除数据而烦恼，有其一些历史数据长时间存在数据库，存储空间岌岌可危，那么下面这个定时删除，一定会给你带来帮助，其实就是一个存储过程，挂一个定时任务就行。

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
--存储过程脚本GRANT SELECT ON dba_tab_partitions TO SYSTEM;
CREATE OR REPLACE FUNCTION high_value_to_date(  p_table_name      VARCHAR2,  p_partition_name  VARCHAR2) RETURN DATE IS  v_high_value LONG;  v_date       DATE;BEGIN  SELECT high_value  INTO v_high_value  FROM dba_tab_partitions  WHERE table_name = p_table_name    AND partition_name = p_partition_name;
  EXECUTE IMMEDIATE 'BEGIN :1 := ' || v_high_value || '; END;'    USING OUT v_date;
  RETURN v_date;EXCEPTION  WHEN OTHERS THEN    RETURN NULL;END;
exec DROP_OLD_PARTITIONS('SYSTEM');
SELECT * FROM DBA_ERRORS  WHERE name='PURGE_OLD_PARTITIONS'
CREATE OR REPLACE PROCEDURE drop_old_partitions(p_owner IN VARCHAR2) IS    v_cutoff_date          DATE;    v_partition_high_value DATE;    v_sql                  VARCHAR2(4000);BEGIN    -- 计算截止日期（保留数据的起始时间点）    v_cutoff_date := TRUNC(SYSDATE) - 1;    -- 遍历目标表所有分区    FOR part_rec IN (       SELECT          table_owner,         table_name,         partition_name,         high_value       FROM          dba_tab_partitions       WHERE         TABLE_OWNER= upper(p_owner)         AND interval = 'YES'         AND high_value IS NOT NULL    ) LOOP        BEGIN            -- 动态执行HIGH_VALUE转换            EXECUTE IMMEDIATE 'BEGIN :1 := ' || part_rec.high_value || '; END;'                USING OUT v_partition_high_value;
            DBMS_OUTPUT.PUT_LINE('Partition: ' || part_rec.partition_name                 || ', High Value: ' || TO_CHAR(v_partition_high_value, 'YYYY-MM-DD'));
            -- 检查分区是否需要删除            IF v_partition_high_value < v_cutoff_date THEN                v_sql := 'ALTER TABLE ' || part_rec.table_owner || '.' || part_rec.table_name                        || ' DROP PARTITION ' || part_rec.partition_name;                DBMS_OUTPUT.PUT_LINE('Executing: ' || v_sql);                EXECUTE IMMEDIATE v_sql;                                DBMS_OUTPUT.PUT_LINE('Successfully dropped partition: ' || part_rec.partition_name);            END IF;
        EXCEPTION            WHEN OTHERS THEN                DBMS_OUTPUT.PUT_LINE('Error processing partition ' || part_rec.table_owner || '.' || part_rec.table_name                    || ': ' || SQLERRM);        END;    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Partition cleanup completed.');EXCEPTION    WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE('Critical error: ' || SQLERRM);        RAISE;END drop_old_partitions;/

BEGIN    DBMS_SCHEDULER.CREATE_JOB(        job_name        => 'auto_drop_old_partitions',        job_type        => 'PLSQL_BLOCK',        job_action      => 'BEGIN drop_old_partitions(''SYSTEM''); END;',         start_date      => SYSTIMESTAMP,        repeat_interval => 'FREQ=DAILY; BYHOUR=2;',        enabled         => TRUE,        comments        => 'Automatically drop old interval partitions'    );END;/

-- 查询所有定时任务（当前用户）SELECT job_name, enabled, state, last_start_date, next_run_date, repeat_intervalFROM dba_scheduler_jobs WHERE JOB_NAME='AUTO_DROP_OLD_PARTITIONS'
-- 查询指定任务详情SELECT * FROM dba_scheduler_jobs WHERE job_name = 'AUTO_DROP_OLD_PARTITIONS';
-- 查看任务失败详情SELECT job_name, actual_start_date, run_duration, error#, additional_infoFROM dba_scheduler_job_run_detailsWHERE job_name = 'AUTO_DROP_OLD_PARTITIONS'AND status = 'FAILED';

 

四、总结

 

自动化不是取代DBA，而是将精力从重复劳动转移到架构优化，立即部署这些脚本，告别996！

 

图片

 

作者丨詹姆斯邦德007
来源丨公众号：IT邦德（ID：jeamesDB）
dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn
热门文章
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节，那么长度值可能…

访客 2024年03月04日

只能说作者太用心了，优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年（即30岁左右），能做到年入40w-50w；有…

访客 2023年08月20日

230721

活动预告
Copyright © 广州新炬网络技术有限公司 All rights reserved. 粤ICP备2024180830号-4