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








分类: Oracle

2009-06-07 21:31:44

环境:OS: AIX 4.3.3

     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


刚开始,我想直接 drop 掉这个索引 PK_KC03
drop index PK_KC03;
,里面有一部分是关于“Dropping an Index with Dependencies”的说明。


一、查找  primary key constraint
select constraints_name
from user_constraints
where table_name = 'KC03'
   and constraint_type = 'P';


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';


The index enforcing this constraint will have the same name as the constraint. Try to drop it.



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).



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';


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';

--------- -------------- ----------- ------------                         
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           
------------------------------ ------------------------------ -           
R_OWNER                        R_CONSTRAINT_NAME              DELETE_RU   
------------------------------ ------------------------------ ---------   
LOP                            SYS_C00381                     R           
LOP                            SYS_C00442                     CASCADE     
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) |