【基于SSH框架的企业级应用开发】Oracle数据库应用

Oracle数据库应用

表空间和用户权限管理

表空间

1.分类:

• 永久性表空间
• 临时性表空间
• 撤销性表空间

2.表空间的目的

• 对不同用户分配不同的表空间,对不同的模式对象分配不同的表空间,方便对用户数据的操作,对模式对象的管理
• 可以将不同数据文件创建到不同的磁盘中,有利于管理磁盘空间,有利于提供I/O性能,有利于备份和恢复数据等
• 一般完成Oracle系统的安装并创建oracle实例后,oracle系统会自动建立多个表空间

3.创建表空间

语法:create tablespace 空间名

DATAFILE ‘filename’ [SIZE integer [K | M]]

[AUTOEXTEND [OFF |ON]]

在语法中:

• DATAFILE 指定组成表空间的一个或多个数据文件,当有多个数据文件时使用逗号分隔
• filename’ 数据文件的路径和名称
• Size指定文件的大小,用K指定千字节大小,用M指定兆字节大小
• AUTOEXTEND子句用来禁用数据文件的自动扩展,设置为on则空间使用完毕会自动扩展,设置为off则很容易出现表空间剩余容量为0的情况,使数据不能存储到数据库中

1. 删除表空间

语法:DROP TABLESPACE 空间名

删除表空间之前最好对数据进行备份

自定义用户管理

1. SYS用户

sys用户是oracle中的一个超级用户.数据库中所有数据字典和视图都存储在sys模式中.数据字典存储了用来管理数据库对象的所有信息,是oracle数据库中非常重要的系统信息.sys用户主要用来维护系统信息和管理实例.

1. System用户

System用户是oracle中默认的系统管理员,它拥有DBA权限.该用户拥有oracle管理工具使用的内部表和视图.通常system用户管理oracle数据库的用户,权限和存储等.不建议在sustem模式中创建用户表.system用户不能以syspoer或SYSDBA角色登陆系统,只能以默认方式登陆

1. Scott用户

Scott用户是oracle数据库的一个示范用户,一般实在数据库安装时创建.scott用户模式包含四个示范表,其中一个是Emp表,使用users表空间存储模式对象,出于安全考录,不同数据库表需要设置不同的访问权限.就需要不同的用户.

创建用户

语法:

CREATE USER user

IDENTIFIED BY password

[DEFAULT TABLESPACE tablespace]

[TEMPORARY TABLESPACE tablespace]

user是用户名,用户名必须是一个标识符

Password是用户口令,口令必须是一个标识符,且不区分大小写

DEFAULT TABLESPACE/TEMPORARY RABLESPACE 为用户确定默认表空间或临时表空间

修改口令

将martin的口令修改为mpwd:

ALTER USER martin

IDENTIFIED BY mpwd

数据库权限管理

权限是用户对一项功能的执行权力

1. 系统权限

指授权用户是否可以连接到数据库上及在数据库中可以进行哪些系统操作.系统权限是在数据库中执行某种系统级别的操作,或者针对某一类的对象执行某种操作的权力.

如:在数据库中创建表空间的权力

常见的系统权限如下:

• CREATE SESSION:连接到数据库
• CEEATE TABLE:创建表
• CREATE VIEW:创建视图
• CREATE SEQUENCE:创建序列

1. 对象权限

指用户对数据库具有对象所拥有的权限.对象权限是针对某个特定的模式对象执行操作的权力,只针对模式对象来设置和管理对象权限

Oracle数据库用户获得权限途径:

• 管理员直接向用户授予权限
• 管理员将权限授予角色,然后在将角色授予一个或多个用户

使用角色能够更加方便和高效的对权限进行管理,所以数据库管理员通常使用角色向用户授予权限,而不是直接向用户授予权限

Oracle中常用的系统预定义角色如下

• Connect:需要连接上数据库的用户,特别是那些不需要创建表的用户,通常授予该角色
• RESOURCE:更为可靠和正式的数据库用户可以授予该角色,可以创建表/触发器/过程等
• DBA:数据库管理员角色,拥有管理数据库的最高权限,一个具有DBA角色的用户可以撤销任何其他用户甚至其他DBA权限,这是很危险的,所以不要轻易授予该角色

授权:

GANT 权限|角色 TO 用户名;

取消授权:

REVOKE 权限|角色 from 用户名;

数据库用户安全设计原则:

