# 1. 查看CHECK约束
SQL> select constraint_name, constraint_type, table_name, status, search_condition
from dba_constraints
where constraint_type = ‘C’
and table_name = ‘EMP’;

CONSTRAINT_NAME C TABLE_NAME STATUS SEARCH_CONDITION
—————————— – —————————— ——– ——————————
SYS_C001234 C EMP ENABLED “EMPNO” IS NOT NULL
SYS_C001235 C EMP ENABLED “ENAME” IS NOT NULL
CK_EMP_SAL C EMP ENABLED sal > 0
CK_EMP_DEPTNO C EMP ENABLED deptno BETWEEN 10 AND 90

# 2. 查看约束列信息
SQL> select constraint_name, column_name
from dba_cons_columns
where table_name = ‘EMP’
and constraint_name like ‘CK%’;

CONSTRAINT_NAME COLUMN_NAME
—————————— —————————–
CK_EMP_SAL SAL
CK_EMP_DEPTNO DEPTNO

# 3. 创建CHECK约束
SQL> alter table emp
add constraint ck_emp_sal
check (sal > 0);

Table altered.

# 4. 创建多列CHECK约束
SQL> alter table emp
add constraint ck_emp_comm
check (comm >= 0 and comm <= sal);

Table altered.

# 5. 创建复杂CHECK约束
SQL> alter table orders
add constraint ck_orders_amount
check (total_amount > 0 and
total_amount <= 1000000 and order_date >= to_date(‘2020-01-01’, ‘YYYY-MM-DD’));

Table altered.

# 6. 禁用CHECK约束
SQL> alter table emp disable constraint ck_emp_sal;

Table altered.

# 7. 启用CHECK约束
SQL> alter table emp enable constraint ck_emp_sal;

Table altered.

# 8. 删除CHECK约束
SQL> alter table emp drop constraint ck_emp_sal;

Table altered.

# 1. 创建数据验证存储过程
SQL> create or replace procedure validate_check_constraints(
p_table_name in varchar2
) as
v_constraint_name varchar2(100);
v_search_condition varchar2(4000);
v_sql varchar2(4000);
v_count number;
begin
— 查询CHECK约束
for rec in (
select constraint_name, search_condition
from dba_constraints
where table_name = p_table_name
and constraint_type = ‘C’
and status = ‘ENABLED’
and search_condition not like ‘%IS NOT NULL’
) loop
dbms_output.put_line(‘Checking constraint: ‘ || rec.constraint_name);
dbms_output.put_line(‘Condition: ‘ || rec.search_condition);

— 检查是否有违反约束的数据
v_sql := ‘select count(*) from ‘ || p_table_name ||
‘ where not (‘ || replace(rec.search_condition, ‘”‘, ”) || ‘)’;
execute immediate v_sql into v_count;

if v_count > 0 then
dbms_output.put_line(‘Warning: Found ‘ || v_count || ‘ rows violating constraint’);
else
dbms_output.put_line(‘All rows satisfy constraint’);
end if;
dbms_output.put_line(”);
end loop;
end validate_check_constraints;
/

Procedure created.

# 2. 执行数据验证
SQL> set serveroutput on
SQL> exec validate_check_constraints(‘EMP’);

Checking constraint: CK_EMP_SAL
Condition: sal > 0
All rows satisfy constraint

Checking constraint: CK_EMP_DEPTNO
Condition: deptno BETWEEN 10 AND 90
All rows satisfy constraint

PL/SQL procedure successfully completed.

# 3. 创建插入数据验证函数
SQL> create or replace function validate_data_against_check(
p_table_name in varchar2,
p_column_name in varchar2,
p_value in number
) return boolean as
v_search_condition varchar2(4000);
v_sql varchar2(4000);
v_result number;
begin
— 查询CHECK约束条件
select search_condition
into v_search_condition
from dba_constraints c, dba_cons_columns cc
where c.constraint_name = cc.constraint_name
and c.table_name = cc.table_name
and c.table_name = p_table_name
and cc.column_name = p_column_name
and c.constraint_type = ‘C’
and c.status = ‘ENABLED’
and rownum = 1;

