- 作者:三十而立
在给数据库做维护时候,出现一个问题。
问题不是很复杂,但是却是很新奇,该错误居然由外键引起。这个错误如何起因,又是如何解决的,一起来看看过程中吧。
数据库使用了很久要求,对数据库进行清理,清理过程中发现有些tablespace碎片化的厉害,所以给用户建议,导出这些tablespace,然后重建,做Imp处理,这样的话,起到shrink的作用,嘻嘻,其实这已经不是shrink了,不过是达到shrink的目标而已。
删除表空间的时候,但是还是保留了user,没有删除用户,而是选择直接删除表空间。
这个ORA-2449问题也就因此而起
SQL> drop tablespace changeinfo including contents and datafiles;
drop tablespace changeinfo including contents and datafiles
*第 1 行出现错误:
ORA-02449: 表中的唯一/主键被外键引用
既然是报这个错误,所以马上想到的就是
首先查询了一下dba_segments这个表,看看有没有其他的用户的segment也在这个上面,
SQL>conn / as sysdba
SQL>select owner, segment_name, segment_type from dba_segments where tablespace_name = 'CHANGEINFO' and owner<>'HISMGR';
没有找到其他的记录 也就是没有其他的segment使用这里。
那么是不是有其他的tablespace里的对象引用了这里的tablespace里的对象了么
SQL> select d.owner, b.table_name, a.owner, c.table_name
2 from dba_tables a, dba_constraints b, dba_constraints c, dba_tables d
3 where b.r_constraint_name = c.constraint_name
4 and b.r_owner = c.owner
5 and c.table_name = a.table_name
6 and a.owner = c.owner
7 and a.tablespace_name = 'CHANGEINFO'
8 and d.table_name = b.table_name and d.owner = b.owner;
也没有其他的用户reference这个tablespace上的对象呀。也并没有其他的表空间的表reference到这个表空间和表空间的里的任何对象。
在网上查询了一些相同的error号,
有人提出
当分区表本身包含外键参考另一个表,那么删除表空间时就会出现ORA-2449错误
自己做了相应的一个实验
SQL> create tablespace test_1 datafile 'F:DEVELOPERORACLEPRODUCT10.2.0ORADA
TAORCL est_1TEST_1.01.DBF' size 10m;
表空间已创建。
SQL> create user test_1 identified by "test_1" default tablespace test_1;
用户已创建。
SQL> grant connect, resource to test_1;
授权成功。
SQL> conn test_1/test_1
SQL> select segment_name from user_segments ;
未选定行
SQL> create table t_1 (id int primary key);
表已创建。
SQL> create table t_2 (id int, pid int, df date,
2 foreign key (pid) references t_1)
3 partition by range(df)
4 (partition p1 values less than (to_date('2009-11-05', 'yyyy-mm-dd')),
5 partition p2 values less than (maxvalue));
表已创建。
SQL> select segment_name, segment_type from user_segments ;
SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------
T_1 TABLE
SYS_C0010950 INDEX
T_2 TABLE PARTITION
T_2 TABLE PARTITION
SQL> drop tablespace test_1 including contents and datafiles;
drop tablespace test_1 including contents and datafiles
ORA-02449: 表中的唯一/主键被外键引用
确实出现这个错误了。 按照metalink说的,应该是分区表的外键导致的。显示删除外键试试。
SQL> select constraint_name, constraint_type, table_name from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SYS_C0010951 R T_2
SYS_C0010950 P T_1
SQL> alter table t_2 drop constraints SYS_C0010951;
表已更改。
SQL> conn / as sysdba
已连接。
SQL> drop tablespace test_1 including contents and datafiles;
Tablespace dropped
这回就可以成功删除了,应该算是个处理上的bug叻。后来在metalink上查到确实有这个问题 bug号Bug No. 623961
不过metalink给出的方法简单,在drop的时候加上cascade constraints就可以了。
SQL>drop tablespace test_1 including contents and datafiles cascade constraints;
用户现在的表对象都没有
SQL> select segment_name, segment_type from user_segments ;
未选定行
把这个问题记一下,以便以后的朋友知道如何解决。 不过这里的case也是奇怪,为什么我的user的default tablespace为Test_1, 却可以删除掉这个tablespace的呀,应该至少检查一下,在没有用户使用了这个tablespace做default才能删除哟。不知道这个是ORacle的bug,还是Oracle另有考虑叻。
很多的问题值得我们去深思,就像这里最后引入的新问题,究竟是什么原因了,自己去探索探索吧。如果把学习当作是探索,你和把神秘和未知变成了兴趣,不要小敲了这小小的差别哟,只就是非常magical的一股力量。
- 如果没有那句“三十而立”,三十岁的男人正可以轻轻松松