Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2503438
  • 博文数量: 323
  • 博客积分: 10211
  • 博客等级: 上将
  • 技术积分: 4934
  • 用 户 组: 普通用户
  • 注册时间: 2006-08-27 14:56
文章分类

全部博文(323)

文章存档

2012年(5)

2011年(3)

2010年(6)

2009年(140)

2008年(169)

分类: Oracle

2009-06-07 21:31:44

该案例来自ITPUB。在这里记录一下处理过程。
 
环境:OS: AIX 4.3.3
DB: ORACLE 8.1.5.0.0

故障现象:数据库出现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.

 


阅读(2493) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~