《Oracle管理索引、权限和角色》由会员分享，可在线阅读，更多相关《Oracle管理索引、权限和角色（25页珍藏版）》请在金锄头文库上搜索。
1、1掌握维护oracle数据完整性的技巧 2理解索引概念，会建立索引3管理oracle的权限和角色维护数据的完整性数据的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中，数据完整性 可以使用约束、触发器、应用程序（过程、函数）三种方法来实现，在这三种方法中，因为 约束易于维护，并且具有最好的性能，所以作为维护数据完整性的首选。约束约束用于确保数据库数据满足特定的商业规则。在oracle中约束包括not null、unique（唯一）、primary key、foreign key 和 check（检查）五种。not null（非空）如果在列上定义了 not null，那么当插入
2、数据时，必须为列提供数据。un ique（唯一）当定义了唯一约束后，该列值是不能重复的，但是可以为null。primary key（主 键）用于唯一的标示表行的数据，当定义主键约束后，该列不但不能重复而且不能为null。需要说明的是：一张表最多只能有一个主键，但是可以有多个un qiue约束。foreign key（外键）用于定义主表和从表之间的关系。外键约束要定义在从表上，主表则必须具有主键约束或是 unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。check 用于强制行数据必须满足的条件，假定在sal列上定义了 check约束，并要求sal列值在 10
3、002000之间，如果不在10002000之间就会提示出错。 商店售货系统表设计案例-创建SQL create table scott.goods (-商品表goodsld char(8) primary key,-主键goodsName varchar2(30),un itprice n umber(10,2) check (un itprice 0), category varchar2(8),provider varchar2(30);SQL create table customer (-客 户表customerId char(8) primary key,-主键n ame varch
4、ar2(50) not n ull,-不为空address varchar2(50),email varchar2(50) uniq ue,sex char(2) default 男check (sex in (男,女),cardId char(18);SQL create table purchase (-购买表customerld char(8) refere nces customer (customerld),goodsld char(8) refere nces goods (goodsId),n ums n umber(5) check (n ums betwee n 1 and
5、30);商店售货系统表设计案例(2)-维护增加商品名不能为空SQLalter table goods modify goodsName not n ull;增加身份证不能重复SQLalter table customer add con stra int card unique uniq ue(cardld);增加客户的住址限制SQLalter table customer add con stra int addresscheck check (address in (东城西 城)； 删除约束 当不在需要某个约束时，可以删除。SQLalter table 表名 drop constraint
6、 约束名称；特别说明：在删除主键约束的时候，可能有错误，比如：SQLalter table 表名 drop primary key;这是因为如果两张表存在主从关系，那么在删除主表的主键约束时，必须带上cascade选 项，如下:SQLalter table 表名 drop primary key cascade;显示约束信息1显示约束信息通过查询数据字典视图user_constraints，可以显示当前用户所有的约束的信息。SQLselect con stra int_n ame,c on strai nt_type,status,validated from user_c on strai
7、nts where table_name =表名；2显示约束列通过查询数据字典视图user_cons_cloumns，可以显示约束所对应的表列信息。SQLselect colum n_n ame,positi on from user_c on s_cloum ns wherecon strai nt_n ame二约束名；3当然也有更容易的方法，直接用pl/sql developer查看即可。表级定义和列级定义列级定义列级定义是在定义列的同时定义约束如在departme nt表定义主键约束SQLcreate table departme nt4(dept_id n umber(2) con s
8、trai nt pk_departme nt primary key,n ame varchar2(12),loc varchar2(12);表级定义 表级定义是指在定义了所有列后，再定义约束。这里需要注意的是not n ull约束只能在列级上定义。以在建立employee2表时定义主键约束和外键约束为例：SQLcreate table employee2(emp_id n umber(4), name varchar2(15),dept_id n umber(2),con strai nt pk_employee primary key (emp_id),con strai nt fk_de
9、partme nt foreig n_key (dept_id)refere nces departme nt4(dept_id);管理索引-原理介绍索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引有很多种，我们主要介绍常用的几种：为什么添加了索引后，会加快查询速度呢？管理索弓卜-创建索引单列索引单列索引是基于单个列所建立的索引，比如：create index 索引名 on 表名(列名)复合索引复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引，但是要求列的组合必 须不同，比如：create in dex emp_idx1 on em
10、p (en ame,job); create in dex emp_idx2 on emp (job, name);使用原则(1)在大表上建立索引才有意义在where子句或是连接条件上经常引用的列上建立索引(3)索引的层次不要超过4层索引缺点分析索引有一些先天不足：1建立索引，系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。2更新数据的时候，系统必须要有额外的时间来同时对索引进行更新，以维持数据和索引的 一致性。实践证明不恰当的索引不但于事无补反而会降低系统性能。因为大量的索引在进行插入、修改和删除时比没有索引花费更多的时间。比如在如下字段建立索引应该是不恰当的：1很少或从不引用的字段
11、2逻辑型的字段，如男或女(是或否)等。综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标。其他索引按照数据存储方式，可以分为B*树、反向索引、位图索引；按照索引列的个数分类，可以分为单列索引、复合索引；按照索引列值的唯一性，可以分为唯一索引和非唯一索引。此外还有函数索引，全局索引，分区索引.对于索引来说，在不同的情况我们会在不同的列上建立索引，甚至建立不同种类的索引，青 记住，技术是死的，人是活的。比如：B*树索引建立在重复值很少的列上，而位图索引则建立在重复值很多、不通知相对固定的 列上。显示索引信息显示表的所有索引在同一张表可
12、以有多个索引，通过查询数据字典视图dba_indexs和userndexs，可以显 示索引信息。其中dba_indexs用于显示数据库所有的索引信息，而userndexs用于显示 当前用户的索引信息：select in dex_ name,i ndex_type from user_i ndexs where table _n ame二表名；显示索引列通过查询数据字典视图usernd_columns，可以显示索引对应的列的信息select table _n ame,colum n_n ame from user_ in d_colum ns wherein dex_name二IND_ENAME
13、;你也可以通过pl/sql developer工具查看索引信息。掌管权限和角色这一部分我们主要看oracle中如何管理权限和角色，权限和角色的区别在哪里。当刚刚建立用户时，用户没有任何权限，也不能执行任何操作。如果要执行某种特定的数据 库操作，则必须为其授予系统的权限；如果用户要访问其他方案的对象，则必须为其授予对 象的权限。为了简化权限的管理，可以使用角色。权限 权限是指执行特定类型的sql命令或是访问其他方案对象的权利/包括系统权限和对象权限 两种：系统权限介绍：系统权限是指执行特定类型sql命令的权利。它用于控制用户可以执行的一个或是一组数据 库操作。比如当用户具有create tabl
14、e权限时可以在其方案中建表当用户具有create any table权限时，可以在任何方案中建表。oracle提供了 100多种系统权限。常用的有；create sessio n 连接数据库create table 建表create view 建视图create public synonym 建同义词create procedure 建过程、函数、包 create trigger 建触发器create cluster 建簇显示系统权限oracle提供了 100多种系统权限，而且oracle的版本越高，提供的系统权限就越多，我们 可以查询数据字典视图system_privilege_map，可以
15、显示所有系统权限。select * from system_privilege_map order by n ame;授权系统权限一般情况，授予系统权限是由dba完成的，如果用其它用户来授予系统权限，则要求该用 户必须具有gra nt any privilege的系统权限在授予系统权限时，可以带有with admi n option选项，这样，被授予权限的用户或是角色还可以将该系统权限授予其它的用户或是 角色。为了让大家快速理解，我们举例说明：1创建两个用户ken,tom。初始阶段他们没有任何权限，如果登录就会给出错误的信息。 create user ken identified by m123;2给用户ken授权 gra nt create sessi on ,create table to ken with adm in opti on; gra nt create view to ken;3给用户tom授权我们可以通过ken给tom授权，因为with admin option是加上的