— 验证数据
v_sql := ‘select count(*) from dual where ‘ ||
replace(replace(v_search_condition, p_column_name, p_value), ‘”‘, ”);
execute immediate v_sql into v_result;

if v_result = 1 then
return true;
else
dbms_output.put_line(‘Error: Value ‘ || p_value || ‘ violates constraint: ‘ || v_search_condition);
return false;
end if;
exception
when no_data_found then
return true;
end validate_data_against_check;
/

Function created.

# 4. 测试验证函数
SQL> set serveroutput on
SQL> begin
if validate_data_against_check(‘EMP’, ‘SAL’, 1000) then
dbms_output.put_line(‘Validation passed for SAL=1000’);
end if;

if not validate_data_against_check(‘EMP’, ‘SAL’, -100) then
dbms_output.put_line(‘Validation failed for SAL=-100 as expected’);
end if;
end;
/

Validation passed for SAL=1000
Error: Value -100 violates constraint: sal > 0
Validation failed for SAL=-100 as expected

PL/SQL procedure successfully completed.

# 1. 创建ORA-02290错误监控脚本
$ vi /home/oracle/scripts/monitor_ora02290.sh

#!/bin/bash
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn
# ORA-02290错误监控脚本

export ORACLE_HOME=/oracle/app/oracle/product/19c/dbhome_1
export ORACLE_SID=FGEDUDB
export PATH=$ORACLE_HOME/bin:$PATH

# 定义变量
ERROR_LOG=”/home/oracle/scripts/ora02290_errors.log”
EMAIL=”admin@fgedu.net.cn”

# 检查ORA-02290错误
check_ora02290_errors() {
local last_check_file=”/home/oracle/scripts/last_ora02290_check.txt”
local current_time=$(date +%s)

if [ -f “$last_check_file” ]; then
local last_check=$(cat “$last_check_file”)
local time_diff=$((current_time – last_check))

if [ $time_diff -lt 3600 ]; then
echo “Last check was $time_diff seconds ago, skipping…”
return 0
fi
fi

# 检查告警日志中的ORA-02290错误
local ora_errors=$(grep “ORA-02290” /oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log | tail -10)

if [ -n “$ora_errors” ]; then
echo “Found ORA-02290 errors:” >> “$ERROR_LOG”
echo “$ora_errors” >> “$ERROR_LOG”
echo “Timestamp: $(date)” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”

# 发送告警邮件
echo “ORA-02290 error detected: check constraint violation” | mail -s “ORA-02290 Alert” “$EMAIL”

# 分析约束错误
analyze_check_errors
fi

# 更新最后检查时间
echo “$current_time” > “$last_check_file”
}

# 分析CHECK约束错误
analyze_check_errors() {
# 查询CHECK约束信息
local check_info=$(sqlplus -s / as sysdba < exit
EOF
)

echo “CHECK Constraints:” >> “$ERROR_LOG”
echo “$check_info” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}

# 主函数
main() {
check_ora02290_errors

echo “ORA-02290 error monitoring completed at $(date)” >> “$ERROR_LOG”
}

# 执行主函数
main

# 2. 设置脚本权限
$ chmod +x /home/oracle/scripts/monitor_ora02290.sh

# 3. 测试脚本
$ /home/oracle/scripts/monitor_ora02290.sh

# 4. 设置定期监控
$ crontab -e

# 每小时检查ORA-02290错误
0 * * * * /home/oracle/scripts/monitor_ora02290.sh >> /home/oracle/scripts/monitor_ora02290.log 2>&1

# 1. 创建CHECK约束监控视图
SQL> create or replace view check_constraint_monitor as
select
c.constraint_name,
c.table_name,
c.status,
c.validated,
c.search_condition,
(select column_name from dba_cons_columns cc
where cc.constraint_name = c.constraint_name
and cc.table_name = c.table_name
and rownum = 1) as column_name
from dba_constraints c
where c.constraint_type = ‘C’
and c.status = ‘ENABLED’
and c.search_condition not like ‘%IS NOT NULL’;

View created.

# 2. 查询CHECK约束状态
SQL> select * from check_constraint_monitor where rownum <= 10;

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED COLUMN_NAME SEARCH_CONDITION
—————————— —————————— ——– ————- ————— ——————————
CK_EMP_SAL EMP ENABLED VALIDATED SAL sal > 0
CK_EMP_DEPTNO EMP ENABLED VALIDATED DEPTNO deptno BETWEEN 10 AND 90
CK_ORDERS_AMOUNT ORDERS ENABLED VALIDATED TOTAL_AMOUNT total_amount > 0

