Expert One-on-One Oracle阅读笔记(3)

王朝oracle·作者佚名 2006-01-31

宽屏版字体: 小|中|大|超大

绑定地址

bln

绑定缓冲区大小

avl

真实值长度

flag

内部标记

value

绑定值的字符串表示（如果可能，会是一个十六进制dump）

其中dty：SELECT text

FROM ALL_VIEWS WHERE view_name = ‘USER_VIEWS’ 可看到一个将dty数值转换为字符串表示的函数。

此后我们可以看到WAIT段，即真正的等待事件。

对于ENQUEUE事件，实际就是锁。可用以下函数（传入参数为p1）判断类型：

CREATE OR REPLACE FUNCTION

enqueue_decode(l_p1

in number) return varchar2

l_str

varchar2(25);

BEGIN

SELECT

CHR(BITAND(l_p1, -16777216) / 16777215) ||

CHR(BITAND(l_p1, 16711680) / 65535) || ‘

‘ ||

DECODE(BITAND(l_p1, 65535),

0, ‘No lock’,

1, ‘No lock’,

2, ‘Row-Share’,

3, ‘Row-Exclusive’,

4, ‘Share’,

5, ‘Share Row-Excl’,

6, ‘Exclusive’ )

INTO

l_str

FROM

DUAL;

RETURN

l_str;

END;

XCTEND（事务边界）段记录了提交等：

rlbk

回滚标记：0

提交

1 回滚

rd_only

只读标记：0

变化提交或回滚

1 事务只读

STAT段记录了运行时SQL真正的执行计划：

cursor #

游标号

执行计划行号

cnt

查询计划中流经此步骤的行数

pid

此步骤的父ID

pos

执行计划中的位置

obj

访问的对象的对象ID

操作的文本描述

PARSE

ERROR段

len

SQL语句长度

dep

SQL语句递归深度

uid

分析的方案

oct

Oracle命令类型

lid

权限方案ID

tim

定时器

err

ORA错误代码

ERROR段

cursor #

游标数

err

ORA错误代码

tim

定时器

10.5

DBMS_PROFILER

10.6

StatsPack

10.7 V$表

V$EVENT_NAME

说明事件名和p1、p2、p3三个参数。

V$FILESTAT和V$TEMPSTAT

说明系统I/O概况。

V$LOCK

说明系统锁的情况。但注意Oracle并不在外部保存行锁，此视图可以找到TM（DML

Enqueue）锁，即说明产生了行锁。

V$MYSTAT

说明当前会话的统计信息。需要V_$STATNAME（不用V$STATNAME，只是V_$STATNAME的一个同义词）和V_$MYSTAT上的SELECT权限。

CREATE VIEW MY_STATS

SELECT a.name,

b.value

FROM V$STATNAME a, V$MYSTAT

WHERE a.statistic# =

b.statistic#;

V$OPEN_CURSOR

记录所有会话打开的游标。由于Oracle也会缓存已关闭的游标，因此此视图中也会包含已关闭的游标信息。

V$PARAMETER

说明了所有的init.ora参数。

V$SESSION

记录数据库的每个会话。需要对V_$SESSION的SELECT权限。

V$SESSION_EVENT

说明会话的事件情况。

V$SESSION_LONGOPS

记录CBO认为执行时间超过6秒的命令及进展。

V$SESSION_WAIT

记录所有正在等待某事件的会话及已等待时间。

V$SESSTAT

类似V$MYSTAT，但显示所有会话。

V$SESS_IO

说明会话的I/O信息

V$SQL和V$SQLAREA

记录SQL信息。建议使用V$SQL，V$SQLAREA是从V$SQL合并而来的视图，代价较高，对已经繁忙的系统是一个负担。

V$STATNAME

说明了统计号到统计名的映射。

V$SYSSTAT

记录实例层面的统计信息。当数据库关闭时才清空，也是StatsPack很多数据的来源。

V$SYSTEM_EVENT

记录实例层面的等待事件信息。也是StatsPack很多数据的来源。

第 11

章优化器计划稳定性

11.1 概览

CREATE OR REPLACE ONLINE

MyOutLine

FOR CATEGORY

mycategory

SELECT

……;

需要CREATE

OUTLINE权限

