oracle 数据库应用

        表空间

            用户权限管理

                        序列

                    同义词

                 索引

        分区表

表空间：

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

分类:

永久性表空间

一般保存表，视图，过程和索引的数据。SYSTEM,SYSAUX,USERS,EXAMPLE表空间是默认安装的。

临时性表空间

只用于保存系统中短期数据，如排序数据等。

撤销表空间

用来帮助回退未提交的数据，已提交了的数据在这里没用。

目的：

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

可以将不同数据文件创建到不同的磁盘中，有利于管理磁盘空间，有利于提高i/o 性能，备份恢复数据等。

（一般在安装完oracle数据库后，自动会创建多个表空间。）

创建表空间：

语法：

(关键字大写 = .=)

CREATE TABLESPACE tablespacename

DATAFILE 'filename' [SIZE integer [K | M] ]

[AUTOEXTEND [OFF | ON ] ];

在语法中

tablespacename :表空间名称。datafile指定组成表空间一个或多个数据文件，当有多个的时候用“,”分隔。

filename是数据文件的路径和名称。SIZE指定文件大小K：千字节，M兆字节。

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

示例：

查看表空间：

系统存储表空间的表：dba_data_files

调整表空间大小：

删除表空间：

前者直接删除表空间，后者删除表空间并清理里面的数据文件。

用户：

数据库安装好了之后，默认有三个用户：

SYS,SYSTEM,SCOTT

sys和system是oracle的系统用户，而scott则是一个示范用户。

sys：

oracle的超级管理，oracle中非常重要的信息则由他来管理和维护SYS 用户只能以SYSOPER或SYSDBA角色登录系统。

system：

oracle中的默认管理员，有dba的权限，可以管理oracle内部表或视图（例如角色分配），system不能以SYSOPER或SYSDBA角色登录系统，只能以默认方式登录。

scott:

是oracle的一个示范用户，一般在数据库安装的时候创建，默认包含4个示范表，emp就是其中一个，使用USERS表空间存储模式对象。

为什么叫做scott？

这个就要追朔到Oracle的创业阶段了， 1977年6月，埃里森,Bob Miner和Ed Oates在硅谷共同创办了一家名为软件开发实验室（Software Development Laboratories，SDL）的计算机公司,这个只有三个人的公司就是后来在独领数据库风骚的ORACLE公司的前身。 当时埃里森年界32岁，由于合同的关系还在自己原来的公司里不能出来，只是一个程序员而已。他过不来，总要有写代码的人，公司的第一个程序员出现了，他的名字就是scott，他的猫的名字就叫trigger，可能是为了这个第一位的程序员的缘故吧，所以也就有了scott这个用户，而且一直没有忘怀，留恋至今。

介绍完oracle默认有的用户，那么如何创建用户呢？

创建用户语法：

CREATE USER user

IDENTIFIED BY password

[DEFAULT TABLESPACE tablespace]

[TEMPORARY TABLESPACE tablespace]

语法中:

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

password是用户口令，必须是一个标识符，而且不区分大小写。

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

创建一个名为A_hr的用户，口令为bdqn，默认表空间为tp_hr临时表空间为temp

给该用户分配的空间为：unlimited （无限制）。

也可以先创建一个无效的用户，在登录的时候可以修改密码：

（expire：无效的）

创建完用户，将该用户从数据库里面查询出来：

DBA_USERS(系统用户表)

查询表空间限制大小：

修改/删除：

删除用户：DROP USER username CASCADE;

必须使用CASCADE选项删除用户和用户模式对象。

数据库权限管理：

    权限是用户对一项功能的执行权力。在oracle中，根据系统管理方式不同，可将权限分为系统权限和对象权限两类。

系统权限：

    常见的系统权限：

CREATE SESSION:连接到数据库

CREATE TABLE:创建表

CREATE VIEW :创建视图

CREATE SEQUENCE:创建序列

对象权限：

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

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

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

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