# 3. 创建约束违反检查视图
SQL> create or replace view constraint_violation_check as
select
‘EMP’ as table_name,
‘SAL’ as column_name,
‘CK_EMP_SAL’ as constraint_name,
count(*) as violation_count
from emp
where sal <= 0 union all select 'EMP', 'DEPTNO', 'CK_EMP_DEPTNO', count(*) from emp where deptno not between 10 and 90;

View created.

# 4. 查询约束违反情况
SQL> select * from constraint_violation_check;

TABLE_NAME COLUMN_NAME CONSTRAINT_NAME VIOLATION_COUNT
—————————— —————————— —————————— —————
EMP SAL CK_EMP_SAL 0
EMP DEPTNO CK_EMP_DEPTNO 0

# 5. 创建ORA-02290错误告警
SQL> create or replace procedure ora02290_error_alert as
v_check_count number;
v_violation_count number;
v_email varchar2(100) := ‘admin@fgedu.net.cn’;
begin
— 获取CHECK约束数量
select count(*) into v_check_count
from dba_constraints
where constraint_type = ‘C’
and status = ‘ENABLED’
and search_condition not like ‘%IS NOT NULL’;

— 获取违反约束的数据数量
select sum(violation_count) into v_violation_count
from constraint_violation_check;

dbms_output.put_line(‘CHECK constraints: ‘ || v_check_count);
dbms_output.put_line(‘Violations found: ‘ || v_violation_count);

if v_violation_count > 0 then
dbms_output.put_line(‘— Constraint Violations —‘);
for rec in (select * from constraint_violation_check where violation_count > 0) loop
dbms_output.put_line(‘Table: ‘ || rec.table_name);
dbms_output.put_line(‘Column: ‘ || rec.column_name);
dbms_output.put_line(‘Constraint: ‘ || rec.constraint_name);
dbms_output.put_line(‘Violations: ‘ || rec.violation_count);
end loop;

dbms_output.put_line(‘Recommendation: Fix data or adjust constraint’);
else
dbms_output.put_line(‘All data satisfies CHECK constraints’);
end if;
end ora02290_error_alert;
/

Procedure created.

# 6. 执行告警存储过程
SQL> set serveroutput on
SQL> exec ora02290_error_alert;

CHECK constraints: 3
Violations found: 0
All data satisfies CHECK constraints

PL/SQL procedure successfully completed.

# 7. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘ORA02290_ERROR_ALERT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘ORA02290_ERROR_ALERT’,
start_date => systimestamp,
repeat_interval => ‘FREQ=DAILY;INTERVAL=1’,
enabled => true
);
end;
/

PL/SQL procedure successfully completed.

# 问题现象
SQL> insert into emp (empno, ename, sal, deptno)
values (8000, ‘TEST’, -100, 20);

insert into emp (empno, ename, sal, deptno)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_EMP_SAL) violated

# 分析步骤

# 1. 查看约束信息
SQL> select constraint_name, search_condition, status
from dba_constraints
where constraint_name = ‘CK_EMP_SAL’;

CONSTRAINT_NAME SEARCH_CONDITION STATUS
—————————— —————————— ——–
CK_EMP_SAL sal > 0 ENABLED

# 2. 查看约束列信息
SQL> select constraint_name, column_name
from dba_cons_columns
where constraint_name = ‘CK_EMP_SAL’;

CONSTRAINT_NAME COLUMN_NAME
—————————— —————————–
CK_EMP_SAL SAL

# 3. 分析错误原因
# 错误代码：ORA-02290
# 错误信息：check constraint (SCOTT.CK_EMP_SAL) violated
# 约束名称：CK_EMP_SAL
# 约束条件：sal > 0
# 冲突值：-100
# 错误原因：试图插入负数的工资值

# 4. 解决方案1：提供有效值
SQL> insert into emp (empno, ename, sal, deptno)
values (8000, ‘TEST’, 1000, 20);

1 row created.

SQL> commit;