使用时指定会话的CATEGORY即可：

ALTER SESSION SET USE_STORED_OUTLINES =

mycategory;

11.2 OPS的使用

对已封装的应用中SQL进行的优化方法

ALTER SESSION SET CREATE_STORED_OUTLINES =

test;

执行应用，如一个存储过程等

ALTER SESSION SET CREATE_STORED_OUTLINES =

FALSE;

SET LONG 5000

SELECT name, sql_text FROM user_outlines WHERE

category = ‘test’;

此时可以看到所运行的SQL语句。也可以通过一个ON

LOGON触发器来实现，即一登陆就ALTER

SESSION…

优化时修改OPTIMIZER_GOAL后：

ALTER SESSION SET OPTIMIZER_GOAL =

FIRST_ROWS;

ALTER OUTLINE name

REBUILD;

ALTER SESSION SET OPTIMIZER_GOAL =

CHOOSE;

此时就固定为OPTIMIZER_GOAL

= FIRST_ROWS时的执行计划了。

一个开发工具

由于开发环境与实际部署环境可能不一致，为了保证执行计划与开发环境一致，可以建立一个ON

LOGON触发器来将执行计划归入一个category中，然后exp/imp到新环境中。

用来观察是否使用了索引

SELECT name, hint FROM

user_outline_hints

WHERE hink LIKE

‘INDEX%’;

用来观察应用使用了什么SQL语句

11.3 OPS如何工作

OUTLINES与OUTLINE_HITS

均分别有DBA_、USER_、ALL_三张视图，其中DBA_多一个owner字段，说明创建者，另两张与用户有关系。

DBA_OUTLINES：

NAME

OUTLINE名，若创建时未指定，则使用系统命名

OWNER

创建时的方案名

相关的权限

CREATE ANY

OUTLINE – 创建概要，否则报ORA-18005错误

ALTER ANY

OUTLINE – 修改或重新计算概要

DROP ANY

OUTLINE – 删除概要

EXECUTE ON

OUTLN_PKG – 执行OUTLINE包

注意这里权限都是全局的，概要不存在真正的所有者。

使用DDL

CREATE <OR REPLACE> OUTLINE

OUTLINE_NAME

<FOR CATEGORY

CATEGORY_NAME>

ON STATEMENT

使用ALTER SESSION

ALTER SESSION SET CREATE_STORED_OUTLINES =

TRUE;

ALTER SESSION SET CREATE_STORED_OUTLINES =

FALSE;

ALTER SESSION SET CREATE_STORED_OUTLINES =

mycategory;

当设为TRUE时，所创建的概要归类入DEFAULT。

11.5 OUTLN用户

所有8i数据库中均缺省创建，缺省密码为OUTLN，并可在安装后立即更改。方案含有两个表和一些索引，存放于SYSTEM表空间中，若需要大量使用概要，可用如下方法转移表空间（其中一张表含有LONG类型字段，无法ALTER TABLE

MOVE）。

EXP USERID=OUTLN/OUTLN

OWNER=OUTLN

ALTER USER OUTLN DEFAULT TABLESPACE

tools;

REVOKE UNLIMITED TABLESPACE FROM

OUTLN;

ALTER USER OUTLN QUOTA 0K ON

SYSTEM;

ALTER USER OUTLN QUOTA UNLIMITED ON

tools;

DROP TABLE ol$;

DROP TABLE

ol$hints;

IMP USERID=OUTLN/OUTLN

FULL=YES

若系统已经使用了概要，则操作应尽量在单用户模式下执行，数据库无其它活动终端用户。

11.6 在数据库间转移概要

EXP USERID=OUTLN/OUTLN QUERY=”where category=’test’”

tables=(ol$, ol$hints)

IMP USERID=OUTLN/OUTLN FULL=Y

IGNORE=YES

这里也可以使用参数文件来定义导出的查询条件。

11.7 获得正确的概要

有时仅修改某些参数是无法获得所需要的执行计划的，还要添加提示。但概要的使用是基于相同的SQL文本，为了不修改应用但使用添加了提示的执行计划，可以采用如下方法：

例如需要SELECT FROM

(SELECT /*+ use_hash(emp) */ FROM emp)

emp,