使用角色授权可以更方便快捷对权限管理，所以数据库管理员通常使用角色向用户授予权限，而不是直接向用户授予权限。

常见角色：CONNECT角色，RESOURCE角色，DBA角色etc.

一般程序使用前两者角色就好。DBA具有所有的系统权限，包括给用户授权。

CONNECT：需要连接上数据库的用户，特别是那些不需要创建表的用户，通常授予该角色。

RESOURCE：更为可靠和正式的数据库用户授予，可以创建表，触发器，过程等。

DBA：数据库管理员角色，拥有管理数据库的最高权限。一个具有DBA角色的用户可以撤销任何别的用户甚至别的DBA权限，这是很危险的，不要轻易授予该角色。

授权：

GRANT 权限|角色 TO 用户名；

撤销权限：

REVOKE 权限|角色 FROM 用户名；

例:

序列：

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

创建序列语法：

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 ：增/减 量

MAXVALUE / MINVLAUE :最大值/最小值

NOMAXVALUE/NOMINVALUE:没有最大值/没有最小值

默认NOMAXVALUE和NOMINALUE

CYCLE/NOCYCLE：指定在序列到了最大值或最小值后，是否从头开始。默认NOCYCLE

CACHE/NOCACHE：（缓存数据）加快访问速度而预先分配序列号。如果忽略不写，默认分配20个。

创建序列：

访问序列：

seql为序列名,seql.nextval为序列的下一个值。

更改序列：

删除序列：

使用序列：

sys_guid()为系统函数，每次会生成不同的32为唯一编码。

同义词：

用途：

简化sql语句

隐藏对象的名称和所有者

为分布式数据库的远程对象提供了位置的透明性

提供对对象的公共访问

分类：    

私有同义词|公有同义词

语法：

CREATE [OR REPLACE] SYNONYM [schema.]synonym_name

FOR [schema.]object_name;

公有同义词:

CREATE [OR REPLACE] PUBLIC SYNONYM [schema.]synonym_name

FOR [schema.]object_name;

一个用户如果要创建同义词，那么必须授予他权限

索引:

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

分类：

B树索引，唯一索引和非唯一索引，反向键索引，位图索引。

    B树索引:通常能也称为标准索引。索引顶部为根，其中包含指向索引中下一级的项。下一级分为支块，分支块又指向索引中的下一级块。最低一级为叶节点，其中包含指向表行的索引项。叶块为双向链接，有助于按关键字值的升序和降序扫描索引。B树索引类似于SQL Server中的非聚集索引。

    CREATE [UNIQUE] INDEX index_name ON table_name (column_list)

    [TABLESPACE tablespace_name]

语法中

UNIQUE:用于指定唯一索引，默认情况下是非唯一索引。

index_name:指所创建的索引名称。

table_name:表示为之创建索引的表名。

column_list：在其创建索引名称的列名的列表，可以基于多列创建索引，列之间用逗号分割。

tablespace_name:为索引指定表空间。

唯一索引和非唯一索引：

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

非唯一索引：单个关键字可以有多个与关联的行。

在薪水级别(sqlgrade)表中，为级别编号(grade)列创建唯一索引

CREATE UNIQUE INDEX index_unique_grade ON  sqlgrade(grade);

反向键索引：

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

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

CREATE INDEX index_reverse_empno ON employee(empno) REVERSE;

位图索引

位图索引优点在于，最适于低基数列（即该列的值是有限的，理论上不会无穷大）。例如员工表中的工种列job。性别，图书类别等。

优点；

对于大批即时查询，可以减少响应时间。

相比其他索引技术，占用空间明显少。

即使在配置很低的终端硬件上，也能获得显著的性能。

不适合用在频繁发生INSERT,UPDATE,DELETE 操作的表上。这些DML操作在性能上代价很高。位图索引在最适合数据仓库和决策支持系统。

在员工(employee)表中，为工种(job)列创建位图索引

