/*給創(chuàng)建bitmap index分配的內(nèi)存空間參數(shù)，以加速建索引*/
show parameter create_bit;
/*改變索引的存儲(chǔ)參數(shù)*/
alter index index_name pctfree 30 storage(initial 200k next 200k);
/*給索引手工分配一個(gè)分區(qū)*/
alter index index_name allocate extent (size 200k datafile '$ORACLE/oradata/..');
/*釋放索引中沒(méi)用的空間*/
alter index index_name deallocate unused;
/*索引重建*/
alter index index_name rebuild tablespace tablespace_name;
/*普通索引和反轉(zhuǎn)索引的互換*/
alter index index_name rebuild tablespace tablespace_name reverse;
/*重建索引時(shí)，不鎖表*/
alter index index_name rebuild online;
/*給索引整理碎片*/
alter index index_name COALESCE;
/*分析索引,事實(shí)上是更新統(tǒng)計(jì)的過(guò)程*/
analyze index index_name validate structure;
desc index_state;
drop index index_name;
alter index index_name monitoring usage;-----監(jiān)視索引是否被用到
alter index index_name nomonitoring usage;----取消監(jiān)視
/*有關(guān)索引信息的視圖*/
select * from dba_indexes/dba_ind_columns/dbs_ind_eXPressions/v$object_usage;
########## 數(shù)據(jù)完整性的治理(Maintaining data integrity) ##########
alter table table_name drop constraint constraint_name;----drop 約束
alter table table_name add constraint constraint_name primary key(column_name1,column_name2);-----創(chuàng)建主鍵
alter table table_name add constraint constraint_name unique(column_name1,column_name2);---創(chuàng)建唯一約束
/*創(chuàng)建外鍵約束*/
alter table table_name add constraint constraint_name foreign key(column_name1) references table_name(column_name1);
/*不效驗(yàn)老數(shù)據(jù)，只約束新的數(shù)據(jù)[enable/disable：約束/不約束新數(shù)據(jù);novalidate/validate:不對(duì)/對(duì)老數(shù)據(jù)進(jìn)行驗(yàn)證]*/
alter table table_name add constraint constraint_name check(column_name like 'B%') enable/disable novalidate/validate;
/*修改約束條件，延時(shí)驗(yàn)證，commit時(shí)驗(yàn)證*/
alter table table_name modify constraint constraint_name initially deferred;
/*修改約束條件，立即驗(yàn)證*/
alter table table_name modify constraint constraint_name initially immediate;
alter session set constraints=deferred/immediate;
/*drop一個(gè)有外鍵的主鍵表,帶cascade constraints參數(shù)級(jí)聯(lián)刪除*/
drop table table_name cascade constraints;
/*當(dāng)truncate外鍵表時(shí)，先將外鍵設(shè)為無(wú)效，再truncate;*/
truncate table table_name;
/*設(shè)約束條件無(wú)效*/
alter table table_name disable constraint constraint_name;
alter table table_name enable novalidate constraint constraint_name;
簡(jiǎn)潔實(shí)用，謝謝http://wfly2004.blog.163.com/blog/static/11764272010629114155174/
http://hi.baidu.com/zhangcheng1/blog/item/54deb0cc9ab69d1701e9281e.html