(SELECT /*+ use_hash(dept) */ FROM dept)

dept

WHERE

emp.deptno=dept.deptno;

则可以在另一个方案中删除emp、dept表，将内层查询语句建立成名为emp和dept的视图，然后对SELECT *

FROM emp,dept WHERE emp.deptno=dept.deptno; 建立概要。则此后可以指定应用使用此概要（SQL文本一致）。

这也是利用了OPS是全局的，并不关心所引用对象，而是纯粹根据SQL文本进行转换。

11.8 管理概要

通过DDL

ALTER OUTLINE outline_name RENAME TO

new_name;

ALTER OUTLINE outline_name CHANGE CATEGORY TO

new_category_name;

ALTER OUTLINE outline_name

REBUILD;

DROP OUTLINE

outline_name;

OUTLN_PKG包

作用：提供批量管理的功能；提供EXP/IMP的API

由DBMSOL.SQL和PRVTOL.PLB脚本（%ORACLE_HOME%/RDBMS/ADMIN）创建，而这两个脚本由CATPROC.SQL调用并缺省安装到数据库。

DROP_UNUSED

– 删除所有类别中所有未使用的概要。

EXEC

OUTLN_PKG.DROP_UNUSED;

DROP_BY_CAT

– 删除指定类别中的所有概要。

EXEC

OUTLN_PKG.DROP_BY_CAT(category_name);

UPDATE_BY_CAT

– 重命名一个类别或将其合并入另一个类别。

EXEC

OUTLN_PKG.UPDATE_BY_CAT(old_category_name, new_category_name); 若新名已被用，则合并，且若新旧类别存在相同SQL文本的概要，保留新类别中的，而此重复的概要仍保留于原类别中。

11.9 最后说明

创建概要需要CREATE ANY

OUTLINE权限，若无权限，利用ALTER

SESSION方式来创建概要时不会提示错误，但不会创建概要。

删除用户时即便指定CASCADE选项，也不会删除其创建的概要。

如果CURSOR_SHARING设为force，则用DDL和ALTER

SESSION两种方法获得的SQL文本可能是不同的，前者就是输入的SQL，而后者是系统内部已经转换过绑定变量的SQL。

概要的使用依靠文本完全匹配，即便是大小写不同也会造成SQL文本不匹配。

OR扩展问题：由于WHERE条件中有OR的SQL会被改写为UNION

ALL模式，概要记录的提示可能无法正常使用，而只是作用到第一个条件上。因此要注意USER_OUTLINE_HINTS表中HINT LIKE

‘USE_CONCAT%’的概要和提示，应当删除或移走。

使用概要对性能影响很小。创建概要时接近首次分析该条语句的时间，此后第一次分析慢于正常分析时间，而随后概要已经进入缓存，将不会观察到性能影响。

11.10 可能遇到的错误

ORA-18001 – 使用ALTER OUTLINE语法错误

ORA-18002 – 所引用的概要不存在（从未创建过或者被删除）

ORA-18003 – 概要的数字签名已存在，数字签名用于快速查找到合适的概要，此错误极少发生

ORA-18004 – 概要已存在，一般是命名冲突

ORA-18005 – 需要CREATE ANY OUTLINE权限

ORA-18006 – 需要DROP ANY OUTLINE权限

ORA-18007 – 需要ALTER ANY OUTLINE权限

第 12

章分析函数

12.1 分析函数如何工作

语法

FUNCTION_NAME(<参数>,…)

OVER

(<PARTITION BY

表达式,…> <ORDER BY 表达式 <ASC | DESC> <NULLS FIRST | NULLS LAST>>

<WINDOWING子句>)

PARTITION子句

ORDER BY子句

WINDOWING子句

缺省时相当于RANGE UNBOUNDED PRECEDING

1. 值域窗（RANGE WINDOW）

RANGE N PRECEDING

仅对数值或日期类型有效，选定窗为排序后当前行之前，某列（即排序列）值大于/小于（当前行该列值

–/+ N）的所有行，因此与ORDER BY子句有关系。

2. 行窗（ROW WINDOW）

ROWS N PRECEDING

选定窗为当前行及之前N行。

还可以加上BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND n

FOLLOWING

函数

AVG(<distinct | all> expr)

