oracle9i学习笔记

dex_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);

/*不效验老数据，只约束新的数据[enable/disable：约束/不约束新数据;novalidate/validate:不对/对老数据进行验证]*/
alter table table_name add constraint constraint_name check(column_name like 'B%') 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;

/*当truncate外键表时，先将外键设为无效，再truncate;*/
truncate table table_name;

/*设约束条件无效*/
alter table table_name disable constraint constraint_name;

alter table table_name enable novalidate constraint constraint_name;

/*将无效约束的数据行放入exception的表中，此表记录了违反数据约束的行的行号；在此之前，要先建exceptions表*/
alter table table_name add constraint constraint_name check(column_name >15) enable validate exceptions into exceptions;

/*运行创建exceptions表的脚本*/
start $ORACLE_HOME/rdbms/admin/utlexcpt.sql;

/*获取约束条件信息的表或视图*/
select * from user_constraints/dba_constraints/dba_cons_columns;

################## managing password security and resources ####################

alter user user_name account unlock/open;----锁定/打开用户;

alter user user_name password expire;---设定口令到期

/*建立口令配置文件,failed_login_attempts口令输多少次后锁，password_lock_times指多少天后口令被自动解锁*/
create profile profile_name limit failed_login_attempts 3 password_lock_times 1/1440;
/*创建口令配置文件*/
create profile profile_name limit failed_login_attempts 3 password_lock_time unlimited password_life_time 30 password_reuse_time 30 password_verify_function verify_function password_grace_time 5;
/*建立资源配置文件*/
create profile prfile_name limit session_per_user 2 cpu_per_session 10000 idle_time 60 connect_time 480;

alter user user_name profile profile_name;

/*设置口令解锁时间*/
alter profile profile_name limit password_lock_time 1/24;

/*password_life_time指口令文件多少时间到期，password_grace_time指在第一次成功登录后到口令到期有多少天时间可改变口令*/

上一页  [1] [2] [3] [4] [5] [6] [7] [8] [9] [10] 下一页    >>