阿里巴巴数据库操作手册

一、 目的

明确建表操作的风险及标准流程，最大限度避免建表操作带来的故障。

二、 适用范围

l 项目预发布新建表 l 项目正式发布新建表 l 不包含数据订正所建临时表 l 不包含导数据所建的中间表

三、 风险评估

l 登录到错误的schema下，导致表建到错误的schema里，而应用无法访问。

l 忽略了TABLESPACE参数，导致表建到了默认表空间，导致后续空间增长和维护困难。 l 对于未来增量较快的表选择了一个空间规划不足的表空间，导致后续空间增长和维护困难。

l 脚本末尾缺少分号，导致该表没有被创建上，而执行DDL的过程又不会报错。

l 其他原因漏建了表，导致应用访问错误。

l 所建的表定义（表名、字段名、字段定义、字段个数、字段顺序）跟测试环境不一致，导致应用访问错误。

l 同步库没有及时创建相应的表，或者没有更新同步配置，导致同步及应用出问题。

四、 操作流程

1. 准备工作

a) 在项目需求分析阶段，跟数据库设计人员一起明确新表所存放的数据库。具体设计原则本文不繁述。

b) 准备发布脚本时，检查tablespace定义，检查tablespace剩余空间，参考表空间自身负荷及新表的预期负荷，为每个新建的表选择合适的表空间，并在建表语句中添加tablespace的配置。

c) 定发布计划时，跟开发接口人一起商定好建表操作的时间点。如小需求没有发布计划评审，则必须在提交测试时（即表结构冻结时）即开始与开发接口人确定建表时间点。如果发生计划外的发布建表需求，则要追究项目跟进的应用DBA沟通不力的责任。

d) 以目前的认知，仅建表操作本身不会对数据库造成任何风险，故操作的时间点可以放宽：

在变更时间窗口内，均可以执行建表操作。

e) 建表操作属于预授权变更，在做之前必须在ITIL中提交相应的变更申请。 2. 执行过程

a) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、system等用户建表。

b) 执行建表脚本。若一次建表个数超过三个以上，要求将脚本事先保存为文本文件，上传至数据库服务器，执行时使用 @create_table_ddl.sql的方式直接执行。

c) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。 3. 验证方案

a) 常规检查：@dbcheck

b) 检查表定义是否与测试库一致：

exec pkg_check.CompareObject(‘user‗,‘TABLE_NAME‗); c) 立即联系开发接口人进行应用测试，【建表】变更是否成功以应用测试结果为准。 d) 同步库若建表，也需要执行 a) 和 b) 两个步骤。

02-数据订正

一、 目的

明确【数据订正】操作的种类、风险，并根据各种类型的数据订正制定完善的步骤和回退方案，最大限度减少此类操作带来的故障。

二、 适用范围

l 新建表数据初始化 l 现有表新增数据 l 现有表删除数据

l 现有表上新增字段初始化 l 现有表上现有字段值修改

三、 风险评估

l 业务风险：订正本身所包含的业务不正确，导致给客户给公司带来损失。

l 程序风险：订正本身业务正确，但是应用程序无法兼容订正的数据，导致应用出错。 l 数据库风险：订正本身业务正确，应用程序也可以兼容，但是订正速度过快、订正并发压力过大，导致数据库无法正常提供服务。通常会造成表空间耗尽、undo消耗过快、archive增长过快、备库恢复压力大等问题。 l 沟通风险：在业务方-开发接口人-DBA三方的沟通交流过程中，信息传递错误或者不及时，导致最终订正的数据没有达到预期的目的。

l 回滚风险：主要是因为业务方的原因，订正完成一段时间后要求回退，若在订正前没有备份原始数据，则可能导致无法顺利回退或者回退难度极大，给客户给公司带来损失。 l 同步风险：各类同步架构下，数据订正可能导致同步堆积和同步延时，影响正常同步业务，所以有些大规模订正必须要正确屏蔽同步，并在多个库分别执行相同的订正脚本。

l 缓存：有些表在应用层面做了缓存，制定订正计划的时候要考虑到订正后是否需要更新缓存。

四、 操作流程

1. 准备工作

a) 需求分析阶段确认项目涉及的数据订正范围和数据量。

b) 跟开发人员确定订正后是否涉及到对缓存的刷新和订正。 c) 根据数据量评估对数据同步的影响，决定是否屏蔽同步。（应用DBA必须熟悉同步采用的技术、正常情况下的同步量和延时、可以容忍的同步延时、屏蔽同步的具体方法。） d) 注意规划订正速度，以防undo消耗殆尽。 e) 订正脚本：

i. 开发接口人直接提供可执行的SQL脚本，DBA只负责拷贝执行。

ii. 开发接口人提供主键及更新字段新值列表，由DBA导入数据库，写SQL脚本关联原表批量订正。

iii. 开发接口人提供订正逻辑，由DBA翻译为批量提交SQL脚本。 iv. 订正脚本要求可断点续跑，可反复执行。

v. 严禁仅用一个事务来处理大规模订正（影响的记录数超过1万笔）。超过一万笔的订正必须分段提交。

vi. 确认订正脚本的执行计划正确。

vii. 脚本中加入―进度报告‖，即调用如下包（但是对于trigger中判断client_info的不允许这样处理。）： Dbms_Application_Info.set_client_info(n || ‗ rows commit.‘); –n为变量，累加，表示当前订正的总记录数。

f) 开发阶段跟开发接口人确认数据订正逻辑，完成订正脚本，并跟开发接口人确认脚本是否正确，同时按照需求准备备份脚本。

g) 测试阶段在测试库执行订正脚本，由开发接口人和测试人员验证订正的正确性，应用DBA协助验证。

h) 发布前确定订正速度和并发度，确定订正时间段，预估订正总时长，若涉及量较大，需要跨天做订正，则应规划好每日订正的数据量和时间段。 i) 备份要求：

i. 新建表初始化：无需备份，回退时直接truncate即可。

ii. 现有表新增数据：新建备份表记录下新增记录的主键，或者在新增记录中特定字段标识区分出订正所新增的数据，回退时定向delete这些记录。

iii. 现有表删除数据：新建备份表记录下删除数据的完整记录，回退时直接从备份表中取出数据insert到原表。

iv. 现有表上新增字段初始化：无需备份，回退时将该字段update为NULL或者开发接口人要求的值。不得将删除字段作为回退手段。

v. 现有表上现有字段值修改：新建备份表记录下所改动记录的主键及所改动字段的原始值，回退时将改动过的字段按照主键更新到原表（若应用程序在回滚前已经修改了记录，则要根

据具体业务具体分析回滚方案）。

vi. 备份表：备份表统一命名为 table_name_bak_mmdd_operator，最后的operator为操作DBA的姓名每个字的首字母，如果超长了，则将原表名缩减。 创建人有责任定期删除创建时间超过一个月以上的备份表。 2. 执行过程

a) 如果需要，按照备份脚本备份数据。

b) 执行订正脚本。查看订正进度，使用如下脚本： select client_info from v$session where client_info is not null; –这个脚本必须配合前面描述的―进度报告‖脚本执行。 c) 检查undo消耗: @undo d) 检查表空间消耗: @tbs e) 检查归档空间

f) 检查同步延时是否异常。

g) 如果需要刷新应用缓存，在订正结束后通知应用刷新缓存。

3. 验证方案

a) 以应用验证为主，数据库辅助做一些count等验证。以应用验证通过为操作成功标准。

五、 核心对象风险

l 考虑到对erosa和otter的影响，严禁数据订正更新主键值。

六、 回退方案

按照备份时所做的各种不同的回退方案进行回退，回退之后也要要求应用做验证。

03-创建、删除、修改sequence

一、 目的

明确定义对于sequence对象的操作风险及步骤。

二、 适用范围

l 项目发布创建新sequence。

l 以删除、重建的方式修改sequence的起始值。 l 在线修改sequence的cache值。

三、 风险评估

l Sequence命名与应用程序中不一致，导致应用无法正常访问sequence。

l 双向同步的库，多库创建同名sequence，起始值和步长值设置不合理，导致生成的值在表中对应主键值同步产生冲突。

l 删除、重建sequence的过程中，应用无法访问sequence，高并发的应用可能会产生故障。 l 删除、重建sequence之后没有对sequence的权限进行恢复，导致原本访问该sequence的其他schema无法正常访问。

l Sequence的cache设置不合理，设置过小会导致大量的系统相关等待，反之则导致sequence生成值断层过多浪费严重。

l Java程序的int16数据类型只能容纳最大21亿，所以sequence不能超过这个值，如果有可能超过，需要跟开发确认。

四、 操作流程

1. 准备工作

a) 默认使用变更系统生成的sequence名称，如果要修改，必须跟开发人员沟通一致。 b) 与开发人员、项目发布负责人沟通变更时间点。对于删除、重建的操作必须明确告诉他们其间会有短暂的无法访问，如果是高并发的应用则选择在系统访问量最低的时候执行，规避风险。

c) 根据并发数确定cache值，默认为100，如遇特殊需求，酌情调整。

d) 删除、重建的操作，事先检查是否有其他schema拥有对于该sequence的访问权限： SELECT grantee, owner, table_name, privilege FROM dba_tab_privs

WHERE table_name = upper(‘重建的对象名‗);

e) 全面考虑同步的风险，确定同步环节中各个数据库的同名sequence起始值及步长，保证不会发生冲突，通常有如下两种做法：

i. 起始值相差不大，步长值等于数据库个数。以双库同步为例，起始值分别设为1和2，步长均设为2。

ii. 起始值相距较大，步长值相同。以双库同步为例，A库起始值设为1，B库起始值设为2亿，步长均设为1。相差的值可以根据增长预期进行调整。 2. 执行过程 a) 标准新建脚本：

CREATE SEQUENCE seq_tablename START WITH 1 CACHE 100; 命名规范： seq_tablename

默认不指定recycle和max value。 b) 标准重建脚本：

DROP SEQUENCE seq_tablename ;

CREATE SEQUENCE seq_tablename START WITH 1 CACHE 100;

为了尽量缩短sequence不可用时间，这两个语句一起放在SecureCRT的chartWindow中一起执行。

c) 标准修改cache脚本：

ALTER SEQUENCE seq_tablename CACHE 200;

d) 标准赋权脚本：

GRANT SELECT ON seq_tablename to username; 3. 验证方案

a) @dbcheck 检查是否有失效对象

b) 通知应用验证是否可以正常访问sequence

五、 核心对象风险

高并发对象重建时短暂不可访问；

04_增加、删除唯一约束

一、 目的

明确增删唯一约束操作的风险及标准流程，最大限度避免增删唯一约束操作带来的故障。

二、 适用范围

l 项目发布新建表的增删唯一约束 l 对于旧表的增删唯一约束

三、 风险评估

l 对现有表新增唯一约束的操作，会堵塞包括查询在内的所有操作，风险很大，请谨慎使用，尽量在新建表时和开发讨论后增加。

l 没有指定index,系统自动创建了index，删除约束时，自动创建的index同时删除了。 l 在高峰期创建,导致大量的library cache lock/pin的等待 l 有同步的应用，先要在源端加，后在目标端加。 l 表里有重复的数据，导致操作失败。

四、 操作流程

1. 准备工作

a) 检查唯一建字段上是否存在index。没有的话,需首先创建index( 步骤详见增加index手册)。

b) 检查唯一键上是否有重复数据，如有，需和开发讨论如何处理。

c) 根据应用的需求和数据库的负载情况，确定操作的时间点。对于数据量和访问量较大的表，变更时间点要谨慎选择.

d) 检查字段上是否已经有了约束。

e) 增加和删除唯一约束属于标准变更，需要开发在ITIL中提交事件单，应用dba提交变更单，有技术经理审批后执行。

f) 对现有表新增约束，如果使用validate这个参数，会导致该表上连查询在内的所有操作都被锁住，风险非常大；如果使用novalidate参数，这个参数会导致数据字典不一致（及导致sqlldr的时候会导入重复数据）。两者相比，故通常情况下用validate的风险更大，默认必须使用novalidate参数。

g) 约束名与所依赖索引名一致。 2. 执行过程

a) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。

b) 执行增加或删除的命令。命令模板如下：

ALTER TABLE 表名 ADD CONSTRAINT 表名_uk unique (字段名) USING INDEX 索引名 NOVALIDATE;

ALTER TABLE 表名 DROP CONSTRAINT 约束名 KEEP INDEX;

如有otter同步，要注意执行顺序：先在源数据库端加后在目标端增加。

c) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。 3. 验证方案

a) 常规检查：@dbcheck

b) 检查表定义是否与测试库一致：

exec pkg_check.CompareObject(‘user‗,‘TABLE_NAME‗); c) 检查约束是否加上或删除：

select* fromdba_cons_columns wheretable_name=upper(‘table_name’)

五、 核心对象风险

1. 核心表访问量大，数据量大。增加唯一约束时会短暂出现library cache pin/lock。执行时间要订在核心表访问的低峰期。

六、 回退方案

1. 执行前需准备好回退的脚本。

2. 回退时需得到开发的确认，并确认回退的时间点。

05-加字段

一、 目的

阐述表变更的风险及其步骤，降低对应用的影响和避免故障。

二、 适用范围

l 所有在使用的表的加字段

三、 风险评估

l 新增字段的类型、长度（精度）是否合适 解决方法：跟应用明确加字段和改字段的风险，确认新增字段类型正确、长度（精度）合适。以及跟应用明确老数据是否要订正？如何订正？新增列是否非空？是否有默认值等等。 l 新增字段的非空属性、默认值以及老数据问题。

新增字段如果是NOT NULL的，则一定要有默认值，否则老应用的insert代码可能报错。 表如果存在老数据，带上默认值的时候会导致oracle去订正老的数据行的新增列。如果老数据非常多，表的并发访问高，很有可能导致大面积的阻塞等待以及产生大事务，甚至有可能导致undo耗尽。倘若回滚，还会因为回滚产生的并发会话导致load飙升。

解决方法：先不带not null不带默认值加上列，再更改列默认值，再批量订正老数据，然后再加上not null属性。

如果是大表，并且并发访问很高的表，则新增列不允许为NOT NULL，以简化后面变更步骤，降低风险！

l 新增字段导致依赖对象失效、sql游标失效问题。

表的DML并发很高的时候，如果表上面还有依赖对象，新增字段会导致依赖对象失效。默认其他DML会话会尝试去自动编译这个依赖对象，此时很可能会出现大面积的library cache pin。应用会话的连接时间会加长，进而导致出现后续应用报不能取得连接池错误。应用服务器load由此飙升。

表新增字段也会导致跟该表有关的SQL的游标失效，如果SQL的并发很高（查询SQL或者DML SQL），失效后SQL会重新解析，此时也可能会出现大量的library cache pin & library cache lock。

解决方法：选择在业务低峰期发布，同时在数据库级别开启trigger禁用客户端程序自动编译功能，字段加完后再禁用该trigger。

l 表的依赖对象是否要相应调整。

表上面的依赖对象如果有存储过程或触发器等，逻辑是否需要相应调整。 l 是否涉及到同步。

同步中的表需要两地都要变更。涉及到erosa的要更新一下数据字典。Erosa需要重启一下。 l 是否要通知其他关联的部门。如DW, ASC或CRM等等。

有些表很多部门都用，需要沟通约定时间一起变更。如果有同步方案，同步方案的变更也要考虑。

四、 操作流程

1. 准备工作

a) 该表的数据量以及大小，以及数据变更量（按日/时/分/秒等） b) 该表的并发访问数，以及频率最高的几种sql的访问方式 2. 执行过程

以表T1 加字段 col2为例。T1的数据量非常大，访问频率很高。 a) 在sysdba下开启trigger禁用客户端自动编译功能。（可选）

Alter trigger SYS.ddl_trigger_for_database enable; b) 变更字段

以下加字段同编译失效对象连着执行。编译时先编译trigger再编译存储过程或package等 @conn zzzzzz/aaa

Alter table t1 add col2 varchar2(20); Alter trigger trg_t1_search compile; @conn retl/rrr

Alter trigger trg_t1_sync compile; @conn bopsretl/bbb

Alter trigger trg_t1_sync compile; @conn zzzzzz/aaa

Alter procedure sp_test compile;

后面3个trigger的编译可以开三个窗口同时进行。 另开一个窗口，在admin用户下查看当前失效对象 @dbcheck

c) 老数据订正

–如果需要默认值，加上默认值 Alter table t1 modify col2 default ‗Y‘; –数据订正存储过程

Create or replace procedure sp_dml0214 As

Cursor c1 is select rowed rid, id, col2 from t1 where col2 is null; V_cnt number := 0; Begin

For rec_c1 in c1 loop

V_cnt := v_cnt + 1;

Update t1 set col2=‘Y‘ where rowed = rec_c1.rid and id=rec_c1.id;

If mod(v_cnt,500)=0 then

Commit;

Dbms_application_info.set_client_info(‗sp_dml0214 ‗ || v_cnt || ‗ rows!‘);

End if;

End loop;

Commit;

Dbms_application_info.set_client_info(‗sp_dml0214 ‗ || v_cnt || ‗ rows!‘); End; /

Exec sp_dml0214;

另开一个窗口，查看订正进度 col machine for a19 col status for a12

col client_Info for a50

select sid,serial#,status,machine,client_Info,sql_hash_value from v$session where client_Info is not null;

d) 订正完后加上NOT NULL属性 Alter table t1 add col2 not null;