一组或选定窗中表达式的平均值

CORR(expr,

expr)

即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) *

STDDEV_POP(expr2))，两个表达式的互相关，-1（反相关） ~ 1（正相关），0表示不相关

COUNT(<distinct> <*> <expr>)

计数

COVAR_POP(expr, expr)

总体协方差

COVAR_SAMP(expr, expr)

样本协方差

CUME_DIST

累积分布，即行在组中的相对位置，返回0 ~ 1

DENSE_RANK

行的相对排序（与ORDER BY搭配），相同的值具有一样的序数（NULL计为相同），并不留空序数

FIRST_VALUE

一个组的第一个值

LAG(expr,

<offset>, <default>)

访问之前的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如第一行不存在之前行）

LAST_VALUE

一个组的最后一个值

LEAD(expr,

<offset>, <default>)

访问之后的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如最后行不存在之前行）

MAXexpr)

最大值

MIN(expr)

最小值

NTILE(expr)

按表达式的值和行在组中的位置编号，如表达式为4，则组分4份，分别为1 ~ 4的值，而不能等分则多出的部分在值最小的那组

PERCENT_RANK

类似CUME_DIST，1/(行的序数 - 1)

RANK

相对序数，允许并列，并空出随后序号

RATIO_TO_REPORT(expr)

表达式值 / SUM(表达式值)

REGR_

xxxx(expr, expr)

线性回归函数

ROW_NUMBER

排序的组中行的偏移

STDDEV(expr)

标准差

STDDEV_POP(expr)

总体标准差

STDDEV_SAMP(expr)

样本标准差

SUM(expr)

合计

VAR_POP(expr)

总体方差

VAR_SAMP(expr)

样本方差

VARIANCE(expr)

方差

12.2 例子

竖表转横表

一般形式为将一个列为C!, C2, … CN的表，以C1, C2, … CX为基准，将CX+1, … CN的不同值改为列。一般化的语法：

SELECT C1, C2, … CX,

MAX(DECODE(rn, 1, CX+1, NULL)) CX+1_1, … MAX(DECODE(rn, 1, CN, NULL))

CN_1

MAX(DECODE(rn, 2, CX+1, NULL)) CX+1_2, … MAX(DECODE(rn, 2, CN, NULL))

CN_2

…

MAX(DECODE(rn,N,CX+1, NULL)) CX+1_N, … MAX(DECODE(rn,N, CN, NULL))

CN_N

FROM

(SELECT C1, C2, …

CN,

ROW_NUMBER() OVER (PARTITION

BY C1, C2, … CX ORDER BY <something>) rn

FROM T

WHERE …)

GROUP BY C1, C2, … CX;

通用包：

CREATE OR REPLACE PACKAGE

pkg_pivot

TYPE refcursor IS REF CURSOR;

TYPE ARRAY IS TABLE OF VARCHAR2(30);

PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT

NULL,

p_max_cols_query IN VARCHAR2

DEFAULT NULL,

p_query IN

VARCHAR2,

p_anchor IN

ARRAY,

p_pivot IN

ARRAY,

p_cursor IN OUT

refcursor);

END;

CREATE OR REPLACE PACKAGE BODY

pkg_pivot

PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT

NULL,

p_max_cols_query IN VARCHAR2

DEFAULT NULL,

p_query IN

VARCHAR2,

p_anchor IN

ARRAY,

p_pivot IN

ARRAY,

p_cursor IN OUT

refcursor)

l_max_cols NUMBER;

l_query LONG;

l_cnames ARRAY;

BEGIN

IF (p_max_cols IS NOT NULL)

THEN

EXECUTE IMMEDIATE p_max_cols_query INTO

l_max_cols;

ELSE