Commit complete.

# 5. 解决方案2：修改约束条件（不推荐）
SQL> alter table emp drop constraint ck_emp_sal;

Table altered.

SQL> alter table emp
add constraint ck_emp_sal
check (sal >= 0);

Table altered.

# 6. 解决方案3：禁用约束（不推荐）
SQL> alter table emp disable constraint ck_emp_sal;

Table altered.

SQL> insert into emp (empno, ename, sal, deptno)
values (8001, ‘TEST1’, -100, 20);

1 row created.

SQL> commit;

Commit complete.

# 7. 启用约束（需要先修复数据）
SQL> update emp set sal = 0 where sal < 0;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter table emp enable constraint ck_emp_sal;

Table altered.

# 8. 预防措施
# – 应用程序验证数据范围
# – 使用触发器验证数据
# – 定期检查约束违反
# – 制定数据质量标准

# 1. 创建复杂CHECK约束
SQL> create table products (
product_id number primary key,
product_name varchar2(100) not null,
category varchar2(50),
price number,
discount number,
status varchar2(20),
constraint ck_product_price check (price > 0),
constraint ck_product_discount check (discount >= 0 and discount <= price), constraint ck_product_status check (status in ('ACTIVE', 'INACTIVE', 'DISCONTINUED')), constraint ck_product_category check (category in ('ELECTRONICS', 'CLOTHING', 'FOOD', 'BOOKS')) );

Table created.

# 2. 测试CHECK约束
SQL> insert into products values (1, ‘Laptop’, ‘ELECTRONICS’, 1000, 50, ‘ACTIVE’);

1 row created.

SQL> insert into products values (2, ‘Phone’, ‘ELECTRONICS’, -100, 0, ‘ACTIVE’);

insert into products values (2, ‘Phone’, ‘ELECTRONICS’, -100, 0, ‘ACTIVE’)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_PRODUCT_PRICE) violated

SQL> insert into products values (3, ‘Tablet’, ‘ELECTRONICS’, 500, 600, ‘ACTIVE’);

insert into products values (3, ‘Tablet’, ‘ELECTRONICS’, 500, 600, ‘ACTIVE’)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_PRODUCT_DISCOUNT) violated

SQL> insert into products values (4, ‘Watch’, ‘ELECTRONICS’, 200, 10, ‘PENDING’);

insert into products values (4, ‘Watch’, ‘ELECTRONICS’, 200, 10, ‘PENDING’)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_PRODUCT_STATUS) violated

# 3. 创建多表CHECK约束（使用触发器）
SQL> create or replace trigger trg_check_order_amount
before insert or update on orders
for each row
declare
v_max_amount number;
begin
— 查询客户最大订单金额限制
select max_order_amount
into v_max_amount
from customers
where customer_id = :new.customer_id;

if :new.total_amount > v_max_amount then
raise_application_error(-20001,
‘Order amount ‘ || :new.total_amount ||
‘ exceeds customer limit ‘ || v_max_amount);
end if;
end;
/

Trigger created.

# 4. 测试触发器约束
SQL> insert into orders (order_id, customer_id, total_amount)
values (1001, 100, 50000);

insert into orders (order_id, customer_id, total_amount)
*
ERROR at line 1:
ORA-20001: Order amount 50000 exceeds customer limit 10000
ORA-06512: at “SCOTT.TRG_CHECK_ORDER_AMOUNT”, line 10
ORA-04088: error during execution of trigger ‘SCOTT.TRG_CHECK_ORDER_AMOUNT’

# 5. 创建条件CHECK约束
SQL> alter table employees
add constraint ck_emp_salary_dept
check (
(department_id = 10 and salary >= 5000) or
(department_id = 20 and salary >= 4000) or
(department_id = 30 and salary >= 3000) or
(department_id not in (10, 20, 30))
);

Table altered.

# 6. 测试条件约束
SQL> insert into employees (employee_id, name, department_id, salary)
values (1001, ‘John’, 10, 4000);

insert into employees (employee_id, name, department_id, salary)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CK_EMP_SALARY_DEPT) violated

SQL> insert into employees (employee_id, name, department_id, salary)
values (1001, ‘John’, 10, 6000);

1 row created.

SQL> commit;

Commit complete.