e) (国际站 可选)中美都变更，erosa重启 –更新erosa数据字典 ./getDict.sh –erosa重启 ./erctl stop

./erctl start

3. 验证方案

a) 验证sys下的trigger已经禁用

Select owner,trigger_name,status from dba_triggers where owner in (‗SYS‘) and trigger_name=‘ ddl_trigger_for_database enable‘; b) 验证结构正确 Desc zzzzzz.t1

c) 验证无失效依赖对象 @dbcheck

五、 核心对象风险

核心对象风险指的是业务上重要的表，并且数据量很大或表大小很大或并发访问数很高时，变更的潜在风险。前面已经阐述。

六、 回退方案

1. 大表的新增字段不允许回滚。因为回滚即删掉字段，会导致锁表，持续时间很长进而导致一序列的严重问题。

2. 普通表的新增字段如果是发布失败，后续还会有二次发布，也不考虑回滚。或者只是将not null属性拿掉，下次发布再加上。

3. 普通表如果应用有足够的理由要求回滚，则回滚。

06-加not null字段

一、 目的

阐述表变更的风险及其步骤，降低对应用的影响和避免故障。

二、 适用范围

l 所有在使用的表的加not null字段，但核心表（并发访问高的大表不允许加not null）。

三、 风险评估

l 新增字段的类型、长度（精度）是否合适

解决方法：跟应用明确加字段和改字段的风险，确认新增字段类型正确、长度（精度）合适。是否有默认值？以及跟应用明确老数据是否要订正？如何订正？ l 新增字段的非空属性、默认值以及老数据问题。

新增字段是NOT NULL的，则一定要有默认值，否则老应用的insert代码可能报错。 表如果存在老数据，带上默认值的时候会导致oracle去订正老的数据行的新增列。如果老数据非常多，表的并发访问高，很有可能导致大面积的阻塞等待以及产生大事务，甚至有可能导致undo耗尽。倘若回滚，还会因为回滚产生的并发会话导致load飙升。

解决方法：先不带not null不带默认值加上列，再更改列默认值，再批量订正老数据，然后再加上not null属性。

如果是大表，并且并发访问很高的表，则新增列不允许为NOT NULL，以简化后面变更步骤，降低风险！

l 新增字段导致依赖对象失效、sql游标失效问题。

表的DML并发很高的时候，如果表上面还有依赖对象，新增字段会导致依赖对象失效。默认访问这些依赖对象的会话（如DML会话，或者应用调存储过程等）会尝试去自动编译这个依赖对象（9i所有会话都会尝试去编译，10g以后只有一个会话去主动编译，其他等待），此时很可能会出现大面积的library cache pin。应用会话的连接时间会加长，进而导致出现后续应用报不能取得连接池错误。应用服务器load由此飙升。

表新增字段也会导致跟该表有关的SQL的游标失效，如果SQL的并发很高（查询SQL或者DML SQL），失效后SQL会重新解析，此时也可能会出现大量的library cache pin & library cache lock。

解决方法：选择在业务低峰期发布，同时在数据库级别开启trigger禁用客户端程序自动编译功能，字段加完后再禁用该trigger。 l 表的依赖对象是否要相应调整。

表上面的依赖对象如果有存储过程或触发器等，逻辑是否需要相应调整。

l 是否涉及到同步。

同步中的表需要两地都要变更。涉及到erosa的要更新一下数据字典。Erosa需要重启一下。 l 是否要通知其他关联的部门。如DW, ASC或CRM等等。

有些表很多部门都用，需要沟通约定时间一起变更。如果有同步方案，同步方案的变更也要考虑。

l 表结构变更后应用需要重启。

应用端缓存的表结构跟实际结构有冲突报错。

四、 操作流程

1. 准备工作

a) 该表的数据量以及大小，以及数据变更量（按日/时/分/秒等） b) 该表的并发访问数，以及频率最高的几种sql的访问方式 2. 执行过程

以表T1 加字段 col2为例。T1的数据量非常大，访问频率很高。 a) 在sysdba下开启trigger禁用客户端自动编译功能。（可选） Alter trigger SYS.ddl_trigger_for_database enable; b) 变更字段

以下加字段同编译失效对象连着执行。编译时先编译trigger再编译存储过程或package等 @conn zzzzzz/aaa

Alter table t1 add col2 varchar2(20); –如果需要默认值，加上默认值 Alter table t1 modify col2 default ‗Y‘; Alter trigger trg_t1_search compile; @conn retl/rrr

Alter trigger trg_t1_sync compile; @conn bopsretl/bbb

Alter trigger trg_t1_sync compile; @conn zzzzzz/aaa

Alter procedure sp_test compile;

另开一个窗口，在admin用户下查看当前失效对象 @dbcheck

c) 禁用ddl trigger

Alter trigger SYS.ddl_trigger_for_database disable; d) 老数据订正

–数据订正存储过程

Create or replace procedure sp_dml0214 As

Cursor c1 is select rowed rid, id, col2 from t1 where col2 is null; V_cnt number := 0; Begin

For rec_c1 in c1 loop

V_cnt := v_cnt + 1;

Update t1 set col2=‘Y‘ where rowid = rec_c1.rid and id=rec_c1.id;

If mod(v_cnt,500)=0 then Commit; End if; End loop; Commit; End; /

Exec sp_dml0214;

另开一个窗口，用@sqlinfo脚本查看订正进度

e) 订正完后加上NOT NULL属性(核心表不要做了)，风险和步骤详情参见文档：4.增加、删除唯一约束

Alter table t1 modify col2 not null;

f) 表涉及到同步后，再多个节点变更，erosa是否重启取决于erosa版本。 –更新erosa数据字典 ./getDict.sh –erosa重启 ./erctl stop ./erctl start 3. 验证方案

a) 验证sys下的trigger已经禁用

Select owner,trigger_name,status from dba_triggers where owner in (‗SYS‘) and trigger_name=upper(‘ ddl_trigger_for_database‘); b) 验证结构正确 Desc zzzzzz.t1

c) 验证无失效依赖对象 @dbcheck

d) 跟测试库比对。

五、 核心对象风险

核心对象风险指的是业务上重要的表，并且数据量很大或表大小很大或并发访问数很高时，变更的潜在风险。前面已经阐述。

六、 回退方案

1. 大表的新增字段不允许回滚。因为回滚即删掉字段，会导致锁表，持续时间很长进而导致一序列的严重问题。

2. 普通表的新增字段如果是发布失败，后续还会有二次发布，也不考虑回滚。或者只是将not null属性拿掉，下次发布再加上。

3. 普通表如果应用有足够的理由要求回滚，则回滚

07-赋权

一、 目的

明确常用赋权操作标准流程，以及赋权过程中可能产生的风险，最大限度避免赋权操作带来的系统故障。

二、 适用范围

l 对数据库对象的授权操作，数据库对象包括表、存储过程、同义词、视图和序列等。授权类型包括查询、增删改、执行。 l 对数据库用户的系统授权操作。

三、 风险评估

l 对数据库用户进行系统授权时，需要根据实际情况进行，避免因对用户授予过高的系统权限或角色，进而使该用户存在误操作引发数据库或应用故障的风险。 l 对于存储机密数据的表的授权，需要慎重。以免泄露机密数据。

l 对于涉及同步的数据库，需要分别在同步的两端数据库执行相同的授权操作。

l 10G之前版本，grant操作需要获得Exclusive级别的library cache lock/pin。其风险主要针对于procedure、function等，对table基本无影响。若procedure正在执行时，对其本身或者其依赖的procedure、function进行授权，将阻塞其他要执行此procedure或其依赖procedure、function的会话，直到授权前正在执行的procedure结束。

l 对数据库对象授权时，不会引起依赖对象失效，但会导致library cache中与授权对象有依赖关系的游标失效，进而产生硬解析。如果对象的依赖游标过多，或执行频率较高，可能会对系统造成较大的冲击，造成CPU繁忙，latch争用严重，最常引起的latch争用有 shared pool、library cache还会有library cache pin、cursor pin s:wait x等争用出现。如果争用比较严重，甚至可能导致数据库crash。为避免此类情况出现，对于新建对象，应尽可能的先把权

限授予给可能会使用到的用户；对于在使用的对象，应充分评估对象依赖游标的个数和执行次数，选择执行低峰进行操作。 l 对于grant any table，或者grant DBA/ EXP_FULL_DATABASE等涉及大量对象的系统授权操作，应该作为重大变更对待，此类操作的风险极大，务必在业务低峰期进行操作。

四、 操作流程

1. 准备工作

a) 确认此次授权是否属于正常的业务需要。

b) 若赋予的为系统权限，禁止使用with admin option选项。

c) 若赋予的为对象权限，请确认此对象在数据库中缓存的游标个数，以及每个游标在不同时段的执行频率，根据具体的情况选择合适的变更时间窗口进行授权。 d) 准备授权脚本。

e) 新建对象的授权需要走事件流程。

f) 在用对象的授权或涉及大量对象的系统授权需要走一般变更或重大变更流程。 2. 执行过程

a) 以赋权对象所在的用户登录数据库，SHOW USER检查是否连接到正确的schema。 b) 如果被依赖对象的执行频率很高，需要打开DDL TRIGGER. c) 执行赋权脚本。

d) 查看过程若无报错，退出当前登录。 3. 验证方案,以下列举两种验证方式：

使用被赋权用户登录：

i. 验证对象权限：select owner,grantee,table_name,privilege from user_tab_privs

where grantee=‘&USER_NAME‘ and table_name=‘&object_name‘;

ii. 验证系统权限：select username,privilege from user_sys_privs;

五、 核心对象风险

核心对象上的依赖sql往往较多，而且执行频率较高，授权操作会导致对象依赖的游标失效，进而导致硬解析风暴。应该尽量选择业务低峰期来进行核心表的赋权操作。

六、 回退方案

我们遭遇的授权操作的最大风险第一是导致的硬解析风暴，第二是授权操作涉及数据字典的修改，甚至可能会导致row cache lock的出现。对于硬解析风暴的风险，回退的方案不是revoke对象的权限，而是等待硬解析风暴过去。对于赋权操作引发的问题，要根据具体的情况而定。提前把方案一定要整理好，慎重选择变更的时间，避免出现问题。

08-修改字段长度

一、 目的

阐述表变更的风险及其步骤，降低对应用的影响和避免故障。

二、 适用范围

l 所有在使用的表修改字段长度，具体是number型和varchar2型，只允许范围扩大。

三、 风险评估

l 相关表的长度是否一并修改

当该表某个字段长度加长后，可能有关联的表的数据来自于该表，那么那个关联的表的相应字段也应该加长。这点由应用去评估。

该表上如果有物化视图，则物化视图的基表的对应的字段长度也要加长。

该表上如果有存储过程、触发器、package，里面的代码中跟该字段有关的变量如果声明的是具体的长度，则也要加长。正确的声明方式是col%type。

l 修改字段导致依赖对象失效、sql游标失效问题。 表的DML并发很高的时候，如果表上面还有依赖对象，修改字段长度会导致依赖对象失效。默认其他DML会话会尝试去自动编译这个依赖对象，此时很可能会出现大面积的library cache pin。应用会话的连接时间会加长，进而导致出现后续应用报不能取得连接池错误。应用服务器load由此飙升。

表修改字段长度也会导致跟该表有关的SQL的游标失效，如果SQL的并发很高（查询SQL或者DML SQL），失效后SQL会重新解析，此时也可能会出现大量的library cache pin & library cache lock。

解决方法：选择在业务低峰期发布，同时在数据库级别开启trigger禁用客户端程序自动编译功能，字段加完后再禁用该trigger。

l 表的依赖对象是否要相应调整。

表上面的依赖对象如果有存储过程或触发器等，逻辑是否需要相应调整。

l 是否涉及到同步。

同步中的表需要两地都要变更。涉及到erosa的要更新一下数据字典。Erosa是否需要重启取决于erosa版本。

l 是否要通知其他关联的部门。如DW, ASC或CRM等等。

有些表很多部门都用，需要沟通约定时间一起变更。如果有同步方案，同步方案的变更也要考虑。

四、 操作流程

1. 准备工作

a) 该表的数据量以及大小，以及数据变更量（按日/时/分/秒等） b) 该表的并发访问数，以及频率最高的几种sql的访问方式 2. 执行过程

以表T1 加字段 col2为例。T1的数据量非常大，访问频率很高。 a) 在sysdba下开启trigger禁用客户端自动编译功能。（可选）

Alter trigger SYS.ddl_trigger_for_database enable; b) 变更字段

以下加字段同编译失效对象连着执行。编译时先编译trigger再编译存储过程或package等 @conn zzzzzz/aaa

Alter table t1 modify col2 varchar2(50); Alter trigger trg_t1_search compile; @conn retl/rrr

Alter trigger trg_t1_sync compile; @conn bopsretl/bbb

Alter trigger trg_t1_sync compile; @conn zzzzzz/aaa

Alter procedure sp_test compile;

后面3个trigger的编译可以开三个窗口同时进行。 另开一个窗口，在admin用户下查看当前失效对象 @dbcheck

c) 禁用ddl trigger

Alter trigger SYS.ddl_trigger_for_database disable;

d) 涉及到同步的表，各个节点都变更，erosa重启取决于版本 –更新erosa数据字典 ./getDict.sh –erosa重启

./erctl stop ./erctl start 3. 验证方案

a) 验证sys下的trigger已经禁用

Select owner,trigger_name,status from dba_triggers where owner in (‗SYS‘) and trigger_name=upper(‗ ddl_trigger_for_database ‘); b) 验证结构正确 Desc alibaba1949.t1

c) 验证无失效依赖对象 @dbcheck

d) 跟测试库比对结构

五、 核心对象风险

核心对象风险指的是业务上重要的表，并且数据量很大或表大小很大或并发访问数很高时，变更的潜在风险。前面已经阐述。

09-改动统计信息

一、 目的

ORACLE优化器依据对象、系统的统计 信息来产生执行计划。因此如何收集对象、系统的统计信息尤其重要，本文档主要介绍收集对象统计信息的操作方法。某些情况下，如执行计划走错、表缺少关键字 段统计信息，需要我们手工的设置统计信息，因此也会涉及修改对象统计信息的内容。对于执行计划走错，通过修改统计信息来修正的情况，要对CBO算法有简单的了解，知道哪些统计信息涉及到cost计算的过程。可以参考lewis的CBO优化法则来了解cost的计算.本手册不会涉及这些内容。

二、 适用范围

l 新建表，表里初始化了大量的数据。

l 对于已经存在的表，表里数据量变化比较大。比如表删除了大量数据。需要重新收集统计信息。

l 由于表上统计信息不准确或缺失导致执行计划走错。 l 表统计信息过于陈旧，可能导致执行计划错误

三、 风险评估

l 统计信息的改变会涉及到表上所有SQL在下一次硬解析的时候用到，因此影响面广。在操作的时候，需要确认影响的范围，不要单纯为了某一个SQL的执行计划正确，而导致更多的SQL执行计划走错。

l 请仔细评估好，no_invalidate的设置问题，这个参数设置为true，表上依赖的SQL不会立马失效，即不会立刻采用表上新的统计信息。只有下一次硬解析的时候才会用新的统计信息来生成执行计划。绝大多数时候，我们这边采用的参数值是false.代表让表上依赖的游标立刻失效，在下一次解析的时候，能够立刻用上表上新的统计信息。

l no_invalidate在设置为false会导致在收集统计信息完成后，表上所有的sql重新解析，对于核心表以及一些依赖sql很多的表，要尽量放到业务低峰期去操作，否则可能遭遇硬解析的风暴，导致系统CPU繁忙，latch争用（shared pool latch ,library cache latch，library cache pin,cursor pin s:wait x）.如果遭遇这种情况，大多数时候，这种争用会随着硬解析完成而很快结束，但是也有可能会导致ORACLE CRASH。

l ORACLE优化器依赖准确健全的统计信息来产生优秀的执行计划，虽然收集统计信息理论上是为了让更精准的、更能反映目前数据的分布的统计信息产生出更优秀的执行计划，但是无论如何ORACLE无法保证这一点，有可能执行计划更优秀或者不变，有可能更糟糕了，相信随着ORACLE版本的不断提升，优化器的BUG会越来越少。

l ORACLE的dbms_stats不能单独收集列的统计信息，要了解到，如果收集了某一列的统计信息，表的统计信息会随着更新。 l 把estimate_percent设置的比较小，可以加快收集统计信息的时间，在不收集直方图的情况下，设置较小的值一般也不会有任何问题。可是如果表存在直方图，那么还是建议你根据情况把这个参数设置的大点。

l 目前生产环境都关闭了绑定变量窥探的功能，因此对于收集了直方图的列，需要确认传入的是文本变量。

四、 操作流程

几个重要收集参数的介绍以及使用规范：

1. no_invalidate 是否让表上的游标立即失效：自动定期执行（crontab）设置true，手动收集设置false。

2. force 是否对锁定统计信息的表收集统计信息 ：不指定，统一规范使用默认值false 不收集锁定表的统计信息，如果需要收集请提单给出原因。

3. degree 收集统计信息的并行度 ： 不指定，使用默认值1；如果为了加快收集时间，可以设置高的并行度，需要提单给出理由。

4. estimate_percent 采样百分比：一般设置成 0.5 ，可以让收集统计信息的时间缩短。这个值是个最小值，如果ORACLE觉得这个值小，会自动调大。采样的大小不要超过100M，采样的时间控制在1分钟以内。

5. method_opt 收集直方图的方法 ：分以下几种情况：

a) 执行计划走错：