RAISE_APPLICATION_ERROR(-20001, 'Cannot

figure out max cols');

END IF;

l_query := 'select ';

FOR i IN 1 .. p_anchor.count

LOOP

l_query := l_query || p_anchor(i) ||

',';

END LOOP;

FOR i IN 1 .. l_max_cols

LOOP

FOR j IN 1 ..

p_pivot.count

LOOP

l_query := l_query ||

'max(decode(rn,'||i||','||p_pivot(j)||',null)) '||p_pivot(j) || '_' || i ||

',';

END LOOP;

l_query := RTRIM(l_query,',') || ' from (' || p_query || ') group by

FOR i IN 1 .. p_anchor.count

LOOP

l_query := l_query || p_anchor(i) ||

',';

END LOOP;

l_query := RTRIM(l_query,',');

EXECUTE IMMEDIATE 'alter session set

cursor_sharing=force';

OPEN p_cursor FOR l_query;

EXECUTE IMMEDIATE 'alter session set

cursor_sharing=exact';

END;

其中：

p_max_cols_query为SELECT MAX(COUNT(*)) FROM TABLE_NAME GROUP BY C1, C2, …

CX;

p_query为SELECT C1, C2, … CN ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX

ORDER BY <something>) rn FROM TABLE_NAME;

p_anchor为pkg_pivot.array(C1, C2, … CX)

p_pivot为pkg_pivot.array(CX+1, CX+2, … CN)

p_cursor为返回的游标。

12.3 最后说明

PL/SQL与分析函数

PL/SQL不支持分析函数的语法，可以通过以下两种方法解决：

１．使用动态游标；

２．将含分析函数的语句创建为视图。

WHERE子句中的分析函数

由于查询仅在最后的ORDER BY子句前执行分析函数，因此WHERE条件中无法使用分析函数，只能利用嵌套循环实现。

第 13

章物化视图

8.1.5企业版/个人版开始支持

需要权限：GRANT CREATE MATERIALIZED VIEW，还必须直接赋予GRANT QUERY REWRITE。为实现查询重写，必须使用CBO。

13.1 物化视图如何工作

设置

COMPATIBLE参数必须高于8.1.0

QUERY_REWRITE_ENABLED = TRUE

QUERY_REWRITE_INTEGRETY =

ENFORCED - 查询仅用Oracle强制与保证的约束、规则重写；

TRUSTED – 查询除用Oracle强制与保证的约束、规则，也可用用户设定的数据间的任何关系来重写；

STALE_TOLERATED – 即便Oracle知道物化视图中数据过期（与事实表等不同步），也重写查询。

创建物化视图的用户必须具有直接赋予的GRANT QUERY REWRITE权限，不能通过角色继承。

内部机制

全文匹配

部分匹配：从FROM子句开始，优化器比较之后的文本，然后比较SELECT列表

一般重写方法：

数据充分

关联兼容

分组兼容

聚集兼容

13.2 确保使用物化视图

约束

考虑到现实环境的数据量，可以将主键、外键、非空等约束置为NOVALIDATE，并调整QUERY_REWRITE_INTEGRITY为TRUSTED，这样可以达到“欺骗”数据库的目的，但必须注意如果无法保证此类约束的真实有效，查询改写后可能造成结果不精确。

维度

实际就是指明已存在的表中各列的归并关系，从而关联事实表后形成的物化视图可用于向“上”归并（相当于用表中代表更高归并关系的列关联事实表）。标准语法：

CREATE DIMENSION

time_hierarchy_dim

LEVEL day IS

time_hierarchy.day

LEVEL mmyyyy IS

time_hierarchy.mmyyyy

LEVEL yyyy IS

time_hierarchy.yyyy

HIERARCHY time_rollup

(day CHILD OF mmyyyy CHILD OF

yyyy)

ATTRIBUTE mmyyyy

DETERMINES mon_yyyy;

13.3

DBMS_OLAP

估计（物化视图）大小

DBMS_OLAP.ESTIMATE_SUMMARY_SIZE(视图名, 视图定义, 估计行数, 估计字节数);

其中后两个参数为NUMBER型输出参数。

维度有效性检查

DBMS_OLAP.VALIDATE_DIMENSION(视图名, 用户名, FALSE, FALSE);

SELECT * FROM 维度表名

WHERE ROWIN IN (SEELCT bad_rowid FROM

MVIEW$_EXCEPTION);

所选出行即为不符合维度定义的行。

推荐物化视图

首先必须添加合适的外键，包通过外键来判定表之间的关系而不是维度。

DBMS_OLAP.RECOMMEND_MV(事实表名, 1000000000, ‘’);

第二个参数表示物化视图可用的空间大小，可传入一个较大的数。第三个参数传入需要保留的特定物化视图，传入空即为不考虑其他物化视图。

