# 1. 查看表的列定义
SQL> select column_name, data_type, nullable, data_default
from dba_tab_columns
where table_name = ‘EMP’
order by column_id;

COLUMN_NAME DATA_TYPE N DATA_DEFAULT
—————————— ——————– – ——————————
EMPNO NUMBER N
ENAME VARCHAR2 N
JOB VARCHAR2 N
MGR NUMBER Y
HIREDATE DATE N
SAL NUMBER Y
COMM NUMBER Y
DEPTNO NUMBER N

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

CONSTRAINT_NAME C TABLE_NAME STATUS
—————————— – —————————— ——–
SYS_C001234 C EMP ENABLED
SYS_C001235 C EMP ENABLED
SYS_C001236 C EMP ENABLED
SYS_C001237 C EMP ENABLED

# 3. 查看约束列信息
SQL> select c.constraint_name, cc.column_name, c.search_condition
from dba_constraints c, dba_cons_columns cc
where c.constraint_name = cc.constraint_name
and c.table_name = ‘EMP’
and c.constraint_type = ‘C’;

CONSTRAINT_NAME COLUMN_NAME SEARCH_CONDITION
—————————— —————————– ——————————
SYS_C001234 EMPNO “EMPNO” IS NOT NULL
SYS_C001235 ENAME “ENAME” IS NOT NULL
SYS_C001236 JOB “JOB” IS NOT NULL
SYS_C001237 HIREDATE “HIREDATE” IS NOT NULL

# 4. 查询有默认值的列
SQL> select column_name, data_type, data_default
from dba_tab_columns
where table_name = ‘EMP’
and data_default is not null;

COLUMN_NAME DATA_TYPE DATA_DEFAULT
—————————— ——————– ——————————
HIREDATE DATE SYSDATE

# 5. 添加默认值
SQL> alter table emp modify (hiredate default sysdate);

Table altered.

# 6. 添加NOT NULL约束
SQL> alter table emp modify (sal not null);

Table altered.

# 7. 删除NOT NULL约束
SQL> alter table emp modify (sal null);

Table altered.

# 8. 验证列定义
SQL> select column_name, nullable, data_default
from dba_tab_columns
where table_name = ‘EMP’
and column_name in (‘EMPNO’, ‘ENAME’, ‘HIREDATE’, ‘SAL’);

COLUMN_NAME N DATA_DEFAULT
—————————— – ——————————
EMPNO N
ENAME N
HIREDATE N SYSDATE
SAL Y

# 1. 创建数据验证存储过程
SQL> create or replace procedure validate_not_null_columns(
p_table_name in varchar2,
p_data in sys_refcursor
) as
v_column_name varchar2(100);
v_nullable varchar2(1);
v_sql varchar2(4000);
v_count number;
begin
— 查询NOT NULL列
for rec in (
select column_name, nullable
from dba_tab_columns
where table_name = p_table_name
and nullable = ‘N’
order by column_id
) loop
dbms_output.put_line(‘Checking column: ‘ || rec.column_name);

— 检查是否有NULL值
v_sql := ‘select count(*) from ‘ || p_table_name ||
‘ where ‘ || rec.column_name || ‘ is null’;
execute immediate v_sql into v_count;

if v_count > 0 then
dbms_output.put_line(‘Warning: Found ‘ || v_count || ‘ NULL values in column ‘ || rec.column_name);
else
dbms_output.put_line(‘Column ‘ || rec.column_name || ‘ has no NULL values’);
end if;
end loop;
end validate_not_null_columns;
/

Procedure created.

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

Checking column: EMPNO
Column EMPNO has no NULL values
Checking column: ENAME
Column ENAME has no NULL values
Checking column: JOB
Column JOB has no NULL values
Checking column: HIREDATE
Column HIREDATE has no NULL values
Checking column: DEPTNO
Column DEPTNO has no NULL values

PL/SQL procedure successfully completed.

# 3. 创建插入数据验证函数
SQL> create or replace function validate_insert_data(
p_table_name in varchar2,
p_column_name in varchar2,
p_value in varchar2
) return boolean as
v_nullable varchar2(1);
v_data_type varchar2(100);
begin
— 查询列定义
select nullable, data_type
into v_nullable, v_data_type
from dba_tab_columns
where table_name = p_table_name
and column_name = p_column_name;

— 检查NOT NULL约束
if v_nullable = ‘N’ and p_value is null then
dbms_output.put_line(‘Error: Column ‘ || p_column_name || ‘ cannot be NULL’);
return false;
end if;

— 检查数据类型
if p_value is not null then
if v_data_type like ‘NUMBER%’ then
if not regexp_like(p_value, ‘^[0-9.-]+$’) then
dbms_output.put_line(‘Error: Invalid number format for column ‘ || p_column_name);
return false;
end if;
elsif v_data_type like ‘DATE%’ then
begin
execute immediate ‘select to_date(:1, ”YYYY-MM-DD”) from dual’
using p_value;
exception
when others then
dbms_output.put_line(‘Error: Invalid date format for column ‘ || p_column_name);
return false;
end;
end if;
end if;

