ORA-00406 问题的解决

在oracle8.1.6下删除表中的一个列，结果提示错误。

 

SQL> select * from v$version;

BANNER
------------------------------------------------------
Oracle8i Release 8.1.6.0.0 - Production
PL/SQL Release 8.1.6.0.0 - Production
CORE    8.1.6.0.0       Production
TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

 

SQL> create table test_tianyc(a date, b date);

 

表已创建。

 

SQL> alter table test_tianyc drop column b;

*

ORA-00406: COMPATIBILITY 参数需要是 8.1.0.0.0 或更大

 

SQL>

 

Oracle对这个错误的解释如下：

  

ORA-00406 COMPATIBLE parameter needs to be string or greater

Cause: The value of the COMPATIBLE initialization parameter is not high enough to allow the operation. Allowing the command would make the database incompatible with the release specified by the current COMPATIBLE parameter.

Action: Shut down and restart with a higher compatibility setting.

 

那么，看一下这个参数：

 

SQL> show parameter COMPATIBLE

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----

compatible                           string      8.0.5

SQL>

 

在PFILE中，将该参数修改为8.1.0，然后重启数据库，即可正常删除该列：

 

SQL> show parameter COMPATIBLE

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----

compatible                           string      8.1.0

 

SQL> alter table test_tianyc drop column b;

 

表已更改。

 

SQL>

 

下面是oracle9.2.0.1中对该参数的介绍：

Parameter type	String
Syntax	COMPATIBLE = release_number
Default value	8.1.0
Parameter class	Static
Range of values	Default release to current release
Real Application Clusters	Multiple instances must have the same value.

allows you to use a new release, while at the same time guaranteeing backward compatibility with an earlier release. This is helpful if it becomes necessary to revert to the earlier release.

This parameter specifies the release with which the Oracle server must maintain compatibility. It allows you to take advantage of the maintenance improvements of a new release immediately in your production systems without testing the new functionality in your environment. Some features of the current release may be restricted.

When using the standby database feature, this parameter must have the same value on the primary and standby databases, and the value must be 7.3.0.0.0 or higher. The default value is the earliest release with which compatibility can be guaranteed.

The value of COMPATIBLE must be set to 8.1.0 or higher in order to use stored columns of UROWID type. You can use ROWID pseudo columns for index-organized tables regardless of the parameter setting.