数据库原理 主键索引Primary Key Index role(角色), privileges(权限),user(用户)

whenever you create a primary key, Oracle creates an index on the column(s) that make up the primary key. If an index already exists on those columns, then Oracle will use that index
一旦你创造了一个主键, Oracle将创建一个此列上的索引来组成主键. 如果这些列上的索引已经存在, 那么Oracle将会使用这个索引.

Indexes是Primary Key 的主要部分. 根据Primary Key的选项, index分为unique和nonunique. Deferrable Primary key 约束使用nonunique index. Indexes are not used to enforce the business rule of the primary key, but an index is still required. Index的好处是对表的查询. 如果Primary Key约束失效, the Index将会删除查询性能会受到影响

索引也需要物理存储空间, Primary Key应该为index分配tablespace. Because of I/O contention an dthe fact that indexes grow differently than tables, 一般把indexes放在单独的tablespaces中.

其中的ALTER TABLE语句创建了Primary Key, 而且为Index分配了表空间. 如果没有为primary key index分配表空间, 这个index将建在你默认的tablespace里.

CREATE TABLE students
(student_id    VARCHAR2(10),
   student_name  VARCHAR2(30),
   college_major VARCHAR2(15),
   status        VARCHAR2(20)) TABLESPACE student_data;

ALTER TABLE students
  ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
   USING INDEX TABLESPACE student_index;

To create tables and indexes in tablespaces other than your default requires privileges. If you have the RESOURCE role, then you may still have the privilege(权利) UNLIMITED TABLESPACE—this privilege is automatically inherited with the RESOURCE role and gives you unlimited tablespace quotas(配额). With UNLIMITED TABLESPACE you are able to create objects in any tablespace including the SYSTEM tablespace. For this reason, the privilege is often revoked(撤消) from application developers and tablespace quotas are added.

The change made to developer accounts(开发者帐号) is similar to the change made to the SCOTT account as follows

REVOKE UNLIMITED TABLESPACE FROM SCOTT;
ALTER USER SCOTT QUOTA UNLIMITED ON STUDENT_DATA;
ALTER USER SCOTT QUOTA UNLIMITED ON STUDENT_INDEX;
ALTER USER SCOTT DEFAULT TABLESPACE STUDENT_DATA;

在SQLPLUS下可以做如下操作 :

建一个角色vodmanager:

create    role    vodmanager identified   by    vodmanager ;

给角色授予权限:

  grant   create    session,create   table,create   view,create   any   index,create    sequence,create    type   to    vodmanager;

建立一个用户:

create   user    vodmanager1 identified   by    vodmanager1 default    tablespace    users   temporary    tablespace   temp;

给用户分配角色:

grant   vodmanager   to    vodmanager1;

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>