1. 收集指定列的基本统计信息：for columns A size repeat,B size repeat 2. 收集指定列的直方图：for columns A size auto,B size auto

b) 统计信息全为空：收集所有列的基本信息，同时收集个别列的直方图： for columns size repeat, A size auto,B size auto

c) 定时收集统计信息：for all columns size repeat

6. cascade 设置成true，收集索引的统计信息。9I默认值是false,10G默认值是true. 7. 如果是分区表，需要指定partname参数。

更多参数的说明请参照我写的dbms_stats包参数介绍。文档位于：数据库管理—–à知识总结—-àdbms_stats包使用规范

1. 准备工作

a) 准备脚本，确认好收集对象的schema,table name，以及收集统计信息的参数设置等。参数的设置要依据规范。不同与规范的参数要提单给出理由。

b) 9I统计信息收集、修改前，请做好统计信息的备份。stattab为备份统计信息表。 begin

dbms_stats.export_table_stats(ownname=>‘xx‘,tabname=>‘xx‘,stattab=>‘stattab‘);

end;

/

备份表的创建方式如下：

begin

dbms_stats.CREATE_STAT_TABLE(OWNNAME=>‘xx‘,STATTAB=>‘stattab‘,TBLSPACE=>‘xx‘); end;

/

2. 执行过程

以收集统计信息对象所在的用户登录数据库，以下为参考。 a) 分析表的统计信息 begin

dbms_stats.gather_table_stats(ownname => ‗xx‘, tabname => ‗xx‘, no_invalidate => FALSE, estimate_percent => 0.5,

method_opt => ‗for all columns size repeat‘, cascade => true); end;

/

以上仅为举例，特别是直方图的设置要依据情况，直方图的收集方法请依据规范来。 b) 设置列的统计信息

begin

dbms_stats.set_column_stats( ownname => user, tabname => ‗table_name‗, colname => ‗column_name‗, no_invalidate => false, distcnt => 1000000, density =>1/100000); end;

/

说明：针对特定表的特定字段，如何获取准确的distinct 值： SELECT COUNT(DISTINCT column_name) FROM table_name; density统一设置为1/distinct值. c) 设置表的统计信息 begin

dbms_stats.set_table_stats( ownname => user, tabname => ‗table_name‘, numrows => 1000000, numblks => 66, no_invalidate => false, force => false); end;

/

说明：针对特定表，如何获取准确的 numrows: SELECT COUNT(*) FROM table_name; d) 修改索引的统计信息 begin

dbms_stats.set_index_stats(ownname => user, indname => ‗index_name‘, numrows => 1000, numlblks => 300, numdist => 600, clstfct => 400, no_invalidate => false); end;

/

clstfct的含义为聚簇因子。将群集因子改大，会增大该索引访问的cost；反之，则减小cost。 e) 删除表、索引、列的统计信息

begin

dbms_stats.delete_column_stats(ownname => user, tabname => ‗table_name‘, colname => ‗column_name‘, no_invalidate => false, force => false); end; /

begin

dbms_stats.delete_table_stats(ownname => user, tabname => ‗table_name‘, no_invalidate => false, force => false); end;

/

需要注意删除表的统计信息默认会把列、索引、表的都删除。 begin

dbms_stats.delete_index_stats(ownname => user, indname => ‗index_name‘, no_invalidate => false, force => false); end;

/

3. 验证方案

@tabstat脚本查看统计信息是否为最新，如果修改统计信息是为了验证执行计划，那么还需

要explain出问题的sql的执行计划，确认统计信息修改后，走了期望的执行计划。

五、 核心对象风险

数据库的核心对象上往往依赖SQL比较多，而且执行频率上也往往比较高。因此分析、修改核心表的统计信息要尽量放到业务低峰期去操作，1）以避免大量硬解析所导致的系统风险，2)分析统计信息也可能遭遇原有SQL执行计划走错的风险。

六、 回退方案

1. 10G之后回退统计信息，可以采取ORACLE dbms_stats提供的restore_table_stats来闪回统计信息 begin

dbms_stats.restore_table_stats(ownname => user, tabname => ‗table_name‘, as_of_timestamp => SYSTIMESTAMP-1, no_invalidate => FALSE); end;

/

代表把表的统计信息闪回到一天以前。这个闪回不仅会闪回表的统计信息，索引，列的统计信息都会闪回。 2. 9I回退统计信息 begin

dbms_stats.import_table_stats(ownname=>user,tabname=>‘table_name‘,stattab=>‘stattab‘); end; /

10-新建索引

一、 目的

明确新建索引操作的风险及标准流程，最大限度避免新建索引操作带来的故障。

二、 适用范围

l 项目预发布新建索引

l 项目正式发布新建索引 l l l l

改变sql执行计划新建索引

删除索引插入数据后的新建索引， 不包括Impdp导入时候的索引新建 分区表上的索引基本上使我们常用的local index 无特殊说明，本手册只是针对常规B-tree索引，以及我们常用到的Local Partitioned Indexes

和函数索引， 不涉及Bitmap类型,domain类型,临时表上的索引及其他复杂索引。

三、 命名规则

普通索引 ： 表名称_字段名_IND 函数索引： 表名称_字段名_FIND

四、 风险评估

l 登录到错误的schema下，导致索引建到错误的schema里，而应用无法访问，建议登陆到own schema 再创建索引。

l 没有指定索引表空间，导致建到了默认表空间，有可能在新建的时候空间不足，后续空间增长和维护困难。

l 对于未来增量较快的索引选择了一个空间规划不足的表空间，导致后续空间增长和维护困难。

l 为加快创建速度，并行创建索引完成后， 需要将索引的并行度重新改回1. 以免后来对该索引的使用开启不必要的并行， 对于小的索引， 不建议用并行创建，可能会浪费空间。 l 不允许设置nologging选项创建索引。 l 单个索引条目的大小（索引键值的定义长度总和）不能超过6398字节（db_block_size为8k）， 在建组合索引的时候需要注意到这一点。

l 新建索引时没有指定online， 结果阻塞表上的DML操作，影响应用。 l 新建大索引时，需要注意排序的空间， 保证临时表空间足够可用。

l 在9i以及之前的版本，新建索引将不会自动收集统计信息，需要指定compute statistics参数，否则索引建完后仍有可能不被CBO所采用，但是这个操作会对表的统计信息重新做了收集，也可能原来表是没有统计信息的，这个会带来了隐含的风险，如果条件允许的情况下，可在新建索引后，对整个表做下统计信息的收集，尽量保持一致。在9i以后的版本新建索引时， oracle修复了这个问题，自动收集索引的统计信息。

l 由于我们的系统都禁用了index skip scan, 所以新建索引尤其是复合索引， 要注意前导列的合理设置， 防止造成索引用不上的情况。 l 如果修改统计信息，则需要备份统计信息。

五、 操作流程

4.1 创建新表上的索引 （预授权变更）

4.1.1准备工作

a) 在项目SQL审核阶段，跟设计人员一起明确新索引的设计方案，包括建何种索引，在什么字段新建索引，如何设置前导列。具体设计原则本文不繁述。

b) 准备发布脚本时，检查表空间定义，检查表空间剩余空间，估算新建索引需要占用的空间，参考表空间自身负荷及新索引的预期负荷，为每个新建的索引选择合适的表空间，通常与新建的表分开存放，并在建索引语句中添加tablespace的配置。

c) 新建表上的索引操作，可与新建表同时操作，即与开发接口人一起商定好建表以及建索引操作的时间点。如果发生计划外的发布建索引需求，则要追究项目跟进的应用DBA沟通不力的责任。

d) 以目前的认知，在新建表上建操作本身不会对数据库造成风险，故操作的时间点可以放宽：在变更时间窗口内，均可以执行建索引操作。

e) 创建新表上的索引操作属于预授权变更，在做之前必须在ITIL中提交相应的变更申请。 4.1.2执行过程

a) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、system等用户建表。

b) 执行建索引脚本。同步库新建索引， 也需执行a) 和 b) 两个步骤。

c) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。

4.1.3验证方案

a) 常规检查：@dbcheck @ind

b) 通过检查对象定义过程检查索引是否与测试库一致： exec pkg_check.CompareObject(‘user‗,‘TABLE_NAME‗);

c) 查看执行计划，判断sql review中的sql执行计划是否正确。

d) 立即联系开发接口人进行应用测试，变更是否成功以应用测试结果为准。 4.2创建已有表的索引 （一般变更或重大变更） 4.1.1准备工作

a) 创建已有表的索引通常有两种情况， 一种为项目新上SQL需要在已有表上创建索引，则需在项目SQL审核阶段，跟设计人员一起明确索引的设计方案。另外一种是DBA自发的为优化sql或者解决数据库性能问题而需要添加索引，同样需要考虑建何种索引，在什么字段新建索引，如何设置前导列，以及新建索引预期效果和可能会影响的SQL范围，具体设计原则本文不繁述。

b) 准备新建索引脚本时，检查表空间定义，检查表空间剩余空间，估算新建索引需要占用的空间（具体的估算方法本手册不阐述），参考表空间自身负荷及新索引的预期负荷，为每个新建的索引选择合适的表空间，通常与新建的表分开存放，并在建索引语句中添加tablespace的配置。

c) 在已有表上新建索引操作会对数据库造成一定的风险，故操作的时间需要注意：由DBA发起的操作， 尽量安排在晚上十点后操作，新上项目发布新建操作可以与项目发布同时（重大变更除外）。 d) 在已有表上建索引操作属于一般变更或者重大变更，在做之前必须在ITIL中提交相应的变更申请。 4.1.2执行过程

a) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、system等用户建表。

b) 执行建索引脚本。同步库新建索引， 也需执行a) 和 b) 两个步骤。

c) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。

4.1.3验证方案

a) 常规检查：@dbcheck @ind

b) 通过检查对象定义过程检查索引是否与测试库一致：

exec pkg_check.CompareObject(‘user‗,‘TABLE_NAME‗);

c) 立即联系开发接口人进行应用测试，变更是否成功以应用测试结果为准。 d) Explain plan for 关注的SQL

六、 核心对象风险

1） 由于核心对象通常比较大， sql关联比较复杂，访问比较频繁， 新建index的变更很可能会超过十分钟，风险比较大，所以务必要在业务低峰期进行。所制定的方案需要召开cab会议，必须经过主管同意。

2） 在核心对象上新建索引，需要关注新索引影响的范围是单个sql还是会影响其他sql，应该事前在SQL全文索引中，检查并整理出可能影响到的sql，如果新建后导致其他sql执行计划发生了意想不到的变化， 需要考虑后续方案或者回退方案。

3） 大致估算新建索引大小，需要考虑用于排序的临时表空间是否足够，如果可能会不够的话，加大临时表空间。考虑索引所在的表空间是否足够， 是否预加空间。

4） 查看索引的统计信息是否和表的统计信息相一致， 特别是在9i中.原来表的统计信息是空的，新建索引时不允许加compute statistics参数。

5） 加大db_file_multiblock_read_count =128，尽量缩短创建时间。 6） 11g以前在创建索引的时候要确保长事务已经提交。

七、 回退方案

新建索引的过程存在一定风险，建成后也可能会导致单条或多条sql改变执行计划，直接或间接影响到数据库和应用， 必须准备好回退方案。 若索引已建成，但是没有达到预期效果 需要回滚。 1） 直接删除索引 (drop index详见操作手册12)

Drop index indexname ;

2) 设置索引不可用 (Oracle 11g以前)，索引更新停止，需要rebuild才能重新使用， 不推荐使用。(rebuild index 详见操作手册11)

Alter index indexname unusable;

3) 设置索引不可见（Oracle 11g开始），索引正常更新，但会被CBO忽略 .除非显式的设置OPTIMIZER_USE_INVISIBLE_INDEXES参数为true

Create / Alter index indexname invisible;

若create index online 长时间没有响应，系统仍可控的话， 不要轻易尝试cancel ,试着找出阻塞的原因， 如果kill后遇到数据字典不一致， 或者等待index (re)build online cleanup 以SYS登录 DECLARE RetVal BOOLEAN;

OBJECT_ID BINARY_INTEGER; WAIT_FOR_LOCK BINARY_INTEGER; BEGIN

OBJECT_ID := 608365; 索引的OBJECT_ID

WAIT_FOR_LOCK := NULL; RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (); COMMIT; END; /

11-重建索引

一、 目的

明确新建索引操作的风险及标准流程，最大限度避免新建索引操作带来的故障。

二、 适用范围

l 索引本身失效， 或者由于其他操作导致索引失效的情况 l 有索引unusable需要改为可用的情况

l 由于数据删除率过大， B-tree高度过高等导致的索引效率降低， 空间浪费的情况， 但是我们总是建议在需要的时候才去重建索引 l 移动索引至其他表空间的情况 l 无特殊说明，本手册只是针对常规B-tree索引，以及我们常用到的Local Partitioned Indexes和函数索引， 不涉及Bitmap类型和unique类型，临时表上的索引及其他复杂索引， 重建的方式为rebuild online。

三、 风险评估

l 没有指定重建索引tablespace name，导致建到了默认表空间，所在表空间不足，后续空间增长和维护困难。

l 所在表空间需要有重建后索引大小的空闲空间， 否者重建索引将会失败，需要预先加好空间。

l 若重建索引需要用到临时表空间，需确保临时表空间有重建后索引大小的空闲空间， 否者重建时的排序会失败。

l 为加快重建速度，并行重建索引完成后， 需要将索引的并行度重新改回1. 以免后来对该索引的使用开启不必要的并行。 l 重建索引时不设置nologging选项 l 重建索引时没有指定online， 结果阻塞表上的DML操作，影响应用， 但是在11G前，rebuild online 在开始和结束阶段还是会申请类型为4的TM锁，然后再做锁转化，如果当时有DML没有提交的的话， 还是会发生堵塞，所以在重建索引的时候，尽量避免并发的DML，设置online rebuild时， 在该表上将无法进行并行的DML操作。

l 在9i以及之前的版本，重建索引将不会自动收集统计信息，需要指定COMPUTE STATISTICS参数， 但是在收集索引统计信息的同时也会收集该列和表的统计信息。

l 分区索引重建时应该分别对每个分区进行重建， 在9i中加online选项的话默认会将分区索引的统计信息清除，可能引起执行计划出错。 l 移动lob的数据对象， 会自动将对lob对象所对应的lob类型的索引移动到同一个表空间， 需关注该表空间空余空间大小。

四、 操作流程

4.1准备工作

a) 首先要明确因何种原因重建索引，根据原因确定重建方案和时间，如果不是需要紧急处理的情况，重建索引应该安排在晚上十点后完成。

b) 准备重建索引脚本或命令，可用explain大致估算重建后索引需要占用的空间，检查目标表空间定义和剩余空间，检查临时表空间是否足够。

c) 检查数据库版本，确定重建索引的其他选项， 包括是否开启并行，是否收集统计信息等 d) 创建新表上的索引操作属于一般变更或重大变更，在做之前必须在ITIL中提交相应的变更申请。 4. 2执行过程

e) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、system等用户建表。

f) 执行重建索引脚本或命令。对于同步库， 则需根据重建原因判断是否在同步库也重建索引。

g) 索引重建过程中， 需要监控temp表空间的消耗，目标表空间的消耗， 以及通过v$session_longops 等其他视图查看重建的进度。

h) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至执行通过，最后退出当前登录。 4.1.3验证方案

i) 常规检查：@dbcheck @ind

j) 查看sql的执行计划，判断所关注的sql执行计划是否正确。

k) 联系开发接口人进行应用检查，变更是否成功以应用测试结果为准。 l) 如果有修改统计信息， 需要检查统计信息。

五、 核心对象风险

1） 由于核心对象通常比较大， sql关联比较复杂，访问比较频繁， 重建index的变更很可能会超过十分钟，风险比较大，所以务必要在业务低峰期进行。

2） 对于核心对象的索引重建， 事先需要跟应用沟通一下，所制定的方案需要在同版本的测试库上做完整的测试，确认下的方案需要经过cab会议烤炉，经过主管同意。

3） 在核心对象上重建索引，需要关注新索引影响的范围是单个sql还是会影响其他sql，应该事前在SQL全文索引中，检查并整理出可能影响到的sql，如果重建后导致其他sql执行计划发生了意想不到的变化， 需要考虑后续方案或者回退方案。

4） 在11G版本之前，重建索引前， 要确保索引所在表上的长事务sql已经结束。 5） 对核心大索引进行rebuild，可以在会话级加大db_file_multiblock_read_count以便加大单次读取的block的数量， 但是在linux上单次IO的上限是1M。

6） 查看索引的统计信息是否和表的统计信息相一致，再9i中，要注意到重建索引并收集索

引统计信息会引起的表和索引列统计信息的改变。,

7） 需要考虑用于排序的临时表空间是否足够，如果可能会不够的话， 建议创建一个大的临时表空间用来重建索引，重建完索引后在drop掉该表空间。考虑索引所在的表空间是否足够， 是否预加空间。

六、 回退方案

重建索引的过程存在一定风险，重建后也可能会导致单条或多条sql改变执行计划，直接或间接影响到数据库和应用， 必须准备好回退方案。

1）若在rebuild过程中失败了，在目标表空间创建的是temporary 类型的segment， 系统会自动清理， 对原来的索引并没有影响， 但为了确保Smon已经被唤醒并清理完毕， 再一次的重建最好在一个小时之后在进行。