1. 数据库用户权限按照最小分配原则
2. 数据库用户分为管理、应用、维护、备份四类用户
3. 不允许使用Sys和System用户建立数据库应用对象
4. 禁止GRANT dba TO user

序列

创建序列

序列是用来生成唯一，连续的整数的数据库对象。序列通常用来自动生成主键或唯一键的值，序列可以按照升序排列，也可以按降序排列。

创建序列语法：

CREATE SEQUENCE 名字

[START WITH integer]

[INCREMENT BY integer]

[MAXBALUE integer |NOMAXVALUE]

[MINVALUE integer |NOMINVALUE]

[CYCLE |NOCYCLE]

[CACHE integer | NOCACHE]

• 在语法中：
  START WITH：指定要生成的的第一个序列号，对于升序序列，其默认值为序列的最小值，对于降序序列，其默认值为序列的最大值
• INCREMENT BY：用于指定序列号之际的间隔，默认值为1，如果n为正值，则生成的序列将按升序排列：为负值，则生成序列降序排列
• MAXBALUE ：指定序列可以生成最大值
• NOMINVALUE：指定了NOMINVALUE，oracle将升序序列的最大值设为1027  ，将降序序列的最大值设为-1.这是默认选项
• MINVALUE ：指定了序列最小值。MINVALUE 必须小于等于START WITH的值，并且必须小于MAXVALUE
• NOMINVALUE：指定了NOMINVALUE，oracle将升序序列的最小值设为1，将降序序列的最小值设为-1026 .这是默认选项
• CYCLE ：指定序列在打到最大值或最小值后，将继续从头开始生成值
• NOCYCLE：指定序列在达到最大值或最小值后，将不能再继续生成值。这是默认选项
• CACHE ：使用CACHE选项可以预先分配一组序列号，并将其保留在内存中，这样可以更快的访问序列号，当用完缓存中的所有序列号时，oracle将生成另一组数值，并将其保留在缓存中
• NOCACHE：使用NOCACHE选项，则不会加快访问速度而预先分配序列号，如果在创建序列时忽略了NOCACHE/CACHE选项，oracle将默认缓存20个序列号

需求：创建序列。从序号10开始，每次增加1，最大为2000不循环，再增加会报错，缓存30个序列号

访问序列

创建序列之后，可以通过NETTVAL和CURRVAL伪列来访问该序列的值。可以从位列中选择值，但是不能操纵他们的值。

• NEXTVAL:创建序列后第一次使用NEXTVAL时，将返回该序列的初始值。以后再引用NEXTVAL时，将使用INCREMENT by子句来增加序列值，并返回这个新值
• CURRVAL：返回序列的当前值，即最后一次引用NEXTVAL时返回的值

更改序列

1. 设置或删除MINVALUE或MAXVALUE
2. 修改增量值
3. 修改缓存中序列号的数目

更改序列语法：

ALTER SEQUENCE [shema.]sequence_name

[INCREMENT BY integer]

[MAXBALUE integer |NOMAXVALUE]

[MINVALUE integer |NOMINVALUE]

[CYCLE |NOCYCLE]

[CACHE integer | NOCACHE]

删除序列

语法：

DROP SEQUENCE [schema.] sequence_name;

使用序列

可以使用序列设置oracle的主关键字，所得值为从给定的起点开始的一系列整数值。序列所生成的数字只能抱着在单个实例里是唯一的，这就不适合将它用作并行或者远程环境里的主关键字，因为各自环境里的序列可能会生成相同的数字，导致冲突的发生，所以不需要并行的环境中，可选择使用序列作为主关键字。

【提示】

使用序列设置关键字时,在数据库迁移时需要特别注意.由于迁移后的表已经存在数据,如果不修改序列的起始值,将会在表中插入重复数据,违背主键约束.所以再修改序列时要修改序列的起始值

同义词

在数据库中,同义词可以提供其他数据库对象的别名,同义词是使用对象的一个别名,不占用任何实际的存储空间,只是在oracle的数据紫电中保存定义描述.

同义词用途

1. 简化SQL语句
2. 隐藏对象的名称和所有者
3. 为分布是数据库的远程对象提供了位置透明性
4. 提供对对象的公共访问

同义词分类

1. 私有同义词

只能被当前模式的用户访问,且私有同义词名称不可与当前模式的对象名称相同,要在当前模式下创建私有同义词,用户必须拥有CREATE SYNINYM系统权限.

创建私有同义词语法:

CREATE [OR REPLACE] SYNONYM [schema.]synonym_name

For [schema.] object_name;

语法中:

