第三部分 用户、表、索引和视图

1、第三部分 用户、表、索引和视图一、用户管理1、用户管理用户：为了使用Oracle，需要为使用者建立自己的用户帐户，建立并管理帐户是用户管理的任务，实现阻止非授权用户访问数据库中的信息。在实际应用中，应该避免使用sys、system、scott等帐户。模式：数据库模式定义为数据库对象的集合，而模式名称就是拥有或控制这些数据库对象集合的用户名称。所有的数据库对象 包括表、索引、视图、触发器、存储过程、程序包、函数等都归Oracle数据库中的一个用户所有。甚至Oracle数据字典、系统目录也是sys模式的一部分。（1）建立帐户CREATE USER user_nameIDENTIFIED BY pa

2、sswordDEFAULT TABLESPACE def_tablespaceTEMPORARY TABLESPACE temp_tablespace例：CREATE USER oracle_admin IDENTIFIED BY oracle_adminCREATE USER mytest IDENTIFIED BY hello（2）改变用户密码ALTER USER usre_name IDENTIFIED BY new_password或password /仅用于修改用户自己的密码ALTER USER mytest IDENTIFIED BY window（3）锁定及解除帐户ALTER U

3、SER user_name ACCOUNT lock|unlockALTER USER mytest ACCOUNT lockALTER USER mytest ACCOUNT unlock（3）修改用户表空间ALTER USER user_name DEFAULT TABLESPACE new_def_tablespaceTEMPORARY TABLESPACE new_def_tablespace从10g开始，用户的默认表空间由system修改为sysaux，但是建议在使用user表空间。ALTER USER mytest DEFAULT TABLESPACE users TEMPORAR

4、Y TABLESPACE temp（4）修改表空间配额ALTER USER user_name QUOTA nM ON tablespaceALTER USER mytest QUOTA 10M ON users查看分配给用户的表空间份额可以使用dba_ts_quotas、user_ts_quotas两个视图。（5）删除用户DROP USER user_name cascade（6）查看用户信息在dba_users数据字典中存有用户信息。Dba_users结构：USERNAME 用户名称USER_ID 用户IDPASSWORD 口令（加密）ACCOUNT_STATUS 用户状态LOCK_DAT

5、E 锁定日期EXPIRY_DATE 到期日期DEFAULT_TABLESPACE 缺省表空间TEMPORARY_TABLESPACE 临时表空间CREATED 创建日期PROFILE 概要文件INITIAL_RSRC_CONSUMER_GROUP 用户所在组EXTERNAL_NAME 外部名User_users数据字典中存有当前用户的信息。2、系统权限管理在Oracle数据库中，有两类权限：对象权限和系统权限。对象权限是由用户赋予的访问或操作数据库对象的权限。而系统权限是用来许可对各种特性的访问，或许可Oracle数据库中的特定任务。每个用户的Oracle数据库的系统权限存储在dba_sys_

6、privs数据库视图中，用户自己的系统权限在user_sys_privs视图中存储。常见的系统权限见P197表8-1向用户赋予系统权限的基本语法：GRANT system_privilege TO username WITH ADMIN OPTION删除用户系统权限的基本语法：REVOKE system_privilege FROM username例：GRANT create session TO mytestGRANT create table TO mytestREVOKE create session FROM mytestREVOKE create table FROM mytest

7、授权能力是可以传递的，当某个用户的系统权限被收回之后，从该用户转授出去的权限不能自动被收回，因此，撤销系统权限时不要求是最初授予系统权限的用户。任何具有admin option系统权限的数据库用户都能够取消其他用户的系统权限。3、对象权限管理最常用的对象权限如下：SELECT、INSERT、UPDATE、DELETE、EXECUTE、INDEX、REFERENCES和ALTER。授予、取消对象权限的语法格式：GRANT object_privilege ON object_name TO username WITH GRANT OPTIONREVOKE object_privilege ON

