内容发布更新时间 : 2024/11/18 7:31:06星期一 下面是文章的全部内容请认真阅读。

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

一、用户管理

1、用户管理

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

CREATE USER user_name IDENTIFIED BY password

[DEFAULT TABLESPACE def_tablespace]

[TEMPORARY TABLESPACE temp_tablespace] 例：

CREATE USER oracle_admin IDENTIFIED BY oracle_admin CREATE USER mytest IDENTIFIED BY hello （2）改变用户密码

ALTER USER usre_name IDENTIFIED BY new_password 或password //仅用于修改用户自己的密码 ALTER USER mytest IDENTIFIED BY window （3）锁定及解除帐户

ALTER USER user_name ACCOUNT [lock|unlock] ALTER USER mytest ACCOUNT lock ALTER USER mytest ACCOUNT unlock （3）修改用户表空间

ALTER USER user_name DEFAULT TABLESPACE new_def_tablespace [TEMPORARY TABLESPACE new_def_tablespace]

从10g开始，用户的默认表空间由system修改为sysaux，但是建议在使用user表空间。 ALTER USER mytest DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp （4）修改表空间配额

ALTER USER user_name QUOTA nM ON tablespace ALTER 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 用户ID

PASSWORD 口令（加密）

ACCOUNT_STATUS 用户状态 LOCK_DATE 锁定日期 EXPIRY_DATE 到期日期 DEFAULT_TABLESPACE 缺省表空间 TEMPORARY_TABLESPACE 临时表空间 CREATED 创建日期 PROFILE 概要文件 INITIAL_RSRC_CONSUMER_GROUP 用户所在组 EXTERNAL_NAME 外部名 User_users数据字典中存有当前用户的信息。 2、系统权限管理

在Oracle数据库中，有两类权限：对象权限和系统权限。对象权限是由用户赋予的访问或操作数据库对象的权限。而系统权限是用来许可对各种特性的访问，或许可Oracle数据库中的特定任务。

每个用户的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 mytest GRANT create table TO mytest

REVOKE create session FROM mytest REVOKE create table FROM mytest 授权能力是可以传递的，当某个用户的系统权限被收回之后，从该用户转授出去的权限不能自动被收回，因此，撤销系统权限时不要求是最初授予系统权限的用户。任何具有admin option系统权限的数据库用户都能够取消其他用户的系统权限。 3、对象权限管理

最常用的对象权限如下：SELECT、INSERT、UPDATE、DELETE、EXECUTE、INDEX、REFERENCES和ALTER。

授予、取消对象权限的语法格式：

GRANT object_privilege ON object_name TO username [WITH GRANT OPTION] REVOKE object_privilege ON object_name FROM username grant select on scott.emp to mytest WITH GRANT OPTION revoke select on scott.emp from mytest

用户可以查询 user_tab_privs、all_tab_privs、dba_tab_privs数据库视图，检查对表的访问权限，用户对自己建立的对象拥有所有权限。

与系统权限不同的是只有授权者才能够撤销其为其他用户授予的权限，同时从该用户转授出去的权限能自动收回。 4、角色管理

数据库管理员为每个用户直接授予或取消明确的对象权限，同时公司又有大量的员工，流动性大，则管理员的工作将是非常巨大的。实际应用中，总是根据需要建立若干个数据库角色，

角色就是对象权限和系统权限的命名集合。数据库管理员为不同的数据库角色授予不同的权限满足不同的需要，再将角色授予每个用户。角色的变化相对较小。而增减用户时只是给撤销用户或建立新用户并授予角色即可，因此管理的复杂度小，任务轻。 （1）创建角色

CREATE ROLE role_name [IDENTIFIED BY role_password] CREATE ROLE athos （2）为角色授权

为角色授权与为用户授权使用的grant语句大体相同，取消数据库角色的权限与revoke语句也大致相同。

GRANT select,insert , update on mytable to athos （3）将角色赋予用户、收回角色

GRANT role_name TO user_name [with admin option] GRANT role_name TO role_name[with admin option] GRANT athos TO mytest REVOKE athos FROM mytest

在默认情况下，当把某个角色授予一个用户时，该角色对用户开始起作用。当该用户登录系统后，就会自动拥有该角色的权限。但是可以限制用户自动拥有授予其的角色权限。 Alter user mytest default role all except XXX Connect mytest/hello

Set role xxx identified by xxx （4）撤销角色

