ORA-2449错误

今天在删除一个表空间的时候碰到了这个错误。

 

 

由于表空间要重建，而用户不需要，因此选择了删除表空间，而保留用户：

SQL> drop tablespace zhejiang including contents and datafiles;
drop tablespace zhejiang including contents and datafiles
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用

按道理来说，不应该存在其他的表空间的对象参考当前表空间的信息：

SQL> select distinct owner
  2  from dba_segments
  3  where tablespace_name = 'ZHEJIANG';

OWNER
------------------------------
ZHEJIANG_OPERATOR
ZHEJIANG
ZHEJIANG_KHD

SQL> select distinct tablespace_name
  2  from dba_segments
  3  where owner in ('ZHEJIANG', 'ZHEJIANG_OPERATOR', 'ZHEJIANG_KHD');

TABLESPACE_NAME
------------------------------
ZHEJIANG

难道真是其他用户的对象参考了当前对象下的表：

SQL> select t1.owner, t1.table_name, t1.tablespace_name, t2.owner, t2.table_name, t2.tablespace_name
  2  from dba_tables t1, dba_constraints c1, dba_indexes i1, dba_tables t2, dba_constraints c2
  3  where t1.owner = c1.owner
  4  and t1.table_name = c1.table_name
  5  and c1.constraint_type = 'P'
  6  and i1.index_name = c1.index_name
  7  and i1.owner = c1.index_owner
  8  and i1.owner = t1.owner
  9  and i1.table_name = t1.table_name
 10  and (t1.tablespace_name = 'ZHEJIANG' or i1.tablespace_name = 'ZHEJIANG')
 11  and t2.owner = c2.owner
 12  and t2.table_name = c2.table_name
 13  and c2.constraint_type = 'R'
 14  and c2.r_constraint_name = c1.constraint_name
 15  and c2.r_owner = c1.owner
 16  and t2.tablespace_name != 'ZHEJIANG';

未选定行

上面的SQL包含了非分区表的情况，可以看到没有任何其他表空间的对象依赖当前表空间下的主键。

下面考虑分区表和唯一键的情况：

SQL> select t1.owner,
  2     t1.table_name,
  3     nvl(t1.tablespace_name, p1.def_tablespace_name) tablespace_name,
  4     t2.owner,
  5     t2.table_name,
  6     nvl(t1.tablespace_name, p1.def_tablespace_name) tablespace_name
  7  from dba_tables t1,
  8     dba_constraints c1,
  9     dba_indexes i1,
 10     dba_part_tables p1,
 11     dba_tables t2,
 12     dba_constraints c2,
 13     dba_part_tables p2
 14  where t1.owner = c1.owner
 15  and t1.table_name = c1.table_name
 16  and c1.constraint_type in ('P', 'U')
 17  and i1.index_name = c1.index_name
 18  and i1.owner = c1.index_owner
 19  and i1.owner = t1.owner
 20  and i1.table_name = t1.table_name
 21  and t1.table_name = p1.table_name (+)
 22  and t1.owner = p1.owner(+)
 23  and (nvl(t1.tablespace_name, p1.def_tablespace_name) = 'ZHEJIANG' or i1.tablespace_name = 'ZHEJIANG')
 24  and t2.owner = c2.owner
 25  and t2.table_name = c2.table_name
 26  and c2.constraint_type = 'R'
 27  and c2.r_constraint_name = c1.constraint_name
 28  and c2.r_owner = c1.owner
 29  and t2.owner = p2.owner (+)
 30  and t2.table_name = p2.table_name (+)
 31  and nvl(t2.tablespace_name, p2.def_tablespace_name) != 'ZHEJIANG';

未选定行

仍然没有找到违反条件的结果。看来问题多半是bug了，查询metalink果然发现了这个bug的描述：Bug No. 6239613。Oracle居然在11.1.0.7才解决了这个问题。

简单的说，当分区表本身包含外键参考另一个表，那么删除表空间时就会出现ORA-2449错误，下面通过一个例子来重新问题：

SQL> create tablespace test datafile '+MEMBER/tradedb/tradedb_test_1_1g' size 1024m;

表空间已创建。

SQL> create user test identified by test default tablespace test quota unlimited on test;

用户已创建。

SQL> grant connect, resource to test;

授权成功。

SQL> conn test/test
已连接。
SQL> create table t (id number primary key);

表已创建。

SQL> create table t_child (id number, fid number, created date,
  2  constraint fk_t_child foreign key (fid) references t)
  3  partition by range (created)
  4  (partition p1 values less than (to_date('2009-1-1', 'yyyy-mm-dd')),
  5  partition p2 values less than (maxvalue));       

表已创建。

SQL> conn / as sysdba
已连接。
SQL> drop tablespace test including contents and datafiles;
drop tablespace test including contents and datafiles
*
第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用

SQL> drop tablespace test including contents and datafiles cascade constraints;

表空间已删除。

虽然错误很奇怪，但是解决方法很简单，通过添加CASCADE CONSTRAINTS参数可以解决这个问题，或者手工删除包含外键的分区表，然后在删除表空间同样可以解决这个问题。

SQL> drop tablespace zhejiang including contents and datafiles cascade constraints;

表空间已删除。

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-615962/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-615962/