2）若rebuild index online 长时间没有响应，系统仍可控的话， 不要轻易尝试cancel ,试着找出阻塞的原因， 如果cancel 或kill后遇到数据字典不一致(Bug 3805539)， 如果不着急可以等待pmon自己去过清理， 如果着急的话， 可以尝试手动清理清理（10GR2后）

Logon as sysdba DECLARE RetVal BOOLEAN;

OBJECT_ID BINARY_INTEGER; WAIT_FOR_LOCK BINARY_INTEGER; BEGIN

OBJECT_ID := 608365; 索引的OBJECT_ID WAIT_FOR_LOCK := NULL;

RetVal := SYS.DBMS_REPAIR.ONLINE_INDEX_CLEAN (); COMMIT; END; /

3）若rebuild index已经完全结束，则无法回滚到原先的索引， 对此引发的突发问题， 需要根据遇到的实际场景， 进行有效地决策。

12-删除索引

一、 目的

明确删除索引操作的风险及标准流程，最大限度避免删除索引操作带来的故障。

二、 适用范围

l 由于新建了索引，冗余的索引可以删除的情况。

l 存在的索引没有在被使用或者并没有加快查询，浪费空间和降低DML效率的情况。 l 由于SQL走错执行计划，通过删除索引来调整执行计划的情况。

l 在IMP导入或者大数据量修订的时候，不允许先删除索引之后再重建的方式， 而是先设置索引不可用。

l 由于删除表所引起的索引删除不在此范围内。

l 无特殊说明，本手册只是针对常规B-tree索引，以及我们常用到的Local Partitioned Indexes和函数索引， 不涉及Bitmap类型和domain类型的索引及其他复杂索引。

l 在11G中， 如果不是为了节约空间的考虑， 可以选择将index设置成不可见，索引正常更新，但会被CBO忽略，观察三天没有问题的话再删除。

三、 风险评估

l 登陆错了schema，没有经过检查就删除，导致该schema下的同名的索引被删除。 l 试图删除没有在使用的索引前， 没有monitor一段时间， 结果发现该索引还是有在被使用的， 导致改sql走错执行计划。

l 试图删除关联强制唯一或主键的索引或者尝试删除local index的一个索引分区会报错。 l 如果索引正在被使用，尝试删除索引的话因为申请不到类型为6的TM锁，直接抛出resource busy的错误

l 在IMP等导入数据的时候删除索引， 因索引缺失引发的其他sql走错执行计划的风险。 l 索引删除后shared_pool中的与这个表相关的sql都会被aged out，必须重新分析,这个带来了潜在的风险。

l 尝试删除系统维护的Lob字段将会抛错， 只能对对应的lob字段进行操作

四、 操作流程

4.1准备工作

a) 首先要明确因何种原因删除索引，根据原因确定删除方案和时间，如果不是需要紧急处理的情况，删除索引应该安排在晚上十点后完成。 b) 准备删除索引脚本或命令，如果是删除不会被用到的索引，需要确保没有sql引用该索引， 我们通常的方式是：

1） monitoring index usage 至少一个星期， 以索引所在的schema然后查看V$OBJECT_USAGE

2） 通过查看dba_hist_sql_plan，v$segment_statistics ，idb的全文索引， AWR，报告等方式确认。

c) 删除索引前必须准备好回滚方案， copy出索引的完整定义放置变更单中， 以便能及时回滚， 并且备份索引的统计信息。

d) 删除索引操作属于一般变更或重大变更，在做之前必须在ITIL中提交相应的变更申请。 4. 2执行过程

e) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、

system等用户建表。

f) 执行删除索引脚本或命令。对于同步库， 则需根据删除原因判断是否在同步库也删除索引。

g) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至执行通过，最后退出当前登录。 4.1.3验证方案

h) 常规检查：@dbcheck @ind

i) 整体的load

j) 查看关注的sql的执行计划，判断所关注的sql执行计划是否正确。

五、 核心对象风险

1） 由于核心对象通常比较大， sql关联比较复杂，访问比较频繁， 删除index的变更风险比较大，所以务必要在业务低峰期进行。

2） 对于核心对象的索引删除， 事先需要跟应用沟通一下，所制定的方案需要在同版本的测试库上做完整的测试，确认下的方案需要经过cab会议讨论，经过主管同意。

3） 在核心对象上删除索引，需要关注影响的范围是单个sql还是会影响其他sql，应该事前在SQL全文索引中，检查并整理出可能影响到的sql，如果删除后导致其他sql执行计划发生了意想不到的变化， 需要考虑后续方案或者回退方案。

六、 回退方案

删除索引的过程存在一定风险，重建后也可能会导致单条或多条sql改变执行计划，直接或间接影响到数据库和应用， 必须准备好回退方案。

1）因事先保存了索引创建的完整定义，回滚的第一选择是创建一个和原先一样的索引， 并且回滚统计信息。具体创建的方法参见《新建索引操作手册》

2）若按照原来的定义创建后问题还是没有解决，则无法回滚到原先的索引， 对此引发的突发问题， 需要根据遇到的实际场景， 进行有效地决策。

13-加分区

一、 目的

明确加分区操作的风险及标准流程，最大限度避免加分区操作带来的故障。

二、 适用范围

l 日常分区表维护。

l 主要考虑增加range,list分区（hash分区，不允许新加分区，在系统规划初始的时候充分考虑数据增长情况）。

l 不考虑子分区情况(目前无系统使用子分区)。

l 不适用分区情况，本文后面详细叙述为何不使用分区。

三、 风险评估

l 登录到错误的schema下，导致分区到错误的schema里，而应用无法访问。

l 忽略了TABLESPACE参数，导致分区建到了默认表空间，导致后续空间增长和维护困难。建议分区表的表空间和产品DBA确认沟通好。

l 对于未来增量较快的表选择了一个空间规划不足的表空间，导致后续空间增长和维护困难。

l 脚本末尾缺少分号，导致该分区没有被创建上，而执行DDL的过程又不会报错。 l 其他原因漏建了分区，导致应用访问错误。

l 同步库没有及时创建相应的分区，或者没有更新同步配置，导致同步及应用出问题。 l 加分区，可能导致SQL执行计划走错的风险。这个在国际站美国库上存在过。请务必选择在系统低峰期操作。

l 分区表的分区名需与应用DBA，产品DBA，应用团队沟通确认，是否有特殊命名规则依赖。

四、 操作流程

1. 准备工作

a) 在准备发布脚本时，检查分区类型，评估tablespace的剩余空间，选择合适的表空间，并在加分区的时候指定表空间名字。

b) 严禁使用Spilt 方式增加分区，理由如下： i. 若spilt的分区有数据，会导致local index为unusable；一般分区都是大表，后果不堪设想。 ii. 若spilt的分区有数据，会占用大量的临时段。举例：

1. 假设A类型的数据有200M，它会在原来的分区扩展200M的临时段，然后再把这200M切换到新的分区里去。

iii. 不要存在侥幸心理，若存在各种历史原因，请先修改表结构定义后，再加分区！ c) 在确定加分区类型之前，先确认分区类型，然后选择相应的action进行操作： 使用下面的命令确认所有者，表名，分区类型，默认表空间，间隔：

Select OWNER, table_name, PARTITIONING_TYPE,DEF_TABLESPACE_NAME,INTERVAL From Dba_Part_Tables where owner=:1 and table_name=:2; i. 如果是LIST分区：

若是list分区，先查看list分区的定义： apollo@CRMG>Select

high_value,tablespace_name

From

User_Tab_Partitions

Where

table_name=‘ORD_ORDER‘;

HIGH_VALUE TABLESPACE_NAME —————————— —————————– ‘save_temp‘ APOLLO_IND ‗closed‘ APOLLO_IND default APOLLO_IND 说明：

如果HIGH_VALUE有default，那么该表是无法再加分区，会报错： ORA-14323: cannot add partition when DEFAULT partition exists 如果确认没有default，那可以直接加分区了：

alter table table_name add partition partition_name values (‗分区条件‘) tablespace tablespace_name;

结论：list分区严禁给予default分区，否则无法添加其他值的分区。

ii. 如果是RANGE分区

Range分区我们主要用在时间上比较多，对range分区进行分类，有两种模式： apollo@CRMG>Select a.table_name,a.Interval a.partitioning_type=‘RANGE‘;

From

User_Part_Tables

a

Where

TABLE_NAME INTERVAL —————————— ——————————

WT2 NUMTOYMINTERVAL(1,‘MONTH‘)

WT

如果interval字段非空且是11G以后版本的数据库，那么该分区使用了自动加分区特性，不需要DBA进行任何操作，Oracle会自动加分区。

如果interval字段为空，则需要加分区，查看当前分区值：

Select partition_name,high_value From User_Tab_Partitions Where table_name=‘TABLE_NAME‘;

确认当前分区的high_value以后，即可增加分区。

d) 使用@dep脚本，检查分区表的对象依赖。确保在变更过程中，对象始终有效。 e) 若所加的上一个分区，有统计信息，先将统计信息导出，修改分区名字后，再导入。若分区内容有重大变化，请勿复制统计信息。 导出统计信息：

dbms_stats.export_table_stats(ownname => ‗user_name‘,stattab => ‘stattab‘,tabname => ‗table_name‘,partname => ‗partition_name‘); 修改统计信息：

Update stattab Set c2=‘partition_name‘ and c1=‘table_name‘; 导入统计信息：

dbms_stats.import_table_stats(ownname => ‗user_name‘,tabname => ‗table_name‘,stattab => ‘stattab‘,partname => ‗partition_name‘);

f) 若是核心表，加分区操作务必放在系统低峰时间进行，不得在高峰时段做此操作。有必要和应用负责人约定加分区时间，防止意外情况发生。其它非核心表，则可以在变更窗口内进行。

g) 加分区操作属于标准变更或重大变更（根据表的情况而定），在做之前必须在ITIL中提交相应的变更申请。 2. 执行过程

a) 开启DDL TRIGGER禁止远程编译。

b) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、system等用户加分区。

c) 加分区脚本必须一条条粘贴，只有确认上一条命令执行成功后，方可执行下一条命令。 d) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。 e) 关闭DDL TRIGGER开启远程编译。 3. 验证方案

a) 检查数据库中是否有失效对象：@dbcheck b) 检查所加分区是否添加成功：@size

c) 立即联系开发接口人进行应用测试，【加分区】变更是否成功以应用测试结果为准。 d) 检查分区的统计信息是否被导入成功。@tabstat e) 同步库若建表，也需要执行 a) ， b) 两个步骤。

五、 核心对象风险

选择在系统低峰执行，评估SQL执行计划走错的可能。

六、 回退方案

若分区表加错，需要根据分区类型，选择不同的回滚方案。主要步骤分为三步： 1. 确认所删分区是否有数据：

Select count(*) from table_name partition(partion_name); 2. 评估分区类型

a) List分区：确认无误后，可以删除。 b) Rang分区：确认无误后，可以删除。

c) 任何分区删除操作必须加上 update global Indexes，防止全局索引失效。 3. 索引维护

由于索引删除以后，global的索引的状态是unsable的。因此必须check索引状态： Select status from user_indexes where table_name=‘table_name‘;

14-发布存储过程、触发器、视图

一、 目的

明确发布存储过程、触发器、视图操作的风险及标准流程，最大限度避免发布存储过程、触发器、视图操作带来的故障。

二、 适用范围

l 创建存储过程、触发器、视图 l 删除存储过程、触发器、视图 l 修改存储过程、触发器、视图

三、 风险评估

l 登录到错误的schema下，导致代码建到错误的schema里，而应用无法访问。 l 漏发代码，导致应用无法访问。 l 提取了错误代码，导致业务逻辑错误。 l 代码编译不通过，导致脚本无法调用。 l 代码逻辑错误，导致数据不正确。

l 代码中有SQL性能差，引起数据库压力高。 l 没有备份旧的代码，导致无法及时回滚。

l 变更的对象有大量并发访问，变更时出现大量library cache pin/lock，导致数据库无法提供服务。

四、 操作流程

1. 准备工作

a) 熟悉要变更对象的代码逻辑，与应用人员沟通新发布脚本的具体功能，发布到哪个数据库环境，及变更对业务的影响。

b) 整理要发布的对象脚本，保存为一个文件，如果代码较多则保存为多个文件。 c) 如果是修改或删除存储过程或视图，到线上检查是否有依赖的对象，变更后依赖对象是否能编译通过，如果有依赖对象需要在变更方案中增加依赖对象的编译脚本，如果有依赖对象不能编译通过，应与应用人员反馈，并修改代码，线下调试通过。

d) 备份本次变更线上运行的对象代码，放在变更方案的回滚脚本中，如果备份脚本较多则单独保存到文件。线上可以用@source显示对象的定义脚本。 e) 如果是删除视图或存储过程，需与应用人员确认对象可以删除，并且从v$sql中检查对象是否还有调用，如果对象还有调用则不能删除。

f) 修改或删除对象需要分析对象的调用逻辑及时间点，如果对象运行的时间很长，则需要选择一个对象没有运行的时间点操作，以免编译锁等待。

g) 如果有其它数据库核心代码（并发访问量非常大）引用变更的对象，变更方案里需增加启停ddl_trigger (ddl_trigger_for_database)的过程。

2. 执行过程

a) 使用ssh工具连接到线上库，在admin环境下，用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、system做变更。

b) 如果有其它数据库核心代码（并发访问量非常大）引用变更的对象，先打开ddl_trigger。 c) set define off

d) 执行对象变更脚本。

e) 如果是新建存储过程或视图对象脚本编译出错，检查出错原因，修改确认再执行，如果

不能找到出错原因，或者是代码本身有逻辑错误，则联系应用人员进行修改，线下验证后再重新发布。

f) 如果是变更存储过程或视图对象脚本编译出错，先回滚代码，然后检查出错原因，修改确认再执行，如果不能找到出错原因，或者是代码本身有逻辑错误，则联系应用人员进行修改，线下验证后再重新发布。

g) 11g

h) 如果是在已有表上新增或修改触发器脚本编译出错，先回滚代码，然后检查出错原因，修改确认再执行，如果不能找到出错原因，或者是代码本身有逻辑错误，则联系应用人员进行修改，线下验证后再重新发布。

i) 对象编译通过后再编译变更依赖的对象。

j) 如果打开了ddl_trigger，变更验证完成后关闭ddl_trigger。 k) 所有对象发布完毕后，退出业务账户到admin环境。

3. 验证方案

a) 在admin下用@dbcheck脚本验证发布是否引起依赖对象失效，如果有对象失效，检查失效原因，重新编译失效对象，如果有依赖对象编译不能通过，则先回滚当前变更，再查找原因，重新制定变更方案。

b) 在admin下用pkg_check.CompareObject对比线上与线下库是否一致。

Execute pkg_check.CompareObject(‘schema_name’,’object_name’,’object_type’);

如果有线上与线下不一致，通知应用人员，检查不一致原因，直至线上与线下脚本一致。 c) 立即联系开发接口人进行应用测试，如果应用验证有问题，则与应用沟通是否需要回滚，变更是否成功以应用测试结果为准。对于定时执行的对象，应与应用跟踪定时执行的结果，确实变更是否成功。

五、 核心对象风险

变更的对象有大量访问，变更时出现大量library cache pin，导致数据库无法提供服务。

六、 回退方案

使用变更前备份的对象定义脚本回滚，对象定义的回滚脚本要放在数据库本机上，回滚时直接在sqlplus中用@文件名方法运行，最后检查失效对象。

15-启停触发器、启停job

一、 目的

明确启停触发器、启停job操作的风险及标准流程，最大限度避免操作带来的故障。

二、 适用范围

l 启停触发器 l 启停job

三、 风险评估

l 打开触发器代码编译不通过，导致表无法做DML操作。 l 线上触发器或job代码逻辑错误，导致数据错误。 l Job执行时间或调度错误，引起数据错误。 l 代码中有SQL性能差，引起数据库压力高。

四、 操作流程

1. 准备工作

a) 熟悉要变更的触发器或JOB的代码逻辑，与应用人员沟通触发器或JOB的具体功能，在哪个数据库环境，及变更对业务的影响。

b) 线上检查触发器或JOB的逻辑是否正常。

c) 编制变更方案，需要说明操作的数据库，使用的业务帐号，及变更脚本，CHECK脚本，回滚脚本。

启停触发器变更方案示例：

–running on db(usoint,hzoint)

Select owner,trigger_name,status from dba_triggers where trigger_name in (‗xxx1‘);

@conn zzzzzz/xxx Alter trigger xxx1 enable; Alter trigger xxx2 disable;

–check

@dbcheck

Select owner,trigger_name,status from dba_triggers where trigger_name in (‗xxx1‘);

–undo

Alter trigger xxx1 disable;

Alter trigger xxx2 enable;

启停job变更方案示例： –running on db(usoint,hzoint)

–查看当前数据库job列表及运行情况 @job

@conn zzzzzz/xxx

–关闭JOB

Exec dbms_job.broke(job_id,true); –打开JOB

Exec dbms_job.broke(job_id,false); –删除JOB

Exec dbms_job.remove(job_id); –创建JOB

variable jobno number;–用于保存submit返回的job编号 Exec sys.dbms_job.submit( job => jobno,–返回的job编号 what => ‗xxxxxx‘,–job运行的脚本

next_date => to_date(‗‘,‘YYYY-MM-DD HH24:MI:SS‘),–下次运行时间 interval => ‗xxxxx‘ –运行间隔 );

select :jobno from dual; –运行JOB

Exec dbms_job.run(job_id); 2. 执行过程

