Hi SANNAHA

A Link is a Promise.

0%

Oracle错误代码与处理

发表于 更新于

使用 Oracle 过程中遇到的错误代码与处理方法。

ORA-00054

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

锁表了，重试、等待、增加超时时间。

ORA-01400

ORA-01400: cannot insert NULL into ("XXX_SODS"."XXX_INFO"."XXX_ID").

试图将NULL值插入非空约束的列中。

ORA-01427

ORA-01427: 单行子查询返回多个行。

1
2
3
4
5
UPDATE XXX_INFO A
  SET A.XXX_NAME =
    (SELECT B.XXX_NAME
      FROM TEMP_XXX_INFO B
      WHERE B.XXX_CODE = A.XXX_CODE);

怀疑是符合 B.XXX_CODE = A.XXX_CODE 条件的B.XXX_NAME有多条，使用下面的SQL进行验证：

1
2
select * from XXX_INFO where XXX_CODE in 
  (select XXX_CODE from XXX_INFO group by XXX_CODE having count(XXX_CODE) > 1);

查询结果不为空，表明确实有重复数据。结合实际情况发现 XXX_NAME 中的值都是相同的，任取一条即可，可以使用 distinct 或进行如下修改：

1
2
3
4
5
6
UPDATE XXX_INFO A
  SET A.XXX_NAME =
    (SELECT B.XXX_NAME
      FROM TEMP_XXX_INFO B
-     WHERE B.XXX_CODE = A.XXX_CODE);
+     WHERE B.XXX_CODE = A.XXX_CODE and rownum=1);

ORA-01481

ORA-01481: 无效数字格式模型。

原来的字段 `I_STARTDATE` 的数据类型就是字符串 `VARCHAR2` 而非 `DATE`，所以使用 `to_char(fieldname,'yyyymmdd')` 时函数就无法正确判断了，也就是提示数字格式模型无效。

这种情况下，可以使用 TO_DATE 转为 DATE 类型再继续使用 TO_CHAR()。

1
V_STARTDATE := TO_CHAR(TO_DATE(I_STARTDATE, 'YYYY-MM-DD'), 'YYYYMMDD');

ORA-01653

ORA-01653: 表 A 无法通过 1024 (在表空间 XXX_ODS 中) 扩展。

表空间不足，可能没有开启自动扩展，或者达到了自动扩展的上限（默认 32 GB）。
  1. 查看表空间使用信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--查看表空间使用信息及占用率