DROP ROLE role_name DROP ROLE athos （5）查看角色信息

可以通过user_role_privs、role_sys_privs、role_tab_privs等数据字典视图查看有关角色的信息

二、管理表

表是数据库中最重要、最基本的对象，是实际存储数据的地方。对数据库的许多管理和操纵，实际上就是对数据库中表的管理和操纵。 表由行和列组成，行也称为记录，列也称为字段或域。每一行都是这样实体的一个完整描述。行的顺序可以是任意的。

列的顺序也是任意的。每一个列都有一种数据类型，具有不可分割性，列名在一个表中具有唯一性。行在一个表中的唯一性一般由用户通过增加列的主键来强制实现。

在物理上，需要确定表存储在哪个表空间，Oracle怎样将表物理存储在磁盘上，Oracle如何将表中数据与内存进行映射，Oracle怎样控制表上特定操作的日志。 1、SQL数据类型

数值类型：number类型，可以规定数字长度和小数位数；

字符类型：包括char、nchar、varchar2、nvarchar2、raw等。Char和nchar为存储固定长度的字符串；Varchar2和nvarchar2为存储可变长度的字符串。

日期时间类型：date和timestamp类型，date只能存储到整数秒，timestamp可以为秒提供6位小数精度。 2、创建表和约束 创建表：

CREATE TABLE [schema_name.]table_name (

Column_name datatype [DEFAULT defaultvalue][CONSTRAINT constraint_exp ] [,Column_name datatype [DEFAULT defaultvalue][CONSTRAINT constraint_exp ]] [,……]

[constraint ……] ) 例：

create table 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系统中，可以使用声明型完整性约束和参照完整性约束来确保表中数据的正确。常见约束：主键约束、外键约束、唯一性约束、检查约束等。 主键约束：

Create中可以使用选项：constraint 主键约束名 primary key (主键字段集) ALTER TABLE [schema.]table_name

Add [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 (parent_table_columns ) ALTER TABLE table_name

ADD 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) 检查约束：

指定表中的某个列的取值范围。如学生成绩在0——100之间，性别为’男’或’女’等。 ALTER TABLE table_name ADD CONSTRAINT ck_name CHECK(ck_expression) 3、表类型

表类型包括：堆表、外部表、索引组织表、临时表、分区表、簇表、散列簇表等 （1）堆表：数据在磁盘上随机存储。关系表一般都建成堆表。 例：创建主表学科表（学科ID、学科名称、描述）、加主键约束（学科ID）、创建子表课程表（课程ID、课程名称、学科ID、课程学时等）、加外键约束（学科ID）

（2）外部表：在数据库之外的文件系统中存储的只读表。

创建一个文本文件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 oracle_loader

DEFAULT DIRECTORY ext_data_files

ACCESS PARAMETERS(fields terminated by ‘,’) LOCATION(‘extsales.txt’) )

REJECT LIMIT unlimited （3）临时表

只在事务处理和会话期间存在数据的表。建立方法与常规表相同，用户只需要建立一次临时表，以后就可以在需要时使用它。临时表分为会话临时表和事务临时表，会话临时表中的数据在一次会话结束后删除，事务临时表中的数据在事务提交或回滚之后删除。 创建会话临时表：

CREATE GLOBAL TEMPORARY TABLE session_table ( ) ON COMMIT PRESERVE ROWS 创建事务临时表：

CREATE GLOBAL TEMPORARY TABLE session_table ( ) ON COMMIT DELETE ROWS 4、表特性

Tablespace子句：表空间子句，用来指定存储表的表空间，缺省情况下，存储在用户的默认表空间中。

Select default_tablespace from user_users;//查默认表空间 CREATE TABLE table_name (……) TABLESPACE ts_name ALTER TABLE table_name TABLESPACE ts_name Storage子句：（存储属性）用来管理如何给对象分配磁盘空间。表空间的存储参数具有5个属性：initial、next、pctincrease、minextents和maxextents。创建表时如果不指定Storage子句，则表的存储属性取表空间的存储属性，否则使用创建表时指定的存储属性。 Initial：第一个盘区（分区Extent）的大小；

Next：第一个盘区填满后，随后盘区分配的空间大小； Pctincrease：表示“不断增长比例”，通常设为0；从第三个盘区开始前一NEXT值*(1+PCTINCREASE/100)

Minextents：为表分配盘区数量的下限； Maxextents：为表分配盘区数量的上限。 CREATE TABLE test(a number) STORAGE(