• OR REPLACE:表示在同义词存在的情况下替换该同义词
• synonym_name:表示要创建的同义词名称
• object_name:指定要为值创建同义词的对象的名称

1. 公有同义词

公有同义词可被所有的数据库用户访问.公有同义词可以隐藏数据库对象的所有者和名称,并降低SQL语句的复杂性

语法:

CREATE [OR REPLACE] PUBLIC SYNONYM synonym_name

FOR [schema.]object_name

1. 公有同义词喝私有同义词的区别

私有同义词只能在当前模式下访问.且不能与当前模式的对象同名

共有同义词可被所有的数据库用户访问

1. 使用同义词前,要获得同义词对应对象的访问权限
2. 对象(如表)与私有同义词不能同名;对象和公有同义词同名时,数据库优先选择对象作为目标,私有同义词和公有同义词同名时,数据库优先选择私有同义词作为目标,私有同义词和公有同义词同名时,数据库优先选择私有同义词作为目标

删除同义词

DROP SYNONYM语句用于从数据库中删除同义词.要删除同义词,用户必须拥有相应的权限

删除语法:

DROP [PUBLIC] SYNONYM [schema.]synonym_name;

索引

索引是与表关联的课选结构,是一种快速访问数据的途径,可提高数据库性能.数据库可以明确的简历索引,加快对表执行SQL语句的速度,当索引键作为查询条件时,该索引将直接只想包含这些值的行的位置,即便删除索引,也无需修改任何SQL语句的定义

索引的分类

索引分类表

物理分类	逻辑分类
分区或非分区索引	单列或组合索引
B树索引(标准索引)	唯一或非唯一索引
正常或反向键索引	基于函数索引
位图索引

B数索引

也称为标准索引.索引的顶部为根,其中包含指向索引中下一级的项.下一级为分支块,分支块又指向索引中下一句的块,最低一级为叶节点,其中包含只想表行的索引项.业块为双向链接,有助于按关键字值的升序和降序扫描索引

创建普通索引语法如下

CREATE [UNIQUE] INDEX index_name ON table_name(colum_list)

[TABLESPACE tablespace_name];

在语法中

• UNIQUE:用于指定唯一索引,默认情况下为非唯一索引
• Index_name:指所创建索引的名称
• Table_name:表示为之创建索引的表名
• Colum_list:在起上创建索引的列名的列表,可以基于多列创建索引,列之间用逗号分隔
• Tablespace_name:为索引指定表空间

 

唯一索引和非唯一索引

• 唯一索引:定义索引的列中任何两行都没有重复值.唯一索引中的索引关键字只能指向表中的一行,在创建主键约束和创建索引约束时都会创建一个与之对应的唯一索引
• 非唯一索引:单个关键字可以有多个与其关联的行

反向键索引

与常规B数索引相反,反向键索引在保持列顺序的同时反转索引列的字节.反向键索引通过反转索引键的数据值来实现.优点时对于连续增长的索引列,反转索引列可以将索引数据分散在多个索引块间,减少I/O瓶颈的发生

反向键索引通常建立在一些值连续增长的列上,如系统生成的员工编号,但不能执行范围搜索

在员工( employee)表中.为员工编号( empno)列创建反向键索引: 
SQL> CREATE INDEX index reverse empno ON employee (empno) REVERSE ;

 

位图索引 
位图索引的优点在于,它最适于低基数列(即该列的值是有限的,理论上不会是无穷大)。例如 员工表中的工种(iob)列,即便是几百万条员工记录.工种也是可计算的。工种列可以作为位图寿 引.类似的还有图书表中的图书类别列等。 

位图索引具有下列优点。 
(1)对于大批即时查询.可以减少响应时间。 

(2)相比其他索引技术,占用空间明显减少。 
(3)即使在配置很低的终端硬件上,也能获得显著的性能。 
位图索引不应当用在频繁发生 INSERT、 UPDATE、 DELETE操作的表上。这些DML操作在性能方面的代价很高。位图索引最适合于数据仓库和決策支持系统。 

其他索引 

• 组合索引:在表内多列上创建。索引中的列不必与表中的列顺序一致,也不必相互邻接类似于 SOL Server I中的复合索引,如员工表中部门和职务列上的索引。组合索引最多包含 32列 
• 基于函数的索引:若使用的函数或表达式涉及正在建立索引的表中的一列或多列,则创建基于函数的索引。可以将基于函数的索引创建为B树或位图索引

创建索引原则 

创建索引时需遵循的原则如下。