8、object_name FROM usernamegrant select on scott.emp to mytest WITH GRANT OPTIONrevoke select on scott.emp from mytest用户可以查询 user_tab_privs、all_tab_privs、dba_tab_privs数据库视图，检查对表的访问权限，用户对自己建立的对象拥有所有权限。与系统权限不同的是只有授权者才能够撤销其为其他用户授予的权限，同时从该用户转授出去的权限能自动收回。4、角色管理数据库管理员为每个用户直接授予或取消明确的对象权限，同时公司又有大量的员工，流动性

9、大，则管理员的工作将是非常巨大的。实际应用中，总是根据需要建立若干个数据库角色，角色就是对象权限和系统权限的命名集合。数据库管理员为不同的数据库角色授予不同的权限满足不同的需要，再将角色授予每个用户。角色的变化相对较小。而增减用户时只是给撤销用户或建立新用户并授予角色即可，因此管理的复杂度小，任务轻。（1）创建角色CREATE ROLE role_name IDENTIFIED BY role_passwordCREATE ROLE athos（2）为角色授权为角色授权与为用户授权使用的grant语句大体相同，取消数据库角色的权限与revoke语句也大致相同。GRANT select,inse

10、rt , update on mytable to athos（3）将角色赋予用户、收回角色GRANT role_name TO user_name with admin optionGRANT role_name TO role_namewith admin optionGRANT athos TO mytest REVOKE athos FROM mytest在默认情况下，当把某个角色授予一个用户时，该角色对用户开始起作用。当该用户登录系统后，就会自动拥有该角色的权限。但是可以限制用户自动拥有授予其的角色权限。Alter user mytest default role all excep

11、t XXXConnect mytest/helloSet role xxx identified by xxx（4）撤销角色DROP ROLE role_nameDROP ROLE athos（5）查看角色信息可以通过user_role_privs、role_sys_privs、role_tab_privs等数据字典视图查看有关角色的信息二、管理表表是数据库中最重要、最基本的对象，是实际存储数据的地方。对数据库的许多管理和操纵，实际上就是对数据库中表的管理和操纵。表由行和列组成，行也称为记录，列也称为字段或域。每一行都是这样实体的一个完整描述。行的顺序可以是任意的。列的顺序也是任意的。每一个列

12、都有一种数据类型，具有不可分割性，列名在一个表中具有唯一性。行在一个表中的唯一性一般由用户通过增加列的主键来强制实现。在物理上，需要确定表存储在哪个表空间，Oracle怎样将表物理存储在磁盘上，Oracle如何将表中数据与内存进行映射，Oracle怎样控制表上特定操作的日志。1、SQL数据类型数值类型：number类型，可以规定数字长度和小数位数；字符类型：包括char、nchar、varchar2、nvarchar2、raw等。Char和nchar为存储固定长度的字符串；Varchar2和nvarchar2为存储可变长度的字符串。日期时间类型：date和timestamp类型，date只能存

13、储到整数秒，timestamp可以为秒提供6位小数精度。2、创建表和约束创建表：CREATE TABLE schema_name.table_name (Column_name datatype DEFAULT defaultvalueCONSTRAINT constraint_exp ,Column_name datatype DEFAULT defaultvalueCONSTRAINT constraint_exp ,constraint )例：create table employee_history( employee_id number(6) not null, salary num

14、ber(8,2), hire_date date default sysdate,termination_date date, termination_desc varchar2(200) );Create table as select创建表Create table emp_copy as select * from emp注：创建一个表并带有数据，表结构（所包含的字段及类型）不变，但是不复制约束。Oracle系统中，可以使用声明型完整性约束和参照完整性约束来确保表中数据的正确。常见约束：主键约束、外键约束、唯一性约束、检查约束等。主键约束：Create中可以使用选项：constraint