a) 使用ssh工具连接到线上库，在admin环境下，用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、system做变更。 b) 执行对象变更脚本。

c) 如果是打开trigger，trigger编译错误，则直接回滚，把trigger关闭，检查错误原因，线下验证后再重新发布。

d) 所有对象发布完毕后，退出业务账户到admin环境。 3. 验证方案

a) 在admin下用@dbcheck脚本验证发布是否引起依赖对象失效，如果有对象失效，检查失效原因，重新编译失效对象，如果有依赖对象编译不能通过，则先回滚当前变更，再查找原因，重新制定变更方案。

b) 如果是启停job，用@job脚本检查job的当前状态。

c) 立即联系开发接口人进行应用测试，如果应用验证有问题，则与应用沟通是否需要回滚，变更是否成功以应用测试结果为准。对于定时执行的对象，应与应用跟踪定时执行的结果，确实变更是否成功。

五、 核心对象风险

无

六、 回退方案

1 2 变更方案 Enable trigger Disable trigger 回滚方案 Disable trigger Enable trigger 3 4 5 6 Run job Job Broke=false Job Broke=true Submit job 根据job 逻辑编写数据回滚方案 Job Broke=true job Broke=false remove job Submit job 7 Remove job 检查失效对象。

16-创建dblink

一、 目的

明确创建dblink操作标准流程及其操作风险，最大限度避免创建dblink操作带来的风险。

二、 适用范围

l 适用于创建fixed user认证类型的database link。

三、 风险评估

l 在错误的schema创建了dblink或者创建了public类型的dblink，导致无关用户可以获取数据，产生数据泄漏的风险。

l 使用了错误的连接字符串，导致无法获取数据或者数据不正确。

l 两个数据库之间使用不同的字符集，创建后没有验证，导致查询结果乱码或插入乱码的数据。

l Dblink理论上不能被用于应现业务逻辑，以避免造成两数据库间产生大量的网络交互，甚至造成堵塞，影响其他业务的正常使用。

四、 操作流程

1. 准备工作

a) 确认dblink创建的用途，根据实际情况选择需要创建dblink的schema。

b) 确认$ORACLE_HOME/network/admin/tnsnames.ora文件中已包含创建dblink时所需的连接字符串，如果没有，请提单给产品DBA添加。 c) 确认连接字符串可通过tnsping测试。

d) 确认需要创建dblink的用户已被赋予create database link的权限。 e) 如果两个数据库之间的字符集不一致，需要确认是否要转码。

2. 执行过程

a) 以正确的用户身份登录，show user确认当前登录用户。原则上禁止使用sys、system用

户创建dblink。 b) 执行创建语句。

create database link dblink_name connect to &username identified by ―password‖ using ‗tnsnames‘;

password用双引号包起来，11G后区分密码的大小写。tnsnames用单引号包起来。 3. 验证方案

尝试使用新建的dblink desc远端表。如果正常显示，则表明dblink创建成功。 desc table_name@dblink_name

五、 核心对象风险

创建DB Link不会对数据库带来风险，但需要确认dblink的使用目的，以避免产生网络堵塞或远端数据库机密表、敏感数据的泄漏。

六、 回退方案

drop database link dblink_name;

17-01-MOVE表

一、 目的

明确MOVE TABLE操作的风险及标准流程，最大限度避免MOVE TABLE操作带来的故障。

二、 适用范围

l 优化操作中，为了降低表，分区，LOB的高水位 l 操作对象包括：普通表，分区，子分区，LOB字段 备注：表中不能包含LONG 字段

三、 风险评估

l MOVE 表期间，会阻塞所有关于此表的DML和DDL操作，且INDEX会失效 l MOVE 表完成后，表统计信息会失效，需要重新收集（9i）

l 如果表上存在TRANSACTION，则无法使用MOVE操作，会报resource busy l MOVE到其他表空间，保证表空间有足够的空闲空间

四、 操作流程

1. 准备工作

a) 和应用确认MOVE表的持续时间和期间无法操作表（包括查询）的风险，看是否能接受或者选择停相关应用模块

b) 事先整理完成MOVE TABLE和REBUILD INDEX的脚本 c) 确认原来表的大小@size，方便操作前后对比 d) 如果前后表空间不同，请确认空闲空间@tbs 2. 执行过程

a) alter table *** move tablespace *** [partition *** tablspace_name ***]

[lob(***) store as (tablespace ***)] parallel ?;

b) MOVE操作期间，查看数据库等待事件@showlong和锁情况@lock c) 重建索引，需要考虑是否要收集统计信息，详见【重建INDEX】 d) 查看失效对象@dbcheck 3. 验证方案

a) 常规检查：@dbcheck b) 大小确认：@size

c) 数据库是否正常：@active

五、 核心对象风险

不建议使用MOVE操作

六、 回退方案

无需回退

17-02-shrink表

一、 目的

明确SHRINK TABLE操作的风险及标准流程，最大限度避免SHRINK TABLE操作带来的故障。

二、 适用范围

l 优化操作中，为了优化表，分区，LOB的空间

l 操作对象包括：普通表，分区，子分区，LOB字段 l 只针对9i以上的数据库版本 备注：以下不支持shrink操作

Tables with a LONG column on-commit materialized views

Tables with function-based indexes/bitmap join indexes SECUREFILE LOBs Compressed tables

三、 风险评估

l ALTER TABLE *** ENABLE/DISABLE ROW MOVEMENT 会使依赖对象失效（10g） l 不建议直接对表进行shrink space，为了减少锁表时间，先进行shrink space compact 进行空间整理，但不调整HW，然后再对表进行shrink space（注意UNDO空间的消耗） l 基于表ROWID的物化视图，SHRINK完后需要REBUILD,不然原来的数据的DML操作，通过刷新无法同步

l 属于DDL，不会影响有关TABLE DML 的触发器

l shrink space 操作会阻塞DML和DDL，此时对表采用顺序读，时间根据表而定。

四、 操作流程

1. 准备工作

a) 确认shrink space表的持续时间和期间无法操作表（包括查询）的风险， b) 事先整理完成shrink 表的脚本和编译脚本（10g） c) 确认原来表的大小@size，方便操作前后对比 d) 明确不能使用shrink space CASCADE操作 2. 执行过程 （务必严格按照以下步骤） a) alter table *** enable row movement b) compile_dep_obj(only 10g)

c) alter table *** shrink space compact;

or alter table *** modify lob(***)(shrink space compact); or alter table *** modify partition *** shrink space compact ; or alter table *** modify partition *** lob(***)(shrink space compact); d) 查看UNDO消耗 @undo

e) alter table *** shrink space;

or alter table *** modify lob(***)(shrink space);

or alter table *** modify partition *** shrink space;

or alter table *** modify partition *** lob(***)(shrink space); f) alter table *** disable row movement

g) compile_dep_obj(only 10g)

h) shrink操作期间，查看数据库等待事件@active和锁情况@lock

i) 如果DB是定期收集统计信息的，建议使用REBUILD INDEX和并重新分析表，否则建议只使用SHRINK INDEX COMPACT,SHRINK INDEX操作 3. 验证方案

a) 常规检查：@dbcheck b) 大小确认：@size c) 数据库是否正常：@active

五、 核心对象风险

不建议使用shrink space操作

18-临时重跑任务

一、 目的

明确临时重跑任务操作的风险及标准流程，最大限度避免临时重跑任务操作带来的故障。

二、 适用范围

l 由于任务失败或项目要求需要重跑任务

l 由于业务需要，修改原有的任务，临时执行。

三、 风险评估

l 登录到错误的schema下，运行了其他schema下名字相同的procedure。 l 重新执行的时间点错误，可能会导致任务的逻辑错误和数据错误。 l 没有和开发确认任务逻辑，导致任务执行错误或数据错误。

四、 操作流程

1. 准备工作

a) 需要开发人员ITIL提单，主管审批。

b) 开发人员需提供任务的执行脚本，开发需要确认脚本是否能够执行以，同时明确执行的

风险点。

c) 开发人员提供执行的时间点，确认是否存在和执行时间有关的风险。

d) 执行前，开发和dba需要弄清楚任务的业务逻辑。如要修改业务逻辑，开发人员需要提供给dba经过严格测试后的procedure。DBA只负责执行，不对其中的任何逻辑负责。 e) 执行前检查是否有相同的任务在执行。 SELECT/*+ RULE */* FROMDBA_JOBS_RUNNING; （10.2前的版本加上以上的hint）

f) 任务的procedure需要在任何时候可以重复执行。 2. 执行过程

a) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。 b) 执行任务脚本，将脚本放在后台跑: nohup 脚本名.sh & ;

c) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。

3. 验证方案

a) 开发人员验证数据是否和任务的业务逻辑匹配。 b) 根据需求选择是否通知开发刷新缓存。

五、 核心对象风险

1. 定时任务对核心表的操作较少，基本是查询和更新操作。

2. 核心对象由于访问量高和数据量大，要考虑任务对核心表的更新和查询所带来的压力，执行时间尽可能避开高峰时间。

六、 回退方案

1. 执行前需准备好回退的脚本。

2. 回退时需得到开发的确认，并确认回退的时间点。

19-数据迁移工具之copy data

一、 目的

说明使用copy data进行数据迁移的前置条件、操作步骤，降低对对应用造成的影响及避免故障

二、 适用范围

l 开发查问题需要访问线上大量数据时，可以将线上数据拉到线下方便开发处理 l 开发在线上做数据订正时，需要从导入一些业务提供的关联数据 l 配合项目从线上拉数据做环境初始化 l 线上库之间的数据迁移不建议使用

三、 copy data简介

l 基本语法： COPY

{FROM

database

|

TO

database

|

FROM

database

TO

database}

{APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query

a) database：username/password@dbname

from database|to database可以任选一，缺少的一方默认使用当前sqlplus登录的数据库代替。 b) query：普通的select语句，多行时行尾使用连字符 ‘-‗ 换行标记 l 支持的数据类型：CHAR,DATE,LONG,NUMBER,VARCHAR2

l copy data把sqlplus作为数据流的管道，不需要中间文件，数据库名使用tns中的配置。query子句在数据源库上执行，结果直接发到目标库。 l 四种模式：

c) append：目标表不存在时先建表再插入数据，若存在直接加载数据。 d) create：直接建表，若目标表存在，报错退出。 e) insert：直接插入数据，若目标表不存在，报错退出。

f) replace：目标表存在时会先删除表，再重建及加载数据，一般很少用这个模式 注意：create、replace 后面没有table关键字，insert后面没有into和values关键字 l 参数：使用sqlplus参数，跟数据库无关。这些参数可以在sqlplus里，show 参数名来查看具体值。

a) arraysize：一个批次传输的数据量,默认值是100，可以在sqlplus里，使用show arraysize查看。

b) copycommit：定义多少批次自动commit，copycommit*arraysize=一次commit的数据量，这个乘积对事务大小有影响。默认是0，就是所有数据加载完毕才做一次commit，建议使用非0数字。

c) long：可以传输long数据的长度，默认值是20000.

四、 风险评估

l copy data对带精度的数字、压缩的大字段处理有缺陷，使用前需要了解表数据格式 l 使用copy data时，如果没有设置批量commit，会出现长事务，导致undo资源占用过大，影响其他应用

l copy data的query子句没有控制好执行计划，导致性能很差，对线上库产生影响。

五、 操作流程

1. 准备工作

a) 检查数据源的表结构，确认是否有带小数的number列、比较长的varchar2列。如果存在带小数的number列，为防止精度丢失，可以提前仿照源表结构在目标库上建一个空表。对于不确定源表中是否有带小数的数据时，也可以这样处理。

如果存在长字段的varchar2类型，跟开发确认该字段的数据应用是否压缩后存储的，如果非压缩可以使用copy data迁移数据。

b) 使用explain 检查query子句的执行计划，可以加hint调整。

c) 预先执行query子句估算数据量，以确定copycommit、arraysize的大小。并预估数据所需空间。

d) Create模式建表没法指定表空间，会自动在用户的默认表空间下，可以先在指定的表空间上建空表，再copy 数据

2. 执行过程

这里给出一个示例，把拉线上数据到开发库，方便开发查问题。 >show user

USER is ―ZZZZZ‖ >show arraysize arraysize 100 >show copycommit copycommit 0 >show long long 20000

>set copycommit 10

>copy to zzzzzz/password@oindeve create - > zzzzzz.ws_anti_punish_det_zzj0223 -

> using -

> select a.product_id product_id,a.SPAM_TYPE SPAM_TYPE, - > a.PUNISH_TYPE PUNISH_TYPE,a.DOWN_RANK DOWN_RANK, - > b.PUNISH_DETAIL PUNISH_DETAIL - > from zzzzzz.zzzz_zzzzz_zzzzz a, - > zzzzzz.yyyy_yyyy_yyyy b - > where a.SPAM_TYPE=‗repost‘ -

> and b.PUNISH_DETAIL like ‗%downRank%‘ - > and a.product_id=b.PRODUCT_ID;

Array fetch/bind size is 100. (arraysize is 100)

Will commit after every 10 array binds. (copycommit is 10)

Maximum long size is 20000. (long is 20000) 1 2

上面脚本是由开发提供查询sql，将结果数据拉到线下开发库上。

直接登录到admin用户下，默认当前库作为数据源。admin用户有所有表的查询权限，不需要登录到表的属主schema下。create创建的表在用户的默认表空间下。

copycommit设为10，arraysize使用默认值100。这个语句执行后可以到开发库上查新表ws_anti_punish_det_zzj0223的数据量，可以看到每次以1000递增。表明是每1000条数据插入commit一次。

copy data执行完毕后会提示处理了多少数据，可以到目标库上核对数据量。

下面这个例子是用其他库拉数据到当前库。 sys@OINDEV>@conn zhangzj Enter password:

zhangzj@OINDEV>set copycommit 10

zhangzj@OINDEV>copy from zhangzj/password@ointest create my_objs0223 using select * from dba_objects;

Array fetch/bind size is 100. (arraysize is 100)

Will commit after every 10 array binds. (copycommit is 10) Maximum long size is 20000. (long is 20000) Table MY_OBJS0223 created.

40310 rows selected from zhangzj@ointest. 40310 rows inserted into MY_OBJS0223.

40310 rows committed into MY_OBJS0223 at DEFAULT HOST connection.

有时开发根据业务方提供的一批数据清单做数据订正，清单一般是id、email一类的excel或者文本文件，数据量又比较大。可以让开发把清单导入线下库的临时表。然后把临时表拉到线上进行数据订正。 3. 验证方案

请开发核对数据量，重点核对带小数位的数字列、比较长的varchar2列等数据。

六、 核心对象风险

线上库之间的数据迁移不建议使用copy data。

七、 回退方案

使用copy data从线上拉数据时不需要回退，如果发现query子句的性能有问题，直接kill即可。从线下拉数据做数据订正时，需要先将数据放入中间表。除非是新表数据初始化。

19-数据迁移工具之exp/imp

一、 目的

说明使用exp/imp进行数据迁移的前置条件、操作步骤，降低对对应用造成的影响及避免故障

二、 适用范围

l 所有线上库

三、 风险评估

l exp导出数据时没有使用compress=n参数，导致所有数据被压缩在一个extent里，导入可能由于没有连续的blocks满足需要，导致imp失败。

l 进行跨版本迁移数据时，exp导出数据没有使用低版本软件，导致无法imp数据 l 有些os对文件大小有，exp数据时需要使用filesize参数来分割导出文件 l exp导出数据时没有正确估计dmp文件所需空间，导致主机磁盘满。

l imp导入数据时没有使用ignore=y参数，目标库上存在表的情况下数据无法导入 l imp导入大量数据时没有使用commit=y参数，导致事务太久，undo资源占用过大无法及时回收。

l 跨字符集的数据迁移，由于字符集不兼容导致数据迁移失败。

l imp跨schema进行数据迁移时，没有正确指定fromuser、touser，导致数据没有正确导入 l imp时由于目标库上表结构和源库不一致，导入数据失败。 l imp导入结构后，由于导出不完整，导致有对象失效。

l 导入表空间不存在或者空间不足，导致表创建失败或者数据导入失败，导致其他应用报错

四、 操作流程

1. 准备工作

a) 检查源数据库和目标库的版本、字符集，如果目标库版本低于源库，使用目标库的软件做导出。字符集不一致，不建议使用exp/imp迁移数据。

b) 检查目标库上表结构和源结构是否一致，如果不一致，先修复结构，保证一致。 c) user_segments里查出导出表所占的空间大小，检查os对文件大小的。如果表大小超出文件大小，exp导出时加上这两个参数：

filesize=小于文件的数值m,file=exp01.dmp,exp02.dmp,…多个dmp文件 d) 表比较多的情况下，建议用parfile。各个参数在parfile里写好。 tables=(tab1,tab2,tab3,..)

e）根据需要书写query子句，这个参数跟direct=y冲突

2. 执行过程

a) 如果目标表是已存在数据，跟应用确认后，可以先进行导出备份，以防后面需要回退。 这里给出一个示例，将数据从schema:zhangzj迁移到zzzzzz。

先根据需求编辑exp、imp的参数文件：‘-‗后面是参数说明，实际使用时去掉 cat exp_ws0208.par

userid=zhangzj/password@oindeve

direct=y –直接路径导出，加快导出速度

compress=n –避免数据全部压缩在一个数据块上 file=exp0208.dmp

log=exp0208.log

recordlength=65535 –写dmp文件时一次IO的大小，上限是65535，可以加快导出速度 tables=yz_ongimt_jingtzt