CREATE BITMAP INDEX index_bit_job ON employee(job);

其它索引

组合索引：在表内创建，索引中的列不必与表中的列顺序一致，也不必互相邻接，类似于SQL Server中的复合索引，如员工表中部门和职务列上的索引。组合索引最多包含32列。

基于函数索引：若使用函数或表达式设计正在建立索引的表中的一列或多列，则创建基于函数的索引。可以将给予函数的索引创建为B树索引或位图索引。

在员工(employee) 表中，为员工名称(ename)列创建大写函数索引。

CREATE INDEX index_ename ON employee(UPPER(ename));

经验：创建组合索引时将唯一性高（该列上存储的大部分数据是唯一的）的列放在第一位。

创建索引原则：

频繁搜索的列可以作为索引。

经常排序，分组的列。

经常用作连接的列（主键/外键）。

将索引放在一个单独的表空间中，不要放在有回退段，临时段，和表的表空间中。

对大型索引而言，考虑使用NOLOGGING子句创建大型索引。

根据业务数据发生的频率，定期重新生成或重新组织索引，并进行碎片整理。

仅包含几个不同值的列不可以创建为B树索引，根据需要创建位图索引。

不要在仅包含几行的表中创建索引。

删除索引：

DROP INDEX index_name;

在SQL Server中删除索引时，必须指明表的名称和索引的名称。而oracle索引名在用户账户中是唯一的，删除时不需要指定表名。

    何时删除索引？

1程序不需要索引。

2执行批量加载前删除索引。大量加载数据前删除索引，加载后再重建索引有以下好处：2.1提高加载性能，2.2更有效地使用索引空间。

3索引已损坏。

重建索引：

ALTER INDEX 。。。REBUILD 

何时应该重建索引？

用户将表移动到新的表空间，索引并不会自动转移，所以需要将索引移到指定的表空间。

索引中包含很多已删除的项，对表进行频繁的删除，造成索引空间浪费，可以重建索引。

需要将现有正常索引转换为反向键索引。

分区表：

分区表的分类

oracle 提供的分区有以下几种：

范围分区，列表分区，散列分区，复合分区，间隔分区，虚拟列分区。

其中间隔分区和虚拟列分区是oracle 11g新增特性 。下面介绍两个重要分区表。

分区在创建表的时候一起运行。

1 范围分区

创建一个表，并创建范围分区；

PARTITION BY RANGE (列名)

(

PARTITION 区名 VALUES LESS THAN(to_date('2013-04-1','yyyy-mm-dd')),

PARTITION 区名 VALUES LESS THAN(to_date('2013-07-1','yyyy-mm-dd')),

PARTITION 区名 VALUES LESS THAN(maxvalue),

);

上图中，p1表示员工入职日期小于13年4-1号。

p2表示大于4-1号小于7-1号，以此类推，p5表示剩余部分。

maxvalue:最大值，一般创建范围分区的时候都会将最后一个值设置为maxvalue

需要查看4-1号到7-1号13年入职的数据。：

SELECT * FROM 表名 PRATITION(区名);

2 间隔分区

PARTITION BY RENGE(表名)

INTERVAL(NUMTOYMINTERVAL(一个分区的个数,'单位'))

（PARTITION 名 VALUES LESS THAN(to_date('2013-04-1','yyyy/mm/dd')));

查询出分区的情况，系统会感根据条件自动分区。

再根据查询出来系统自动创建的分区SYS_P82。

间隔分区只需要创建第一个开始的分区，如p1

INTERVAL代表间隔

NUMTOYMINTERVAL(3,'MONTH')表示每3个月为一个分区

第二个参数可以是MONTH /YEAR

与此函数相关的还有NUMTODSINTERVAL(n,'interval_unit')

此处第二个参数可以为DAY,HOUR,MINUTE,SECOND.

不支持YEAR和MONTH.

系统根据数据会自动创建分区。

qq1843620566 欢迎交流