全部博文（323）
分类： Oracle
2009-06-07 21:31:44
故障现象：数据库出现ORA-01578错误：数据块损坏(文件号8，块号36385)
用以下语句查询：
SELECT segment_name,segment_type FROM dba_extents WHERE file_id=8 AND 36385 BETWEEN block_id AND block_id + blocks -1;
结果为：
segment_name segment_type
----------------------- ------------------------
PK_KC03 INDEX
有的文章介绍说如果segment_type为INDEX的话，只要删除该索引再重建即可。
处理过程：
刚开始，我想直接 drop 掉这个索引 PK_KC03
drop index PK_KC03;
但出错：VBO-4545:无法移去索引。ORA-02429无法删除用于强制唯一/主关键字的索引。
后来找到下面这篇文章，里面有一部分是关于“Dropping an Index with Dependencies”的说明。
后来大概操作步骤如下：
一、查找 primary key constraint
select constraints_name
from user_constraints
where table_name = 'KC03'
and constraint_type = 'P';
结果为：PK_KC03
二、
select table_name,constraint_name
from user_constraints
where r_constraint_name = 'PK_KC03'
and status = 'ENABLED';
结果为：无。(如果此处有记录，请参照中的说明。)
三、alter table kc03 disable primary key;
(此步完成后，索引 PK_KC03 已被删除。)
四、alter table kc03 enable primary key using index pctfree 10 storage (initial 160M next 20M minextents 1 maxextents 121 pctincrease 0);
(此步完成后，重新生成索引 PK_KC03。)
(我的 KC03 中有250万条记录，本以为要很长时间，没想到只用了13分钟。)
摘录一下这篇文章：
Dropping an Index with Dependencies
This relationship between the parent and child tables does complicate system maintenance.
For example, you could have a problem with the parent's primary key index. Many of the
constraints you created are supported by indexes (primary key or unique). When Oracle created
those indexes, it used the default storage parameters. Indexes have storage parameters
similar to those of tables (first extent, next extent, percent increase). If the defaults
turn out to be too small, the index can reach the maximum number of extents allowed. At
that point, no more records can be inserted into the table because the next entry for the
index can't be created. Before referential integrity, the index could simply be dropped
and recreated. Now all the foreign key constraints which reference this primary key must
be disabled before the parent's primary key can be dropped and recreated.
You'll drop and recreate the client table's primary key index. First, find the client table's
primary key constraint. Type:
SQL> select constraint_name
2 from user_constraints
3 where table_name = 'CLIENT'
4 and constraint_type = 'P';
CONSTRAINT_NAME
------------------------------
SYS_C00369
The index enforcing this constraint will have the same name as the constraint. Try to drop it.
Type:
SQL> drop index sys_c00369;
drop index sys_c00369
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
The primary key constraint on the client table must be disabled. When a constraint is
disabled, the index enforcing it is dropped. Try to use the alter table disable command
to disable the primary key. Type:
SQL> alter table client disable primary key;
alter table client disable primary key
*
ERROR at line 1:
ORA-02297: cannot disable constraint (LOP.SYS_C00369) - dependencies exist
The client primary key constraint can't be disabled until any dependencies caused by
referential integrity constraints are disabled. This can be done by adding the keyword
cascade to the alter table disable command. However, since there is no cascade option
for the alter table enable command, it's a good idea to identify the enabled dependent
constraints before you do this. If you don't, you won't know which of the dependent
constraints were enabled before this operation (some may have been disabled for other reasons).
Type:
SQL> select table_name,constraint_name
2 from user_constraints
3 where r_constraint_name = 'SYS_C00369' and status = 'ENABLED';
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
ACCOUNT SYS_C00381
Now disable the client table's primary key. Type:
SQL> alter table client disable primary key cascade;
Table altered.
The dependent constraint should be disabled. Type:
SQL> select status from user_constraints
2 where constraint_name = 'SYS_C00381';
STATUS
--------
DISABLED
The index enforcing the client table's primary should be gone. Type:
SQL> select * from user_indexes
2 where index_name = 'SYS_C00369';
no rows selected
When you enable the primary key constraint, you can tell Oracle the storage parameters to use
for the primary key index. This will be done through the using index clause of the alter
table...enable constraint command. (This also could have been part of the original create
table statement.) Type:
SQL> alter table client
2 enable primary key
3 using index storage(initial 5000 next 1000 pctincrease 100);
Table altered.
The enable clause could have been written enable constraint sys_c00369. This command created
the new primary key index. Oracle gives it the same name as the constraint. Look at the
storage parameters for this index. Type:
SQL> select uniqueness,initial_extent,next_extent,pct_increase
2 from user_indexes
3 where index_name = 'SYS_C00369';
UNIQUENES INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
--------- -------------- ----------- ------------
UNIQUE 6144 2048 100
Oracle created an initial extent of 6144 bytes. On this system (Windows95), the Oracle
blocksize is 2048 bytes. The request was for an initial extent of 5000 bytes which isn't
a multiple of the blocksize; so, Oracle rounded the initial extent to 3 blocks. It also
rounded up the size of the next extent to be 2048 since the requested size was less than
one block. The minimum number of blocks for a first extent is two. The minimum number
of blocks for a next extent is 1. (This applies to tables as well as indexes.)
The foreign key constraint of the account table can be enabled now that the client table's
primary key has been restored. Type:
SQL> alter table account enable constraint sys_c00381;
Table altered.
Check the account table's foreign key constraints. Type:
SQL> select * from user_constraints
2 where table_name = 'ACCOUNT'
3 and constraint_type = 'R';
OWNER CONSTRAINT_NAME C
------------------------------ ------------------------------ -
TABLE_NAME
------------------------------
SEARCH_CONDITION
---------------------------------------------------------------------------
R_OWNER R_CONSTRAINT_NAME DELETE_RU
------------------------------ ------------------------------ ---------
STATUS
--------
LOP SYS_C00381 R
ACCOUNT
LOP SYS_C00442 CASCADE
ENABLED
Check the columns on which the constraints were placed. Type:
SQL> select * from user_cons_columns
2 where constraint_name in ('SYS_C00369','SYS_C00381');
OWNER CONSTRAINT_NAME
------------------------------ ------------------------------
TABLE_NAME COLUMN_NAME POSITION
------------------------------ ------------------------------ ---------
LOP SYS_C00369
CLIENT CLIENT_NO 1
LOP SYS_C00381
ACCOUNT CLIENT_NO
Constraint sys_c00381 is defined on the account.client_no column and it references constraint
sys_c00369, the primary key constraint defined on the client.client_no column.