exp parfile=exp_ws0208.par –进行数据导出

cat imp_ws0208.par userid=zzzzzz/xxxxxx

commit=y –开启批量提交，避免长事务 ignore=y –如果目标表已经存在，只导入数据 fromuser=zhangzj

touser=zzzzzz

tables=yz_ongimt_jingtzt file=exp0208.dmp log=imp0208.log

buffer=100000 –大小控制导入速度的，设置过大会导致日志产生很快

imp parfile=imp_ws0208.par –进行数据导入

注意上面的fromuser和touser。如果将表导入到两个schema:zzzzzz,yyyyy 需要按照这种格式配置参数：

fromuser和touser一一对应，即使导出时只有一个schema. fromuser=zhangzj,zhangzj touser=zzzzzz,yyyyy 1 2

b) exp导出数据时，检查exp的日志，如果报错，一般是参数配置错误，参考官方文档调整参数。

c) imp导入数据过程，需要监控下数据库事务和日志产生速度。 d) 对导入的表收集统计信息。

3. 验证方案

对比exp、imp的日志，确认导出导入数据量是否一致。并在数据库上检查数据量。 比如上面的数据迁移，检查数据量跟日志显示是否一致。

select count(*) from zzzzzz.yz_ongimt_jingtzt;

跨schema或者数据库迁移数据时，除检查日志外，还需要检查源和目标的对象数据量、 是否有失效对象。

select object_type,count(*) from dba_objects where owner=‘源schema‘; select object_type,count(*) from dba_objects where owner=‘目标schema‘; select * from dba_objects where status<>‘VALID‘ and owner=‘ 目标schema‘;

五、 核心对象风险

由于核心表访问、变更频繁，不宜直接使用imp对核心表大量导入数据。

六、 回退方案

exp对应用无影响，不需要回退。

imp后可能数据有误，需要进行回退操作。

如果目标表本来就是空表，跟应用确认后，直接清空即可。

如果目标表原有数据，跟应用确认是否使用原有备份数据进行恢复。若需要，先exp备份当前数据，然后清空再导入前面的备份数据。

19-数据迁移工具之sqlldr

一、 目的

说明使用sqlldr进行数据迁移的前置条件、操作步骤，降低对对应用造成的影响及避免故障

二、 适用范围

l 所有线上库：数据迁移，数据初始化，以及应用几乎只读的表

三、 风险评估

使用sqlldr迁移数据时，普通方式加载数据本质是insert，对表的结构影响不大。使用需要rows控制事务大小。风险主要在使用direct方式加载上：

l 默认情况下会同时维护索引，如果设置参数SKIP_INDEX_MAINTENANCE=true，则不会维护索引。导致索引处于处于unusable状态。

l direct方式加载数据时，表上unique约束始终有效，unique约束的校验是在表加载数据完

毕后rebuild索引时完成的，如果索引build时发现有违反unique约束的数据，会导致该索引处于unusable状态。

l direct方式加载数据时，索引结构相当于rebuild，对临时空间需求比较大。如果空间不足，会导致索引处于unusable状态。

l 如果数据空间不足导致加载报错，也会导致索引处于unusable状态。

l 如果启用savepoint机制，sqlldr进程被中断失败时，会使索引处于unusable状态。

l direct方式加载数据时会自动disable表上的触发器和约束(check、外键)，在加载完成再次enable，由于enable时需要获得一个互斥的表锁，如果获得表锁失败，会导致无法成功enable。 l 表上的约束如果以enable novalidate方式启用时，direct方式加载数据时不会校验该约束，可能导致非法数据出现。

四、 操作流程

1. 准备工作

a) sqlldr不支持cluster表，及有global index的分区表。检查加载数据的目标表结构，尤其是触发器和约束，以及表上索引状态。如果有处于unusable的索引，需要评估是否rebuild生效，或者删除。

SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = ‗tablename‘ and status<>‗VALID‘;

SELECT INDEX_NAME, PARTITION_NAME, STATUS

FROM USER_IND_PARTITIONS WHERE STATUS <> ‗VALID‘;

b) 如果有enable novalidate方式启用的约束，需要提前评估是否再对该约束enable validate一次。

select constraint_name, status, validated

from user_constraints

where table_name = upper(‗tablename‘);

enable validate约束:该操作会锁表，需要在业务低峰期处理

alter tablename enable validate constraint constraint_name;

c) 估算表数据需要的空间和加载过程索引需要的临时空间大小。索引需要的临时空间计算方法：

1.3*(number_of_rows)*( 10 + sum_of_column_sizes + number_of_columns ) 一般情况下是1.3倍，安全起见可以使用2倍系数【参考】

d) 根据加载的文本文件内容编写controlfile，sqlldr命令使用的各个参数。SKIP_INDEX_MAINTENANCE 这个参数默认值是false，不建议修改。 e) 检查表上是否有活动事务 select *

from v$locked_object a, dba_object b where a.object_id = b.object_id and b.owner = ‘schema‘

and b.object_name = ‘tablename‘;

2. 执行过程

如果目标表是已存在数据，跟应用确认后，可以先进行exp导出备份，以防后面需 要回退。

这里给出一个示例，从算法提供的文本文件加载数据。

先根据需求编辑的control文件：‘-‗后面是参数说明，实际使用时去掉 more ld_ws.ctl load data

infile ‗totaldata_ae.txt‘

append into table X_table_name (

id sequence(200000000,1), –这里产生表id列的数据，自增长。2亿是起始值，1代表步长 GMT_CREATE ―sysdate‖, –对gmt_create使用sysdate填充，下同 GMT_MODIFIED ―sysdate‖,

PRODUCT_ID POSITION(1) CHAR TERMINATED BY ‗\\^‘ ―TO_NUMBER(:PRODUCT_ID)‖, –这里开始读取正式数据，注意position(1)，指定从文本各行的第一个字符开始。如果不指定product_id数据的起始位置，上面的gmt_create,gmt_modified列数据初始化会报错 group_ID TERMINATED BY ‗\\^‘, FINGER_PRINT TERMINATED BY ‗\\^‘, IMG_VERSION TERMINATED BY ‗\\^‘, IMG_URL TERMINATED BY ‗\\^‘, COMPANY_ID TERMINATED BY ‗\\^‘, OFFLINE_DATE TERMINATED BY ‗\\^‘

―to_number(:OFFLINE_DATE)/3600/24+to_date(‘1970-01-01 00:00:00′, ‗yyyy-mm-dd hh24:mi:ss‘)‖,–这里时间数字转成日期格式 grade TERMINATED BY ‗\‘ ―TO_NUMBER(:grade)‖ )

–加载数据

sqlldr userid=username/password@dbname direct=true control=ld_ws.ctl log=ld_ws.log bad=ld_ws.err 1

2

ld_ws.err保存不符合上面control文件格式的数据

3. 验证方案

a) 检查sqlldr的日志，确认是否报错，以及非法的数据。并在数据库上检查数据量和导入的数据量是否一致。

比如上面的数据迁移，检查数据量跟日志显示是否一致。

select count(*) from zzzzzz.x_table_name;

b) 检查是否有处于unusable状态的索引及索引分区，如果有，rebuild该索引。 SELECT INDEX_NAME, STATUS FROM USER_INDEXES WHERE TABLE_NAME = ‗tablename‘ and status<>‗VALID‘;

SELECT INDEX_NAME, PARTITION_NAME, STATUS

FROM USER_IND_PARTITIONS

WHERE STATUS <> ‗VALID‘;

c) 检查是否存在disable状态约束和触发器，如果存在手工enable select trigger_name, status from user_triggers where table_name = ‗tablename‘ and status <> ‗ENABLED‘;

select constraint_name, status from user_constraints where table_name = ‗tablename‘ and status <> ‗ENABLED‘;

五、 核心对象风险

由于核心表访问、变更频繁，不宜直接使用sqlldr对核心表大量导入数据。需要先导入一个同构的中间表，再把数据分批insert到正式表。

六、 回退方案

sqlldr加载后可能数据有误，需要进行回退操作。

如果目标表本来就是空表，跟应用确认后，直接清空即可。

如果目标表原有数据，跟应用确认是否使用原有备份数据进行恢复。

20-固定执行计划-baseline

一、 目的

在遭遇执行计划不稳定或者执行计划错误的情况下，通过baseline来固定SQL执行计划以确保执行计划稳定性、提高性能。baseline是oracle 11G提供的稳固sql执行计划的功能，是spm功能的一部分。本人更建议通过10G的sql profile来实现baseline的这些功能，因为baseline用起来稍微繁琐点，sql profile的使用规范请参照我写的其他文档。

二、 适用范围

l 执行计划走错，导致查询性能降低，数据库压力飙升 l 执行计划不稳定，有走错的风险。

l 执行计划错误，需要增加hint，应用来不及修正发布，临时用baseline固定。

l 数据库升级，可以在源库生成baseline，然后把baseline迁移至新库，这样能确保升级前后执行计划性能不变。详情参阅ORACLE 11G SPM部分。

三、 风险评估

l outline、sql profile、baseline这三种固定执行计划的方式都依赖于SQL的文本，如果SQL文本变了，就失去了固定的作用。因此对于并发量非常大、应用修改代码容易的SQL，应该确保通过添加hint的方式来矫正执行计划，以免通过baseline方式修正执行计划的SQL文本变化，导致baseline失效，查询计划走错，导致数据库压力飙升。

l 在做数据迁移过程中，如果原系统中存在已经建立过的baseline，请不要在数据迁移过程中忘记在新系统中安装他们。

l 需要注意，表对象被删除时，baseline并不会被删除，当然这个应该也不是很严重的问题。如果想要删除baseline，必须采取显式的用命令删除。

l baseline是依赖sql文本来进行匹配的，这意味着一份SQL计划基线可能同时被用于两张有相同名称但分属于不同schema下的表。

四、 操作流程

1）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER Argument Name Type In/Out Default?

—————————— ———————– —— ——–

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER IN DEFAULT FIXED VARCHAR2 IN DEFAULT ENABLED VARCHAR2 IN DEFAULT 2）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER Argument Name Type In/Out Default? —————————— ———————– —— ——–

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER IN DEFAULT SQL_TEXT CLOB IN

FIXED VARCHAR2 IN DEFAULT ENABLED VARCHAR2 IN DEFAULT 3）FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER Argument Name Type In/Out Default? —————————— ———————– —— ——–

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER IN DEFAULT SQL_HANDLE VARCHAR2 IN

FIXED VARCHAR2 IN DEFAULT ENABLED VARCHAR2 IN DEFAULT DBMS_SPM包里有三个同名的LOAD_PLANS_FROM_CURSOR_CACHE函数，作用各不同。第一个函数用于直接对特定sql_id、plan_hash_value的共享池对象创建baseline。一般

用于当前sql执行计划已经是正确的，只是为了通过baseline来进一步固定。也可以用于sql对应多个执行计划，只有一个执行计划是需要通过baseline来稳固的情况。这种情况可以根据plan_hash_value来确定期望的执行计划。 第二个函数一般用于修正执行计划，当然也能实现第一个函数的功能。修正执行计划的情况，sql_id,plan_hash_value要是添加过hint的sql的，不是原始sql的。sql_text是原始sql的。这个函数是我建议在修正执行计划的时候使用的，不推荐使用后面将要介绍的第三种。 第三个函数一般用于修正执行计划，也能够实现第一个函数的功能。修正执行计划的情况，sql_id,plan_hash_value要是添加过hint的sql的，不是原始sql的。SQL_HANDLE是依据原始sql创建的baseline的SQL_HANDLE。

上面三个函数的FIXED，ENABLED统一都设置成YES.FIXED为YES,代表我们创建的baseline禁止演化，演化的意思是，当ORACLE发现一个比目前执行计划更高效的执行计划时，自动创建一个不可接受状态的baseline.ENABLED的意思是，让baseline立即起效。 1. 准备工作

a) 建议以system用户来执行创建baseline，这样普通用户就不需要赋予administer sql management object权限了。

b) 通过HINT构造出产生正确执行计划的sql 文本

2. 执行过程

我们以如下查询为例。object_id列上存在索引。查询默认的执行计划走了 object_id列上的索引。 sql 文本 sql_id select count(*) from wxh_tbd where object_id=:a 85f05qy1aq0dr plan_hash_value 1501268522 我们可能对于这个查询计划的固化有两种需求： 1）想继续用走索引的执行计划，为确保执行计划不走错，通过baseline来固化执行计划。步骤如下： declare

l_pls number; begin

l_pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id=> ‗ bmrc3akp0v4uh ‗, plan_hash_value => 1501268522, enabled => ‗YES‘);

end; /

2)不想用走索引的执行计划，想让执行计划走全表扫描。可以通过如下方式操作： 步骤一：通过添加HINT，构造出需要的执行计划。 需要注意的是，这一步的意义是需要在共享池里产生出正确的执行计划，后面需要跟它做关联，需要确保这个执行计划不要被刷新出共享池。否则第二步的关联会无效。

select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=1; 查询v$sql获得这个sql的sql_id和plan_hash_value sql文本 sql_id select /*+ full(wxh_tbd) */count(*) from wxh_tbd where object_id=:a bmrc3akp0v4uh plan_hash_value 853361775 步骤二：依据原始sql文本与正确的执行计划做关联。关联前最好再执行一下，步骤一里添

加过HINT的sql语句，以免执行计划已经被刷出共享池。 declare

m_clob clob; begin

select sql_fulltext into m_clob

from v$sql

where sql_id = ‗9jnx2cjukjtu7′————–原始sql的 sql_id

and child_number = 0;—————–为了让sql只返回一行，也可以rownum=1代替 dbms_output.put_line(m_clob);

dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache( sql_id => ‗bmrc3akp0v4uh‘,————HINT SQL_ID

plan_hash_value => 853361775,——————-HINT PLAN_HASH_VALUE sql_text => m_clob,————————-原始SQL文本 fixed => ‗YES‘, ———————禁止演化baseline enabled => ‗YES‘)); end;

/

3. 验证方案

另开一个SESSION，确定已经用到了baseline。

explain plan for select count(*) from wxh_tbd where object_id=:a; select * from table(dbms_xplan.display); ————————————–

| Id | Operation | Name | ————————————–

| 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | |* 2 | TABLE ACCESS FULL| WXH_TBD | ————————————– Note

—–

- SQL plan baseline ―SYS_SQL_PLAN_41832bd9cca3d082″ used for this statement

执行计划的note部分显示已经用到了baseline ，执行计划也由索引改为了全表扫描。 （另外一种baseline常用的方式，步骤比较复杂。参照： http://space.itpub.net/22034023/viewspace-697568）

五、 核心对象风险

理论上baseline影响到的只是一条特定的SQL,因此相对风险比较低。对于核心表，请选择业务低峰期来进行创建baseline.

六、 回退方案

declare

l_pls number;

begin

l_pls := DBMS_SPM.DROP_SQL_PLAN_BASELINE( sql_handle => ‗SYS_SQL_e395be286a1fb3′,

plan_name => ‗ SYS_SQL_PLAN_41832bd9cca3d082′

); end; /

通过如上方式删除创建的baseline。需要查询dba_sql_plan_baselines来获取sql_handle和plan_name.

20-固定执行计划-outline

一、 目的

通过outline来固定SQL执行计划以确保执行计划稳定性以及提高性能。outline是oracle 9i提供的唯一的固化执行计划的方法。ORACLE 10G后，ORACLE更建议通过sql profile,baseline来固定执行计划。我们线上库用outline来固定执行计划的case还很少，在我看来outline有三个明显的缺点：1）即使使用了outline，还是可能观察到执行计划的改变。2）outline的系统级启用，需要设置参数use_stored_outlines，而这个参数不可以在初始化参数文件里设置，每次需要在数据库启动后，显式的进行设置，当然这个缺点可以通过系统触发器来进行规避。3）对于执行计划走错的SQL，通过outline修正执行计划，过程繁琐。

二、 适用范围

l 对于执行计划容易出错的SQL通过outline进行固化。 l SQL动态拼接，无法直接在原始SQL文本里添加HITN。

l 执行计划走错，数据库压力飙升，应用端不能及时发布修正。对于SQL执行次数非常高的SQL，如果要通过outline固定执行计划，只能作为临时方案，以免SQL文本变更，导致执行计划再次走错，数据库崩溃。

l ORACLE版本升级。通过在源库生成outline然后导入到新库，来保证升级前后执行计划的稳固。

三、 风险评估

l outline、sql profile、baseline这三种固定执行计划的方式都依赖与SQL的文本，如果SQL文本变了，就失去了固定的作用。因此，通过outline来固定执行计划，一般只能作为临时方案来应急，特别是执行频率非常高的SQL，最好通过HINT或者分析统计信息等手段解决。 l outline是9I所提供的固化执行计划的方式，它一个很大的缺点是，数据库启动后，需要显示的去设置use_stored_outlines参数值为true,才能启用已经创建的outline。而且这个参数use_stored_outlines不能在初始化参数文件里设置。

l outline是依赖sql文本进行匹配的，这意味着一份outline可能同时被用于两张有相同名称但分属于不同schema下的表，使用前需要确认是否有此类情况，如果存在，而且两个不同schema的SQL要走不同的执行计划，那么使用outline就可能有问题。解决的办法是，修改原始SQL文本使之不相同。

l 创建的outline的信息最终会保存在系统的outln用户下，Outln用户是一个非常重要的系统用户，其重要性跟sys，system一样。在任何情况下都不建议用户删除outln，否则会引起数据库错误。