return true;
end validate_insert_data;
/

Function created.

# 4. 测试验证函数
SQL> set serveroutput on
SQL> begin
if validate_insert_data(‘EMP’, ‘EMPNO’, ‘7369’) then
dbms_output.put_line(‘Validation passed’);
end if;

if not validate_insert_data(‘EMP’, ‘EMPNO’, null) then
dbms_output.put_line(‘Validation failed as expected’);
end if;
end;
/

Validation passed
Error: Column EMPNO cannot be NULL
Validation failed as expected

PL/SQL procedure successfully completed.

# 1. 创建ORA-01400错误监控脚本
$ vi /home/oracle/scripts/monitor_ora01400.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-01400错误监控脚本

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/ora01400_errors.log”
EMAIL=”admin@fgedu.net.cn”

# 检查ORA-01400错误
check_ora01400_errors() {
local last_check_file=”/home/oracle/scripts/last_ora01400_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-01400错误
local ora_errors=$(grep “ORA-01400” /oracle/app/oracle/diag/rdbms/fgedudb/FGEDUDB/trace/alert_FGEDUDB.log | tail -10)

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

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

# 分析约束错误
analyze_notnull_errors
fi

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

# 分析NOT NULL错误
analyze_notnull_errors() {
# 查询NOT NULL列信息
local notnull_info=$(sqlplus -s / as sysdba < exit
EOF
)

echo “NOT NULL Columns:” >> “$ERROR_LOG”
echo “$notnull_info” >> “$ERROR_LOG”
echo “” >> “$ERROR_LOG”
}

# 主函数
main() {
check_ora01400_errors

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

# 执行主函数
main

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

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

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

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

# 1. 创建NOT NULL约束监控视图
SQL> create or replace view notnull_constraint_monitor as
select
tc.table_name,
tc.column_name,
tc.data_type,
tc.data_length,
tc.nullable,
tc.data_default,
c.constraint_name
from dba_tab_columns tc
left join dba_cons_columns cc on tc.table_name = cc.table_name
and tc.column_name = cc.column_name
left join dba_constraints c on cc.constraint_name = c.constraint_name
and c.constraint_type = ‘C’
where tc.nullable = ‘N’
and tc.owner = ‘SCOTT’;

View created.

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

TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH N DATA_DEFAULT CONSTRAINT_NAME
—————————— —————————— ———- ———– – ——————— ——————————
EMP EMPNO NUMBER 22 N SYS_C001234
EMP ENAME VARCHAR2 10 N SYS_C001235
EMP JOB VARCHAR2 9 N SYS_C001236
EMP HIREDATE DATE 7 N SYSDATE SYS_C001237
EMP DEPTNO NUMBER 22 N SYS_C001238

# 3. 创建NULL值检查视图
SQL> create or replace view null_value_check as
select
‘EMP’ as table_name,
‘EMPNO’ as column_name,
count(*) as null_count
from emp
where empno is null
union all
select ‘EMP’, ‘ENAME’, count(*) from emp where ename is null
union all
select ‘EMP’, ‘JOB’, count(*) from emp where job is null
union all
select ‘EMP’, ‘HIREDATE’, count(*) from emp where hiredate is null
union all
select ‘EMP’, ‘DEPTNO’, count(*) from emp where deptno is null;

View created.

# 4. 查询NULL值检查结果
SQL> select * from null_value_check;

TABLE_NAME COLUMN_NAME NULL_COUNT
—————————— —————————— ———-
EMP EMPNO 0
EMP ENAME 0
EMP JOB 0
EMP HIREDATE 0
EMP DEPTNO 0

# 5. 创建ORA-01400错误告警
SQL> create or replace procedure ora01400_error_alert as
v_notnull_count number;
v_null_value_count number;
v_email varchar2(100) := ‘admin@fgedu.net.cn’;
begin
— 获取NOT NULL列数量
select count(*) into v_notnull_count
from dba_tab_columns
where nullable = ‘N’
and owner = ‘SCOTT’;

— 获取NULL值数量
select sum(null_count) into v_null_value_count
from null_value_check;

dbms_output.put_line(‘NOT NULL columns: ‘ || v_notnull_count);
dbms_output.put_line(‘NULL values found: ‘ || v_null_value_count);

if v_null_value_count > 0 then
dbms_output.put_line(‘— NULL Value Details —‘);
for rec in (select * from null_value_check where null_count > 0) loop
dbms_output.put_line(‘Table: ‘ || rec.table_name);
dbms_output.put_line(‘Column: ‘ || rec.column_name);
dbms_output.put_line(‘NULL Count: ‘ || rec.null_count);
end loop;

dbms_output.put_line(‘Recommendation: Fix NULL values in NOT NULL columns’);
else
dbms_output.put_line(‘All NOT NULL columns have valid values’);
end if;
end ora01400_error_alert;
/

Procedure created.

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

NOT NULL columns: 5
NULL values found: 0
All NOT NULL columns have valid values

PL/SQL procedure successfully completed.

# 7. 创建定时任务
SQL> begin
dbms_scheduler.create_job(
job_name => ‘ORA01400_ERROR_ALERT_JOB’,
job_type => ‘STORED_PROCEDURE’,
job_action => ‘ORA01400_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, job, deptno)
values (8000, ‘TEST’, null, 20);

insert into emp (empno, ename, job, deptno)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“SCOTT”.”EMP”.”JOB”)

# 分析步骤

# 1. 查看列定义
SQL> select column_name, nullable, data_default
from dba_tab_columns
where table_name = ‘EMP’
and column_name = ‘JOB’;

COLUMN_NAME N DATA_DEFAULT
—————————— – ——————————
JOB N

# 2. 查看约束信息
SQL> select constraint_name, search_condition
from dba_constraints
where table_name = ‘EMP’
and constraint_type = ‘C’
and search_condition like ‘%JOB%’;

CONSTRAINT_NAME SEARCH_CONDITION
—————————— ——————————
SYS_C001236 “JOB” IS NOT NULL

# 3. 分析错误原因
# 错误代码：ORA-01400
# 错误信息：cannot insert NULL into (“SCOTT”.”EMP”.”JOB”)
# 约束名称：SYS_C001236
# 约束类型：CHECK约束
# 冲突列：JOB
# 错误原因：试图向NOT NULL列插入NULL值

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

1 row created.

SQL> commit;

Commit complete.

# 5. 解决方案2：设置默认值
SQL> alter table emp modify (job default ‘CLERK’);

Table altered.

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

1 row created.

SQL> commit;

Commit complete.

# 6. 解决方案3：删除NOT NULL约束（不推荐）
SQL> alter table emp modify (job null);

Table altered.

SQL> insert into emp (empno, ename, deptno)
values (8002, ‘TEST2’, 20);

1 row created.

SQL> commit;

Commit complete.

# 7. 恢复NOT NULL约束
SQL> alter table emp modify (job not null);

Table altered.

# 8. 预防措施
# – 为NOT NULL列设置默认值
# – 应用程序验证必填字段
# – 使用触发器自动填充字段
# – 数据导入前验证数据完整性

# 1. 创建测试表
SQL> create table test_import (
id number not null,
name varchar2(50) not null,
email varchar2(100),
created_date date default sysdate not null
);

Table created.

# 2. 准备导入数据（包含NULL值）
$ cat /tmp/test_data.csv
1,John,john@fgedu.net.cn
2,Jane,
3,,jane@fgedu.net.cn
4,Bob,bob@fgedu.net.cn

# 3. 使用SQL*Loader导入
$ cat /tmp/test_import.ctl
LOAD DATA
INFILE ‘/tmp/test_data.csv’
INTO TABLE test_import
FIELDS TERMINATED BY ‘,’
(id, name, email, created_date)

# 4. 执行导入
$ sqlldr scott/tiger control=/tmp/test_import.ctl log=/tmp/test_import.log

SQL*Loader: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.

Commit point reached – logical record count 4

# 5. 查看导入日志
$ cat /tmp/test_import.log

Record 2: Rejected – Error on table TEST_IMPORT, column EMAIL.
ORA-01400: cannot insert NULL into (“SCOTT”.”TEST_IMPORT”.”EMAIL”)
Record 3: Rejected – Error on table TEST_IMPORT, column NAME.
ORA-01400: cannot insert NULL into (“SCOTT”.”TEST_IMPORT”.”NAME”)

Table TEST_IMPORT:
2 Rows successfully loaded.
2 Rows not loaded due to data errors.

# 6. 修改控制文件处理NULL值
$ cat /tmp/test_import_fixed.ctl
LOAD DATA
INFILE ‘/tmp/test_data.csv’
INTO TABLE test_import
FIELDS TERMINATED BY ‘,’
(
id,
name “NVL(:name, ‘UNKNOWN’)”,
email “NVL(:email, ‘N/A’)”,
created_date “NVL(:created_date, SYSDATE)”
)

# 7. 重新导入
$ sqlldr scott/tiger control=/tmp/test_import_fixed.ctl log=/tmp/test_import_fixed.log

SQL*Loader: Release 19.0.0.0.0 – Production on Mon Mar 31 10:00:00 2026
Copyright (c) 1982, 2020, Oracle and/or its affiliates. All rights reserved.

Commit point reached – logical record count 4

# 8. 验证导入结果
SQL> select * from test_import;

ID NAME EMAIL CREATED_D
———- ————————————————– —————————————————- ———
1 John john@fgedu.net.cn 31-MAR-26
2 Jane N/A 31-MAR-26
3 UNKNOWN jane@fgedu.net.cn 31-MAR-26
4 Bob bob@fgedu.net.cn 31-MAR-26