1、第三部分 用户、表、索引和视图一、用户管理1、用户管理用户：为了使用 Oracle，需要为使用者建立自己的用户帐户，建立并管理帐户是用户管理的任务，实现阻止非授权用户访问数据库中的信息。在实际应用中，应该避免使用sys、system、scott 等帐户。模式：数据库模式定义为数据库对象的集合，而模式名称就是拥有或控制这些数据库对象集合的用户名称。所有的数据库对象 包括表、索引、视图、触发器、存储过程、程序包、函数等都归 Oracle 数据库中的一个用户所有。甚至 Oracle 数据字典、系统目录也是 sys 模式的一部分。（1）建立帐户CREATE USER user_nameIDENTIFI
2、ED BY passwordDEFAULT 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）锁定及解除
3、帐户ALTER USER 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 TABLESPAC
4、E users TEMPORARY 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 口令（加密）ACCOU
5、NT_STATUS 用户状态LOCK_DATE 锁定日期EXPIRY_DATE 到期日期DEFAULT_TABLESPACE 缺省表空间TEMPORARY_TABLESPACE 临时表空间CREATED 创建日期PROFILE 概要文件INITIAL_RSRC_CONSUMER_GROUP 用户所在组EXTERNAL_NAME 外部名User_users 数据字典中存有当前用户的信息。2、系统权限管理在 Oracle 数据库中，有两类权限：对象权限和系统权限。对象权限是由用户赋予的访问或操作数据库对象的权限。而系统权限是用来许可对各种特性的访问，或许可 Oracle 数据库中的特定任务。每个用
6、户的 Oracle 数据库的系统权限存储在 dba_sys_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 m
7、ytestREVOKE create table FROM mytest授权能力是可以传递的，当某个用户的系统权限被收回之后，从该用户转授出去的权限不能自动被收回，因此，撤销系统权限时不要求是最初授予系统权限的用户。任何具有admin option 系统权限的数据库用户都能够取消其他用户的系统权限。3、对象权限管理最常用的对象权限如下：SELECT、INSERT、UPDATE、DELETE 、EXECUTE、INDEX、REFERENCES 和ALTER。授予、取消对象权限的语法格式：GRANT object_privilege ON object_name TO username WITH
8、GRANT OPTIONREVOKE object_privilege ON 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 语句大体相同，取消数据库
10、角色的权限与 revoke 语句也大致相同。GRANT select,insert , 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在默认情况下，当把某个角色授予一个用户时，该角色对用户开始起作用。当该用户登录系统后，就会自动拥有该角色的权限。但是可以限制用户自动拥有授予其的角色权限。A
11、lter user mytest default role all except 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 和
13、 nvarchar2 为存储可变长度的字符串。日期时间类型：date 和 timestamp 类型，date 只能存储到整数秒，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
14、employee_history(employee_id number(6) not null,salary number(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 系统中，可以使用声明型完整性约束和参照完整性约束来确保表中数据的正确。常
15、见约束：主键约束、外键约束、唯一性约束、检查约束等。主键约束：Create 中可以使用选项：constraint 主键约束名 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 CONSTRAIN
16、T fk_name FOREIGN KEY (外键列表) REFERENCES parent_table (parent_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 uniq
17、ue_key_name UNIQUE (column_names)检查约束：指定表中的某个列的取值范围。如学生成绩在 0100 之间，性别为男 或女等。ALTER TABLE table_name ADD CONSTRAINT ck_name CHECK(ck_expression)3、表类型表类型包括：堆表、外部表、索引组织表、临时表、分区表、簇表、散列簇表等（1）堆表：数据在磁盘上随机存储。关系表一般都建成堆表。例：创建主表学科表（学科 ID、学科名称、描述） 、加主键约束（学科 ID） 、创建子表课程表（课程 ID、课程名称、学科 ID、课程学时等） 、加外键约束（学科 ID）（2）外部
18、表：在数据库之外的文件系统中存储的只读表。创建一个文本文件 teachers.csv，数据用逗号分隔，存储在 D:目录中。创建外部表（需要具有 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 o
19、racle_loaderDEFAULT DIRECTORY ext_data_filesACCESS PARAMETERS(fields terminated by ,)LOCATION(extsales.txt)REJECT LIMIT unlimited（3）临时表只在事务处理和会话期间存在数据的表。建立方法与常规表相同，用户只需要建立一次临时表，以后就可以在需要时使用它。临时表分为会话临时表和事务临时表，会话临时表中的数据在一次会话结束后删除，事务临时表中的数据在事务提交或回滚之后删除。创建会话临时表：CREATE GLOBAL TEMPORARY TABLE session_table
20、 ( )ON COMMIT PRESERVE ROWS创建事务临时表：CREATE GLOBAL TEMPORARY TABLE session_table ( )ON COMMIT DELETE ROWS4、表特性Tablespace 子句：表空间子句，用来指定存储表的表空间，缺省情况下，存储在用户的默认表空间中。Select default_tablespace from user_users;/查默认表空间CREATE TABLE table_name () TABLESPACE ts_nameALTER TABLE table_name TABLESPACE ts_nameStorag
21、e 子句：（存储属性）用来管理如何给对象分配磁盘空间。表空间的存储参数具有 5个属性：initial、next、pctincrease、minextents 和 maxextents。创建表时如果不指定 Storage子句，则表的存储属性取表空间的存储属性，否则使用创建表时指定的存储属性。Initial：第一个盘区（分区 Extent）的大小；Next：第一个盘区填满后，随后盘区分配的空间大小； Pctincrease：表示“不断增长比例 ”，通常设为 0；从第三个盘区开始 前一 NEXT 值*(1+PCTINCREASE/100)Minextents：为表分配盘区数量的下限；Maxexten
22、ts：为表分配盘区数量的上限。CREATE TABLE test(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
23、+200K可通过数据字典表 DBA_TABLES、ALL_TABLES、USER_TABLES 查看参数设置情况，如：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 t
24、ype)删除列：ALTER TABLE table_name DROP column_name type 标注不用的列：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 sc
25、hema.table_name drop storage | reuse storage需要具有 drop 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_constrain
26、ts、all_constraints;三、索引索引是一种树状结构，用于提高检索速度，特别是在大数据量情况下，效果更佳明显。创建索引：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 index
27、test where object_name=DBA_INDEXES /观察 cost 开销的值CREATE INDEX 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 in
28、dextest where owner=SYSSelect owner,object_name from indextest 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 rea
30、donly如：Grant create view to scottCreate or replace view computesalary(employee_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.s
31、al),avg(e.sal)FROM emp e,dept dWHERE e.deptno=d.deptnoGROUP BY d.dname;视图的定义原则：1.视图的查询可以使用复杂的 SELECT 语法，包括连接/ 分组查询和子查询；2.在没有 WITH CHECK OPTION 和 READ ONLY 的情况下，查询中不能使用 ORDER BY 子句；3.如果没有为 CHECK OPTION 约束命名，系统会自动为之命名，形式为 SYS_Cn;4.OR REPLACE 选项可以不删除原视图便可更改其定义并重建，或重新授予对象权限。DML 操作应遵循的原则：1.简单视图可以执行 DML 操
32、作；2.在视图包含 GROUP 函数，GROUP BY 子句，DISTINCT 关键字时不能删除数据行；3.在视图不出现下列情况时可通过视图修改基表数据或插入数据：a.视图中包含 GROUP 函数，GROUP BY 子句，DISTINCT 关键字；b.使用表达式定义的列；c.ROWNUM 伪列。d.基表中未在视图中选择的其他列定义为非空且无默认值。WITH CHECK OPTION 子句限定：通过视图执行的 INSERTS 和 UPDATES 操作不能创建该视图检索不到的数据行，因为它会对插入或修改的数据行执行完整性约束和数据有效性检查。视图的删除：DROP VIEW VIEW_NAME 语句
33、删除视图。删除视图的定义不影响基表中的数据。只有视图所有者和具备 DROP VIEW 权限的用户可以删除视图。视图被删除后，基于被删除视图的其他视图或应用将无效。内嵌视图：嵌入到父查询中的查询语句。可以出现在 insert into 、update、delete from 中。如：Select dname, count(*),to_char(count(*)/total_t*100,99.99)|% pctFrom dept,emp,(select count(*) cnt from emp) total_empWhere dept.deptno=emp.deptnoGroup by dnam
34、e,total_t再如：select ename,hiredatefrom (select ename,hiredatefrom emporder by hiredate)where rownum6;对象视图：将关系表投射到特定数据类型的虚拟对象表中，视图的每行都是带有属性、方法和唯一标识(OID)的对象实例。创建对象类型：CREATE TYPE emp_type is object(empid number,empname varchar2(10),emphiredate date)CREATE VIEW emp_view of emp_type with object oid(empid)AsSelect e.empno,e.ename ,e.hiredate from emp e物化视图：物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照。物化视图查询速度更快。