l 需要注意，表对象被删除时，outline并不会被删除，这个应该也不是很严重的问题。如果想要删除outline，必须显式的用命令删除。

四、 操作流程

1. 准备工作

a) 检查创建outline的用户是否有相关权限。创建、删除、更改outline的系统权限分别是create any outline,drop any outline,alter any outline。

b) 为了编辑私有outline。需要创建工作表（10G以后默认工作表就是存在的，不需要做任何操作）。请确认工作表是否存在。通过如下方式创建工作表. CONNECT / AS SYSDBA

ALTER SESSION SET current_schema = system;

execute dbms_outln_edit.create_edit_tables CREATE OR REPLACE PUBLIC SYNONYM ol$ FOR system.ol$; CREATE OR REPLACE PUBLIC SYNONYM ol$hints FOR system.ol$hints; CREATE OR REPLACE PUBLIC SYNONYM ol$nodes FOR system.ol$nodes; GRANT SELECT,INSERT,UPDATE,DELETE ON system.ol$ TO public;

GRANT SELECT,INSERT,UPDATE,DELETE ON system.ol$hints TO public; GRANT SELECT,INSERT,UPDATE,DELETE ON system.ol$nodes TO public;

c) 确认数据库有相关的trigger来确保每次数据库启动后，会自动设置初始化参数use_stored_outlines，如果不设置初始化参数，所有之前创建的outline将不会生效。可以通过如下方式来创建系统级别的trigger.

Create the following trigger to be run on startup of the database: create or replace trigger enable_outlines_trig after startup on database begin

execute immediate(‘alter system set use_stored_outlines=true‘); end;

/

d) 获得需要固化SQL的文本 2. 执行过程

我们以如下查询为例。object_id列上存在索引。查询默认的执行计划走了 object_id列上的索引。

select * from wxh_tbd where object_id=:1

我们可能对于这个查询计划的固化有两种需求：

1）想继续用走索引的执行计划，为确保执行计划不走错，通过outline来固化执行计划。步骤如下：

create or replace outline pub_out on select * from wxh_tbd where object_id=:1; 这样整个固化的过程就完成了，非常的简单。

2)不想用走索引的执行计划，想让执行计划走全表扫描。可以通过如下方式操作： 步骤一：基于原始SQL文本创建一个outline。由于默认的执行计划是走索引的，因此后续我们需要修改这个outline,,让它走全表

create or replace outline pub_out on select * from wxh_tbd where object_id=:1; 步骤二：创建两个私有outline，后面我们要交换这两个outline的hint。

create or replace private outline pri_out_1 on select * from wxh_tbd where object_id=:1;

create or replace private outline pri_out_2 on select /*+ full(wxh_tbd) */ * from wxh_tbd where object_id=:1;

步骤三：交换两个outline的HITN update

ol$hints

set where

ol_name=decode(ol_name,‘PRI_OUT_1′,‘PRI_OUT_2′,‘PRI_OUT_2′,‘PRI_OUT_1′) ol_name in (‘PRI_OUT_1′,‘PRI_OUT_2′); commit;

步骤四：测试结果。

alter session set use_private_outlines=true;

execute dbms_outln_edit.refresh_private_outline(‘PRI_OUT_1′); execute dbms_outln_edit.refresh_private_outline(‘PRI_OUT_2′); explain plan for

select * from wxh_tbd where object_id=:1;

select * from table(dbms_xplan.display(null,null,‘outline‘));

—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————— | 0 | SELECT STATEMENT | | 577 | 99K| 140 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| WXH_TBD | 577 | 99K| 140 (2)| 00:00:02 | ————————————————————————— Note —–

- outline ―PRI_OUT_1″ used for this statement

在当前SESSION生效，看到执行计划Note部分用到了outline 步骤五：发布到公共outline

create or replace outline pub_out from private PRI_OUT_1;

3. 验证方案

另开一个SESSION，确定已经用到了OUTLINE explain plan for

select * from wxh_tbd where object_id=:1;

select * from table(dbms_xplan.display(null,null,‘outline‘)); PLAN_TABLE_OUTPUT

————————————————————————– Plan hash value: 3295978849

—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————

| 0 | SELECT STATEMENT | | 577 | 99K| 140 (2)| 00:00:02 | |* 1 | TABLE ACCESS FULL| WXH_TBD | 577 | 99K| 140 (2)| 00:00:02 | ————————————————————————— Note

—–

- outline ―PUB_OUT‖ used for this statement

执行计划的note部分显示已经用到了outline ，执行计划也由索引扫描改为了全表扫描。这一步需要注意，请确保参数use_stored_outlines已经正确设置为true.

五、 核心对象风险

理论上outline影响到的只是一条特定的SQL,因此相对风险比较低。对于核心对象，请尽量放到业务低峰期进行操作。对于执行频率非常高的SQL，不建议将使用outline来作为最终方案，以免SQL文本变更后，导致执行计划走错。

六、 回退方案

drop outline outline_name;

20-固定执行计划-sqlprofile

一、 目的

在遭遇执行计划不稳定或者执行计划错误的情况下，通过sql profile来固定SQL执行计划以确保执行计划稳定性以及提高性能。sql profile相对于outline,baseline使用简单方便。可以

实现outline,baseline的一切功能。使用@profile脚本，只需要提供sql_id,hint就可以起到固定执行计划的作用。

二、 适用范围

l 由于sql profile对于固化执行计划操作起来非常的迅速方便，因此对于执行计划突然走错，数据库压力飙升的情况，建议使用sql profile来修改固定执行计划。而outline和baseline相对来说，操作要复杂的多。sql profile是oracle 10G提供的一个功能。

l 对于执行计划走错，但是又来不及发布修改后的SQL，可以暂时通过sql profile 来修正执行计划，等应用发布后，再做删除。

l 需要注意的是，sql profile所接受的hint需要包含query block。因此dba需要具备query block的知识。

l sql profile是依赖sql文本进行匹配的，这意味着一份sql profile可能同时被用于两张有相同名称但分属于不同schema下的表，使用前需要确认。

三、 风险评估

l sql profile是针对sql 文本的，文本不区分大小写，空格，如果文本被修改，那么 sql profile就会不起作用。因此如果多个schema下有同样的sql语句，那么创建的sql profile将对他们都会生效。因此，请确认这种情况是否可以发生。如果不允许发生，那么需要确保不同schema下的sql文本不一样

l 在做数据迁移过程中，如果原系统中存在已经建立过的sql profile，请不要在数据迁移过程中忘记在新系统中安装他们。

l 需要注意，表对象被删除时，sql profile并不会被删除，当然这个应该也不是很严重的问题。如果想要删除sql profile，必须采取显式的删除。

四、 操作流程

1. 准备工作

a) 建议采用system用户来创建，修改，删除sql profile。创建、修改、删除sql profile的用户需要具有create any sql sprofile,alter any sql profile,drop any sql profile的权限。到oracle 11G,不再建议使用上面这3个系统权限，建议使用administer sql management object的系统权限。 b) 找出需要修改sql的sql 文本 2. 执行过程

我们以如下查询为例。object_id列上存在索引。查询默认的执行计划走了 object_i列上的索引。

select count(*) from wxh_tbd where object_id=:a 我们可能对于这个查询计划的固化有两种需求：

1）想继续用走索引的执行计划，为确保执行计划不走错，通过sql profile来固化执行计划。步骤如下：

declare

v_hints sys.sqlprof_attr;

begin

———-HINT部分

v_hints := sys.sqlprof_attr(‘IND(WXH_TBD@SEL$1 WT_OI_IND)‘); ———-SQL语句部分

dbms_sqltune.import_sql_profile(‘select count(*) from wxh_tbd where object_id=:a‘, v_hints,

—————-PROFILE 的名字 ‗SQLPROFILE_NAME3′, force_match => true); end;

/

2)不想用走索引的执行计划，想让执行计划走全表扫描。可以通过如下方式操作： declare

v_hints sys.sqlprof_attr;

begin

v_hints := sys.sqlprof_attr(‘full(wxh_tbd@sel$1)‘);———-HINT部分

dbms_sqltune.import_sql_profile(‘select count(*) from wxh_tbd where object_id=:a‘,———-SQL语句部分

v_hints,

‗SQLPROFILE_NAME3′,——————————–PROFILE 的名字 force_match => true);

end; /

除了上面介绍的方式来使用sql profile，你还可以通过我提供的脚本@profile来进行创建 sql profile。这个脚本的使用方法很简单。你只需要提供sql_id和hint就可以了。由于sql profile的hint需要指定query block,因此需要dba具备查询块的相关知识。@profile的脚本给你提供了原始执行计划的query block供你参考，绝大多数时候，这些已经能够提供给你需要的query block的name了。@profile脚本里的hint可以接受多个hint，之间用空格隔开就可以了。例如：

full(@‖SEL$1″ ―WXH_TBD‖@‖SEL$1″) full(@‖SEL$2″ ―T‖@‖SEL$2″) 3. 验证方案 explain plan for

select count(*) from wxh_tbd where object_id=:a;

Execution Plan

———————————————————- Plan hash value: 853361775

—————————————————————————

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————

| 0 | SELECT STATEMENT | | 1 | 13 | 144 (3)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS FULL| WXH_TBD | 198 | 2574 | 144 (3)| 00:00:01 |

————————————————————————– Note —–

- SQL profile ―SQLPROFILE_NAME3″ used for this statement 从note部分我们看到sql profile已经 起作用了

五、 核心对象风险

理论上sql profile影响到的只是一条特定的SQL,因此相对风险比较低.对于核心表对象上sql profile的创建最好放到业务低峰期操作。

六、 回退方案

exec dbms_sqltune.drop_sql_profile(‘ SQLPROFILE_NAME3′);

21-交换分区

一、 目的

明确交换分区操作的风险及标准流程，最大限度避免交换分区操作带来的故障。

二、 适用范围

l 定时脚本交换分区（目的是导入数据后，新老数据交换过程对应用透明）。

三、 风险评估

l 登录到错误的schema下，导致交换分区被误执行，而应用无法访问。

l 脚本末尾缺少分号，导致该表没有被创建上，而执行DDL的过程又不会报错。 l 交换分区之前确保两边表结构，索引必须一致。

l 缺少异常判断，导致数据异常，不完整等情况的发生。

l 目前erosa和eai同步程序不支持交换分区，因此不允许把交换分区表配置成同步表，从而导致应用无法同步。

四、 操作流程

1. 准备工作

a) 交换分区适用在数据同步，数据定时build的情况；其优点就是数据交换快，前台用户几乎无影响。

b) 明确分区表不支持erosa同步，不支持trigger等方式同步。

c) 和应用部门明确交换分区时间，避免在数据未准备好的情况下，进行分区交换。 d) 分区表，被分区表两边的结构，索引必须一致。

e) 关于交换分区表的索引:

i. 推荐local index，因为local类型的索引，会随着交换分区一同交换过去;

ii. 使用global index，交换分区后索引就无效了；除非交换的时候显式的加上‖ update global Indexes‖，但这样的话交换分区的速度就慢了；失去交换分区的意义，而且容易带来DBA手工执行忘记增加该语句的风险；

iii. 在交换分区的时候，务必加上‖including indexes‖选项，表示连索引一起交换过去。 f) 在交换分区的时候，统计信息是不会被交换的。因此若数据分布固定，建议采用定时分析机制或采用LOCK统计信息的方式。

g) 关于数据校验，我们在交换分区的时候，一般都采用‖Without Validation‖ 选项，因此在交换分区之前，请检查数据的有效性。

h) 在每次交换分区之前，必须显式的用SQL判断被交换表是否有数据，若无数据，不进行交换。

i) 交换分区的表明确不允许应用写操作。

j) 交换分区不得在高峰时段做此操作。有必要和应用负责人约定交换分区时间，防止意外情况发生。在变更窗口内进行即可。

k) 交换分区操作属于标准变更，在做之前必须在ITIL中提交相应的变更申请。 2. 执行过程

a) 脚本自动运行，判断各种异常情况。发送报警给对应DBA。 3. 验证方案

a) 脚本自动运行，无需验证。

五、 核心对象风险

核心对象不允许使用交换分区。

六、 回退方案

原则上只要不是数据异常情况，一般不需要进行回滚。若需要回滚，执行上一次的交换分区方案即可。

22-Truncate

一、 目的

明确truncate表操作的风险及标准流程，最大限度避免truncate表操作带来的故障。本文涉及到truncate table,truncate partition两个操作。

二、 适用范围

l 数据清理

l 定时truncate表数据清理

三、 风险评估

l l l l

登录到错误的schema下，导致truncate到错误的schema里，而应用无法访问。

脚本末尾缺少分号，导致该truncate没有被执行上，而执行DDL的过程又不会报错。 其他原因truncate错了表，导致应用访问错误。

目前同步程序均不支持truncate表，因此被同步环境需要手工truncate。

四、 操作流程

1. 准备工作

a) Truncate操作分为两类：定时truncate，数据清理。操作之前，请明确操作类型，然后选择合适的truncate操作。

b) 定时truncate：

i. 明确告知开发部门truncate操作是不可逆操作；

ii. 确认数据是定期build生成且是可重复操作；

iii. truncate操作，必须要有exception处理，防止诸如表上有事务等，truncate不成功的情况发生。进而影响整个脚本的执行； iv. 模板： Begin

Execute Immediate ‗truncate table table_name‘; Exception When Others Then –which error info you want to do? …….; End;

c) 数据清理

i. 在数据清理之前，明确如下事宜：

1. 知晓备库的延迟时间，针对所有版本数据库；

2. 确认主数据库是否开启闪回功能，针对10g以后的数据库；

3. 确认所做操作表，是否开启闪回数据归档功能，针对11g以后的数据库；

ii. 登录主库使用@size脚本，确认所truncate表的物理size，分区表则明确所truncate分区。 iii. 和应用部门协商所清理数据，是否需要备份。Truncate操作不推荐备份，因为都是临时数据或不重要数据。

1. 若需要备份：truncate表主要是为了节省空间，因此推荐使用EXP的方法导到本地，然后ZIP的方式处理。此外还可以使用create table .. as ..的方法进行备份； 2. 不需要备份，则直接处理。 d) Truncate语法说明： i. Truncate 表：

Truncate table table_name；

ii. Truncate 分区：

Alter Table table_name Truncate Partition partition_name;

e) Truncate操作，会把UNUSABLE的索引变成VALID。这点需要注意，目前规范中不允许把索引置为UNUSABLE。因此该风险理论上不存在，但需要注意。

f) Truncate操作，不需要维护相关依赖。

g) Truncate操作尽量安排在变更窗口执行，若是定时build数据清理，则结合应用处理时间及变更窗口，综合评估。

h) Truncate操作属于一般变更，在ITIL上提单即可。 2. 执行过程

a) 用应用账户登录数据库，SHOW USER检查是否连接到正确的schema。严禁使用sys、system等用户操作。

b) 请再次确认truncate所操作表名，分区名是否正确。

c) Truncate命令必须一条条粘贴，只有确认上一条命令执行成功后，方可执行下一条命令。 d) 查看过程若无报错，退出当前登录。若有报错，找出报错的地方，修改确认再执行，直至全部执行通过，最后退出当前登录。 3. 验证方案

a) 常规检查：@dbcheck

b) 检查表是否被truncate成功：@size

c) 同步库若建表，也需要执行 a) 和 b) 两个步骤。

五、 核心对象风险

核心对象严禁truncate操作，防止数据无法回滚。

六、 回退方案

Truncate操作回滚方案分为有备份和无备份两类： 有备份：直接从备份里恢复。

无备份：马上联系产品DBA，打开备库，然后从备库拖数据进行恢复。

23-删除表

一、 目的

说明删除表的前置条件、操作步骤，降低对对应用造成的影响及避免故障

二、 适用范围

l 所有生产数据库

三、 风险评估

l 应用评估不完善，仍有部分应用访问该表，删除后应用报错 l 跨库依赖关系没有整理，删除表后导致其他库上的应用报错 l DW有访问该表，删除表前没有通知dw，导致dw任务失败 l 重命名表时没有对erosa/otter配置进行更新，导致数据同步失败 l 误操作，登陆到其他schema下，导致删除其他表。 l 删除前未进行备份，后续无法及时进行有效的恢复

四、 操作流程

1. 准备工作

a) 整理依赖该表的对象（存储过程、视图、同义词等）及授权情况：

注意：9i库的同义词依赖在dba_dependencies没有记录，需要查dba_synonyms. 在其他库上查找访问该表所在库的dblink，如果存在dblink： 根据dblink名字查跨库依赖该表的对象：

select * from dba_dependencies where REFERENCED_LINK_NAME = ‗dblink名‗ and referenced_name=‗删除的表‗; 检查跨库的同义词依赖：

select * from dba_synonyms where DB_LINK = ‗dblink名‗ and table_name=‗删除的表‗;

根据dblink名字查使用dblink的物化视图，再检查物化视图的定义，确认是否依赖该表： select * from dba_mviews where MASTER_LINK=‗dblink名字‗;

b) 根据表和其依赖对象，配合开发整理应用，对应用进行改造、下线处理

c) 检查数据库上的定时任务，以确实是否有访问该表及其依赖对象。如果有，跟开发确认后停掉。

d) 在监控系统（全文索引、awr报告等）中对该表进行持续一周以上的监控，监控表对应的seq值变化情况，表gmt_create,gmt_modified等字段最大值变化情况

e) 步骤a、b实施后，如表有依赖对象，先将依赖对象进行提变更将表进行重命名，请开发

