常见的SQL错误和解决方法
前言
今天你会看到每个人——从新手到专家——在使用SQL时犯的各种常见错误。你不能永远避免犯任何错误，但是熟悉广泛的错误将帮助你在尽可能短的时间内解决这些错误。
注：在我们的例子中我们使用的是Oracle7个人版。你特定的执行可能和这个错误类型相似，但是错误号和名称可能不同。我们使用SQL*PLUS来运行我们的SQL语句，并设置ECHO和FEEDBACK为开的状态来查看声明。
记住，一些错误会产生错误信息，而另一些可能只是在逻辑上不充分，它们将不可避免的在接下来引起重大的错误。如果你严格关注细节，你可以避免大多数错误，尽管你还总是会偶尔发现错误。
常见错误
本节描述了许多你在执行所有类型的SQL语句时会得到的常见错误。大多数都很简单，简单到使你想踢自己一脚，而其它的看起来很明显的错误则是由于理解错误而产生的。
表或视图不存在
当你收到一个错误声明你要访问的表不存在时，这是很容易查证的;例如：
输入：
SQL> @tables.sql
输出：
SQL> spool tables.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select owner|| '.' || table_name
2 from sys.dba_table
3 where owner = 'SYSTEM'
4 order by table_name
5 /
from sys.dba_table
*
ERROR at line 2:
ORA-00942: table or view does not exist
SQL> spool off
SQL> |
分析：
注意在table 单词下面的星号。正确的表名是sys.dba_tables 。而之前的表名里缺少了s。
但是如果你知道这个表确实存在而你仍然收到了这个错误信息呢?有的时候当你收到了这个错误时，这个表事实上是存在的，但是可能有个安全问题——那就是，这个表存在，但是你没有权限访问它。这个错误还可能是数据库服务器对“你没有权限访问这个表!”的一种委婉的说法。
提示： 在你惊慌之前，如果有可用的DBA帐户，立即使用一个DBA帐户验证这个表是否存在，或者使用schema 帐户。你会经常发现表确实存在，而是这个用户缺乏适当的权限来访问它。
无效的用户名和密码
输入：
| SQL*Plus: Release 3.2.3.0.0 - on Sat May 10 11:15:35 1997 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter user-name: rplew Enter password: |
输出：
| ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: |
这个错误的原因是输入了不正确的用户名或不正确的密码。再试一次。如果还不成功，重置你的密码。如果你确定你输入了正确的用户名和密码，并且你对不只一个的数据库具有权限，那么确保你正在试图连接的是正确的数据库。
没有指定FROM关键字
输入：
SQL> @tblspc.sql
输出：
SQL> spool tblspc.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select substr(tablespace_name,1,15) a,
2 substrfile_name, 1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
substrfile_name, 1,45) c, bytes
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> spool off
SQL> |
分析：
这个错误可能令人误解。有关键字FROM，但是你在第二行上在substr和file_name之间丢失了一个左括号。这个错误还可能是由于在SELECT语句中的字段名称之间丢失了逗号而引起的。如果在SELECT语句中一个字段后没有跟着一个逗号，这个查询处理器会自动查找FROM关键字。上面的语句修改如下：
SQL> select substr(tablespace_name,1,15) a,
2 substr(file_name,1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name; |
这里不允许使用Group功能
输入：
SQL> select count(last_name), first_name, phone_number
2 from employee_tbl
3 group by count(last_name), first_name, phone_number
4 /
|
输出：
group by count(last_name), first_name, phone_number
*
ERROR at line 3:
ORA-00934: group function is not allowed here
SQL>
|
分析：
对于任何group功能， COUNT不能用在GROUP BY条件中。你只可以列出字段和非group功能，例如在GROUP BY 中使用SUBSTR条件。
提示： COUNT是一个作用于查询中的group上的功能。
上一个语句使用适当的语法修改如下：
SQL> select count(last_name), first_name, phone_number
2 from employee_tbl
3 group by last_name, first_name, phone_number; |
无效的字段名称
输入：
SQL> @tables.sql
输出：
SQL> spool tables.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select owner|| '.' || tablename
2 from sys.dba_tables
3 where owner = 'SYSTEM'
4 order by table_name
5 /
select owner|| '.' || tablename
*
ERROR at line 1:
ORA-00904: invalid column name
SQL> spool off
SQL>
|
分析：
在第一行中，字段tablename 不正确。正确的字段名称是table_name。下划线被忽略了。要查看正确的字段名称，使用DESCRIBE命令。这个错误还可能发生在当你在SELECT语句中试图使用错误的表名来限定一个字段。
缺少关键字
输入：
SQL> create view emp_view
2 select * from employee_tbl
3 / |
输出：
select * from employee_tbl
*
ERROR at line 2:
ORA-00905: missing keyword
SQL>
|
分析：
这里是语法不正确。这个错误发生在当你在任何特定的命令语法中丢失了强制性的词的时候。如果你使用一个命令的可选部分，这个选项可能要求一个特定的关键词。在这个示例中缺少的关键词是as。这个正确的语句如下所示：
SQL> create view emp_view as
2 select * from employee_tbl
3 /
|
缺少左括号
输入：
SQL> @insert.sql
输出：
SQL> insert into people_tbl values
2 '303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')
3 /
'303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')
*
ERROR at line 2:
ORA-00906: missing left parenthesis
SQL>
|
分析：
在第二行在社会保险号码之前缺少一个括号。正确的语法应该如下所示：
SQL> insert into people_tbl values
2 ('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE', '10-JAN-50')
3 /
|
缺少右括号
输入：
SQL> @tblspc.sql
输出：
SQL> spool tblspc.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select substr(tablespace_name,1,15 a,
2 substr(file_name, 1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
select substr(tablespace_name,1,15 a,
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> spool off
SQL>
|
分析：
在第一行的substr那里缺少右括号。正确的语法如下所示：
SQL> select substr(tablespace_name,1,15) a,
2 substr(file_name,1,45) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
|
缺少逗号
输入：
SQL> @ezinsert.sql
输出：
SQL> spool ezinsert.lst
SQL> set echo on
SQL> set feedback on
SQL> insert into office_tbl values
2 ('303785523' 'SMITH', 'OFFICE OF THE STATE OF INDIANA, ADJUTANT GENERAL')
3 /
('303785523' 'SMITH', 'OFFICE OF THE STATE OF INDIANA, ADJUTANT GENERAL')
*
ERROR at line 2:
ORA-00917: missing comma
SQL> spool off
SQL>
|
分析：
在第二行在社会保险号码和SMITH 之间缺少一个逗号。
字段定义模糊
输入：
SQL> @employee_tbl
输出：
SQL> spool employee.lst
SQL> set echo on
SQL> set feedback on
SQL> select p.ssn, name, e.address, e.phone
2 from employee_tbl e,
3 payroll_tbl p
4 where e.ssn =p.ssn;
select p.ssn, name, e.address, e.phone
*
ERROR at line 1:
ORA-00918: column ambigously defined
SQL> spool off
SQL>
|
分析：
在第一行的字段name没有明确定义。给定了两个表别名为e和p。你要决定使用哪个表的name字段并用表的别名定义它。
SQL命令没有恰当的结束
输入：
SQL> create view emp_tbl as
2 select * from employee_tbl
3 order by name
4 /
|
输出：
order by name
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
SQL>
|
分析：
为什么命令没有恰当的结束?你知道你可以使用一个/来结束一个SQL语句。另一个笨蛋。ORDER BY条件不能使用在CREATE VIEW语句中。而是使用GROUP BY来代替。在这里查询处理器在ORDER BY条件之前寻找一个结束符(分号或斜线)，因为处理器假定ORDER BY不是CREATE VIEW语句的一部分。因为在ORDER BY之前没有找到结束符，所以返回了这个错误而不是指向ORDER BY的错误
缺少表达式
输入：
SQL> @tables.sql
输出：
SQL> spool tables.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select owner|| '.' || table,
2 from sys.dba_tables
3 where owner = 'SYSTEM'
4 order by table_name
5 /
from sys.dba_tables
*
ERROR at line 2:
ORA-00936: missing expression
SQL> spool off
SQL>
|
分析：
注意在第一行的table 后面有个逗号：因此，查询处理器在SELECT条件中寻找另一个字段。在这里，处理器不希望遇到FROM 条件。
对于函数来说论据不充分
输入：
SQL> @tblspc.sql
输出：
SQL> spool tblspc.lst
SQL> set echo on
SQL> set feedback on
SQL> set pagesize 1000
SQL> select substr(tablespace_name,1,15) a,
2 decode(substr(file_name,1,45)) c, bytes
3 from sys.dba_data_files
4 order by tablespace_name;
decode(substr(file_name,1,45)) c, bytes
*
ERROR at line 2:
ORA-00938: not enough arguments for function
SQL> spool off
SQL>
|
分析：
对于DECODE函数来说论据不充分。检查你的执行是否采用了恰当的语法。
值不够
输入：
SQL> @ezinsert.sql
输出：
SQL> spool ezinsert.lst
SQL> set echo on
SQL> set feedback on
SQL> insert into employee_tbl values
2 ('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE')
3 /
insert into employee_tbl values
*
ERROR at line 1:
ORA-00947: not enough values
SQL> spool off
SQL>
|
分析：
缺少一个字段的值。在表上执行DESCRIBE命令来找出缺少的字段。只有列出了将被插入的字段，你才可以插入指定的数据，如下面的示例中所示：
输入：
SQL> spool ezinsert.lst
SQL> set echo on
SQL> set feedback on
SQL> insert into employee_tbl (ssn, last_name, first_name, mid_name, sex)
2 values ('303785523', 'SMITH', 'JOHN', 'JAY', 'MALE')
3 /
|
完整性约束冲突——没有找到父键
输入：
SQL> insert into payroll_tbl values
2 ('111111111', 'SMITH', 'JOHN')
3 /
|
输出：
insert into payroll_tbl values
*
ERROR at line 1:
ORA-02291: integrity constraint (employee_cons) violated - parent
key not found
SQL>
|
分析：
这个错误是由试图将不存在于父表中的数据插入表中而引起的。检查父表以确定正确的数据。如果缺少这个数据，那么你必须在试图将数据插入子表之前将数据插入父表中。
Oracle不可用
输入：
| (sun_su3)/home> sqlplus SQL*Plus: Release 3.2.3.0.0 - Production on Sat May 10 11:19:50 1997 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter user-name: rplew Enter password: |
输出：
| ERROR: ORA-01034: ORACLE not available ORA-07318: smsget: open error when opening sgadef.dbf file. |
分析：
你在试着在SQL*PLUS 上显示。数据库可能停止了。检查数据库的状态。还有，如果你有多个数据库的访问权限，确保你正在试着连接的是正确的数据库。
插入的值对于该字段来说过大
输入：
SQL> @ezinsert.sql
输出：
SQL> spool ezinsert.lst
SQL> set echo on
SQL> set feedback on
SQL> insert into office_tbl values
2 ('303785523', 'SMITH', 'OFFICE OF THE STATE OF INDIANA, ADJUTANT GENERAL')
3 /
insert into office_tbl values
*
ERROR at line 1:
ORA-01401: inserted value too large for column
SQL> spool off
SQL>
|
分析：
一个插入的值对于该字段来说过大。在表上执行DESCRIBE命令来确定正确的数据长度。如果有必要的话，你可以在表上执行ALTER TABLE命令来扩大字段的宽度。
TNS：监听器不能解析连接描述符中所给出的SID
输入：
SQLDBA> connect rplew/xxxx@database1
输出：
| ORA-12505: TNS:listener could not resolve SID given in connect descriptor SQLDBA> disconnect Disconnected. SQLDBA> |
分析：
这个错误在Oracle 数据库中是非常常见的。前面所述的错误涉及的监听器所做的过程是允许从一个客户端发来的请求与远程服务器上的数据库通信。在这里你试图连接到数据库。或者是数据库名称输入错误，或者是监听器停止了。检查数据库名称，重试一遍。如果还是失败，将这个错误通知数据库管理员。
在授权过程中权限不足
输入：
SQL> grant select on people_tbl to ron;
输出：
| grant select on people_tbl to ron * ERROR at line 1: ORA-01749: you may not GRANT/REVOKE privileges to/from yourself SQL> |
输入：
SQL> grant select on demo.employee to ron;
输出：
| grant select on demo.employee to ron * ERROR at line 1: ORA-01031: insufficient privileges SQL> |
这个错误发生在你试图授权给另一个用户对表的操作，而你没有适当的权限来这么做。你必须拥有这个表才能将这个表授权给其他的用户。在Oracle里可以使用Admin选项给你授权，这意味着你可以将这个对另一个用户的表的指定的权限授予其他用户。检查你对你授予一个权限所需的特定权限的执行。
在你的语句中换码符——无效的字符
在试图调试一个有问题的SQL语句时，换码符是非常麻烦的。当你在缓冲器中或文件中输入你的SQL语句时，如果你使用回退键，那么这种情况就可能发生。有时回退键会放置一个无效的字符在这个语句中，这取决于你的键是怎样匹配的，尽管你可能不能看到这个字符。
不能创建操作系统文件
这个错误有几个原因。最常见的原因是关联的磁盘满了或者是对文件系统设置了不正确的权限。如果是磁盘满了，你必须删除不需要的文件。如果权限不正确，改变它们为正确的设置。这个错误更偏向于操作系统错误，所以你可能需要从你的系统管理员那里获得些建议。
常见的逻辑错误
今天到目前为止我们介绍了在SQL语句中产生实际错误信息的错误。它们中的大多数是显而易见的，并且它们的解决方法也很循规蹈矩。接下来的几个错误逻辑性更强(或更少)，并且它们可能在之后会引起问题——如果不是立即引起问题的话。
在你的SQL语句中使用保留字
输入：
SQL> select sysdate DATE
2 from dual;
输出：
| select sysdate DATE * ERROR at line 1: ORA-00923: FROM keyword not found where expected |
分析：
在这个示例中查询处理器不希望看到单词DATE ，因为它是保留字。在伪列SYSDATE后面没有逗号：因此，它预期下一个元素应该是FROM条件。
输入：
SQL> select sysdate "DATE"
2 from dual;
输出：
DATE
--------
15-MAY-97
分析：
注意怎样通过使用双引号封装DATE来减低保留字问题。双引号允许你显示文字字符串DATE作为一个字段的别名。
注： 确保要检查你的特定数据库文档来获得保留字列表，因为这些保留字因数据库不同而不同。
你在命名一个字段的别名时可能需要、也可能不需要使用双引号。在下面的示例中你不需要使用双引号，因为TODAY不是保留字。为了确保，请检查你的特定的数据库文档。
输入：
| SQL> select sysdate TODAY 2 from dual; |
输出：
| TODAY -------- 15-MAY-97 SQL> |
选择多个字段时的DISTINCT的使用
输入：
| SQL> select distinct(city), distinct(zip) 2 from address_tbl; |
输出：
| select distinct(city), distinct(zip) * ERROR at line 1: ORA-00936: missing expression SQL> |
分析：
一个城市可能有不止一个的邮递编码。作为一项规则，你应该在某一字段上使用DISTINCT 命令。
删除一个无限定的表
无论什么时候删除一个表，总是要使用owner或schema 。你可以在数据库中有重复的表名。如果你不使用owner/schema 名称，那么会删错表。
下面是具有风险的删除一个表的语法：
语法：
SQL> drop table people_tbl;
下面的语句比上面的安全的多，因为它指定了你想删掉的表的owner。
语法：
SQL> drop table ron.people_tbl;
警告： 在删除表的时候限制这个表通常是一个安全的做法，尽管有时这一步可能不必要。在你通过验证用来连接到数据库所使用的用户id之前不要执行DROP TABLE 命令。
在Multischema数据库中使用公共同义词
同义词使得用户的工作更简单：然而，公共同义词打开了你可能不想所有用户都看到的表。在授权公共同义词，特别是在一个multischema环境中时，使用警告。
可怕的笛卡儿积
输入：
| SQL> select a.ssn, p.last_n 2 from address_tbl a, 3 people_tbl p; |
输出：
SSN LAST_NAME
--------- ---------------
303785523 SMITH
313507927 SMITH
490552223 SMITH
312667771 SMITH
420001690 SMITH
303785523 JONES
313507927 JONES
490552223 JONES
312667771 JONES
420001690 JONES
303785523 OSBORN
313507927 OSBORN
490552223 OSBORN
312667771 OSBORN
420001690 OSBORN
303785523 JONES
313507927 JONES
490552223 JONES
312667771 JONES
420001690 JONES
16 rows selected.
|
这个错误是由于你在WHERE条件中没有将表连接起来。注意选择了多少行。前面所述的表都有4行;因此，我们希望返回4行而不是我们收到的16行。在WHERE条件中没有使用连接，在第一个表的每一行与第二个表的每一行相匹配。要计算返回的总行数，你要将4行乘以4行，得到16。不幸的是，你的大多数表将包含4行以上的数据，有可能达到上千行或几百万行。在这种情况下就不用麻烦的计算这个乘积了，因为你的查询肯定是个很耗时的查询。
执行输入标准失败
假设采用的输入标准就是普遍知道的质量保证(QA)。没有数据输入职员对输入的数据进行定期检查，那么就很可能在你的数据库中存有很多垃圾。一个保持对质量保证的处理的好的方法是使用SQL创建几个QA报告，然后定时运行，并将它们的输出显示给数据输入管理员以采取适当的动作来修正错误或数据不一致。
执行文件系统结构规定失败
你使用不标准的文件系统时它可能会浪费大量的时间。检查你的结构以采用推荐的文件系统结构。
允许大表采取默认的存储参数
默认的存储参数将随着执行而变化，但是它们一般都相当的小。当创建了一个大型的或动态的表并让其使用默认存储，就会产生严重的表破碎情况，这会严重干扰数据库性能。在创建表之前进行良好的计划将帮助避免这种情况。下面的示例使用了Oracle的存储参数选项。
输入：
SQL> create table test_tbl
2 (ssn number(9) not null,
3 name varchar2(30) not null)
4 storage
5 (initial extent 100M
6 next extent 20M
7 minextents 1
8 maxextents 121
9 pctincrease 0};
|
在系统表空间中放置对象
下面的语句显示了在系统表空间中创建一个表。尽管这个语句不会返回错误，但是它很可能在以后引起问题。
输入：
SQL> create table test_tbl
2 (ssn number(9) not null,
3 name varchar2(30) not null)
4 tablespace SYSTEM
5 storage
6 (initial extent 100M
7 next extent 20M
8 minextents 1
9 maxextents 121
10 pctincrease 0};
|
下一个示例纠正了这个所谓的问题：
输出：
SQL> create table test_tbl
2 (ssn number(9) not null,
3 name varchar2(30) not null)
4 tablespace linda_ts
5 (initial extent 100M
6 next extent 20M
7 minextents 1
8 maxextents 121
9 pctincrease 0};
|
分析：
在Oracle中， SYSTEM表空间是用来存储SYSTEM拥有的对象，例如那些组成了数据字典的对象。如果你偶然放置了动态表到这个表空间中，并且它们继续发展，那么你就有崩溃或至少填满空余空间的风险，这会反回来引起数据库崩溃。在这种情况下，数据库可能会强制进入不可恢复的状态。所以要将应用和用户表存储在单独指定的表空间中
压缩大型备份文件失败
如果你做大型的导出，并且没有压缩这些文件，你很可能会没有磁盘空间来存储这些文件。所以要压缩这些导出文件。如果你将存档日志文件存储在硬盘上而不是磁带上，那这些文件可以或很可能应该压缩保存。
对系统资源的预算失败
你应该在创建你的数据库之前总是对你的系统资源进行预算。不对系统资源进行预算的结果可能导致数据库性能很差。你应该总是了解数据库是将用于交易、数据仓库或仅仅是查询。数据库的功能将影响回滚部分的数目和规模。数据库用户的数目将不可避免的影响USERS和TEMP表空间的规模。你是否有足够的空间来放置你更大的表?表和索引应该存储在单独的设备上来减少磁盘内容。你应该在单独的设备上保存重做日志和数据表空间以减少磁盘内容。在考虑系统资源的时候只有这几个问题要解决。
防止产生数据问题
你的数据处理中心应该建立一个备份系统。如果你的系统是小型至中型的，你可以使用EXPORT进行额外的防范以确保你的数据进行了备份。你应该对输出文件进行备份并将其存储在另一个地方以达到更进一步的安全性。记住这些文件可能会很大，会要求很大的空间。
搜索你的数据库中的重复的记录
如果你的数据库进行了很好的计划，那么你应该没有重复记录的问题。你可以使用约束、外键和唯一索引来避免重复记录。
总结
许多不同类型的错误——上百个——会干扰你和你的数据。幸运的是，大多数错误都不是灾难性的，并很好解决。然而，有一些错误如果发生了就非常严重。无论你什么时候试着纠正错误，你都要非常谨慎，因为如果你没有找到问题的根源那这个问题很可能会加倍。当你确实产生了错误，当然这是肯定会的，将它们当作你的学习经历。
提示：我们希望将所有与数据库错误相关的内容都记录下来，特别是我们偶然发现的不常见的错误。一个包含各种错误的文件是一个宝贵的故障排除参考。
注：第21天提供给你一些最常见的Oracle7个人版的错误示例。要查看完整的错误列表和建议的解决方法，请查看你的数据库文档。
问题和解答
问 你使得它听起来好像每一个错误都有一个解决方法，那么为什么还要担心呢?
答 是的，大多数错误都很容易解决;但是假设你在一个生产环境中删除了一个表。你可能需要几小时或几天来做这个数据库恢复工作。数据库将在这段时间里完成这个工作，而你的公司将给几个人支付加班费来完成这个修复。老板不会高兴的。
问 你有任何关于怎样避免错误的建议吗?
答 作为一个人类，你不会永远避免犯错;然而，你可以通过练习、集中注意力、自信、良好的态度和一个没有压力的工作环境来避免大多数的错误。
说了这么多，该考考你的阅读的成果了:
习题
习题提供了测试问题来帮助你巩固对本文描述的内容的理解，还提供了练习以供你使用你所学到的东西。在查看附录F中的答案——“测试和练习的答案”——之前试着回答这个测试和练习问题。
测试
1.一个用户打电话说，“我不能登录到数据库上。但是昨天一切还都运行良好。这个错误说无效的用户/密码。你可以帮我吗?”你应该采取什么样的步骤?
2.为什么表应该有存储条件和一个表空间目的地?
练习：
1. 假设你以SYSTEM登录到数据库上，并且你希望删除在你的schema中的一个叫做HISTORY 的表。你的普通的用户id是JSMITH 。那么删除这个表的正确的语法是什么?
2. 纠正下面的错误：
输入：
| SQL> select sysdate DATE 2 from dual; |
输出：
| select sysdate DATE * ERROR at line 1: ORA-00923: FROM keyword not found where expected |
