背景：环境为ORACLE测试环境，版本11.204，牵涉到OGG的同步，上游环境研发手动修改列为非空，由于下游表对应列存在空值，同步过来的DDL语句会执行失败，进一步导致同步进程ABENED。
主要目标 想建一个触发器，当检测到要执行修改列为非空时，提前删除表内所有内容，来防止DDL执行失败，同步ABENED。
设计环境为 oracle 11g 11.204
模拟表：create test001 as select * from dba_tables;
update test001 set IOT_TYPE='' where 1=1;
模拟语句情况1：alter table test001 modify IOT_TYPE DEFAULT 0 NOT NULL;
模拟语句情况2：alter table test001 modify IOT_TYPE NOT NULL;
此时由于test001表的IOT_TYPE列存在空值，会导致该alter执行不成功。
故想要增加一个触发器，实现当检测到类似格式的DDL语句执行时，在执行DDL语句前先将这张表清空。
期望触发器实现效果：delete from test001 where IOT_TYPE is null; （最优）
delete from test001 where 1=1; （次选，也可以）
这个会判断ddl类型是modify not null, 才会根据modify的字段进行delete:
create or replace trigger ddl_trigger_test001
before alter on SCHEMA
declare
v_ddl_sql clob;
v_sql_text_piece ora_name_list_t;
n pls_integer;
v_tabname varchar2(128);
v_colname varchar2(128);
v_sql_delete varchar2(500);
begin
n := ora_sql_txt(v_sql_text_piece);
FOR i IN 1 .. n
LOOP
v_ddl_sql := v_ddl_sql || v_sql_text_piece(i);
END LOOP;
v_ddl_sql:=upper(trim(regexp_replace(v_ddl_sql,'\s+',' ')));
if regexp_like(v_ddl_sql,'alter table \w+ modify \w+ not null','i') then
v_tabname:= regexp_substr(v_ddl_sql,'ALTER table (\w+) modify (\w+) not null',1,1,'i',1);
v_colname:= regexp_substr(v_ddl_sql,'ALTER table (\w+) modify (\w+) not null',1,1,'i',2);
if ( ora_sysevent = 'ALTER' and ora_dict_obj_type='TABLE' and ora_dict_obj_name=v_tabname and ora_is_alter_column(v_colname))
then
v_sql_delete:='delete from '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' where '||v_colname||' is null';
execute immediate v_sql_delete;
end if;
end if;
end;
/
评论
有用 1
select count(*) from test001;
COUNT(*)
----------
2885
create or replace trigger ddl_trigger_test001
before alter on SCHEMA
declare
l_sysevent varchar2(25);
begin
select ora_sysevent into l_sysevent from dual;
if ( l_sysevent = 'ALTER' )
then
delete from test001 where IOT_TYPE is null;
end if;
end;
/
--Trigger created.
alter table test001 modify IOT_TYPE NOT NULL;
Table altered.
select count(*) from test001;
COUNT(*)
----------
0
评论 有用 1还有几个变量可以设置, 你可以参考一下. alter table 命令挺多的, 没法细分到modify 哪个字段, 是否是not null, 这个trigger 你要想好了.
create or replace trigger ddl_trigger_test001
before alter on SCHEMA
begin
if ( ora_sysevent = 'ALTER' and ora_dict_obj_type='TABLE' and ora_is_alter_column('IOT_TYPE'))
then
execute immediate 'delete from '||ora_dict_obj_owner||'.'||ora_dict_obj_name||' where IOT_TYPE is null';
end if;
end;
/
评论 有用 1之前做过一个类似的，直接通过shell+sql实现，可以钉钉告警或写入日志,可以参考下
1. 统计空行情况，若需要告警或写入日志
cat sql1.sql
set heading off
select count(1) from test001 where IOT_TYPE is null;
复制2. 删除空行，当然也可以做更新字段，按需调整
cat sql2.sql
set heading off
delete from test001 where IOT_TYPE is null;
commit;
复制3. Shell脚本
cat del_null_rows.sh
!/bin/bash
. /home/oracle/.bash_profile
now_date=`date "+%Y-%m-%d_%H:%M:%S"`
echo $now_date
host=`/usr/sbin/ifconfig |sed -n "2p"|awk '{print $2}'`
del_null_rows(){
num=`sqlplus -s / as sysdba <<EOF
start /data/script/sql1.sql
exit
EOF`
if [ $num -gt 0 ] ;then
echo " 【 $now_date 】主机 ${host}：test001.IOT_TYPE为null的有 ${num} 条,已自动处理" >>
gx=`sqlplus -s / as sysdba <<EOF
start /data/script/sql2.sql
exit
EOF`
echo -e "\033[32m*******【 $now_date 】主机 ${host}：删除${gx}条记录 \033[0m"
else
echo -e "\033[32m*******【 $now_date 】主机 ${host}：无须处理 \033[0m"
fi
}
check(){
del_null_rows
}
check
复制4. crontab 任务
*/30 * * * * /data/script/del_null_rows.sh>>/data/script/logs/del_null_rows.log 2>&1复制
评论 有用 0
墨值悬赏