|
数据库的某表，因为数据量一个月达千万级记录，故按月采用分区进行处理。
create table a (
serial char(12) primary key,
info1 char(13),
txn_date char(8)
)
partition by range (txn_date)
( partition p200601 values less than (rpad('200602',8)) tablespace dd,
partition p200602 values less than (rpad('200603',8)) tablespace dd,
partition pmax values less than (maxvalue) tablespace dd
)
create table b (
serial char(12) primary key,
info2 char(5),
txn_date char(8),
constraint fk_aserial foreign key (serial) references a (serial)
)
partition by range (txn_date)
( partition p200601 values less than (rpad('200602',8)) tablespace dd,
partition p200602 values less than (rpad('200603',8)) tablespace dd,
partition pmax values less than (maxvalue) tablespace dd
)
添加记录
insert into a values('111','info111','20060101');
insert into a values('222','info222','20060201');
insert into a values('333','info333','20060301');
insert into a values('444','info444','20060401');
insert into b values('111','info1','20060101');
insert into b values('222','info2','20060201');
insert into b values('333','info3','20060301');
insert into b values('444','info4','20060401');
commit;
查询：
SQL> select * from a;
SERIAL INFO1 TXN_DATE
------------ ------------- --------
111 info111 20060101
222 info222 20060201
444 info444 20060401
333 info333 20060301
SQL> select * from b;
SERIAL INFO2 TXN_DATE
------------ ----- --------
111 info1 20060101
222 info2 20060201
444 info4 20060401
333 info3 20060301
表a添加一个分区：
alter table b split partition pmax at (rpad('200604',8)) into (partition p200603 tablespace dd, partition pmax tablespace dd);
建立a的分区索引
create index a_date on a (txn_date) local
(partition a_idx_200601 tablespace dd,
partition a_idx_200602 tablespace dd,
partition a_idx_200603 tablespace dd,
partition a_idx_max tablespace dd);
截断和删除表b的分区p200602
SQL> alter table b truncate partition p200602 drop storage;
Table truncated.
SQL> alter table b drop partition p200602;
Table altered.
准备截断表a的分区p200602时出错：
alter table a truncate partition p200602 drop storage;
会提示错误：
alter table a truncate partition p200602 drop storage
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
临时禁用约束：
alter table b disable constraint fk_aserial;
可以删除表a的分区了：
SQL> alter table a truncate partition p200602 drop storage;
Table truncated.
SQL> alter table a drop partition p200602;
Table altered.
重新启用约束,提示索引失效：
SQL> alter table b enable constraint fk_aserial;
alter table b enable constraint fk_aserial
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'PE.SYS_C0014230' or partition of such index is in unusable
state
索引状态为UNUSABLE：
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='SYS_C0014228';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ ----------- --------
SYS_C0014228 NORMAL
DD TABLE UNUSABLE
重建索引：
SQL> alter index sys_c0014228 rebuild nologging;
Index altered.
索引正常：
SQL> select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='SYS_C0014228';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TABLESPACE_NAME TABLE_TYPE STATUS
------------------------------ ----------- --------
SYS_C0014228 NORMAL
DD TABLE VALID
重新启用外键，表B自己的索引失效：
SQL> alter table b enable constraint fk_aserial;
alter table b enable constraint fk_aserial
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01502: index 'PE.SYS_C0014230' or partition of such index is in unusable
state
重建表B索引：
SQL> alter index sys_c0014230 rebuild nologging;
Index altered.
终于可以重新使用外键了：
SQL> alter table b enable constraint fk_aserial;
Table altered.
处理完毕~
原文：http://humint.blog.ccidnet.com/blog-htm-itemid-91038-do-showone-type-blog-uid-22648.html |
