Oracle 学习笔记收藏

Resource:

www.itpub.net

www.oradb.com

www.chinaunix.net

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 (transactioncontrol lang )  commit, rollback,savepoint

DCL  (datacontrol 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 assysdba

如果用户在操作系统的权限很高，可省略用户名和密码。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  sessionset 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列表中出现的列，如果未使用组函

数，那么它必须出现在GROUPBY子句中

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 byrollup(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 employeeswhere 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        nonreaptableread     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 whererownum <=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 intovar1, var2..;

Fetch cur_name intorecord_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

记住Forin 方式使用游标会自动打开游标。

Into 的列数要与cursor定义的一致。

游标使用完毕记得要关闭，以释放内存。

FETCH 之后游标才移动到该行，因为fetch之后还可以做NOTFOUND检测。

游标取值失败后，不会将之前的变量值覆盖，可能造成数据重复。

参数游标 ： 确定指向哪个表，但是查询参数可变

         Cursor xx(v1,v1..) IS …;

         Open xx(val1,val2..);

…

参照游标 ：不确定指向哪个表。

更新游标（for update & current of）

Cursor cur_name is

         Select … forupdate; // 将字段悲观锁定

如果改成 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 buffercache 操作影响的数据块，越大越好，命中率越高

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用于指定一个块装了多少的时候允许插入数据。