导读
作者：杨漆
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任务
atrm 9 ## 9为即将执行的任务号