DBA不用再半夜手工执行枯燥的大表DDL Online变更

**导读**

> 作者：杨漆

> 16年关系型数据库管理，从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM；运维路上不平坦，跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿，希望帮到大家少走弯路、少熬夜。

Online业务，大表DDL都只能在业务低谷期执行，通常为凌晨4点左右，这是人最想睡觉的时候，而DBA却要在这个时点起来工作。

怎样减轻DBA的痛苦,将只能在半夜执行的批量DDL变更让机器自动执行？

答：1.设置定时一次性脚本任务

2.将执行结果批量检查，结果记录到一个日志中，便于DBA检查

## 任务脚本

cat /u01/app/alter_columns/alter_columns.sh

#!/bin/bash

# IP

source ~/.bash_profile

script_dir=`dirname $0`

sqlplus -S "/ as sysdba" <<!

set feedback off

## 批量DDL (都是过亿的大表，执行时间较长)

alter table PPP.CHANNEL_RESUME modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order rebuild online parallel 10;

alter table PPP.CHANNEL_RESUME_a1 modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order_a1 rebuild online parallel 10;

alter table PPP.CHANNEL_RESUME_a2 modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order_a2 rebuild online parallel 10;

alter table PPP.CHANNEL_RESUME_a3 modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order_a3 rebuild online parallel 10;

alter table PPP.CHANNEL_RESUME_a4 modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order_a4 rebuild online parallel 10;

alter table PPP.CHANNEL_RESUME_a5 modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order_a5 rebuild online parallel 10;

alter table PPP.CHANNEL_RESUME_a6 modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order_a6 rebuild online parallel 10;

alter table PPP.CHANNEL_RESUME_a7 modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order_a7 rebuild online parallel 10;

alter table PPP.CHANNEL_RESUME_a8 modify channel_order varchar2(80) parallel 10;

alter index PPP.idx_channel_order_a8 rebuild online parallel 10;

### 修改字段（上千万的大表启用并行参数，速度更快）

alter table PPP.CHANNEL_RESUME_SUCCESS_ORDER modify channel_order_num varchar2(80) parallel 10;

### 在线重建索引（上千万的大表启用并行参数，速度更快）

alter index PPP.idx_pay_resume_detail_p rebuild online parallel 10;

!

function tbs_query {

sqlplus -S "/ as sysdba" <<!

set linesize 400

set pagesize 200

set feed off

col OWNER for a10

col TABLE_NAME for a30

col DATA_TYPE for a20

col DATA_LENGTH for 999

-- Query columns 将所有字段修改的结果放到日志里便于观察

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME' and COLUMN_NAME=upper('channel_order');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_A1' and COLUMN_NAME=upper('channel_order_a1');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_A2' and COLUMN_NAME=upper('channel_order_a2');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_A3' and COLUMN_NAME=upper('channel_order_a3');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_A4' and COLUMN_NAME=upper('channel_order_a4');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_A5' and COLUMN_NAME=upper('channel_order_a5');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_A6' and COLUMN_NAME=upper('channel_order_a6');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_A7' and COLUMN_NAME=upper('channel_order_a7');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_A8' and COLUMN_NAME=upper('channel_order_a8');

select OWNER,TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from dba_tab_columns where owner='PPP' and TABLE_NAME='CHANNEL_RESUME_SUCCESS_ORDER' and COLUMN_NAME=upper('channel_order_num');

!

}

tbs_query 2>&1 | sed '/^$/d' | tee $script_dir/results/alter_columns_result_`date +%Y%m%d.rlt`

#### 一次性到点自动执行脚本

## 按装at 软件包

yum -y install at

## 查看at软件包是否已安装

rpm -q at

反馈结果：

at-3.1.8-84.el5

## 确定at安装

chkconfig --list | grep atd

## 启动at服务

service atd restart

/etc/init.d/atd {start|stop|restart|condrestart|status}

chkconfig atd on

## at的访问控制

1.如果系统中有/etc/at.allow文件，那么只有写入/etc/at.allow文件（白名单）中的用户可以使用at命令（/etc/at.deny文件会被忽略）。

2.如果系统中没有/etc/at.allow文件，只有/etc/at.deny文件，那么写入/etc/at.deny文件（黑名单）中的用户不能使用at命令。对root不起作用。

3.如果两个文件都不存在，那么只有root用户可以使用at命令。

设置一次性定时执行任务（at软件都ok的话，可直接执行下面步骤）

## at now + 1 minutes

## at 5pm+3 days

## at -f /u01/app/alter_columns/alter_columns.sh 04:01 2021-07-12

at 4:00 2021-07-12

at> sh /u01/app/alter_columns/alter_columns.sh

crtl + D ## 保存退出

## 查询当前服务器上的at工作

atq

## 显示已经设置的任务内容

at -c 9 ## 9为任务号

## 删除指定的at任务

atrm9 ## 9为即将执行的任务号