15、主键约束名 primary key (主键字段集)ALTER TABLE schema.table_nameAdd constraint 主键约束名 primary key (主键字段集)Alter table employee_history add constraint emp_his_pk primary key (employee_id, hire_date)外键约束：构成表外键的列的取值要么是空值、要么来自参照表（父表）的取值。CREATE TABLE .CONSTRAINT fk_name FOREIGN KEY (外键列表) REFERENCES parent_table (pa

16、rent_table_columns )ALTER TABLE table_nameADD CONSTRAINT fk_name FOREIGN KEY (外键列表) REFERENCES parent_table (parent_table_columns )唯一性约束：确保表中的行对于值为非null的给定列或列组都具有唯一值，可以使用唯一性约束，因为主键只能有一个。唯一性约束的原则：P247。ALTER TABLE table_name ADD CONSTRAINT unique_key_name UNIQUE (column_names)检查约束：指定表中的某个列的取值范围。如学生成绩在

17、0100之间，性别为男或女等。ALTER TABLE table_name ADD CONSTRAINT ck_name CHECK(ck_expression)3、表类型表类型包括：堆表、外部表、索引组织表、临时表、分区表、簇表、散列簇表等（1）堆表：数据在磁盘上随机存储。关系表一般都建成堆表。例：创建主表学科表（学科ID、学科名称、描述）、加主键约束（学科ID）、创建子表课程表（课程ID、课程名称、学科ID、课程学时等）、加外键约束（学科ID）（2）外部表：在数据库之外的文件系统中存储的只读表。创建一个文本文件teachers.csv，数据用逗号分隔，存储在D:目录中。创建外部表（需要具有

