Resource:
www.
www.
www.
Oracle 服务器端组成：
1.内存中的instance （数据库对应的实例）
2.磁盘上的database (数据文件,日志,参数文件…)
对应关系: 1:1(单实例) , N:1(集群RAC，每个主机一个数据库实例，共享一个磁盘阵列)
Listener
运行于服务器上，用于客户机远程
连接数据库，支持多协议，TCP/IP对应默认端口为1521
DML CRUD(insert,select,update,delete)
DDL alter,
create , drop…
TCL (transaction
control lang ) commit, rollback,savepoint
DCL (data
control lang) grant revoke
网络配置助手 （net configuration assistant， netca）设置本地Net服务. 登陆远程服务器。
也可以直接修改xxxx.ora文件，复制到指定目录，通助手设置效果相同。
C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN
sqlnet.ora
tnsnames.ora 解析数据
库名在oracle中使用类似DNS的方式，该文件作用类似于网络中的host文件
Cmd 连接数据库sas
sqlplus /nolog (不
登陆，只是打开sqlplus)
conn oe/oe@sas
或者
sqlplus oe/oe@sas
管理员登陆
conn sys/pwd@sas as
sysdba
如果用户在操作系统的权限很高，
可省略用户名和密码。
conn / as sysdba;
查询语句
数据库与关系代数的关系、
字符串区分大小写,如果不清楚大小写，用upper/lower函数
Select * from employees where upper(last_name) = ‘KING’;
但是如果sql中where 语句后的等号左边存在函数，则oracle中对该字段的索引无效，走全行扫描，性能低。
字符串使用单引号
Oracle中的字典表（比如当前用户下的各个表对象组成的表），字典表中对象的
名字都是大写。
select table_name from user_tables; --user_tables就是个字典表
select * from user_tables where table_name = 'CUSTOMERS';
--CUSTOMERS是大写
空值
NULL 不知道是什么
一个运算表达式中，只要存在一
个空值，结果一定是Null.
由于空值的存在会导致运算的麻
烦，设计时应当尽量避免空值产生。
注意
T and NULL = NULL, F and NULL = F,
因为NULL是T还是F未知，T & T =T, F&T = F, 因此结果无法判断，是null
但是T&F
= F&F = F, 因此 null & f =f
列的别名
在列的名称后可以紧跟别名—或者使用可选的关键字AS放在列名和别名之间
如果别名包含空格或特殊字符或对
大小写敏感，需要双引号引用
*表空间备份之前，要设置表空间进入备份模式
alert tablespace tbs01 begin backup;
一个一个做很麻烦，可以这样
Select name from v$tablespace; 获得所有表空间
Select ‘alter’ || name
|| ‘ begin backup’ from v$tablespace;
则得到生成的语句串
要保存到文件，利用假脱机功能
spool c:\script.sql
set head off – 去掉
结果中的标题，还有很多类似的设置
select ‘alter’ || name
|| ‘ begin backup’ from v$tablespace;
spool off
count 的问题
select count(department_id) from (select distinct department_id from employees)
select count(*) from (select distinct department_id from employees)
如果department_id有为空的，那么结果不同，原因是count(department_id)会不计算空值的行。
可以理解为department_id列结果集中计算有多少个有值的行。
而count(*)把结果集中所有的行都算上，而不管行中的列有没有值。
其中前者可简化 select count(distinct department_id) from employees;
单引号
字符串使用单引号，当数字与字符做比较时，也可以给数字加上单引号来说明是作为字符来比较，不是必须的。
一般多行函数都会先把空值去掉再做计
算。比如AVG，SUM等。
日期是格式敏感的
默认格式是
dd-mm-yy
用 to_date(‘1987-01-12’,’yyyy-mm-dd’) 可将习惯的时间格式转换为日期
Oracle 的时间抽取函数 extract (year from hire_date)
ORDER BY 在select 语句最后出现
默认ASC,
多
列排序 修饰符只修饰紧前一个。
NLS : national - language - support
修改会话设置
alter session
set xxx = xxx;
优先级 ： 会话
设置 > 环境变量 > 服务器默认值
数据类型转换
VCHAR 与 VARCHAR2 一样。VCHAR2 是 Oracle 自己定义的，方便日后升级。
隐藏数据类型转换也会导致某字段
索引失效。
set autotrace on 追
踪执行计划， 查看语句执行过程。
to_char, to_number,
to_date
空值的问题，即导致上面COUNT问题的原因。很多函数会先去掉空值的行。
可使用NVL来给空值指定一个值。
NVL(arg1,arg2) 其中arg1,arg2的值类型必须相同（可以用函数转换解决不同类型匹配）
例如 NVL(to_char(manager_id),
‘NO MANAGER’);
CASE 和 DECODE 只能处理简单的逻辑分支，复杂情况可使用PL/SQL。
连接：
等值连接即内连接
外连接
外连接： 左连接，右连接（ 根据表在逗号的左右判断）
完全外连接（等值+左+右的结果）
语法：字段(+)， 如果(+)在等号右边，则是左外连接，否则反之。 注意(+) 与左右概念是相反位置的。
分组
所有在SELECT列表中出现的列，如果未使用组函
数，那么它必须
出现在GROUP
BY子句中
where 与 having 区别
Where 在分组之前、针对每行进行筛选。
Having 在分组后、对分组运算结果进行筛选
因此：
你无法通过WHERE子句限定组
使用HAVING子句可以限定组
不能在WHERE子句中使用组函数
Select
Select department_id,job_id , count(*)
from employees group by deparment_id, job_id;
分组的扩展关键字
Rollup(上卷) rollup(a,b,c) 分别按照a,b,c a,b a all 进行
分组。若有n个字段，则分组n+1次。如下测试：
Select department_id,job_id
, count(*) from employees
group by
rollup(deparment_id, job_id);
department_id, job_id
department_id
all
Cube 2^n 种分组情
况。 Cube(department_id,
job_id)
四种情况
department_id, job_id
department_id,
job_id
all
如果没有这两个关键字，可以使用union all 将多个情况的结果构造并集，但是麻烦。
单行子查询必须返回一行数据
（空行和多行都不允许）
> < = <> >= <=
多行子查询 ANY, IN ,ALL
Create table t1 as select * from employees;
Create table t1 as select * from employees
where 1=0; 只复制结构
上述方式不会复制外键，索引，
主键等约束。
删除表内容：
delete from t1; DML
不
自动提交 除非正常关闭sqlplus.
truncate table t1 (oracle 自带); DDL
空间管理:
delete 删除数据后不释放空间， 而truncate
释
放空间。
重做数据:
delete 生成，写日志，生成undo data (可能慢)，truncate 不生成
Undo data 更新前的旧数据，在撤销表空间
Redo data 更新后的新数据
Oracle sqlplus中的事务：
• 以第一个DML SQL语句执行开始
• 以下面的事件为止
—
COMMIT或者ROLLBACK语句被执行
— 一个DDL或者DCL语句执行（自动提交）
— 用户退出SQL*Plus
— 系统崩溃
使用COMMIT和ROLLBACK语句，你可以
• 确保数据一致
• 在做永久改变之前可以预览数据改变
• 组逻辑上的相关操作
在COMMIT之后的数据状
态
• 数据库中数据的改变被永久化不会丢失，即使系统崩溃。
• 事务结构，旧事务状态被清除，新事务开始。
• 旧事务中的锁定的行被释放，这些行现在可以被其它用户进行操作。
在Rollback之后数据的状
态
• 数据的改变被撤回
• 数据先前的状态被还原
• 受影响行上面的锁被释放
在下列环境下自动提交会发生
—
DDL语句被执行
—
DCL语句被执行
— 从SQL*Plus中的正常退出，没有显式执行COMMIT或者ROLLBACK语句
• 在SQL*Plus非正常结束或者系统失败，自动回滚发生。
DB 内部调度要遵循可串行化规则，前后顺序不能变。（参考老胡的PPT）
ORACLE事务的隔离级别（独立方式） 读已提交、连续读（可串行化）
没
有提交的数据，其他用户是看不到的，即处在 读已提交 隔离级别。
可
能出现 不可
重复读 现
象，即两次读取的数据可能不一样（被更新了）
Oracle 的语句一致性（多版本并发控制，不同于其他DB）：
1 语句级别一致性， 当执行一个语句，数据与按下回车的那一刻是一致的。
2 事务一致性，得到的数据与事务开始的那一刻是一致的。
默
认在语句级别的一致性。
事
务一致性在可串行化级别中实现。同一个查询，在一个事务中得到的结果是一样的，看不到别人的修改。
Oracle与SQLSERVER/DB2 处理方式不同， 事务A开始后，Oracle允许B修改事务A的数据，而SQLSERVER/DB2 会挂起B的操作直到A事务完成。如果B修改了A的数据，A可能无法再修改那些数据，因为无法保证可串行化。
不
可重复读与幻象读
不
可重复读：对原有数据的修改被读取
幻
象读：原有数据不变，添加了新的数据后被读取
Level
dirtiy read nonreaptable
read phantom read
Read uncommitted
不
加锁
Read commited
锁
定当前游标所处理的记录
Repeatable read
锁
定当前处理的数据
Serlizable
锁
定事务
锁： share exlusive
Share y y
Exlusive n
Oracle redo data 在redo log, undo data 在undo tablespace(公开可访问)，并发时其他人可能访问旧数据。Oracle中共享锁和排他锁可共存，即当事务A写数据X并且未提交时，
事
务B可以访问X的旧数据（共享读），但是不能写X（排他写）。
其
他db 新旧数据都在redo log, 并发时只能等待。
Oracle不会预防死锁，但是会检测，死锁必然是业务逻辑造成的。
锁
的升级：MSSQLServer,DB2 会将锁保存到锁链表中，可能会将整个表锁住。
Oracle不做锁的升级。
请
求锁而得不到操作会被阻塞。
查询时加锁(悲观加锁)： select xxx from yyy for update;
Oracle 有
DML锁、DDL锁、内部锁（栓） 三类锁。
栓
用于锁定内存结构，
一
般锁可能持续很长时间，栓持续时间很短。
锁
一般是队列方式获得锁
栓
是抢先机制，无队列
字
典表
用
户下的User_table, user_view, user_xxxxx…
管
理员的 Dba_table,…
所
有有权限访问的 Full_table,….
约束
表间外键约束，进行如下操作该表
是否受约束影响
Ins upd del
父 n y y
子 y y n
约束不能修改 ，只
能删除重建
视图
虚表，只包含一个查询公式，每次
访问时都执行公式一次。
简单视图可以通过视图的DML操作改变基表，然而也不一定，例如
T1
x int not null, y int
v1
select y from t1;
如果对v1进行插入操作，由于x not null的存在，将无法进行插入。
可以使用替代触发器（取消用户的
操作，以其他操作来替代）来解决，当插入操作进行时，自动设置x的值。
With check option 语句根据创建视图时的where语句条件划定的集合，对DML进行限制，如果修改的数据不符合这个条件，则拒绝DML操作。
TOP-n分析
找y最大的前三个。
Select * from t1 where
rownum <=3 order by y desc;
上述写法错误，因为语句先按照where筛选，只得到前三行数据，因此结果错误。
利用
Select * from (select *
from t1 order by y desc) where rownum <=3;
或者将子查询用视图临时保存下
来。
注意！ 当访
问某行且条件不满足时，rownum不会增加，因为rownum是结果集的行号！因此不能使用between and 来查询中间第xx 到yy范围的数据。
可以使用集合minus来查询中间范围段.
序列的nextval,curval 都不可以更改，只能删掉重建。
查
看执行计划
Set autotrace on
当
开始一个查询，系统首先查询字典表获取一些信息（recursive calls 递归调用次数）。
当
获取这些信息后，会缓存到内存，下次查询该表时，递归调用开销就小了。
十三章 优化部分不作要求。
基数（一个字段值中不重复值的
个数）
Btree 索引: 适用于高基数的字段， 等值查询
Bitmap索引 适用于大量重复值，低基数的字段上， 大量逻辑条件判断。
Hash索引. Oracle不允许。
集合运算
并 Union(合并时会去掉重复值, 后台会排序可能影响效率), union all(不去除重复值).
Intersect 交集
Minus 相减
PL/SQL
SQL的扩展，用于程序设计语言。
数据查询，维护SQL语句包含在存储过程中，作为代码组成部分。
游标和活动集 就是 指针
和数组的关系。
包有声明和实现两部分。
能处理的有： 存储
过程 函数 触发器
包 等。
块
匿名块 存储
过程 函数 触发器
如果一个块要被频繁调用，应当写
成存储过程或者函数。
TIMESTAMP 比Date 更精确
用单引号界定字符串，字符串内的
单引号用两个单引号转义。
NULL是真是假无法判断。
组合数据类型:
Tables 类似数组
记录record
类
似于结构体。
PLSQL只允许DML语句执行，如果要执行DDL，要使用Execute immediate 来动态执行语句。
(类似于js 的eval ?)
Varchar2(30) 是按照字节分配的
Varchar2(30 char) 是按字符分配的（一个字符根据编码不同占用字节数不同）
可变数组 VARRAY
联合数组
xxtype IS TABLE OF VARCHAR(20) INDEX BY BINARY_INTEGER 下标在正负范围内随便写，根据表格属性来访问下标。
….睡着了
PL/SQL 中的select 只能获取一行数据，没有或者多行都是异常。
系统优先将变量名解析为列，因
此不要使用跟列名同名的变量名！
继续走神。。。
条件空值语句
在判断真假时，出现NULL,无法判断真假，在while, if 等判断中都是只在值为真的时候才执行。
PLSQL的for 循环无法实现非1（-1）步长的循环。
循环中的循环变量是自动增加
的，且其作用域是在循环块内，当与外部变量同名时覆盖。
游标
游标分隐式和显示。每条语句执行
时，都会产生游标。
隐式：在声明DML 和PL/SQL 中SELECT 语句时自动产生的游标。
显示：在程序中定义。
Declare –>open >
fetch <> empty ? >close
Declare
Cursor cur_name IS
Select…;
BEGIN
Open cur_name;
Fetch cur_name into
var1, var2..;
Fetch cur_name into
record_var1;
Close cur_name;
END
使用循环获取多行数据。
1普通循环，自己做open fetch close 操作
Open cur
LOOP
Fetch cur into xxxx…;
Exit when cur%NOTFOUND;
Do something…
END LOOP;
Close cur;
2 用于游标的循环 for in , 省略了open fetch close 操作,使用方式简洁。
游标不能重复打开，要再次打
开，要先close
记住For
in 方式使用游标会自动打开游标。
Into 的列数要与cursor定义的一致。
游标使用完毕记得要关闭，以释
放内存。
FETCH 之后游标才移动到该行，因为fetch之后还可以做NOTFOUND检测。
游标
取值失败后，不会将之前的变量值覆盖，可能造成数据重复。
参数游标 ： 确定指向哪个表，但是查询参数可变
Cursor xx(v1,v1..) IS …;
Open xx(val1,val2..);
…
参照游标 ：不确定指向哪个表。
更新游标（
for update & current of）
Cursor cur_name is
Select … for
update; // 将字段悲观锁定
如果改成 select xx for update of xx; 则只锁定某个字段。
Begin
….
Update xx where current of cursor_name; 更新游标指向的
行。
游标与事务
一般的游标
不会产生锁
更新游标
• 打开游标前没有锁
• 使用for update nowait检测锁的冲突
• 打开游标后产生锁（TM：RS，TX：X）
• 关闭游标后，锁直到commit或rollback结束事务才释放
使用更新
游标过程中不要COMMIT或ROLLBACK（不用for update时没事）
隐式游标
可以使用%NOTFOUND属性，但是
不能用%ISOPEN
存储过程，函数，触发器，包
参数类型：
IN 当作常量使用，不允许作为左值出现， 传参为引用类型。
OUT/INOUT 左右都可以,
传参时会将值复制。
INOUT使用NOCOPY
可以不复制，直接传引用，提高效率。
存
储过程可以重载。
//走神中。。。
触发器 与 审计
审
计只要有操作就触发，且无论操作成功还是失败
触发器在insert,update,delete触发。触发器如果不提交，会被回滚。
触
发器可以触发操作，而审计只会在日志中写入记录。
For each row 加在
creat xx … on yy 后面，则是行级别的触发器，不加是语句级别的。
两个记录变量 冒号new/old
:new update/insert时，是将要插入
或者更新的新数据,如果是delete则是空。
:old 如果是insert,则是空，如果是update则是更新前的数据，如果是delete, 则是要删除的数据。
如果插入数据不合规范，可以对:new进行整理，然后执行插入，则:new中的数据会插入行。
比如将序列用触发器写入id值：
在before update/insert 创建触发器，执行
Select seq.nextval into
:new.id from dual;
触发器触发顺序：
1 语句级别的before
2 行级别before的触发器，如果级别相同，先执行后定义的。
3 处理数据
4 行级别的after, 同级执行后定义的
5 下一行。。。
6 语句级别的after
触发器级别
模式级别（用户）， 数据库级别
Oracle 体系结构
实例，数据库
----SGA 系统全局区
1. shared pool 语
句的解析、执行计划的缓存池，适度大小
2. Db buffer
cache 操作影响的数据块，越大越好，命中率越高
3. Log buffer 将Log积累到一定量一次性写入
4. Large pool [可
选] 备份/恢复的工作缓存区，如果没有大
池，则使用共享池
5. Java pool[可
选] 对java应
用的支持，分配给它的缓存
----background process 后台进程
Smon 系统监视器(监视服务器)
Pmon 进程监视器(监视客户端)
Dbw(database writer 数据库写进程) 将内存里的脏数据块写入磁盘，脏数据即在内存中被修改的数据（与磁盘上不一致的）
数据
库的瓶颈就在内存与磁盘的读写的（即IO），因此减少IO是数据库设计的要点。
写数
据的条件：
no free buffer
dirty xxx 脏数据块累计到一定量时
time out
tbs offline 离线表空间
LGWR 日志写进程
1. 写触发条件：
2. Log buffer 1/3
满时
3. 生成 1M redo data 时
4. Per 3 seconds
5. Commit，定义ACID,指数据库事务正确执行的四个基本要
素的缩写.包含:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。Commit 时
写入日志保证事务操作的持久性。
6. 在数据库进程写之前，要将日志写磁盘。
Chpt 当检测点出现，就一定要将内存中的脏数据写入数据库。每隔一定时间要出现检查点，当出现问题时，可
以回复到最后一个检查点。 检查点是性能和可恢复性之间的权衡。检查点越频繁，性能低，但是可恢复性强了。
Arch 归档
----PGA 程序全局区
每个服务器进程都有一个PGA, 排序操作就是发生在PGA内存中，由进程自己使用。
----server process 数据库进程
DATABASE 数据库：
Control file控制文件：
包含很多重要的状态信息，有多个相同的镜像文件来保护这些信息，启动数据库时要求几个文件都在正常状态，写入时向几个文件都写入。一般是两三个文件，最好
分配在不同的磁盘上。
Data file 数据文件：逻辑
上跟表空间对应的，一个或多个数据文件构成一个表空间。一般以dbf坐后缀。
Log file
Initial parameter file spfileSID.ora
Password file
继续走神。。。。。。。
要明确的了解Oracle的体系结构
数据字典：字典表，动态性能表
区别：名字，存储，数据来源
怎样使用字典表
DBA_xxx
Oe_xxx
用户，方案对象，存储结构
一般是只读。当我们执行DDL时，由数据库自己去修改字典表。
动态性能表
以V$为前缀。
V$process , v$session, V$sga ….
记录数据库当前的活动、状态、标
志等。由SYS用户所有，不允许DML.
系统关闭后，字典表会保留在磁
盘，动态性能视图不会保留。
在优化过程中，通过动态视图监控
数据库的话，那么要保证在监控期间数据库不间断运行，应确定好采样范围是有代表性的。
数据库存储
Physical物理结构
1. Data file, 由os block 组成。
Logical 逻辑结构
Db block( oracle 的块，是os block的整数倍, db_block_size 参
数决定, 例如16k, io 的最小单位)，
多个db block 构成一个extend(扩展，或者叫区).
Extend 是空间分配的最小单位。
多个extend 构成一个段segment，
段对应一个实际的对象，比如一个表，一个索引，但是视图不是，视图只是个定义，不会消耗磁盘空间。可能出现一张表是由多个段构成的，比如分区表。
段再构成tablespace.
Table, index 可能跨多
个db file进行存储，修改table,index的内容可能导致多个文件大小同时改变。
物理文件和表空间对应。
系统表空间
非系统表空间 见课
件
空间管理方式：
DMT ：dictionary 字典管理表空间， 表空间的区的分配是由字典管理的。
LMT ：local 本地管理表空间， 区是由本地表空间来管理的。( 9+版本)
建议在Oracle中，有自动选项的，尽量勾选。
不建议使用多个小文件来构建一个
表空间，建议使用一个大文件来存储。
Oracle 自动管理空间方式
Dba_segments 段字典
Dba_extends 区字典， 区的
大小是根据数据量调整的。
Pctfree
Pctused
当一个块装满后，某条记录数据增
多，会导致行迁移，系统在另一个块中存储该行，然后在原位置留一个指针指向那一行。之所以这么做，是因为当插入一个行时，系统分配一个行标识符，即物理地
址，而索引中是包含这个标识符的，如果行迁移时修改行标识符，则所有的索引都要改变，因此系统不修改行的位置，而是在那个位置添加指针。
为了避免行迁移，一个块不应当装
的太满，pctfree,pctused用于指定一个块装了多少的时候允许插入数据。
Oracle 学习笔记
