Order数据库应用

       前言

        主要对创建表空间和用户以及授权、序列、同义词、视图、索引和分区表等数据库对象

表空间

  • 表空间：

    • Oracle数据库包含逻辑结构和物理结构。数据库的物理结构是指构成数据库的一组操作系统文件。数据库的逻辑结构是指描述数据组织方式的一组逻辑概念及他们之间的关系。表空间是数据库逻辑结构的一个重要组件。表空间可以存放各种应用对象，如表、索引。每个表空间由一个或多个数据文件组成。

  • 表空间分类：

    • 永久性表空间：一般保存表、视图、过程和索引的数据。system、sysaux、users、example表空间是默认安装的
    • 临时表空间：只用于保存系统中短期活动的数据，如排序数据等。
    • 撤销表空间：用来帮助回退未提交的事务数据，提交的数据在这里是不可以恢复的。

  • 表空间的目的：

    • 1.对不同的用户分配不同的表空间，对不同模式对象分配不同的表空间，方便对用户数据操作、对模式对象的管理。

    • 2.可以将不同数据文件创建不同的磁盘总，有利于管理磁盘空间，提高I/O性能，有利于备份和恢复数据等。Oracle系统安装创建Oracle实例后，Oracle会自动创建多个表空间。

    • 语法：

      create tablespace tablespacename
datefile 'filename' [size integer [k | M]]
[autoextend [off | on ]];

      tablespacename：是需创建的表空间名称。

      datafile：指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。

      fileName：数据文件的路径和名称.dbf，后缀可以随意。

      size：文件的大小，用K指定千字节大小，M为兆字节大小

      autoextend：启用或禁用数据文件的自动扩展，设置为ON则空间使用完毕自动扩展，OFF很容易出现表空间剩余容量为0的情况，使数据不能存储到数据库中。

      创建表时默认指定的表空间是USERS表空间，如果需要指定表空间可以在代码后跟个如：

      create table table_name(
     ……
 )
tablespace tablespacename

  • 删除表空间：
    可以通过DROP语句来删除用户自定义的表空间。

    语法：

    drop tablespace tablespacename
自定义用户管理

       当创建新数据库时，Oracle将创建一些默认数据库用户，如Sys、System、Scott等。

  • 1.sys用户

    • sys用户是Oracle中的一个超级用户。数据库中所有数据字典和视图都存储在SYS模式中。数据字典存储了用来管理数据对象的所有信息。是Oracle数据库中非常重要的系统信息。sys用户主要用来维护系统信息和管理实例。只能以SYSOPER或SYSDBA角色登录系统。

  • 2.System用户

    • System用户是Oracle默认的系统管理员。拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图。通常通过System用户管理Oracle数据库的用户、权限和存储等。不建议在System模式中创建用户表。不能以SYSOPER或SYSDBA角色登录系统。

  • 3.Scott用户

    • Scott用户是Oracle数据库的一个示范用户，一般在数据库安装时创建。Scott用户模式包含四个示范表，其中一个EMP表。

创建用户的语法：

creae user username
identified by password
[default tablespace tablespace]
[temporary tablespace tablespace]

user - 用户名

password - 用户口令

default tablespace \ temporary tablespace - 为用户确定默认表空间或临时表空间

  • 修改martin用户的密码

    alter user martin 
identified by mpwd

  • 删除用户

    drop user martin cascade;
数据库权限管理

       权限是用户对一项功能的执行权力。在Oracle中，根据系统管理方式的不同。

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

  • 2.对象权限：用户对数据库中具体对象所拥有的权限。对象权限是针对某个特定的模式对象执行操作的权力，只能针对模式对象来设置和管理对象权限，如数据库中的表、视图、序列、存储过程、存储函数等。

Oracle数据库用户有两种途径获得权限。

1) 管理员直接向用户授权。

2) 管理员将权限授予角色，然后将角色授予一个或多个用户。

权限语法：

grant 权限|角色 to 用户名;

撤销权限语法：

revoke 权限|角色 from 用户名;

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

1.数据库用户权限授权按照最小分配原则。
2.数据库用户分为管理、应用、维护、备份四类用户。
3.不允许使用SYS、System用户建立数据库应用对象。
4.禁止grant dba to user。
序列
  • 创建序列

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

创建序列语法：

create sequence sequence_name
    [start with integer]
    [increment by integer]
    [maxvalue integer|nomaxvalue]
    [minvalue integer|nominvalue]
    [cycle|nocycle]
    [cache integer|nocache]

start with：指定要生成的第一个序列号。对于升序序列，默认值为序列的最小值;对于降序序列，默认值为序列最大值。

increment by：用于指定序列号之间的间隔，其默认值为1.如果n为正值,则生成的序列将按生序排列;n为负责，生成的序列将按降序排列。

maxvalue：指定序列可以生成的最大值。

minvalue：指定序列可以生成的最小值。必须小于或等于start with的值。并且小于maxvalue

nomaxvalue：指定这个值。Oracle将生序序列的最大值设为10的27次方，将降序的序列的最大值设为-1.

