ORA-02429: 无法删除用于强制唯一/主键的索引

ORA-02429: 无法删除用于强制唯一/主键的索引 (2008-06-18 15:52:49)
标签：it   分类：ora错误分析

操作：
因为drop 掉了一个2千万记录的大表，现在想收缩其原来所在的表空间的数据文件，但是由于有些小记录的表是create在大表之后的，所以需要移动这些小表，通过dba_extents查到这些在高水位空间上的表

SQL> select segment_name,file_id,extent_id,block_id,bytes
  2  from dba_extents
  3  where file_id in (11,12,14,15,18)
  4  order by file_id,extent_id;
SEGMENT_NAME              FILE_ID  EXTENT_ID   BLOCK_ID      BYTES
---------------------- ---------- ---------- ---------- ----------
IND_T1_N1_V1                   11          0       1081      65536
FY_2004_2                      11          3       1033      65536
FY_2004_2                      11          8       1041      65536
PK_1                           12          0       1089      65536
SYS_C003155                    12          0       1105      65536
SYS_C003159                    12          0       1113      65536
FY_2004_2                      12          4       1049      65536
FY_2004_2                      12          9       1057      65536
PLAN_TABLE                     14          0      35513      65536
FY_2004_2                      14          0      35481      65536
FY_2004_2                      14          5      35489      65536
FY_2006                        15          0      38313      65536
EMP_TEST                       15          0      38361      65536
IND_T1_N1                      15          0      38377      65536
CHAINED_ROWS                   15          0       1297      65536
FY_2006_IND                    15          0      38369      65536
SYS_C003157                    15          0      38321      65536
FY_2004_2                      15          1       1177      65536
FY_2004_2                      15          6       1185      65536
TESTA2                         18          0       1225      65536
TEMP_T2                        18          0       1209      65536
T1                             18          0       1185      65536
T2                             18          0       1193      65536
T_TESTSQL                      18          0       1241      65536
USER_NOTICE                    18          0       1249      65536
TESTA                          18          0       1217      65536
PRI_TAB                        18          0       1177      65536
FY_2004_2                      18          2       1161      65536
FY_2004_2                      18          7       1169      65536

已选择29行。

可以看到象fy_2004_2,plan_table,sys_c003157等对象的block_id都比较大，说明都在相应数据文件比较高水位的地方，象fy_2004_2这个表及索引fy_2006_ind等不需要的对象，我可以直接drop掉，对plan_table等需要保留的表进行move

SQL> alter table plan_table move;

alter table plan_table move

ORA-00997: 非法使用 LONG 数据类型

因为plan_table里出现字段为long类型，所以出错，解决办法请点击这里

move成功后再查看dba_extents 表

SQL> /

SEGMENT_NAME              FILE_ID  EXTENT_ID   BLOCK_ID      BYTES
---------------------- ---------- ---------- ---------- ----------
IND_T1_N1_V1                   11          0       1081      65536
PK_1                           12          0       1089      65536
SYS_C003155                    12          0       1105      65536
SYS_C003159                    12          0       1113      65536
FY_2006                        15          0       1177      65536
SYS_IL0000030377C00026$$       15          0       1169      65536
SYS_LOB0000030377C00026$$      15          0       1161      65536

SYS_C003157                    15          0      38321      65536
EMP_TEST                       15          0       1185      65536
PLAN_TABLE                     15          0       1201      65536
CHAINED_ROWS                   15          0       1297      65536
TESTA2                         18          0       1225      65536
TEMP_T2                        18          0       1209      65536
T1                             18          0       1185      65536
T2                             18          0       1193      65536
T_TESTSQL                      18          0       1241      65536
USER_NOTICE                    18          0       1249      65536
TESTA                          18          0       1217      65536
PRI_TAB                        18          0       1177      65536

已选择19行。
move成功，plan_table的block_id变小了

还有一个索引sys_c003157，目前不知道是函数索引或分区索引还是主键索引，我们先删除一下试试

SQL> drop index sys_c003155;

drop index sys_c003155

ORA-02429: 无法删除用于强制唯一/主键的索引

查一查

SQL> select index_name,column_name,table_name from user_ind_columns;

INDEX_NAME                     COLUMN_NAME            TABLE_NAME
------------------------------ ---------------------- ------------------------------
FY_2006_IND                    ID                     FY_2006
PK_1                           PRI_1                  PRI_TAB
IND_T1_N1                      N2                     T1
IND_T1_N1_V1                   N2                     T1
IND_T1_N1_V1                   V1                     T1
SYS_C003157                    PARTID                 TAB_PART_HASH
SYS_C003155                    PARTID                 TAB_PART_RANGE
IND_PART_RANGE                 PARTDESC               TAB_PART_RANGE
SYS_C003159                    PARTID                 TAB_PART_RANGE_HASH

9 rows selected

我们看到应该是属于主键索引

 

SQL> select owner,constraint_name,constraint_type,table_name,search_condition
  2  from user_constraints;

OWNER  CONSTRAINT_NAME    CONSTRAINT_TYPE TABLE_NAME           SEARCH_CONDITION
------ ------------------ --------------- -------------------- -------------------------------
IC     SYS_C003151        C               PRI_TAB              "PRI_2" IS NOT NULL
IC     SYS_C003152        C               PRI_TAB              pri_3 in ('M','F')
IC     PK_1               P               PRI_TAB            
IC     SYS_C003156        C               TAB_PART_HASH        "PARTDATE" IS NOT NULL
IC     SYS_C003157        P               TAB_PART_HASH
IC     SYS_C003154        C               TAB_PART_RANGE       "PARTDATE" IS NOT NULL
IC     SYS_C003155        P               TAB_PART_RANGE     
IC     SYS_C003159        P               TAB_PART_RANGE_HASH
IC     SYS_C003148        C               USER_NOTICE          "CONTENT" IS NOT NULL
IC     SYS_C003149        C               USER_NOTICE          "ISRED" IS NOT NULL
IC     SYS_C003150        C               USER_NOTICE          "ISSHOW" IS NOT NULL
IC     SYS_C003147        V               V_T1_2             

11 rows selected

SQL> alter table tab_part_hash drop constraint sys_c003157;

Table altered

 再查一查dba_data_files

SQL> /

SEGMENT_NAME              FILE_ID  EXTENT_ID   BLOCK_ID      BYTES
---------------------- ---------- ---------- ---------- ----------
IND_T1_N1_V1                   11          0       1081      65536
PK_1                           12          0       1089      65536
SYS_C003159                    12          0       1113      65536
SYS_C003155                    12          0       1105      65536
FY_2006                        15          0       1177      65536
SYS_LOB0000030377C00026$$      15          0       1161      65536
SYS_IL0000030377C00026$$       15          0       1169      65536
EMP_TEST                       15          0       1185      65536
PLAN_TABLE                     15          0       1201      65536
CHAINED_ROWS                   15          0       1297      65536
TESTA2                         18          0       1225      65536
TEMP_T2                        18          0       1209      65536
T1                             18          0       1185      65536
T2                             18          0       1193      65536
T_TESTSQL                      18          0       1241      65536
USER_NOTICE                    18          0       1249      65536
TESTA                          18          0       1217      65536
PRI_TAB                        18          0       1177      65536

已选择18行。

看来大工告成了，现在可以缩小 表空间 ic_data里的数据文件了

SQL> alter database datafile 15 resize 15m;

Database altered

.........

以上在网上找到的

我自己解决问题如下

alter table fin_ord_ref drop constraint fin_ord_ref_p;