作者：你妈咪叫你吃饭呢 | 来源：互联网 | 2014-07-13 17:52
/*给创建bitmapindex分配的内存空间参数，以加速建索引*/showparametercreate_bit;www.2cto.com/*改变索引的存储参数*/alterindexindex_namepctfree30storage(initial200knext200k);/...
/*给创建bitmap index分配的内存空间参数，以加速建索引*/
show parameter create_bit; www.2cto.com
/*改变索引的存储参数*/
alter index index_name pctfree 30 storage(initial 200k next 200k);
/*给索引手工分配一个分区*/
alter index index_name allocate extent (size 200k datafile &＃39;$ORACLE/oradata/..&＃39;);
/*释放索引中没用的空间*/
alter index index_name deallocate unused;
/*索引重建*/
alter index index_name rebuild tablespace tablespace_name;
/*普通索引和反转索引的互换*/
alter index index_name rebuild tablespace tablespace_name reverse;
/*重建索引时，不锁表*/
alter index index_name rebuild online;
/*给索引整理碎片*/ www.2cto.com
alter index index_name COALESCE;
/*分析索引,事实上是更新统计的过程*/
analyze index index_name validate structure;
desc index_state;
drop index index_name;
alter index index_name monitoring usage;-----监视索引是否被用到
alter index index_name nomonitoring usage;----取消监视
/*有关索引信息的视图*/
select * from dba_indexes/dba_ind_columns/dbs_ind_eXPressions/v$object_usage;
########## 数据完整性的治理(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);-----创建主键
alter table table_name add constraint constraint_name unique(column_name1,column_name2);---创建唯一约束
/*创建外键约束*/
alter table table_name add constraint constraint_name foreign key(column_name1) references table_name(column_name1); www.2cto.com
/*不效验老数据，只约束新的数据[enable/disable：约束/不约束新数据;novalidate/validate:不对/对老数据进行验证]*/
alter table table_name add constraint constraint_name check(column_name like &＃39;B%&＃39;) enable/disable novalidate/validate;
/*修改约束条件，延时验证，commit时验证*/
alter table table_name modify constraint constraint_name initially deferred;
/*修改约束条件，立即验证*/
alter table table_name modify constraint constraint_name initially immediate;
alter session set cOnstraints=deferred/immediate;
/*drop一个有外键的主键表,带cascade constraints参数级联删除*/
drop table table_name cascade constraints; www.2cto.com
/*当truncate外键表时，先将外键设为无效，再truncate;*/
truncate table table_name;
/*设约束条件无效*/
alter table table_name disable constraint constraint_name;
alter table table_name enable novalidate constraint constraint_name;
作者 javawsg
京公网安备 11010802041100号