18、CREATE ANY DIRECTORY权限）：首先创建目录：CREATE DIRECTORY ext_data_files AS D:建立外部表定义（增加 ORGANIZATION EXTERNAL子句）：CREATE TABLE sale_ext(First_name varchar2(15),Last_name varchar2(15),Phone_number varchar2(15)ORGANIZATION EXTERNAL(TYPE oracle_loaderDEFAULT DIRECTORY ext_data_filesACCESS PARAMETERS(fields termi

19、nated by ,)LOCATION(extsales.txt)REJECT LIMIT unlimited（3）临时表只在事务处理和会话期间存在数据的表。建立方法与常规表相同，用户只需要建立一次临时表，以后就可以在需要时使用它。临时表分为会话临时表和事务临时表，会话临时表中的数据在一次会话结束后删除，事务临时表中的数据在事务提交或回滚之后删除。创建会话临时表：CREATE GLOBAL TEMPORARY TABLE session_table (<column specification> )ON COMMIT PRESERVE ROWS创建事务临时表：CREATE GLOB

20、AL TEMPORARY TABLE session_table (<column specification> )ON COMMIT DELETE ROWS4、表特性Tablespace子句：表空间子句，用来指定存储表的表空间，缺省情况下，存储在用户的默认表空间中。Select default_tablespace from user_users;/查默认表空间CREATE TABLE table_name () TABLESPACE ts_nameALTER TABLE table_name TABLESPACE ts_nameStorage子句：（存储属性）用来管理如何给对象

21、分配磁盘空间。表空间的存储参数具有5个属性：initial、next、pctincrease、minextents和maxextents。创建表时如果不指定Storage子句，则表的存储属性取表空间的存储属性，否则使用创建表时指定的存储属性。Initial：第一个盘区（分区Extent）的大小；Next：第一个盘区填满后，随后盘区分配的空间大小； Pctincrease：表示“不断增长比例”，通常设为0；从第三个盘区开始前一NEXT值*(1+PCTINCREASE/100)Minextents：为表分配盘区数量的下限；Maxextents：为表分配盘区数量的上限。CREATE TABLE te

22、st(a number)STORAGE(INITIAL 100K NEXT 100K MINEXTENTS 2 MAXEXTENTS 100 PCTINCREASE 100); 解释：初始给test表分配两个Extent，第一个Extent是100K，因INITIAL=100K第二个Extent是100K，因NEXT=100K假如因表内数据增长，需要分配第三个Extent，因PCTINCREASE是100，则第三个Extent是200K=100K+100K第四个Extent是400K=200K+200K可通过数据字典表DBA_TABLES、ALL_TABLES、USER_TABLES查看参数设

23、置情况，如：select table_name,initial_extent,next_extent,min_extents,max_extents,pct_increase from user_tables;修改5、修改表增加列：ALTER TABLE table_name ADD ( column_name type ,column_name type)更新列：ALTER TABLE table_name MODIFY ( column_name type ,column_name type)删除列：ALTER TABLE table_name DROP column_name type

24、标注不用的列：ALTER TABLE table_name SET UNUSED COLUMN column_nameALTER TABLE table_name SET UNUSED COLUMNS重命名表：ALTER TABLE table_name RENAME TO new_table_name6、删除表DROP TABLE table_name CASCADE CONSTRAINTS /删除主表时删除子表的外键约束7、truncate tableTRUNCATE TABLE schema.table_name drop storage | reuse storage需要具有 drop

25、 table的权限、不产生回滚数据。Drop storage：默认选项，恢复表的最初存储属性REUSE STORAGE：保留表存储分配的盘区。8、数据字典User_tablespace、DBA_tablespace、User_tables、dba_tables、all_tables、user_tab_privs、dba_tab_privs、all_tab_privs、user_sys_privs、dba_sys_privs、user_constraints、dba_constraints、all_constraints;三、索引索引是一种树状结构，用于提高检索速度，特别是在大数据量情况下，效果

26、更佳明显。创建索引：CREATE INDEX index_name ON table_name(column_names)删除索引：DROP INDEX index_name例：CREATE TABLE indextest AS SELECT * FROM DBA_OBJECTS执行查询：SET AUTOTRACE TRACE EXPLAIN /只显示优化器执行路径报告SET AUTOTRACE OFF /关闭跟踪Select owner,object_name from indextest where object_name=DBA_INDEXES /观察cost开销的值CREATE IND

27、EX indextest_objname_idx on indextest(object_name)Select owner,object_name from indextest where object_name=DBA_INDEXES /再观察cost开销的值结论1：有索引时查询开销远远小于没有索引时的开销CREATE INDEX indextest_owner_idx on indextest(owner)Select owner,object_name from indextest where owner=SYSSelect owner,object_name from indexte

28、st where owner=SCOTT观察两者的Operation和cost结论2：当查询结果数据量占总数据量比例大时使用权标扫描，否则使用索引查找。使用索引可以提高查询速度，但是索引的存在也使数据的更新、插入、删除增加了索引维护的开销。主键、外键约束自动创建索引；反转键索引：create index命令中增加reverse选项基于函数的索引：索引可以是以字段为参数的函数。位图索引：CREATE BITMAP INDEX ，只存在于企业版中，是在低区分值列上创建的压缩对象。四、视图视图是一种逻辑表，是查看表的一种方式。可以定义单表视图也可定义多表连接视图。视图分类：关系视图、内嵌视图、对象视

29、图和物化视图。关系视图：关系视图：就是存储的查询语句，平常所说的视图就是关系视图。关系视图是虚拟表。关系视图可以出现在insert、update和delete语句中，就像在表上使用这些语句一样。CREATE OR REPLACE FORCE|NOFORCE VIEW view_namecolumn1 ,column2AS QUERYSTATEMENT WITH CHECK OPTION CONSTRAINT constraint_namewith readonly如：Grant create view to scottCreate or replace view computesalary(e

30、mployee_number,name,salary,salaryNew)As Select empno,ename,sal,sal*.1 From emp使用视图：Desc computesalarySelect * from computesalary再如：CREATE OR REPLACE VIEW dept_sum_vw (name,minsal,maxsal,avgsal)AS SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal) FROM emp e,dept d WHERE e.deptno=d.deptno GROUP BY d.dname;视图的定义原则：1.视图的查询可以使用复杂的SELECT语法，包括连接/分组查询和子查询；2.在没有WITH CHECK OPTION和 READ ONLY 的情况下，查询中不能使用ORDER BY 子句；3.如果没有为CHECK OPTION约束命名，系统会自动为之命名，形式为SYS_Cn;4.OR REPLACE选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。DML操作应遵循的原则：1.简单视图可以执行DML操作；2.在视图包含GROUP 函数，GROUP BY子句，DIS