SELECT c.ts#, c.name,  d.contents, d.extent_management, e.file_bytes, c.used,
       SUBSTR (c.used / e.file_bytes * 100, 1, 5) "使用率"
  FROM (SELECT name, ts#, SUM(used) used
          FROM (SELECT a.allocated_space * (SELECT value  -- 查询db_block_size当前值
                                              FROM v$parameter
                                             WHERE name = 'db_block_size')/1024/1024 used,
                        b.ts#, b.name
                  FROM v$filespace_usage a, v$tablespace b
         WHERE a.tablespace_id = b.ts#)
         GROUP BY name, ts#) c,
        dba_tablespaces d,
       (SELECT ts#, SUM(bytes)/1024/1024 file_bytes
          FROM v$datafile
         GROUP BY ts#) e      
 WHERE c.name = d.tablespace_name
   AND e.ts# = c.ts#
 ORDER BY ts#

1574411937563

  1. 查看表空间名称、对应的 DATAFILE 位置及大小
1
2
3
--查看表空间名称、对应的DATAFILE位置及大小
select tablespace_name, file_id,file_name,round(bytes/1024/1024,0) total_space
 from dba_data_files order by tablespace_name;

1574412112610

  1. 增加表空间大小或添加新的 DATAFILE
1
2
3
4
5
6
--扩展表空间大小
ALTER DATABASE DATAFILE '/oracle/oradata/orcl/.../XXX_ODS.dbf' RESIZE 30G;

--添加新的DATAFILE
ALTER TABLESPACE XXX_ODS
  ADD DATAFILE '/oracle/oradata/orcl/.../XXX_ODS_2.dbf' SIZE 30G;

ORA-01654

ORA-01654: unable to extend index XXXX_DW.IDX_XXX by 8192 in tablespace XXX_DW.

表空间不足，无法自动扩展。可以添加新的 DATAFILE，具体方法参考 ORA-01653。

ORA-01688

ORA-01688: unable to extend table XXX_INFO partition XXX_INFO_2018 by 8192 in tablespace XXX_DW。

表空间不足，无法自动扩展。可以添加新的 DATAFILE，具体方法参考 ORA-01653。

ORA-06512

ORA-06512: at "XXX_DW.DW_XXX", line 233。

ORA-06512 错误消息指示PLSQL代码中未处理的错误的行号。常常跟在其他错误信息之后，用于指示出错位置，帮助排除故障。

ORA-12899

ORA-12899: 列“XXX_ODS”.“DESCRIPTION”.“INFO”的值太大 (实际值: 377, 最大值: 350)。

原因是`DESCRIPTION` 表中 `INFO` 字段的长度小于插入数据的**占用字节数**。

查看插入数据的最大占用字节数，并修改字段长度：

1
2
3
4
5
--查看插入数据的最大占用字节数
select max(lengthb(INFO)) from DESCRIPTION;

--修改字段类型（增加长度）
alter table DESCRIPTION modify INFO varchar2(400);

注意：此处使用的函数可以是lengthb()或vsize()而非length()，因为varchar2(400)中的400指的是字节数而非字符个数。

扩展：length(),lengthb(),vsize() 函数的区别

- length(string)：返回指定**字符串**的**长度**。 - lengthb(string)：返回指定**字符串**的**占用字节数**。 - vsize(expression)：返回指定**表达式**的**占用字节数**。

简单总结一下，length()函数返回字符的个数，另外两个返回的是字节数。那么lengthb()和vsize()又有什么区别呢？通过下面这个测试发现，lengthb()是将括号中的内容视为（转换为）字符串后计算占用字节数，而vsize()不会进行转换。

ORA-12899

ORA-22992

ORA-22992: 无法使用从远程表选择的 LOB 定位符。

使用 database link 从远程库抽取数据时，表中包含 `CLOB` 类型的字段，无法通过指定字段的形式插入数据。可以先创建一个和原表的表结构相同的本地表，再通过 `insert` 的方式插入数据。 
1
2
3
4
5
6
7
8
--创建和原表的表结构相同的本地表
create table XXX_LOCAL as select * from XXX@x_dl where 1=2;

--插入数据
INSERT INTO XXX_LOCAL (xxx,xxx_clob)
  SELECT xxx,xxx_clob FROM XXX@xxx_dl T
  WHERE T.opdate >= TO_DATE(I_STARTDATE ||'00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  AND T.opdate <=TO_DATE(I_ENDDATE ||'23:59:59', 'YYYY-MM-DD HH24:MI:SS');

ORA-30926

ORA-30926: unable to get a stable set of rows in the source tables。ORA-06512: at "XXX_DW.DW_XXX", line 233.

错误指向的SQL片段（line 233）： 
1
2
3
4
5
6
WHILE (V_LOOPDATE <= V_ENDDATE) LOOP
  merge into XXX_INFO t
  using (select g.* from XXX_HISINFO g
          where g.d_date = V_LOOPDATE) k
  on (t.xxx_id = k.xxx_id)
  when matched then ...

这个错误是由于数据来源表存在多条结果造成的，即使用 using (...) on (...) 条件查询 XXX_HISINFO 表得到多个结果，无法确定使用哪个数据进行 merge。错误原因及解决方式与 ORA-01427 相似。

查看哪条数据出现多个结果，再判断是否合理：

1
2
select * from XXX_HISINFO where xxx_id in 
  (select vc_symbol_id from XXX_HISINFO group by xxx_id having count(xxx_id)>1);

我这种情况比较特殊，两条不同的数据经处理后生成了相同的 xxx_id，需要调整on中的条件进行更细致的区分。