配合监控应用一周以上.

f) 通知平台的人，需要配合停起otter，修改表同步配置。 2. 执行过程

1 2

2.1 表重命名步骤：

a) 先备份依赖对象的脚本： 备份存储过程脚本： 使用@source取出脚本 视图备份：

select text from dba_views where owner=‘视图的owner‘ and view_name=‘视图名字‘; 物化视图备份：

select dba_mviews from dba_mviews where owner=‘物化视图的owner‘ and mview_name=‘物化视图名字‘; 同义词备份：

select ‗create synonym ‗ || owner || ‗.‘ || synonym_name || ‗ for ‗ || table_owner || ‗.‘ || table_name || ‗;‘ from dba_synonyms

where table_owner = ‘schema名字‗ and table_name = ‗表名‗;

b) 根据上面备份的脚本，修改其中表名为新名字，整理存储过程、视图、物化视图重建脚本，触发器、同义词重编译脚本

c) 从erosa配置中去掉该表并重启erosa，监控otter同步队列，等待该表的变更消费完毕 d) 重命名表：表名前加上schema以防止误操作

@conn schema/passwd

先删除步骤a 查到的依赖对象：

drop synonym 同义词; –这个需要到同义词的owner下执行 drop procedure 存储过程名; drop view 视图名;

drop materialized view 物化视图名; drop package body 包名; drop package包名;

drop trigger 触发器;

–有通过dblink依赖表的关联库上也需要执行以上操作 Alter table schema.表名 rename to 新表名; @dbcheck –检查失效对象

2.2 删除表步骤

a) 从erosa、otter配置中去掉该表的同步 b) 导出该表进行备份，导出文件保留两周 导数参数文件: cat exp.par direct=y grants=y indexes=y triggers=y constraint=y

tables=(表名)

log=exp_表名_日期.log file=exp_表名_日期.dmp 执行导出：

exp userid=schema/passwd parfile=exp.par

注：如果该表比较大，由于os，一个dmp文件可能无法存放所有数据，可以修改上面的参数文件： cat exp.par direct=y grants=y indexes=y triggers=y constraint=y tables=(新表名)

log=exp_表名_日期.log

filesize=xxxxm –这里指定文件大小，比os小点

file=exp_表名_日期_01.dmp, exp_表名_日期_02.dmp,…. –这里用多个文件 c) 删除表

drop table 新表名;

3. 验证方案

a) 在表所在库及其dblink关联库检查失效对象 b) 检查erosa、otter配置 c) 通知开发检查应用

五、 核心对象风险

对于核心表，关联的应用比其他表要多，风险主要在排查应用上，一般情况下核心表极少有删除需求。

六、 回退方案

上面删除表的过程中，回退可能出现在两个地方： 1. 表重命名的回退： a) 恢复原表名：

@conn schema/passwd

alter schema 新表名 rename to 旧表名表名;

重编译触发器、同义词等

使用备份脚本重建依赖对象（存储过程、视图、物化视图等） @dbcheck –检查失效对象 b) 根据应用需求配置数据同步 c) 通知开发检查应用 2. 表删除的回退： a) 导入备份数据：

cat imp.par: ignore=n grants=y indexes=y constraint=y commit=y tables=(表名)

log=imp_表名_日期.log file=exp_表名_日期.dmp

imp userid=schema/password parfile=imp.par b) 恢复原表名

@conn schema/passwd

alter table 新表名 rename to 旧表名; 重编译触发器等

使用备份脚本重建依赖对象（存储过程、视图、物化视图、同义词等） @dbcheck –检查失效对象 c) 根据应用需求配置数据同步 d) 通知开发检查应用

24- crontab(新增、修改、删除、打开、关闭)

一、 目的

明确crontab(新增、修改、删除、打开、关闭)操作的风险及标准流程，最大限度避免操作带来的故障。

二、 适用范围

l crontab(新增、修改、删除、打开、关闭)

三、 风险评估

l crontab调度的代码逻辑错误，导致运行数据错误。 l crontab执行时间或调度错误，导致运行数据错误。

l crontab调用的代码中有SQL性能差，导致系统压力高。

四、 操作流程

1. 准备工作

a) 熟悉要变更的crontab的代码逻辑，确认数据库环境，及变更对业务的影响。

b) 备份本次变更的crontab的代码，放在变更方案回滚脚本中。

c) 编制变更方案，准备好任务的执行脚本。需要说明操作的主机，crontab所在的系统用户，调度时间，运行脚本，CHECK脚本，回滚脚本。 Crontab变更方案示例： –running on yzstest1 –user：oracle

查看当前crontab 的配置 crontab –l

编辑当前crontab 的配置

crontab –e

crontab调度格式简介：

一个crontab里可以包括多条任务，每条任务的调度格式如下： * * * * * command * 分(0-59) * 小时(0-23) * 天(1-31) * 月(1-12) * 星期(0-6) 0表示星期天 crontab格式示例： * * * * * command 5 16 * * * command */10 * * * * command 0 8,13,19 * * * command 0 0 * * 0 command 每分钟调度一次 每天16时05分调度一次 每隔10分钟调度一次 每天8时、13时、19时0分各调度一次 每周日0时0分调度一次 command 操作系统命令 >/dev/null 2. 执行过程

a) 使用ssh工具连接到线上库，切换到本次变更crontab的用户下。

b) crontab -l显示当前变更的任务列表，核对要变更的任务是否对确。

c) 如果是新增任务或变更任务逻辑，则先手工在shell环境运行任务，验证任务逻辑是否正确。

d) crontab –e执行变更 3. 验证方案

a) 根据调度策略检查调度结果，一般通过任务生成的log及任务产生的数据或文件变更来检查。

b) 如果crontab不能及时检查的，应在定义好的调度时间进行验证。 c) 如果需要应用检查的，应通知应用相关人员进行验证。

五、 核心对象风险

无

六、 回退方案

a) 如果是新增任务则直接删除。

b) 如果是修改要删除任务则根据备份脚本恢复到原crontab配置。

25_在线重定义

一、 目的

明确在线重定义操作的风险及标准流程，最大限度避免在线重定义操作带来的故障。

二、 适用范围

l 修改，删除表字段

l 修改表的物理参数：如tablespace,pctfree等 l 重组数据从而减少碎片 l 修改分区结构（10gR2）

l 修改普通表为IOT表，或相反

注：以下不支持online redefinition操作

表上有物化视图（10g开始可以） 表上有物化视图日志（11g开始可以） IOT表有overflow table

使用BFILE,LONG 或用户自定义类型的表

三、 风险评估

l 需要重定义的表必须有PK,(BY ROWID 存在BUG)

l DBMS_REDEFINITION.START_REDEF_TABLE 此阶段不影响DML

l 周期的执行SYNC_INTERIM_TABLE从而保证物化视图日志不会过大的增长（重定义默认会为表创建物化视图日志）

l 在FINISH_REDEF_TABLE 完成之前，临时表上的触发器不会执行 l 完成后，原表相关的游标都会失效 l 统计信息无法拷贝（9i）

l FINISH_REDEF_TABLE 会被表上的DML阻塞

l 必须保证表空间足够

l 表上不能够存在长时间的事务，如果发生阻塞，建议KILL原事务

l 如果存在EROSA解析，需要重新解析原日志（）？

四、 操作流程

1. 准备工作

a) 确认是否可以进行重定义：DBMS_REDEFINITION.can_redef_table b) 确认表相关的触发器，约束(9i) 2. 执行过程

a) 创建空的临时表（和原表相似，修改必要的字段或物理属性） b) （可选）开启并行：

i. alter session force parallel dml parallel degree-of-parallelism; ii. alter session force parallel query parallel degree-of-parallelism; c) 开启重定义：

DBMS_REDEFINITION.START_REDEF_TABLE ( uname IN VARCHAR2, orig_table IN VARCHAR2, int_table IN VARCHAR2,

col_mapping IN VARCHAR2 := NULL, –指定列的对应关系 options_flag IN BINARY_INTEGER := 1, –默认根据PK

orderby_cols IN VARCHAR2 := NULL, –根据某字段排序ASC/DESC(10g) part_name IN VARCHAR2 := NULL); –分区(10g)

如果以上过程失败，可以调用DBMS_REDEFINITION.ABORT_REDEF_TABLE回滚 检查是否存在错误select * from dba_redefinition_errors;(10g) d) 在临时表上创建需要的触发器，索引，约束和权限（9I）

推荐：自己整理脚本，并使用以下存储过程自动拷贝权限和统计信息 （可选 >9i）使用自动COPY；特点：重定义完成后，依赖对象名称不变 DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (uname =>‗ALP‘,

orig_table =>‗TEST‘,

int_table =>‗TEST_ONLINE_DEF‘, copy_indexes =>0, –不自动拷贝 copy_triggers =>FALSE,

copy_constraints =>FALSE, copy_privileges =>TRUE,

ignore_errors =>TRUE, –遇到错误继续 num_errors =>num_errors, copy_statistics => TRUE， copy_mvlog => FALSE); –11g新 dbms_output.put_line(num_errors); END; /

检查是否存在错误select * from dba_redefinition_errors;(10g)

手动修正，如果还是需要保持依赖对象名称不变，可以通过运行DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT进行手工注册? e) 同步数据

DBMS_REDEFINITION.SYNC_INTERIM_TABLE f) 结束在线重定义

DBMS_REDEFINITION.FINISH_REDEF_TABLE 3. 验证方案

a) 常规检查：@dbcheck b) 数据库是否正常：@active

五、 核心对象风险

不建议使用

六、 回退方案

无需回退

26-物化视图

一、 目的

说明物化视图的常用功能和条件

二、 适用范围

l 所有线上库

三、 风险评估

l on commit模式创建的物化视图会基表上的分布式事务失败。

l 创建物化视图时查询里使用了*代替基表所有列，后续基表加字段时，物化视图需要重建 l 创建支持快速刷新的物化视图，后续遗忘刷新增量数据，导致物化视图log积压很多 l 多表join的物化视图增量刷新时有重复数据，谨慎使用

四、 操作流程

1. 准备工作

a) 检查用户权限，物化视图的所有者需要CREATE MATERIALIZED VIEW和create table权限，以及基表的查询权限。

如果创建支持on commit刷新的物化视图，需要对基表有ON COMMIT REFRESH权限。 如果创建支持query rewrite的物化视图，需要对基表有QUERY REWRITE权限。 b) 检查用户的表空间配额(quota)，物化视图的数据需要存储空间。 c) 如果基表跟物化视图不在一个库上，需要创建dblink。

d) 准备物化视图的查询sql，根据需要调整sql结构，保证执行计划正常。 e) 如果创建支持快速刷新的物化视图，需要保证：

l 物化视图不能包含对不重复表达式的引用，如SYSDATE和ROWNUM l 物化视图不能包含对LONG和LONG RAW数据类型的引用

l 基表必须有主键

l FROM语句列表中的所有表必须建立基于ROWID类型的物化视图日志

l 如果物化视图基表是通过dblink来访问的，查询子句中多表的连接条件必须是主键。 2. 执行过程

a) 创建物化视图日志：（创建非快速刷新的物化视图不需要此步骤）

物化视图的关键点在with子句部分，下面oracle文档上with子句的语法结构：

object id：仅当基表是对象类型时，with 子句可以此项，一般极少使用。

primary key：默认情况下，主键是包含在物化视图日志里，可以不指定此项。

rowid：指定时，物化视图日志里会记录rowid信息。

sequence：指定时，物化视图日志里多一列sequence$$，给每个操作一个SEQUENCE号，从而保证刷新时按照顺序进行刷新。

column：指定哪些列的数据存放在物化视图日志里，注意这些列不能是主键列。 new_value_clause：有两个选项。

默认是excluding new values，即物化视图日志里不记录数据变更时的新值 including new value：即物化视图日志里记录数据变更前后的新旧值，即同 一个主键id会对应两条日志记录。 下面是几个创建物化视图的示例：

CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID; CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, SEQUENCE(amount_sold, time_id, prod_id)INCLUDNG NEW VALUES; CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id); b) 创建物化视图：

创建物化视图的重点在刷新方式上，刷新方式的语法参照下面：

fast：使用快速刷新，需要先做步骤一，这种刷新方式使用物化视图日志做增量，刷 时间短。

complete：每次都是全量刷新，全量刷新时先truncate物化视图现有数据，重新 从基表生成数据，刷新时间比较长，对应用影响大，一般应用有实时要求的 不能采用这种方式。

force：这种刷新方式会先尝试fast刷新，如果不支持fast，则用complete方式数据， 不指定时，默认是这个。

on commit：物化视图的基表数据发生修改时实时刷新物化视图，这种方式对应用 事务稍有影响，一般不建议使用。

on demand：根据需要刷新，一般采用这个方式，用job或者crontab或者应用程序主动调用dbms_mview.refresh方法来刷新数据。不指定的情况下，默认是这个。

build immediate：创建物化视图的同时做一次全量刷新，一般采用这种方式。不指定时默认是这个。

build deferred：创建物化视图时不生成数据，后续第一次刷新时进行全量刷新。不指定时默认是这个。

enable/disable query rewrite：是否支持查询重写，如果是enable，oracle优化器会对一些sql使用该物化视图进行优化，这样来减少查询时间。 下面是几个创建物化视图的示例： CREATE MATERIALIZED VIEW sales_mv BUILD IMMEDIATE

REFRESH FAST ON DEMAND AS SELECT t.calendar_year, p.prod_id, SUM(s.amount_sold) AS sum_sales FROM times t, products p, sales s

WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id GROUP BY t.calendar_year, p.prod_id;

CREATE MATERIALIZED VIEW sales_by_month_by_state TABLESPACE example PARALLEL 4

BUILD IMMEDIATE REFRESH COMPLETE

ENABLE QUERY REWRITE

AS SELECT t.calendar_month_desc, c.cust_state_province, SUM(s.amount_sold) AS sum_sales

FROM times t, sales s, customers c

WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id

GROUP BY t.calendar_month_desc, c.cust_state_province;

下面这个是基于dblink的物化视图，查询子句中连接条件不是主键，因此不支持fast refresh。

CREATE MATERIALIZED VIEW TPCRM_LEADS_INFO BUILD IMMEDIATE REFRESH COMPLETE SELECT o.customer_id as id, o.gmt_create, o.gmt_modified, o.owner_1, c.country,

c.member_id

FROM caesar.caesar_customer_ggs@crmg c, caesar.caesar_opportunity@crmg o WHERE c.is_deleted = ‗n‘ AND o.is_deleted = ‗n‘

AND c.cus_id = o.customer_id;

dbms_mview.explain_mview：如果物化视图创建时报错，可以使用该方法检查报 错原因。下面是个示例，来源于杨铤锟的博客 SQL> alter table fact drop constraint fk_fact_aid;

表已更改。

SQL> alter table dim_a drop primary key;

表已更改。

SQL> create materialized view mv_fact refresh fast on commit as 2 select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 and f.bid = b.id;

from fact f, dim_a a, dim_b b *

ERROR 位于第 4 行:

ORA-12052: 无法快速刷新实体化视图 YANGTK.MV_FACT

SQL> begin

2 dbms_mview.explain_mview(‘select f.rowid f_rowid, a.rowid a_rowid, b.rowid b_rowid, f.id, 3 a.name a_name, b.name b_name, num 4 from fact f, dim_a a, dim_b b 5 where f.aid = a.id(+) 6 and f.bid = b.id‘); 7 end;

8 /

PL/SQL 过程已成功完成。

SQL> select msgtxt from mv_capabilities_table where capability_name = ‗REFRESH_FAST_AFTER_INSERT‘;

MSGTXT

———————————————————- 在内部表的联接列上没有唯一性约束条件

SQL> alter table dim_a add primary key (id);

表已更改。

SQL> alter table fact add constraint fk_fact_aid foreign key (aid) references dim_a(id);

表已更改。 3. 验证方案：

a) 检查物化视图刷新机制是否正常： －－快速刷新指定的物化视图

execute dbms_mview.refresh(‗物化视图名字‗,‗f‘); －－全量刷新指定的物化视图

execute dbms_mview.refresh(‗物化视图名字‗,‗c‘);

b) 对物化视图的基表做数据修改，然后使用物化视图的查询子句创建一个临时表。 create table tmp_物化视图名 as 物化视图的查询子句。

然后分别按照上面方法手工刷新物化视图，刷新后核对物化视图和临时表的数据量是否一致。

五、 核心对象风险

物化视图可以用来对核心表进行数据迁移，以及支持核心表的数据抽取。

由于在数据量比较大的情况下，全量刷新耗时比较久，核心表上物化视图尽量使用fast refresh模式。

核心表访问量大，数据变更频繁，其上的物化视图不能使用on commit模式创建。 创建跨dblink的且多表join的物化视图时，如果join条件不是主键，不支持fast refresh，尽量不要在核心表创建这种物化视图。

六、 回退方案

创建物化视图失败时：

如果基表上第一次创建物化视图，使用包dbms_mview.explain_mview检查创建失败原因，如果是物化视图日志不符合要求，删除物化视图日志即可。

如果不确定基表上是否有其他物化视图，检查基表上是否有其他物化视图，如果没有，直接删除物化视日志。

如果存在，需要评估对其他物化视图的影响。 检查基表上依赖的物化视图：

select a.owner, a.name, a.mview_site

from dba_registered_mviews a, dba_base_table_mviews b where a.mview_id = b.mview_id and b.master = ‗基表名‗;