1. 频繁搜索的列可以作为索引。 
2. 经常排序、分组的列可作为索引
3. 经常用作连接的列(主键/外键)可作为索引。 
4. 将索引放在一个单独的表空间中,不要放在有回退段,临时段和表的表空间中
5. 对大型索引而言,考虑使用 NOLOGGING子句创建大型索引。 
6. 根据业务数据发生的频率,定期重新生成或重新组织索引,并进行碎片整理 
7. 仅包含几个不同值的列不可以创建为B树索引,可根据需要创建位图索引。
8. 不要在仅包含几行的表中创建索引

删除索引 

1.  DROP INDEX语句用于刪除索引 

例如,删除员工( employee)表中的 index_bit_job位图索引 
SQL>DROP INDEX index bit 1ob

【注意】在 SOL Server中创度或除常引时,必指明表的名称和家引名称。而 Oracle索引名在用户户中是唯一的,删除时不要指定表名 

2.何时应删除索引 

1. 应用程序不再需要索引 
2. 执行批量加载前。大量加载数据前除索引,加载后再重建素引有以下好处:①提高加 载性能:②更有效地使用素引空间 
3. 索引已损坏

重建索引 

1.  ALTER INDEX. REBUILD语句用于重建索引 

例如将反向键素引更改为正常的B树引 
SQL>ALTER INDEX index reverse empno REBUILD NOREVERSE 

1. 何时应重建索引 

1. 用户表被移动到新的表空间后.表上的索引不是自动转移,此时需将索引移到指定表空间 
   ALTER INDEX index name REBUILD TABLESPACE tablespace name: 
2. 索引中包含很多已脚除的项。对表进行频繁別除,造成素引空间浪费,可以重建索引 
3. 需将现有的正常索引转换成反向键索引 

分区表 

什么是分区表 

Oracle允许用户把一个表中的所有行分为几个部分,并将这些部分存储在不同的位置,被分区 的表称为分区表,分成的每个部分称为一个分区。 
对于包含大量数据的表来说。分区很有用。

表分区有以下优点 

• 改善表的查询性能,在对表进行分区后,用户执行SOL查询时可以只访问表中的特定分区而非整表
• 表更容易管理。因为分区表的数据存储在多个部分中,按分区加载和除数据比在表中加载和删除更容易 
• 便于备份和恢复,可以独立地备份和恢复每个分区
• 提高数据安全性,将不同的分区分布在不同的磁盘可以减小所有分区的数据同时损坏的可能性 

符合以下条件的可以建成分区表

1. 数据量大于2GB
2. 已有的数据和新添加的数据有明显的界限划分 

表分区对用户是透明的,即应用程序可以不知道表已被分区,在更新和查询分区表时当作普 来操作,但acle优化程序知道表已被分区 
【注意】要分区的表不能具有LONG和 LONG RAW数据类型的列

分区表的分类 

Oracle提供的分区方法有以下几种:范国分区、列表分区、散列分区、复合分区、间隔分区 虚拟列分区等,其中间隔分区和虚拟列分区是 Oracle1的新增特性。下面介绍两个比较重要的 区表 

范围分区 

范围分区{ range」是应用范国比较广的表分区方式.它以列的值的范围作为分 将记录存放到列值所在的 rande分区中。 

间隔分区 

间隔分区( Interval)是 Oracle11g版本新引入的分区方法,是范围分区的一种増强功能,可以 实现范围分区的自动化。 
它的优点为在不需要创建表时就将所有分区划分清楚。间隔分区随着数据的增加会划分更多的 区.并自动创建新的分区

本章总结 

• 一个数据库由一个或多个表空间组成,每个表空间中的数据保存在一个或多个数据文件中。 
• 使用 CREATE USER命令创建用户.同时为用户设置相应的表空间。 
• 用户授权的两种方式,一种是直接授权,一种是通过角色授权。 
• 了解 CONNECT、 RESOURCE和DBA三个常用的角色。 
• 序列用于生成唯一.连续的序号,这些序号可以作为主键或唯一键的值。
• 同义词是表、视图、序列、过程.函数、程序包或其他同义词的别名 
• 同义词简化了SL语句,用于隐藏对象的名称和所有者。 
  索引是与表相关的可选结构,用于更快地检索数据。除了标准索引外,还有唯一索引.组 合索引.、位图索引、反向键索引、基于函数的索引等。 
• 表分区用于管理存储在大表中的数据。 
• 分区方法包括范围分区、散列分区、列表分区、复合分区、间隔分区和虚拟列分区