执行C:\oracle\RDBMS\demo\sadvdemo后执行：

DEMO_SUMADV.PRETTYPRINT_RECOMMENDATIONS

13.4 最后说明

物化视图不为OLTP系统设计

在事实表等更新时会导致物化视图行锁，从而影响系统并发性。

第 14

章分区

14.1 分区的使用

增加可用性

减轻维护负担

提高DML与查询的性能

14.2 分区如何工作

表分区策略

索引分区

本地索引

分为本地前缀索引（Local Prefixed Index）、本地非前缀索引（Local Non-prefixed Index）

1．索引的选择

在单表查询中，本地非前缀索引可能增加可用性，也更加实用。例如表T(a, b)按a区间分区，若在b上建立本地索引，则当某个分区离线，仅查询b的某个值时，该索引可用，而索引(a, b)不可用；删除索引(a, b)，查询(a, b)的某对值，b上的索引仍可用。此时若建立索引(b, a)，则可应对各类查询。

在多表关联（如上例中按照(a, b)值关联）时，系统将发现代价较高而不会用到本地非前缀索引（如上例中(b, a)）。

因此建立本地索引时应当考虑通常的使用环境。

2．无法基于本地非前缀索引建立唯一键或主键。

全局索引

仅有一种，即全局前缀索引

1. 数据仓库环境

在（与建立好相应索引的表）交换分区与索引或分割分区后，全局索引将失效而必须重建，因此全局索引并不适合数据仓库。

例如：

ALTER TABLE

partitioned

EXCHANGE PARTITION

fy_1999

WITH TABLE

fy_1999

INCLUDING INDEXES

WITHOUT

VALIDATION;

ALTER TABLE

partitioned

SPLIT PARTITION

the_rest

(TO_DATE(‘200101’,

’yyyymm’))

INTO (PARTITION fy_2000, PARTITION

the_rest);

2. OLTP环境

一定程度上增加了可用性。当某些分区离线，不含有用于分区的列且合乎查询条件的数据存在于在线分区的索引仍然是可用的，对于不需要查询全表而是通过索引即可得到结果的查询也是有效的（例如COUNT非用于分区的列等）。

第 15

章自治事务

DECLARE

PRAGMA

AUTONOMOUS_TRANSACTION;

15.1 为何使用自治事务

无法回滚的审计

一般情况下利用触发器禁止某些对表的更新等操作时，若记录日志，则触发器最后抛出异常时会造成日志回滚。利用自治事务可防止此点。

避免变异表

即在触发器中操作触发此触发器的表

在触发器中使用DDL

写数据库

对数据库有写操作（INSERT、UPDATE、DELETE、CREATE、ALTER、COMMIT）的存储过程或函数是无法简单的用SQL来调用的，此时可以将其设为自治事务，从而避免ORA-14552（无法在一个查询或DML中执行DDL、COMMIT、ROLLBACK）、ORA-14551（无法在一个查询中执行DML操作）等错误。需要注意的是函数必须有返回值，但仅有IN参数（不能有OUT或IN/OUT参数）。

开发更模块化的代码

在大型开发中，自治事务可以将代码更加模块化，失败或成功时不会影响调用者的其它操作，代价是调用者失去了对此模块的控制，并且模块内部无法引用调用者未提交的数据。

15.2 如何工作

事务控制

DECLARE整个块都是属于父事务的，自治事务从离PRAGMA后的第一个BEGIN开始，只要此BEGIN块仍在作用域，则都属于自治事务。例如在DECLARE模块中声明一个写数据库的函数，则此函数虽然在自治事务所在存储过程执行，但其属于父事务；而自治事务中调用的任何函数和存储过程、激发的任何触发器等均为此自治事务的一部分。

自治事务可以嵌套，嵌套深度等只受INIT.ORA参数TRANSACTIONS（同时并发的事务数，缺省为SESSIONS的1.1倍）制约。

作用域

1. 包中的变量

自治事务可看到并修改父事务的变量，父事务也会察觉到这一改变，且不存在回滚问题。

2. 会话设置/参数

自治事务与父事务共享同一个会话环境，通过ALTER SESSION作的修改对整个会话均有效。但SET TRANSACTION是事务级的，仅对提起修改的事务有效。

