1、Oracle 知识点总结体系结构：数据库的体系结构是指数据库的组成、工作过程与原理，以及数据在数据库中的组织与管理机制。体系结构包括：实例（instence ） ，数据库文件（database） ，用户进程（user process），服务器进程（server process） ，以及其他文件（如参数文件（parameter file） ，密码文件（password file） ，归档日志文件（archive log file）等） 。Oracle 服务器由数据库文件和数据库实例组成。其中数据库实例包括 SGA（即内存结构的集合）和管理数据库的后台进程。数据库文件包括三种：数据文件（data
2、files） ，控制文件（control files）和重做日志文件（redo log files） 。Oracle 中的 3 个主要内存区域是： 系统全局区域(system global area，SGA)- 当数据库实例启动时会首先分配 程序全局区域(program global area，PGA) 用户全局区域(user global area，UGA)SGA 内存结构包括: 共享池（Shared Pool） ，数据缓冲区（Database Buffer Cache） ，重做日志缓冲区（Redo Log Buffer Cache） 。共享池的作用：共享池由库缓存和数据字典缓存组成。缓存和
3、共享 SQL 或 PL/SQL 代码；数据缓冲区的作用：存储从数据文件中读入的数据，提高查询速度；重做日志缓冲区的作用：变化前和变化后的数据在写入数据缓冲区前都会先写入重做日志缓冲区，这样在数据恢复时，Oracle 就知道哪些需要回滚。日志数据首先产生于重做日志缓冲区，当重做日志缓冲区的日志数据达到一定数量时，由日志写进程 LGWR 将日志数据写入重做日志文件中。PGA 是为单独的服务器进程存储私有数据的内存区域，只为各个服务器进程提供一个PGA。PGA 只能由他们的服务器进程访问。UGA 是用于存储会话状态的内存区域。UGA 的位置依赖于服务器是运行在共享服务器模式中，还是专用服务器模式中。
4、在专用服务器模式中，UGA 会在 PGA 中分配，只能够由服务器进程访问。Oracle 进程主要有用户进程、服务器进程和后台进程。用户进程是一个需要与 Oracle 服务器进行交互的程序。服务器进程犹如一个中介，完成用户的各种数据服务请求，而把数据库服务器返回的数据发给客户端。后台进程主要作用是协调好系统的性能。其主要包括进程监控进程(PMON，服务器进程的管理和维护)、系统监控进程(SMON，在数据库出现故障时进行实例恢复)、数据库写进程（DBWR，主要负责将数据缓冲区内的数据写到数据文件） 、重做日志写进程（LGWR，主要负责将重做日志缓冲区内的数据写到重做日志文件）及检查点进程（CKPT
5、，其作用是保证所有修改过的数据库缓冲区都被写入数据库文件） 。以上 5 个进程也是数据库启动过程中必须启动的。数据库启动和关闭数据库的启动 3 个状态：1:NOMOUNT 只打开数据库实例 2:MOUNT 打开实例并读取控制文件 3:OPEN 打开数据库按 123 顺序启动数据库的关闭 3 个状态：1:CLOSE 关闭数据库2:DISMOUNT 3:SHUTDOWN 关闭数据库实例事务事务的概念：事务是一组逻辑工作单元，它由一条或多条 SQL 语句组成。事务的 4 个特性：原子性，一致性，隔离性和持久性事务控制：commit，rollbackSQL 语言SQL 语言分为数据定义语言（DDL）
6、、数据操纵语言（DML）和数据控制语言（DCL） 。DDL 用于定义 SQL 模式、基本表、视图和索引的创建和撤消操作。常用命令有：CREATE、ALTER、DROP。DML 用于数据的插入、修改、删除和查询。常用命令有：INSERT、UPDATE、DELETE、SELECT。DCL 用于对基本表的授权、完整性规则的描述和事务的控制等。常用的命令有：GRANT、REVOKE、COMMIT、SAVEPOINT、ROLLBACK。Char 和 varchar2 两种数据类型的区别：在数据库中 char(n)表示固定长度 n 的字符串，当实际数据不足定义长度时，将使用空格补全右边不足位，当实际数据的
7、长度大于其固定长度时，Oracle 将不允许数据存储于对应列或者变量中，并抛出异常；而 varchar2(n)是可变长的字符串，意味着该列或变量的最大长度不大于 n,但当实际数据小于 n 时，并不在其右端补齐空格。delete 和 truncate 的区别。Delete 是 DML 语句，在删除数据时要记录重做信息，且删除数据后不释放表空间，速度慢。Truncate 是数据定义语言，执行后数据直接删除且释放表空间，不记录日志信息，速度快。PL/SQL 语言PL/SQL 语言的叙述：PL/SQL 是一种块结构的语言，它将一组语句放在一个块中。构成 PL/SQL 程序的基本单元是逻辑块（如过程、函
8、数或匿名块） ，每个逻辑块对应要解决的问题或子问题。PL/SQL块共分为 3 个部分，分别为声明部分、可执行部分和异常处理部分。属性数据类型包括两种，分别是%TYPE 和%ROWTYPE。1、%TYPE 将某种数据类型的变量或列提供给其他变量。其语法格式为：var1 table_name.column_name%TYPE;其中声明了变量 var1，它的数据类型与 table_name 表中的 column_name 字段的数据类型相同。2、%ROWTYPE 提供一种表示表中的某行的记录类型。其语法格式为：var1 table_name%ROWTYPE;其中声明了变量 var1，可以用来存储从
9、table_name 表中提取的一个记录。过程、函数、程序包过程和函数的区别：过程没返回值，而函数有返回值。程序包是对相关过程、函数、变量、游标和异常等对象的封装。程序包由规范和主体两部分组成。规范可以在没有程序包主体的情况下存在，主体不能在没有程序包规格说明的情况下存在。游标和触发器游标的 4 种属性：%Isopen 、%found、%notfound、%rowcount。使用游标的 4 个步骤：定义游标（declare） 、打开游标（open） 、循环取数据（fetch） 、关闭游标（close） 。显式游标和隐式游标在使用过程中的区别：显示游标使用时要定义、隐式游标不需要定义，在上下文中
10、的 dml 语句默认使用隐式游标。触发器定义：触发器是当特定事件出现时自动执行的代码块。触发器与过程的区别：过程是由用户或应用程序甚至是触发器显式调用的，而触发器是由Oracle 根据发生的事件而隐式激活的，它不能被直接调用执行。触发器的类型及其运行时机：Oracle 触发器分为 DML 触发器、模式（DDL 或用户事件）触发器和数据库级触发器。1:DML 触发器：当 DML 语句在表中发生时执行。DML 语句包括以下 3 种类型：a.语句级触发器：无论受触发语句影响的行数是多少，都只激活一次。b.行级触发器：每当触发器语句影响表时就会激活行级触发器，行级触发器在被修改的每一行上执行一次。c.
11、INSTEAD OF 触发器：允许用户修改不能使用 DML 语句修改的视图。INSTEAD OF触发器只可用于视图，不可用于表。2:模式（DDL 或用户事件）触发器：在数据库模式中执行 DDL 语句时激活。3:数据库（系统事件）触发器：在发生 LOGOFF（退出） 、LOGON（登录） 、STARTUP（打开） 、SHUTDOWN（关闭）数据库和 SERVERERROR（生成错误消息）等系统事件时执行。数据库对象（同义词，序列，视图，索引） 同义词的定义：是现有对象的一个别名。同义词的作用：1：简化 SQL 语句。2：隐藏对象的名称和所有者。3：提供对对象的公共访问。同义词的两种类型：1：公有
12、同义词。CREATE public synonym emp_syn FOR SCOTT.emp;2：私有同义词。CREATE synonym emp FOR SCOTT.emp;公有的同义词只能建立在超级用户下定义。序列是用来生成唯一、连续的整数的数据库对象。在表中使用序列：1.创建序列：CREATE SEQUENCE sequence_name START WITH startnum INCREMENT BY step;2.创建表时使用序列：CREATE TABLE table_name ( uid smallint not null, .);3.插入数据时使用序列：INSERT INTO
13、table_name VALUES(sequence_name.NEXTVAL, .);视图是从若干基本表和（或）其他视图构造出来的虚表。视图和表的区别是：创建一个视图时，只是把其视图的定义存放在数据字典中，而不存储视图对应的数据，而表会存储对应的数据。视图的目的和作用：筛选表中数据，简化查询语句，对表起到保护作用。注：加 WITH READ ONLY 选项可使视图不允许 DML 操作 用户管理和权限管理要在超级用户上创建用户权限-创建用户create user zw identified by 123456;-给 zw 登入的权限grant connect to zw;-授权-用系统权限的方
14、式grant create table to zw;-用角色方式grant resource to zw;-例：create user username identified by password;grant resource,connect to username;-GRANT 语句-GRANT 语句的作用是赋与用户权限。-常用的数据对象权限有以下六个：-（1）ALL ON 数据对象名-（2）SELECT ON 数据对象名-（3）UPDATE ON 数据对象名-（4）DELETE ON 数据对象名-（5）INSERT ON 数据对象名-（6）ALTER ON 数据对象名-例：-把查询 de
15、partment 表权限给 zw 用户grant select on department to zw-把所有操作 department 表权限给 zw 用户grant all on department to zw-REVOKE 语句-REVOKE 语句是回收权限语句。回收以上所赋权限的相应语句为：-REVOKE CONNECT FROM USER1;-REVOKE SELECT ON TABLE2 FROM USER2;-REVOKE SELECT,INSERT,DELETE ON TABLE3 FROM USER1，USER2;-例：-回收 sun 用户对 SZD 用户的 emp 表的查
16、询权限revoke select on emp from sun-回收 sun 用户对 SZD 用户的 emp 表的所有权限revoke all on emp from sun备份与恢复数据库导出的四种模式 full(全库导出), owner(用户导出), table(表导出), tablespace(表空间导出)。执行 exp 有三种方法:交互式,命令行和参数文件。1：交互式:直接输入 exp 命令用户可以按照 exp 提示的信息一步一步操作,比较简单. 2：命令行:输入命令行 exp username/password parameter=value. 3：参数文件:输入命令行 exp u
17、sername/password parfile=filename exp 所需的参数从参数文件引入。热备份和冷备份：热备份针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。 （因为不必将 archive log 写入硬盘）索引索引是一种树状结构，可以通过该结构迅速访问表中的数据。索引的类型和其语法：唯一索引，组合索引，反向键索
18、引，位图索引，基于函数的索引。1、唯一索引可以确保在定义索引的列中，表的任意两行的值都不相同，Oracle 自动为表的主键列创建唯一索引。其语法为：CREATE UNIQUE INDEX index_name ON table_name(column_name);2、组合索引是在表中的多个列上创建的索引，可以提高检索数据的速度。其语法为：CREATE INDEX index_name ON table_name(column_name);3、反向键索引通常建立在一些值连续增长的列上，可以提高读取的性能。其语法为：CREATE INDEX index_name ON table_name(col
19、umn_name) REVERSE;4、位图索引适用于低基数列，也就是不同值的数目比表的行数少的列。其语法为：CREATE BITMAP INDEX index_name ON table_name(column_name);5、基于函数的索引是指基于一个或多个列上的函数或表达式创建的索引，便于 WHERE子句中包含函数或表达式以计算查询时使用。其语法为：CREATE INDEX index_name ON table_name(表达式或函数);Oracle 索引包含 B-tree 索引、bitmap 索引、函数索引等。B-tree 索引类似二叉树结构，数据库表中每一行都对应 R-tree 索
20、引中的一个节点。节点中包含数据（索引列）和 rowid 信息，通过索引列找到对应的 rowid 信息，在定位到具体的行来查找数据。如果平凡删除操作，会对相应的索引作对应的修改，会降低性能。建议在操作之前先删除索引再重建索引。补充：创建索引语法：create index index_name on itemfile (itemcode)删除索引语法：drop index index_name;重建索引语法：alter index index_name rebuild;重命名索引：alter index item_index rename to book_index; 索引的优缺点优点:某些情况下
21、，数据查找快。如：用户的查询将要选取记录的 2%-5%或更多，那么它将会执行全部扫描，而不考虑索引是否可用缺点:a)在某些条件下，全表扫描比索引查找更快b)索引占用空间惊人，甚至超过表数据所占空间,不利于管理。c)创建索引后，会降低插入，修改，删除等操作的效率选择索引a.身份证这类唯一属性，应建唯一索引 b.性别,只有男、女、未定等少数几种状态值，应创建位图索引，位图索引更节约空间c.对字段使用函数，不会使用索引，可创建函数索引。下列的表不适合建索引:a.表很小(8K)b.列不经常在 WHERE 子句中使用c.表中的相关列经常被修改Oracle 优化SQL 优化实例: 选择最有效率的表名顺序(
22、只在基于规则的优化器中有效) WHERE 子句中的连接顺序 SELECT 子句中避免使用 * 减少访问数据库的次数 最高效的删除重复记录方法 用 TRUNCATE 替代 DELETE 尽量多使用 COMMIT 用 Where 子句替换 HAVING 子句 使用表的别名(Alias) 用 EXISTS 替代 IN 用 NOT EXISTS 替代 NOT IN 用表连接替换 EXISTS 用 EXPLAIN PLAN 分析 SQL 语句 避免在索引列上使用计算 用=替代 避免在索引列上使用 IS NULL 和 IS NOT NULL 用 UNION-ALL 替换 UNION ( 如果有可能的话) 需要当心的 WHERE 子句