nominvalue：指定这个值。Oracle将生序序列的最小值设为1，将降序的序列的最大值设为10的27次方.

cycle：指定序列在达到最大值或最小值后，将继续从头开始生成值

nocycle：指定序列在达到最大值或最小值后，将不在继续从头开始生成值

cache：使用cache选项可以预先分配一组序列号，并将其保留在内存中。当用完缓存中的所有序列号时，Oracle将生成另一组数值。

nocache：使用nocache选项，则不会为加快访问速度而预先分配序列号，如果创建时忽略了cache\nocache，Oracle默认缓存20个序列号。

  • 访问序列

       创建序列后，可以通过nextval和currval伪列来访问序列的值。可以从伪列中选择值，但不能操纵它们的值。

nextval：创建序列后第一次使用nextval时，将返回该序列的初始值。以后再引用时，将使用increment by子句来增加序列值，返回这个新值。

currval：返回序列的当前值，即最后一次引用nextval时返回的值。

  • 更改序列

    alter sequence命令用于修改序列的定义。
    1.设置或删除minvalue或maxvalue

    2.修改增量值

    3.修改缓存中序列号的数目

    语法：

    alter sequence [schema.]sequence_name
[increment by integer]
[maxvalue integer|nomaxvalue]
[minvalue integer|nominvalue]
[cycle|nocycle]
[cache integer|nocache]

  • 删除序列

       drop sequence命令用于删除序列。也可以使用此语句重新开始一个序列。

语法：

drop sequence [schema.]sequence_name
  • 使用序列

       可以使用序列设置Oracle的主关键字，所得值为从给定的起点开始的一系列整数值。序列所生成的数字只能保证在单个实例里是唯一的，这就不合适将它用作并行或者远程环境里的主关键字。

同义词

同义词用途

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

同义词分类

  • 私有同义词：

       只能被当前模式的用户访问，且私有同义词名称不可与当前模式的对象名称相同。要在当前模式下创建私有同义词，用户必须拥有create synonym系统权限。要在其他用户模式创建私有同义词，必须拥有create any synonym系统权限。

语法：

create [or replace] synonym [schema.]synonym_name
for [schema.]object_name

or replace：在同义词存在的情况下替换该同义词

synonym_name：创建同义词的名称

object_name：创建同义词的对象名称

  • 公有同义词:

       可被所有的数据库访问。可以隐藏数据库对象的所有者和名称，并降低SQL语句的复杂性。创建公有同义词，必须拥有create public synonym系统权限。

语法：

create [or replace] public synonym synonym_name
for [schema.]object_name

  • 删除同义词

    drop synonym语法用于删除同义词。用户必须拥有相应的权限。

    语法：

    drop [public] synonym [schema.]synonym_name
索引

索引分类

  • 分区或非分区索引 - 单列或组合索引

  • B树索引(标准索引) - 唯一或非唯一索引

    通常也称为标准索引。索引的顶部为根，其中包含指向索引中下一级的项。下一级为分支块，分支块又指向索引下一级的块。最低一级为叶节点，其中包含指向表行的索引项。叶块为双向链接。

    语法：

    create [unique] index index_name on table_name(column_list)
[tablespace tablespace_name]

unique：指定唯一索引，默认为非唯一索引

index_name：创建索引的名称

table_name：创建索引的表名。

column_list：创建索引的列名的列表，可以多列创建，用逗号隔开。

tablespace_name：为索引指定表空间

  • 唯一索引:

    • 定义索引的列中任何两行都没有重复。唯一索引中的索引关键字只能指向表中一行。创建主键约束和唯一约束都会创建一个与之对应的唯一索引。

  • 非唯一索引：

    • 单个关键字可以有多个其关联的行。

  • 正常或反向键索引 - 基于函数索引

  • 位图索引
    适用于低基数列。

    优点：

    1.对于大批即时查询，可以减少相应时间。
2.相比其他索引技术，占用空间明显减少。
3.即使在配置很低的终端硬件上，也能获得显著的性能。

    位图索引不应当用在频繁发生insert、update、delete操作的表上。

删除索引

drop index index_bit_job;

重建索引

何时重建索引
    1.用户表被移动到新表空间后，表上的索引不是自动转移，需将索引移到指定表空间。
    2.索引中包含很多已删除的项。对表进行频繁删除，造成索引空间浪费，可以重建。
    3.需将现有正常索引转换成反向键索引。
分区表的分类

        Oracle提供分区有：范围分区、列表分区、散列分区、复合分区、间隔分区、虚拟列分区等

  1. 范围分区
    • 应用范围比较广的表分区方式。以列的值的范围作为分区的划分条件，将记录存放在列值所在的range分区中。
  2. 间隔分区
    • Oracle 11g版本新引入的分区方法，是范围分区的一种增强功能，可以实现范围分区的自动化。
      • 优点为在不需要创建表时就将所有分区划分清楚。间隔分区随着数据的增加会划分更多的分区，并自动创建新的分区。
  3. 列表分区
  4. 散列分区
  5. 复合分区
  6. 间隔分区
  7. 虚拟列分区