3. 数据库修改

父事务已提交的修改对自治事务可见，未提交的对自治事务不可见，自治事务的修改对父事务是否可见取决于隔离级别（Isolation Level）。

对于游标，取决于其打开的位置，若其在父事务中打开，则之前父事务未提交的修改对其是有效的，在自治事务中这些修改也可见；而在自治事务中打开，则父事务未提交的修改不可见。

若使用缺省的READ COMMITTED隔离级别，则自治事务的修改对父事务可见；若改用SERIALIZABLE，则不可见。

4. 锁

父事务与自治事务是完全不同的事务，因此无法共享锁等。

结束一个自治事务

必须提交一个COMMIT、ROLLBACK或执行DDL。

保存点

无法在自治事务中回滚到父事务中的一个保存点，只能在内部使用保存点。

15.3 最后说明

不支持分布式事务

截至8.1.7在自治事务中不支持分布式事务

仅可用PL/SQL

全部事务回滚

若自治事务出错，则全部回滚，即便父事务有异常处理模块。

事务级临时表

每个会话仅一个事务可访问事务级临时表（多个会话中的事务可并发操作）。

变异表

15.4 可能遇到的错误

ORA-06519 – 检查到活动自治事务，回滚——退出自治事务时没有提交、回滚或DDL操作

ORA-14450 – 试图访问正在使用的事务级临时表

ORA-00060 – 等待资源时检查到死锁

第 16 章动态SQL

16.1 为何使用动态SQL

实现动态SQL有两种方式：DBMS_SQL和本地动态SQL（EXECUTE IMMEIDATE）

主要从以下方面考虑使用哪种方式：

1. 是否知道涉及的列数和类型

DBMS_SQL包括了一个可以“描述”结果集的存储过程（DBMS_SQL.DESCRIBE_COLUMNS），而本地动态SQL没有。

2. 是否知道可能涉及的绑定变量数和类型

DBMS_SQL允许过程化的绑定语句的输入，而本地动态SQL需要在编译时确定。

3. 是否使用“数组化”操作（Array

Processing）

DBMS_SQL允许，而本地动态SQL基本不可以，但可以用其他方式实现（对查询可用FETCH BULK

COLLECT INTO，对INSERT等，可用一个BEGIN …

END块中加循环实现）。

4. 是否在同一个会话中多次执行同一语句

DBMS_SQL可以分析一次执行多次，而本地动态SQL会在每次执行时进行软分析。

5. 是否需要用REF

CURSOR返回结果集

仅本地动态SQL可用REF

CURSOR返回结果集。

16.2 如何使用动态SQL

DBMS_SQL

1. 调用OPEN_CURSOR获得一个游标句柄；

2. 调用PARSE分析语句。一个游标句柄可以用于多条不同的已分析语句，但一个时间点仅一条有效；

3. 调用BIND_VARIABLE或BIND_ARRAY来提供语句的任何输入；

4. 若是一个查询（SELECT语句），调用DIFINE_COLUMN或DEFINE_ARRAY来告知Oracle如何返回结果；

5. 调用EXECUTE执行语句；

6. 若是一个查询，调用FETCH_ROWS来读取数据。可以使用COLUMN_VALUE从SELECT列表根据位置获得这些值；

7. 否则，若是一个PL/SQL块或带有RETURN子句的DML语句，可以调用VARIABLE_VALUE从块中根据变量名获得OUT值；

8. 调用CLOSE_CURSOR。

注意这里对任何异常都应该处理，以关闭游标，防止泄露资源。

本地动态SQL

EXECUTE

IMMEDIATE ‘语句’

[INTO

{变量1,

变量2, …

变量N |

记录体}]

[USING [IN

| OUT | IN OUT] 绑定变量1, …

绑定变量N]

[{RETURNING

| RETURN} INTO 输出1 [, …,

输出N]…];

注意本地动态SQL仅支持弱类型REF

CURSOR，即对于REF CURSOR，不支持BULK

COLLECT。

16.3 最后说明

动态SQL的负面：破坏了依赖链、代码更脆弱、很难调优。

第 17

章

interMedia

第 18 章基于C的外部过程