Oracle FAQ???

最新推荐文章于 2024-09-20 11:12:39 发布

SAPLIFE

最新推荐文章于 2024-09-20 11:12:39 发布

阅读量1.5k

点赞数

分类专栏： Oracle &amp; SQL Server 文章标签： oracle table sql 数据库 library database

Oracle & SQL Server 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

FAQ 第二版

一、 SQL&PL SQL

1. 怎么样大批量 的更新数据而不影响正常业务

2. 怎么对 IN 子查询使用绑定变量

3. 并发容易出现的问题与并发控制

4. 怎么使用 object 与 record 类型返回表类型数据

5. 怎么样在业务繁忙时期正确的创建表约束

二、数据库的管理

1. 理解 Oracle 的 Rowid 含义

2. 怎么样管理 Oracle 的内存

3. 怎么样使用物化视图来同步数据

4. 怎么样在线创建索引或者重构索引 / 表

5.怎么样编译失效对象

6. 怎么样在管理自动 undo 表空间

7. 怎么样管理临时表空间与临时文件

8. 怎么样管理分区表与分区索引

9. 怎么样管理 LOB 字段

10.怎么样解除分布式事务的挂起

11. 怎么样管理表空间与数据文件

12. 怎么样管理联机日志文件

三、备份与恢复

1. 数据文件丢失或者数据文件错误的处理方法

2. 当失去媒体管理层，怎么样删除磁带备份

3. 怎么样找回被意外删除的数据

4. 如何在裸设备与文件系统间拷贝数据文件

四、性能调整

1. 怎么样安装与使用 statpacks

2. 如何优化 WEB 分页检索的语句

一、 SQL&PL SQL
1. 怎么样大批量的更新数据而不影响正常业务

1.1 、化整为零
一般情况下，如果需要对一个表进行大批量的更新的时候，由于涉及到的记录数很多，所以可能需要花费的时间也就很长，这种情况下，还采用一个单独的 update 语句来更新的话，就会造成长时间的加锁，影响到业务。
简单的一个例子，如要更新 im_user 表中的非空 ID 为用户表 bmw_users 中的 ID ，关联字段为 im_user.login_id=bmw_users.nick ，语句可以这样写

代码 :

update im_user i set i . id =( select id from bmw_users u

where i . login_id = u . nick )

where i . id is not null ;

这个语句可以更新到几百万记录，当然，耗费时间可能需要 1 小时以上，对于 im_user 这样被频繁更新的表来说，肯定是不现实的，所以，该语句可以改写为如下的 PL/SQL 块。

代码 :

declare

row_num number := 0 ;

begin

for c_usr in ( select login_id from im_user t where id is null ) loop

update im_user i set i . id =

( select id from bmw_users u where i . login_id = u . nick )

where login_id = c_usr . login_id ;

row_num := row_num + 1 ;

if mod ( row_num , 100 ) = 0 then

commit ;

end if;

end loop ;

commit ;

end ;

这样的话，因为每更新 100 条就提交 1 次，对表的影响相对是很小的，而且，如果是一个语句，如果中途执行失败，将导致回滚，同样要耗费很长时间，但是这种情况下，因为是一边执行一边提交，基本可以分很多次来操作，之间不会有影响。

1.2 、巧用临时表
很多情况下，需要更新的数据是根据很多条件判断出来的，查询很慢，但是更新的数据本身不多，比较快，这个时候，就可以考虑用临时表，先把需要更新的数据（包括主键）放入到临时表，然后根据主键更新，可能一个 UPDATE 语句就可以解决问题。
如支付宝迁移时，更新认证表数据：
先创建临时表

代码 :

create table bmw_idauth_db1_20050704 as

select a . id , b . idauth_passdate from bmw_users a , bmw_idauth b

where a . nick = b . nick

and b . status = 'SUCCESS'

and b . idauth_passdate >= to_date ( '20050501' , 'yyyymmdd' );

create table account_db1_20050704 as

select b . account_no , a . idauth_passdate

from bmw_idauth_db1_20050704 a , bmw_payment_account b

where a . id = b . user_id

and b . enabled_status = '1' ;

然后根据临时表来更新，因为记录数本身只在查询获得数据比较慢，而这里更新就很快了。

代码 :

UPDATE ( SELECT a . idauth_passdate ,

b . id_auth_date ,

b . is_id_auth

FROM account_db1_20050704 a , beyond_credit_info b

WHERE a . account_no = b . user_id || '0156' ) x

SET x . id_auth_date = x . idauth_passdate ,

x . is_id_auth = '1' ;

另外一个方面，临时表可以对需要更新的数据做备份，如果发现数据更新错误或者时间，可以回滚。如对需要更新的数据，先创建一个临时备份表出来，这样的话，如果更新失败也可以回滚：

代码 :

create table tmp_table as select id , name , address from test_table where …… ;
update test_table t set name =?, address =?

where id in ( select id from tmp_table );

或者
--where exists (select null from tmp_table tmp where tmp.id=t.id)
当然，如果临时表的数据量也很大的话，也可以与方法 1 结合，在临时表中做循环，如
for c_usr in (select id from tmp_table t) loop

其它很多小技巧，如断点继续（也就是更新失败后，不用重新开始，从失败点继续更新）。采用方法 1 的 PL/SQL 脚本很好实现，或者结合临时表，在临时表中增加一个有序列性质的列，从小序列开始往大序列更新，记录更新到的序列号即可。

2. 怎么对 IN 子查询使用绑定变量

在实际使用中，经常会有带 in 的子查询，如 where id in (1,2,3) 这样的情况，但是如果很多这样的语句在数据库中出现，将引起数据库的大量硬解析与共享池 SQL 碎片。所以，在实际应用中，可以采用其他方法，将这些 in list 给绑定起来。
如果需要绑定 in list ，首先，需要创建两个类型 (type) ：
针对数据类型的
CREATE OR REPLACE TYPE NUMTABLETYPE as table of number;
针对字符串类型的 ( 每个 list 的单元大小不要超过 1000 字节 )
create or replace type vartabletype as table of varchar2(1000);
然后创建两个相关的函数
数字列表函数

代码 :

create or replace function str2numList ( p_string in varchar2 ) return numTableType

v_str long default p_string || ',' ;

v_n number ;

v_data numTableType := numTableType ();

begin

loop

v_n := to_number ( instr ( v_str , ',' ));

exit when ( nvl ( v_n , 0 ) = 0 );

v_data . extend ;

v_data ( v_data . count ) := ltrim ( rtrim ( substr ( v_str , 1 , v_n - 1 )));

v_str := substr ( v_str , v_n + 1 );

end loop ;

return v_data ;

end ;

字符列表函数

代码 :

create or replace function str2varList ( p_string in varchar2 ) return VarTableType

v_str long default p_string || ',' ;

v_n varchar2 ( 2000 );

v_data VarTableType := VarTableType ();

begin

loop

v_n := instr ( v_str , ',' );

exit when ( nvl ( v_n , 0 ) = 0 );

v_data . extend ;

v_data ( v_data . count ) := ltrim ( rtrim ( substr ( v_str , 1 , v_n - 1 )));

v_str := substr ( v_str , v_n + 1 );

end loop ;

return v_data ;

end ;

创建之后，我们就可以采用如下的方式来使用 in list 的绑定了。如可以采用如下的三种方案

代码 :

SELECT /*+ ordered use_nl(a,u) */ id , user_id , BITAND ( promoted_type , 4 ) busauth

from table ( STR2NUMLIST (: bind0 )) a ,

bmw_users u

where u . user_id = a . column_value

SELECT /*+ leading(a) */ id , user_id , BITAND ( promoted_type , 4 ) busauth

from bmw_users u where user_id in

( select * from table ( STR2NUMLIST (: bind0 )) a );

SELECT /*+ index(bmw_users UK_BMW_USERS_USERID) */ id , user_id

from bmw_users where user_id in

( SELECT * FROM THE ( SELECT CAST ( STR2NUMLIST (: bind0 ) AS NUMTABLETYPE ) FROM dual ) WHERE rownum < 1000 )

在如上的方案中，以上语句中的 hint 提示，是为了稳定执行计划，防止 Oracle 对 in list 的错误估计而导致走 hash 连接。一般建议采用第一种方法，比较简单可靠并且可以指定稳定的计划。但是要求数据库的版本比较高，在老版本中 (8i) ，可能只能采用第三种方法。总的来说， 1 、 2 两种方法比 3 要少 6 个逻辑读左右。如：

代码 :

SQL > SELECT /*+ ordered use_nl(a,u) */ id , user_id

2 from table ( STR2NUMLIST ( '1,2,3' )) a ,

3 bmw_users u

4 * where u . user_id = a . column_value

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer = CHOOSE ( Cost = 3279 Card = 8168 Bytes = 334888 )

1 0 NESTED LOOPS ( Cost = 3279 Card = 8168 Bytes = 334888 )

2 1 COLLECTION ITERATOR ( PICKLER FETCH ) OF 'STR2NUMLIST'

3 1 TABLE ACCESS ( BY INDEX ROWID ) OF 'BMW_USERS' ( Cost = 1 Card = 1 Bytes = 39 )

4 3 INDEX ( UNIQUE SCAN ) OF 'UK_BMW_USERS_USERID' ( UNIQUE )

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

......

SQL > SELECT /*+ index(bmw_users UK_BMW_USERS_USERID) */ id , user_id

2 from bmw_users where user_id in

3 * ( SELECT * FROM THE ( SELECT CAST ( STR2NUMLIST ( '1,2,3' ) AS NUMTABLETYPE ) FROM dual ) WHERE rownum < 1000 )

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer = CHOOSE ( Cost = 430 Card = 999 Bytes = 51948 )

1 0 NESTED LOOPS ( Cost = 430 Card = 999 Bytes = 51948 )

2 1 VIEW OF 'VW_NSO_1' ( Cost = 11 Card = 999 Bytes = 12987 )

3 2 SORT ( UNIQUE )

4 3 COUNT ( STOPKEY )

5 4 COLLECTION ITERATOR ( PICKLER FETCH ) OF 'STR2NUMLIST'

6 5 TABLE ACCESS ( FULL ) OF 'DUAL' ( Cost = 2 Card = 82 )

7 1 TABLE ACCESS ( BY INDEX ROWID ) OF 'BMW_USERS' ( Cost = 1 Card = 1 Bytes = 39 )

8 7 INDEX ( UNIQUE SCAN ) OF 'UK_BMW_USERS_USERID' ( UNIQUE )

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

16 consistent gets

0 physical reads

0 redo size

......

3 ．并发容易出现的问题与并发控制

在多进程连接的数据库，并发操作是一个很平常的现象，加上 Oracle 特有的锁机制（不阻塞读），所以理解与控制并发是一个非常重要的事情。
下面用一个简单的例子说明并发处理中的一个问题，如用户表中存放好评的统计数据，假定两个用户同时操作，看如下一个过程。

代码 :

one session other session

----------------------------------------------------------------------------------------------------------------------

T1 > SQL > insert into auction_feedbacks values ()

1 row inserted

T2 > SQL > insert into auction_feedbacks values ()

1 row inserted

T3 > SQL > update bmw_users set rated_sum =

( select count (*) from auction_feedbacks

where username =.....)

where id =?;

1 row updated

T4 > SQL > update bmw_users set rated_sum =

( select count (*) from auction_feedbacks

where username =.....)

where id =?;

1 row updated

T5 > SQL > commit ;

Commit complete

T6 > SQL > commit ;

Commit complete

其中时间 T1<T2<T3<T5<T5<T6
两个会话，同样的执行语句与同样的执行顺序，都想把增加到好评表中的好评统计放到用户表中去，但是，问题出来了，假定原来该用户的好评是 20 个，经过两个人的评价后，好评表中最后是 22 条记录了，而用户表的统计数据是 21 。
错在哪里？谁都没有错，是并发引发的问题。
要控制好并发，就要深刻理解 Oracle 的锁机制， Oracle 如果一个进程发生数据改变，另外一个进程读该数据的时候，将发生一致性读（以 SCN 为基准），所以在上面的例子中，进程 2 读到了进程 1 commit 之前的统计数，这样就漏掉了会话 1 发生的好评。

我们要怎么避免并发呢，其实 Oracle 除了支持一致性读，也支持当前读，也就是说，操作之前检查最新的状态，对于 select 可以用序列事务，对于 DML 本来就是当前读，所以，我们可以利用 update 的条件中增加需要更新值的原始值来避免并发。

我们利用用户表复制中的防止并发操作来说明，同样的两个会话
会话一

代码 :

update rep_users_flag f set f . run_flag = 'runing'

where f . run_flag = 'stop' and f . sp_type = 'users1' ;

会话二

代码 :

update rep_users_flag f set f . run_flag = 'runing'

where f . run_flag = 'stop' and f . sp_type = 'users1' ;

执行同样的语句，如果会话 1 先执行但是还没有提交的时候，会话 2 处于等待状态，但是会话 1 一旦提交，会话 2 的条件 “where f.run_flag=’stop’” 的检查将失效（当前读已经是 runing ，是会话 1 提交后的数据），所以会话 2 能更新到的记录数将是 0 。通过判断 sql%rowcount 返回的处理行数就可以决定是否继续，如上面的例子，如更新到的行数返回 0 ，将退出或者是等待。

如果是更新频繁的业务表，需要与业务表相关实现该功能，如

代码 :

update tabele set a = a + 10 where id =? and a = 10 ; --- 假定a原来的值是10

4. 怎么使用 object 与 record 类型返回表类型数据

通过函数返回一个表类型的数据
先创建一个 object 的类型

代码 :

create or replace type varproperty IS object (

pid number ,

vid number

);

然后创建一个表类型对应到这个 object

代码 :

CREATE OR REPLACE TYPE auc_property AS TABLE OF varproperty ;

创建一个函数返回这个表类型

代码 :

create or replace function GET_PROPERTY ( p_string in varchar2 )

return auc_property

v_str long default p_string || ';' ;

v_pid varchar2 ( 100 );

v_vid varchar2 ( 100 );

v_n number ;

v_m number ;

TYPE auc_property AS TABLE OF varproperty ;

v_data auc_property := auc_property ();

begin

loop

v_n := instr ( v_str , ';' );

v_m := instr ( v_str , ':' );

exit when ( nvl ( v_n , 0 ) = 0 );

v_pid := ltrim ( rtrim ( substr ( v_str , 1 , v_m - 1 )));

v_vid := ltrim ( rtrim ( substr ( v_str , v_m + 1 , v_n - v_m - 1 )));

v_data . extend ;

v_data ( v_data . count ) := varproperty ( v_pid , v_vid );

v_str := substr ( v_str , v_n + 1 );

end loop ;

return v_data ;

end ;

通过游标返回一个表类型的数据

代码 :

create or replace procedure update_property is

-- variale

m_aid varchar2 ( 32 );

-- type

type v_array is record (

aid varchar2 ( 32 ),

astatus number ,

aproperty varchar2 ( 4000 )

);

type t_aucid is table of v_array ;

v_aucid t_aucid := t_aucid ();

-- cursor

cursor cur_pro is select id , APPROVE_STATUS , PROPERTY

from auction_property_temp

where PROPERTY is not null ;

-- start

begin

open cur_pro ;

loop

fetch cur_pro bulk collect into v_aucid limit 1000 ;

for i in 1. . v_aucid . count loop

m_aid := v_aucid ( i ). aid ;

-- delete from auction_property

delete from auction_property where auction_id = m_aid ;

-- insert into new recode

insert into auction_property ( auction_id , property_id , prop_vid , status )

select m_aid , t .*, decode ( v_aucid ( i ). astatus ,- 1 ,- 1 , 0 ) status

from table ( get_property ( v_aucid ( i ). aproperty )) t ;

end loop ;

commit ;

exit when cur_pro % notfound ;

end loop ;

commit ;

close cur_pro ;

end update_property ;

注，以上只是实际的例子，具体的使用还要视情况而定

5. 怎么样在业务繁忙时期正确的创建表约束

如果要在表上面创建一个主键约束、唯一约束、非空约束等等，一般情况下我们会采用如下语句操作：

代码 :

alter table table_name

add constraint constraint_name [ primary key | unique ] ( field_name );

alter table table_name modify CID not null ;

以上两个语句在表不大的情况下，以及业务不繁忙的情况下，一般不会有什么问题，但是，如果遇到一个业务繁忙的大表，需要做如上的操作，我们就要小心了，严重的情况下，将堵塞 select 操作，引发严重的性能问题，如：

代码 :

session1 T1 > alter table table_name

add constraint constraint_name primary key ( field_name );

session2 T2 > select * from table_name where field_name = <: value > -- 其中T2 > T1

-- 这里将阻塞，直到session1结束才开始执行

这是因为 shaerd pool 语句解析的时候，不能获得对象的句柄，所以将发生大量的 library cache pin 的等待事件，语句处于等待解析，所以阻塞了读。
但是，数据的唯一性校验是不阻塞读的，如创建唯一索引， validate 等等
只有表约束的状态改变是阻塞读的，如

代码 :

alter table ... add constraint ;

alter table ... modify constraint enable validate ;

根据这样的情况，我们应当怎么样正确的创建约束呢，以创建唯一约束例子说明，我们可以采用如下两种方法：
方法一
1 、创建唯一索引

代码 :

create unique index index_name on table_name ( field_name ) online ;

2 、创建唯一约束 （其实创建不创建也没有关系，索引可以保持唯一）
这样创建的约束删除的时候，会删除索引，两者有点等价

代码 :

alter table table_name

add constraint constraint_name primary key ( field_name ) using index index_name ;

注意后面的 using index 语句
方法二
1 、创建普通索引

代码 :

create index index_name on table_name ( field_name ) online ;

2 、采用不校验以前数据的方式创建约束

代码 :

alter table table_name

add constraint constraint_name primary key ( field_name )

using index index_name novalidate ;

3 、合并检查以前的数据

代码 :

alter table table_name modify constraint constraint_name validate ;

--- 这里主要是针对以前的记录二、数据库管理

二、数据库的管理
1. 理解 Oracle 的 Rowid 含义

8 以下 ROWID 组成（也叫受限 Rowid ）为： FFFF.BBBBBBBB.RRRR ，占用 6 个字节（ 10bit file#+22bit+16bit ），但是，为了扩充的需要，如数据文件的扩充，现在的 Rowid 改为： OOOOOOFFFBBBBBBRRR ，占用 10 个字节（ 32bit+10bit rfile#+22bit+16bit ）。其中， O 是对象 ID ， F 是文件 ID ， B 是块 ID ， R 是行 ID 。由于 rowid 的组成从 file# 变成了 rfile# ，所以数据文件数的限制也从整个库不能超过 1023 个变成了每个表空间不能超过 1023 个数据文件。
注意：这里的 O ，代表的是 data_object_id ，是与段物理存储位置相关的一个信息，所以 data_object_id + rfile# 就能最终定位到该 rowid 在那个确定的物理数据文件。
如果我们查询一个表的 ROWID ，就可以获得 object 的信息，文件信息，块信息与行信息等等，如根据其中块的信息，可以知道该表确切占用了多少个块，每行在哪个块上，哪个数据文件上。
用例子说明一下 Rowid 的组成：
SQL> select rowid from emp where rownum = 1;
AAAAeNAADAAAAWZAAA
分解一下，可以看到
Data Object number = AAAAeN
File = AAD
Block = AAAAWZ
ROW = AAA

另外，我们需要注意的是， ROWID 是 64 进制的，分布关系如下
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)
拿其中的 Data Object number= AAAAeN 为例子，
N 是 64 进制中的 13 ，位置为 0
13 * (64 ^ 0) = 13
E 是 64 进制中的 30 ，位置为 1
30 * (64 ^ 1) = 1920
A 是 64 进制中的 0
所以 A * (64 ^ 2) = 0
A * (64 ^ 3) = 0
A * (64 ^ 4) = 0
A * (64 ^ 5) = 0
则有 AAAAeN = 0 + 0 + 0 + 0 + 1920 + 13 = 1933 ，表示该行存在的对象，对应的对象号为 1933 。
而且，我们也可以利用 oracle 提供的包， dbms_rowid 来做到这一点：

代码 :

select dbms_rowid . rowid_object ( 'AAAAeNAADAAAAWZAAA' ) data_object_id #,

dbms_rowid . rowid_relative_fno ( 'AAAAeNAADAAAAWZAAA' ) rfile #,

dbms_rowid . rowid_block_number ( 'AAAAeNAADAAAAWZAAA' ) block #,

dbms_rowid . rowid_row_number ( 'AAAAeNAADAAAAWZAAA' ) row # from dual;

DATA_OBJECT_ID # RFILE# BLOCK# ROW#

--------------- ---------- ---------- ----------

1933 3 1433 0

关于更多 dbms_rowid 的用法，可以参考包的说明或者是 oracle 手册。

2. 怎么样管理 Oracle 的内存

2.1 、管理共享池
共享池的管理一直也是颇有争议的一个话题，每个版本不一样，具体管理方式也大不一样（关键是算法不一样，结构还是基本一样）。共享池的内部管理方式，我们可以通过如下的 dump 来分析：
SQL> alter session set events 'immediate trace name heapdump level 2';
通过分析跟踪文件，其实可以发现，在 8i/9i 的管理中，都是通过 extent 、 bucket 与 chunk 来管理共享池。在物理空间管理上，共享池划分为多个 extent ，每个 extent 中包含很多对应的 chunk 。在空闲空间的管理上，则采用 freelist 的方式管理， 8i 共有 11(0-10) 个 bucket 空闲列表，而 9i 则有 255(0-254) 个 bucket 空闲列表，每个 bucket 管理的 chunk 的大小不一样，当申请空间时，则到对应的 bucket 去申请空闲的 chunk ，如果没有，则转到下一个 bucket ，分裂一个大的 chunk 为已用的 chunk 与另外一个小的空闲 chunk ，放到对应的空闲 bucket 中。
如果我们想清理共享池，可以采用如下的命令：
SQL>alter system flush shared_pool

而共享池中，我们比较关心的则是 library cache 的管理，因为这部分是进行语句分析与 cache 的重要部分。我们可以用如下的命令来 dump library cache ：
SQL> alter session set events 'immediate trace name LIBRARY_CACHE level LL';
其中 LL 代表 Level 级别 , 对于 9.2.0 及以后版本 , 不同 Level 含义如下 :
Level =1 , 转储 Library cache 统计信息
Level =2 , 转储 hash table 概要
Level =4 , 转储 Library cache 对象，只包含基本信息
Level =8 , 转储 Library cache 对象，包含详细信息 ( 包括 child references,pin waiters 等 )
Level =16, 增加 heap sizes 信息
Level =32, 增加 heap 信息
Library cache 由一个 hash 表组成 , 而 hash 表是一个由 hash buckets 组成的数组，每个 hash bucket 都是包含 library cache handle 的一个双向链表（每个 hash bucket 中可能包含多个 library cache handle ），每个 Library Cache Handle 指向 Library Cache Object （如 SQL ），含对象名， namespace ，时间戳，引用列表，锁定对象及 pin 对象列表等。另外 Library Cache Handle 指向 Library Cache Object 和一个引用列表。
我们解释一个简单的软分析过程， SQL 语句经过 hash 之后，被对应到相应的 hash bucket ，然后在 bucket 中查找对应的 library cache handle ，然后在 library cache handle 对应的引用列表中检查依赖关系等等，最后根据 heap descriptor ，指向相应的 heap memory 。这个 heap memory 包含的就是 Diana Tree,P-Code,Source Code,Shared Cursor Context area 等重要数据 , 也就是我们通常所说的，解析过的 SQL 及执行计划树 , 真正到这里以后 ,sql 才得以共享 . 也就真正的避免了硬解析

"library cache pin" 是用来管理 library cache 的并发访问的， pin 一个 object 会引起相应的 heap 被载入内存中 ( 如果此前没有被加载 ),Pins 可以在三个模式下获得： NULL,SHARE,EXCLUSIVE ，可以认为 pin 是一种特定形式的锁，当 Library Cache Pin 等待事件出现时 , 通常说明该 Pin 被其他用户已非兼容模式持有。
以下语句可以获得 library cache pin 的被等待的 session 信息

代码 :

SELECT a . SID , a . username , a . program , b . addr , b . kglpnadr , b . kglpnuse ,

b . kglpnses , b . kglpnhdl , b . kglpnlck , b . kglpnmod , b . kglpnreq

FROM v$session a , x$kglpn b

WHERE a . saddr = b . kglpnuse

AND b . kglpnmod <> 0

AND b . kglpnhdl IN ( SELECT p1raw

FROM v$session_wait

WHERE event LIKE 'library%' )

以下语句可以获得被等待的 session 正在执行的语句：

代码 :

SELECT sql_text

FROM v$sqlarea

WHERE ( v$sqlarea . address , v$sqlarea . hash_value ) IN (

SELECT sql_address , sql_hash_value

FROM v$session

WHERE SID IN (

SELECT SID

FROM v$session a , x$kglpn b

WHERE a . saddr = b . kglpnuse

AND b . kglpnmod <> 0

AND b . kglpnhdl IN ( SELECT p1raw

FROM v$session_wait

WHERE event LIKE 'library%' )))

2.2 、管理 data buffer
oracle 的 data buffer 默认只有 default pool ，实际上， data buffer 在 9i 中可以分为 db_cache_size ， db_keep_cache_size 与 db_recycle_cache_size ，而且 db_cache_size 还可以根据块大小划分 db_2k_cache_size ， 4k/8k/16k/32k 等不同的 buffer 区域。对于 9i 以前版本，则由对应的 db_block_buffers, buffer_pool_keep, buffer_pool_recycle 等参数决定。
这些参数可以通过如下命令来修改
SQL> alter system set db_keep_cache_size = xxxx scope = both;
一般而言，如果不是特殊指定，所有对象都是存放在 default pool 中，并且按照特定的 LRU 算法工作，如果要指定一个对象要 keep 或者是 recycle pool ，可以通过如下的语法指定。
SQL>alter table table_name storage(buffer_pool keep);
最后，如果想知道对象处于什么 pool 中，简单的可以在 user_segments 中查询获得。
SQL> select t.segment_name,t.buffer_pool from user_segments t;

如果想计算一个对象在 data buffer 中占用了多少空间（块），则可以采用如下方法

代码 :

SELECT DATA_OBJECT_ID , OBJECT_TYPE

FROM DBA_OBJECTS

WHERE OBJECT_NAME = UPPER ( 'xxx' );

SELECT COUNT (*) BUFFERS

FROM V$BH

WHERE objd = xxx ;

data buffer 也可以被清空，在 Oracle9i 里， Oracle 提供了一个内部事件，用以强制刷新 Buffer Cache ，其语法为：
SQL>alter session set events 'immediate trace name flush_cache level 1';
或者：
SQL>alter session set events = 'immediate trace name flush_cache';
类似的也可以使用 alter system 系统级设置 :
SQL>alter system set events = 'immediate trace name flush_cache';
在 Oracle10g 中， Oracle 提供一个新的特性，可以通过如下命令刷新 Buffer Cache:
SQL>alter system flush buffer_cache;

另外， oracle 9i 可以支持在 sga_max_size 设置的范围内，在线调整不同的内存区大小。在 oracle 10g 中，内存管理又有了新的变化， oracle 默认将自动管理内存的分配，而不需要手工干预共享池与 data buffer 的大小。

3. 怎么样使用物化视图来同步数据

mv 可以用来同步数据，一般采用主键同步或者是 ROWID 同步，我们这里只讨论主键同步（ Rowid 同步其实是为 Rowid 在目标表上创建了一个隐含索引，原理跟主键同步一样）。 mv 的主要原理就是记录更改的日志，并同步到远程。
一个简单的例子：

代码 :

create materialized view log on AUCTION_FEEDBACKS ;

create materialized view AUCTION_FEEDBACKS [ on prebuilt table ] refresh fast as

select * from AUCTION_FEEDBACKS @ lnk_db1 ;

exec dbms_mview . refresh ( 'AUCTION_FEEDBACKS' , method => 'Complete' );

exec dbms_mview . refresh ( 'AUCTION_FEEDBACKS' );

declare jobid number ;

begin

sys . dbms_job . submit ( job => jobid ,

what => 'dbms_mview.refresh(''AUCTION_FEEDBACKS'');' ,

next_date => sysdate ,

interval => 'sysdate+5/1440' );

commit ;

end ;

其中，第 1 步是在主表上创建 mv log ，
第 2 步是在远程站点上创建 mv ，注意，如果选择了选项 on prebuild table 的话，远程必须存在与主站点结构一样的表，但可以没有数据。
第 3 步是全同步，如果没有选择 on prebuild table ，这一步可以省略，
第 4 步是增量刷新，在以后的情况下，一般都只需要做增量刷新即可，
第 5 步是创建一个自动刷新的作业来进行刷新，如每 5 分钟刷新一次，这个操作也可以同 crontab 来代替。

dba_mviews 记录了远程站点上 mv 的数目与属性，需要在创建 MV 的远程站点上查询。
sys.mlog$ 则记录了主站点上的 mv 的 log 数目，如果一个 master 对应到多个站点，也只有一条记录，对应到 dba_mview_logs 视图，需要在主站点查询。
sys.slog$ 记录了主站点上已经注册成功的主表信息，如果一个主表被复制到多个站点，则对应多条记录，在主站点查询。
dba_snapshot_logs 存放了 mv 的 log 日志，如果对应到多个站点，则每个站点都对应一条记录，因为远程站点的 snapshot_id 是不一样的。其实 sys.mlog$ 与 sys.slog$ 的关联就是组成 dba_snapshot_logs 的一个部分，通过查询 dba_views 可以看到其脚本。

dba_registered_snapshots 记录了远程站点的注册信息，只记录注册成功的远程站点，通过 snapshot_id 可以与 dba_snapshot_logs 关联。如

代码 :

SQL > select t . log_owner , t . master , t . log_table , t . current_snapshots ,

r . owner , r . name , r . snapshot_site

from dba_snapshot_logs t , dba_registered_snapshots r

where t . snapshot_id = r . snapshot_id (+)

删除 mv 的时候，需要先删除 mv ，再删除 mv 日志
远程站点： drop materialized view AUCTION_FEEDBACKS;
主站点： drop materialized view log on AUCTION_FEEDBACKS;

注意，删除 mv 的时候，如果主站点需要分发到多个远程站点，只有当所有远程站点的 MV 删除完成后，才可以删除 MV 日志。
删除远程站点的 MV 的时候，要保证与主站点的通信顺畅，如果网络不通，则主站点无法正常 Unregister MV ，而主站点的 mv log 又因为其它站点而不删除，将可能引起主站点 mlog 表的膨胀（因为它不知道这个 mv 的删除，需要等待这个站点的刷新）。这个时候，可以手工强行解除注册。
exec DBMS_SNAPSHOT.PURGE_SNAPSHOT_FROM_LOG (snapshot_id);

如果 mlog 因为日志曾经很多而变得很大的时候，对 mlog 的操作如日志删除将变得很慢，因为 mlog 上没有任何索引，走的都是全表扫描，这个时候，我们可以 move 该日志表或者是等日志表没有记录的时候 truncate 该表。
alter table mlog$_auction_feedbacks move;

如果要对有 mv 复制的表增加字段，最好在停机情况下进行，因为这样不会产生 mv log ，而且最好采用 prebuild 模式，因为这种模式下，删除 mv 的定义将保留表（也就是不删除表与相关数据）。相关步骤：
停机模式下：

代码 :

1 、远程站点：drop materialized view MV_TABLE ;

2 、主站点：alter table master_table add new_field number ;

3 、远程站点：alter table mv_table add new_field number ;

4 、远程站点：create materialized view mv_table on prebuilt table

refresh fast as

select * from master_table @ lnk_db ;
.

为什么要停机操作，是因为创建快速刷新的 mv 的时候，将删除主站点上创建 mv 时间点之前的相关 log ，所以在删除 mv 与重新创建 mv 之前，不能对主表有任何 dml 产生新的日志，否则将可能引发数据的不一致。

4. 怎么样在线创建索引或者重构索引 / 表

在很多情况下，需要在线创建索引或者重组索引以及重组表，重新创建索引的主要原因是因为新的业务的发展的需要，而重组表与索引往往是因为索引的偏移膨胀或者是数据删除引起的稀疏状态，以及表数据删除引起的表数据的稀疏分布，这些情况下需要重组。
当然，如果需要创建的索引很小，或者是需要重组的索引与表都很小，创建与重组过程在几秒之内，这些都可以直接做而不需要讨论。实际情况是，业务很繁重以及表与索引都很大。这些情况下我们需要注意些什么呢？
4.1 、重新创建新索引
首先，评估该索引的需要程度，如果不是特别紧急的大索引，最好在维护时间操作，然后评估该索引是否会对现有的语句造成负面影响，如导致以前的语句错误的走到这个新索引上（在日期打头的索引上，很容易出现这样的问题）。
根据索引大小以及需要在上面创建的表业务是否繁忙，如果业务繁忙，尽量选择业务不繁忙的时间，如凌晨 2 点 -6 点进行操作。创建索引之前评估索引的大小以及索引所在表空间剩余空间的大小。

代码 :

SELECT D . TABLESPACE_NAME , SPACE "SUM_SPACE(M)" , SPACE - NVL ( FREE_SPACE , 0 ) "USED_SPACE(M)" ,

ROUND (( 1 - NVL ( FREE_SPACE , 0 )/ SPACE )* 100 , 2 ) "USED_RATE(%)" , FREE_SPACE "FREE_SPACE(M)"

FROM

( SELECT TABLESPACE_NAME , ROUND ( SUM ( BYTES )/( 1024 * 1024 ), 2 ) SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME ) D ,

( SELECT TABLESPACE_NAME , ROUND ( SUM ( BYTES )/( 1024 * 1024 ), 2 ) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME ) F

WHERE D . TABLESPACE_NAME = F . TABLESPACE_NAME (+)

除此之外，还要评估临时表空间大小是否足够，用于创建索引时的排序操作。然后检查系统的负载压力状态。最后，确定要创建以后，采用 online 模式创建。

代码 :

create index IND_BMW_USERS_FULLNAME_REGDATE on BMW_USERS ( FULLNAME , USER_REGDATE )

tablespace TBS_INDEX2 online ;

然后马上检查系统应用，如果发现有错误走到该索引的语句，并且有严重影响的，可能需要立即删除该索引或者约束。

代码 :

alter table BMW_USERS drop constraint UK_BMW_USERS_USERID cascade ;

drop index IND_BMW_USERS_FULLNAME_REGDATE ;

通过观察，如果确定没有什么问题的，可以马上分析该索引。

代码 :

begin

dbms_stats . gather_index_stats ( ownname => 'TAOBAO' ,

indname => 'UK_BMW_USERS_USERID' ,

estimate_percent => 10

);

end ;

不过，在一些情况下，为了防止创建完索引，执行计划立即出错的情况，我们可以把统计语句放到创建索引中，如：

代码 :

create index IND_BMW_USERS_FULLNAME_REGDATE on BMW_USERS ( FULLNAME , USER_REGDATE )

tablespace TBS_INDEX2 online [ compute statistics ];

在一些情况下，需要改造一个索引，如添加一个字段到索引或者从索引中删除一个字段，这个时候也需要重新创建索引，但是需要严格按照如下顺序来操作
a 、创建新的替代索引（如加字段或者减字段后的索引）
b 、分析该索引，并测试没有问题
c 、删除原来的索引

4.2 、重组索引或者是表
如果索引或者是表因为更新太频繁或者是删除数据过多，可能引起段的数据稀疏分布，造成大量的空间浪费，并且严重影响表或者是索引的扫描速度。这样的情况下，我们需要对该索引或者是表进行重组。
重组之前，同样需要确认索引与表所在的表空间是否有足够的空间，如果是索引重组，还需要确认是否有足够的临时表空间用于排序。同样，如果索引或者表很大，而且使用比较频繁，请确认在业务不繁忙的时候操作。

代码 :

alter index UK_BMW_USERS_USERID rebuild [ tablespace tbs_index2 ] online ;

alter table auction_winners move [ tablespace bmw_auction ];

注意：如果对表进行了 move ，那么该表所有的索引都将失效，必须重新 rebuild 。所以业务繁重的表不适合在线 move 。

如果确认完成，对索引也需要完成上面的分析工作，如果是表，采用如下脚本分析：

代码 :

begin

dbms_stats . gather_table_stats ( ownname => 'TAOBAO' ,

tabname => 'AUCTION_WINNERS' ,

estimate_percent => 10 ,

method_opt => 'FOR ALL INDEXED COLUMNS'

);

end

如果没有必要 online 模式，如维护时候，只需要去掉 online 关键字即可。
关于对分区表的 move 与分区索引的 rebuild 可以参考 “ 怎么样管理分区表与分区索引 ”
关于对 lob 字段的 move ，可以参考 “ 怎么样管理 LOB 字段 ”
表的重整，还可以参考表的在线重定义

5.怎么样编译失效对象

通过如下语句可以查找数据库中的处于无效状态的对象
select * from dba_objects t where t.status != 'VALID'
而这些对象，在很多情况下，是因为本身的错误处于不可用状态，也有一些是因为修改了相关的对象而导致本对象失效。我们可以采用compile命令来编译该对象。
alter function[procedure|package|trigger|view] script_name compile;
如果本身语法没有问题，则可以编译成功，如果本身有语法错误，则需要重新创建。需要注意的是，如果是包的话，没有必要编译包体
compile只对sql pl/sql的代码起作用，如果是物理存储之类的对象不可用，如索引的状态不对，只能采用rebuild或者是重建来处理。

对于很多系统包的失效，因为其关联关系紧密，无法采用普通的compile来编译，但是可以运行Oracle的脚本来编译系统包。

代码:



spool logfile



@$ORACLE_HOME/rdbms/admin/utlrp.sql;



spool off

6. 怎么样在管理自动 undo 表空间

从 Oracle 9i 开始， undo 表空间默认开始自动管理 (AUM) ，从而节省了大量的手工管理 undo 段的时间与精力。在 Oracle 9i 中，与 undo 有关系的参数如下，其中 undo_management 决定了表空间是自动管理 (AUTO) 还是手工管理 (MANUAL) 。

代码 :

SQL > show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 10800

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS1

在 AUM 模式下，只需要指定一个 UNDO 表空间，就可以让 Oracle 去自动创建与管理回滚段， UNDO_RETENTION 参数用以控制事务提交以后 undo 信息保留的时间。该参数以秒为单位， 9iR1 初始值为 900 秒，在 Oracle9iR2 增加为 10800 秒。但是这是一个 NO Guaranteed 的限制。也就是说，如果有其他事务需要回滚空间，而空间出现不足时，这些信息仍然会被覆盖。
从 Oracle10g 开始，如果你设置 UNDO_RETENTION 为 0 ，那么 Oracle 启用自动调整以满足最长运行查询的需要。当然如果空间不足，那么 Oracle 满足最大允许的长时间查询。而不再需要用户手工调整。
同时 Oracle 增加了 Guarantee 控制，也就是说，你可以指定 UNDO 表空间必须满足 UNDO_RETENTION 的限制。
SQL>alter tablespace undotbs1 retention guarantee;
SQL>alter tablespace undotbs1 retention noguarantee;

经常有这样的时候，因为 bug 的问题， 9i 中的 undo 表空间出现了满掉的情况，而且不容易或者根本回收不了空间，这个时候，除了临时的改变 undo_retention 之外，可能需要切换 undo 表空间。
一般临时的解决方案是
alter system set undo_retention = 120;
这个值根据自己的需要设置，一般较小的值有利于空间的立即回收，可能马上就能解决这个问题。如果不能解决，可以参考如下方法来 switch undo 表空间。
6.1 、准备创建 undo 表空间的空间
如果是文件系统，只要有足够的空间即可，如果是裸设备，可能需要先创建裸设备，做 link 之类的，如在 aix 下，如果先创建一个 lv ，然后创建一个链接。
mklv -y'lv1_undo_2g_11' -t'raw' -T O vg_db 16
chown oracle.DBA /dev/rlv1_undo_2g_11
ln -s /dev/rlv1_undo_2g_11 /u01/oracle/oradata/tbdb1/undotbs11.dbf
6.2 、创建 undo 表空间

代码 :

create undo tablespace TBS_UNDO2

datafile '/u01/oracle/oradata/tbdb1/undotbs11.dbf' size 2000m

extent management local ;

如果空间不够，可以继续增加数据文件

代码 :

alter tablespace TBS_UNDO2 add datafile

'/u01/oracle/oradata/tbdb1/undotbs21.dbf' size 2000m ;

6.3 、表空间切换
登陆 SQLPLUS ，执行切换命令

代码 :

ALTER SYSTEM SET undo_tablespace = TBS_UNDO2 ;

修改参数文件
undo_tablespace='TBS_UNDO2'
6.4 、删除原来的 undo 表空间
如果不想保留，或者以后作为下次切换的用途，就可以把原来的 undo 表空间给删除掉。首先要检查原来的表空间（如 UNDO1 ）是否还有活动的事务，通过查询 dba_rollback_segs ，确定 UNDO1 所有的段的状态都是 offline ，并且在 v$transaction 没有在 UNDO1 上的活动事务。然后执行如下命令删除原来的 undo 表空间：
SQL>drop tablespace tbs_undo1;
最后从 OS 上物理删除该数据文件或者是 lv 。

7. 怎么样管理临时表空间与临时文件

如果说 oracle 8i 以下（包括 8i ）还是使用的是有数据文件特性的临时数据文件， Oracle 9i 已经是真正意义的临时文件了，这样的临时文件不需要备份与恢复，也不会记录任何 redo log 信息。
因此， 9i 才有了一个新的关键字 tempfile ，而在 8i 的时候，这个都是 datafile 。如要添加一个新的临时文件， 8i 是：
alter tablespace temp add datafile 'temp file name' size 2000M;
而 9i 是
alter tablespace temp add tempfile 'temp file name' size 2000M;
这个是 9i 一个新的特性，就是真正意义的临时数据文件。而且从 9i 开始，数据库将有默认的临时表空间，新建用户如果不特殊指定将用默认的临时表空间（在老的版本中如果不特殊指定，将默认 system 表空间为临时表空间），从如下视图可以查询到当前默认临时表空间。

代码 :

select * from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE' ;

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ --------------- -----------------------------------------

DEFAULT_TEMP_TABLESPACE TBS_TEMP Name of default temporary tablespace

如我们可以通过如下命令要创建与设置临时表空间

代码 :

create temporary tablespace temp tempfile 'temp file name' size 100m ;

alter database default temporary tablespace temp ;

在 linux/unix 上，如果不指定 reuse 关键字，新创建出来的临时文件都是稀疏文件，也就是说空间不是马上分配的，所以有很快的创建速度。临时表空间一旦创建，就不能转化为永久表空间了，不能执行离线（ offline ）操作，而且当前默认的临时表空间不能被删除。

代码 :

SQL > drop tablespace temp including contents and datafiles ;

drop tablespace temp including contents and datafiles

ORA - 12906 : cannot drop default temporary tablespace

如果是非当前默认的临时表空间，则可以删除，另外，临时文件可以随时增加或者是删除。

代码 :

SQL > alter tablespace tbs_temp add tempfile 'temp file name' size 100M ;

Database altered

SQL > alter database tempfile 'temp file name' drop ;

Database altered

8. 怎么样管理分区表与分区索引

分区表在特定环境下经常被使用，合理的使用分区表，可以使管理简单，应用速度加快，反之，不合理的使用分区表，可能导致管理负担的增加与应用速度的严重减慢。分区表有范围分区， hash 分区， list 分区与混合分区，我们这里只讨论范围分区表与 hash 分区表。
8.1 、创建分区表
创建范围分区表

代码 :

create table test1 ( id varchar2 ( 32 ), ends date )

partition by range ( ENDS )

(

partition TEST_PART1 values less than ( TO_DATE ( '2004-01-01' , 'SYYYY-MM-DD' ))

tablespace TBS_P1

pctfree 10

initrans 4

maxtrans 255

storage

( …… ),

partition TEST_PART2 values less than ( TO_DATE ( '2005-01-01' , 'SYYYY-MM-DD' ))

tablespace TBS_P2

pctfree 10

initrans 4

maxtrans 255

storage

( …… ),

);

创建 hash 分区表

代码 :

create table test2 ( id varchar2 ( 32 ), ends date )

partition by hash ( ID )

(

partition TEST_PART1

tablespace TBS_P1 ,

partition TEST_PART2

tablespace TBS_P2 ,

partition TEST_PART3

tablespace TBS_P3 ,

partition TEST_PART4

tablespace TBS_P4

)

pctfree 20

initrans 8

maxtrans 255

;

需要注意的是，范围分区可以对每个分区定义单独的存储参数，而 hash 分区是不可以对单独的分区定义存储参数的。
分区表在特定的环境下，可以有很大的性能优势，如根据分区索引，仅仅在一个分区内检索数据或者是对单个分区的扫描而非全表扫描。在备份与恢复方面，也可以单个分区操作如 exp 一个分区的数据，删除单个分区而不影响到其它分区等等。
但是，如果行数据需要在分区之间移动，还必须增加 ENABLE ROW MOVEMENT 关键字。

8.2 、添加 / 删除 / 截断 / 重令名分区
采用如下语句，可以对范围分区或者是 hash 分区增加一个新的分区，不过对于 hash 分区，一般确定其分区数目之后（一般采用 2 的 n 次幂作为分区数目），一般不建议增加或者删除其分区。

代码 :

alter table test1

add partition TEST_PART3

values less than ( to_date ( '2006-01-01' , 'YYYY-MM-DD' ))

tablespace TBS_P3 ;

可以采用如下命令删除一个分区。

代码 :

alter table test1 drop partition TEST_PART3 ;

注意，不能删除 hash 分区的一个子分区。
对于每个分区的记录数，可以采用如下命令查看：

代码 :

select * from test1 partition ( TEST_PART3 );

同样，除了 drop 分区，也可以单独删除分区的记录或者是 truncate 分区

代码 :

alter table test1 truncate partition TEST_PART3 ;

delete from test1 partition ( TEST_PART3 );

分区也可以被重令名，如

代码 :

alter table test1 rename partition test_part2 TO test_partition2 ;

8.3 、本地索引与全局索引以及数据重组
本地索引是针对每个分区单独创建的索引，在 olap 的一些环境下，本地索引其独特的优越性，如可以 rebuild 一个分区的本地索引而不影响到其它分区，而且，如果 drop 了一个分区，也不影响整个索引的使用（全局索引如果 drop 了一个分区，需要 rebuild 整个索引）。然而在 oltp 的环境下，本地索引往往因为要扫描更多的索引而导致性能低下，所以在是否选择本地索引还是全局索引的时候，要根据具体的环境而定。
创建本地索引，需要在创建的语句后增加 local 关键字。

代码 :

create index ind_test_ends on TEST1 ( ends ) local ;

注意，如果在分区表上面创建本地的主键或者唯一键约束的时候，需要有分区关键字，而全局约束没有这个限制，如。

代码 :

SQL > alter table TEST1 add constraint pk_test_id primary key ( ID ) using index local ;

ORA - 14039 : partitioning columns must form a subset of key columns of a UNIQUE index

SQL > alter table TEST1 add constraint pk_test_id primary key ( ID , ends ) using index local ;

Table altered

对于分区表的单独分区与本地索引，可以单独 move 或者是 rebuild ，如
move 一个分区

代码 :

alter table test1 move partition TEST_PART1 [ tablespace tbs ];

rebuild 一个分区索引

代码 :

Alter index pk_test_id rebuild partition TEST_PART1 [ tablespace tbs ]| [ online ];

对于全局索引，如果对分区表做了任何数据操作的 DDL ，都需要全部 rebuild 该索引，从 Oracle 9i 开始，提供有 UPDATE GLOBAL INDEXES 关键字，可以在做 DDL 的时候同时更新全局索引，但是如果全局索引很大，这个操作一样很慢。

代码 :

alter table test1 truncate partition TEST_PART2 UPDATE GLOBAL INDEXES ;

8.4 、分区分裂 / 合并与分区交换
我们可以简单的把一个分区分裂成两个分区，如

代码 :

alter table test1 split partition TEST_PART3

at ( TO_DATE ( '2005-10-01' , 'YYYY-MM-DD' ))

into ( partition TEST_PART3_0 , partition TEST_PART3_1 );

同样，也可以把两个分区合并成一个分区

代码 :

alter table test1 merge partitions

TEST_PART3_0 , TEST_PART3_1 into partition TEST_PART3 ;

注意，这两个操作完成后，注意 rebuild 相关索引。

我们可以简单的把一个分区中的数据交换到一个单独的表中或者是把表中的数据交换到分区中 ( 注意：是指数据的交换 ) ，如

代码 :

alter table test1

exchange partition TEST_PART3 with table test3 ;

这个操作把表中的数据放入到了分区中，分区的数据交换到了表中，需要注意的是， test3 与 test1 需要有相同的表结构，包括是否为空都必须一样。做完该操作，也需要 rebuild 分区表与单独表的相关索引。
注意：如果表的数据不符合该分区的分区条件，交换将出错
ORA-14099: all rows in table do not qualify for specified partition
可以强行增加 without validation ，不进行数据的校验，这样虽然大幅度的提高了速度，但是数据的正确性不可以保证，但是，如果你已经确认数据一定是正确的，可以利用该关键字来提高交换的速度。

9. 怎么样管理 LOB 字段

lob 字段这里通常表示 Blob ， Clob 与 Bfiles 字段，但是经常情况下，我们只讨论 Blob 与 Clob 字段，以下的 Blob 字段就表示 Blob 与 Clob 。 Blob 一般用于保存 2 进制的数据，如图片等， Clob 一般可以用于保存文字等字符信息，与数据库的字符集有密切关系。
创建带 LOB 字段的完整语法为：

代码 :

Create table DemoLob ( A number , B clob )

LOB ( b )

STORE AS lobsegname (

TABLESPACE lobsegts

STORAGE ( lobsegment storage clause )

[ CHUNK 8K disable storage in row ]

INDEX lobindexname (

TABLESPACE lobidxts

STORAGE ( lobindex storage clause )

)

TABLESPACE tables_ts

STORAGE ( tables storage clause );

其中， store as (enable storage in row|disable storage in row) 表示是否
对于 enable storage in row ，表示允许小于 4000 字节的 lob 字段信息保存在表段，是默认值，对于大于 4000 字节的 lob 字段保存在 lob 段（同 disable storage in row ），在表段将保留 36-84 字节的控制信息。对于 disable storage in row ， Oracle 将 lob 字段分开保存在 lob 段中，而仅仅在行位置保留 20 字节的指针。对于相当于 disable storage in row 的这部分， UNDO 仅仅是记录指针与相关 lob 索引改变，如果发生更新操作等 DML 操作，原始数据将保留在 LOB 段。
storage as ( CHUNK bytes ) 表示对于 disable storage in row 的这部分，最小的 LOB 块的大小，必须是数据库块 (DB_BLOCK_SIZE) 的整数倍。一个 chunk 最多只保留一行 LOB 数据。
storage as(cache|nocahce) 表示是否允许 lob 段经过 buffer cache 并缓存。默认是 nocache ，表示直接读与直接写，不经过数据库的 data buffer 。
storage as(nocache logging |nocache nologging) ， logging/nologging 属性只对 nocache 方式生效，默认是 logging ，如果是 nologging 方式，对于保存在行外的 log 部分，在 update 等 DML 操作时将不记录 redo 日志。

LOB 段也可以单独利用 move 来重整数据，如
alter table tablename move lob(field) store as (tablespace newts);
如果 LOB 字段在分区表中，则增加 partition 关键字，如
alter table tablename move [partition partname]
lob(field) store as (tablespace newts);
最后，如果想连原表一起 move ，则在表的 move 语句后面增加表的目标表空间，如

代码 :

alter table AUCTION_AUCTIONS move partition AUCTION_PARTITION1

tablespace AUCTION_HISTORY

lob ( DESCRIPTION , STORY ) store as ( tablespace AUCTION_HISTORY );

lob 段的信息可以从 dba/all/user_lobs 中获得，并可以与其它段一样，从 user_segments/user_extents 中获得段与区间信息。

10.怎么样解除分布式事务的挂起

由于远程数据库的异常中断或者是关闭，本地数据库对远程数据库的分布式事务经常会出现连续的报错到alert.log文件中。
ORA-02068: following severe error from LNK_DB2_STB
ORA-01033: ORACLE initialization or shutdown in progress
如果远程数据库短时间内无法恢复，这个错误可能会一直报下去，有的时候，对于这样分布式事务，可以简单称为事务的挂起状态，我们可以采用强制手段将该事务清除。
首先，检查挂住的事务
select local_tran_id from dba_2pc_pending where state='collecting';

首先，可以试图提交或者回滚该事务
SQL> select state, advice from dba_2pc_pending where local_tran_id = "";
强行提交:SQL> commit force "";
强行回滚:SQL> rollback force "";

如果执行失败，也可以强行从数据字典中删除改事务的记录

代码:



SQL> set transaction use rollback segment system;



SQL> delete from dba_2pc_pending where local_tran_id = "";



SQL> delete from pending_sessions$ where local_tran_id = "";



SQL> delete from pending_sub_sessions$ where local_tran_id = "";



SQL> commit;

11. 怎么样管理表空间与数据文件

Oracle 8i 以上，已经趋向使用本地管理的表空间 (LMT) ，而且 9i 已经默认使用本地管理表空间，在默认情况下，该处都默认是本地管理的表空间。本地管理表空间在文件头部用位图标记使用的块与未使用的块而不是字典管理表空间的字典表 UET$ 与 FET$ 。大大提高了并发处理能力并防止了空间碎片的发生。主要语法为：
主要语法： CREATE TABLESPACE 表空间名字
　　　　　　　　　　 DATAFILE ' 数据文件详细信息 '
　　　　　　　　　　 [EXTENT MANAGEMENT { LOCAL
　　　　　　　　　　 {AUTOALLOCATE | UNIFORM [SIZE INTETER [K|M] ] } } ]
自动段空间管理（ ASSM ），它首次出现在 Oracle920 里。有了 ASSM ，链接列表被位图数组所取代，它是一个二进制的数组，能够迅速有效地管理存储扩展和剩余区块（ free block ），因此能够改善分段存储本质。关键字 SEGMENT SPACE MANAGEMENT AUTO 指定了该表空间为自动段空间管理的表空间（ ASSM 必须是本地管理表空间）。如创建一个段自动管理的表空间例子：

代码 :

SQL > create tablespace TBS_TAOBAO

datafile '/u01/oracle/oradata/tbdb2/tbs_taobao_01.dbf' size 2000M ,

'/u01/oracle/oradata/tbdb2/tbs_taobao_02.dbf' size 2000M

extent management local uniform size 1M segment space management auto ;

对于已经创建的表空间，建议使用固定大小的数据文件，并且不自动扩展数据文件大小。

数据文件的管理主要是给表空间添加新的数据文件，如表空间不足够的情况下，经常会有这样的需求：

代码 :

SQL > alter tablespace TBS_TAOBAO add datafile

'/u01/oracle/oradata/tbdb2/tbs_taobao_03.dbf' size 2000M ;

或者有的时候，需要编辑数据文件的特性，如 resize ，或者改为自动分配 & 禁止自动分配空间等等。

代码 :

SQL > alter database datafile 'xxxx' resize 1000M ;

SQL > alter database datafile 'xxxx' autoextend on maxsize 2048M ;

有的时候，还有这样的需求，在特定的数据文件上分配新的 extent 给特定表，我们可以采用如下的命令：

代码 :

SQL > alter table test allocate extent

( datafile '/u01/oracle/oradata/tbdb2/tbs_taobao_03.dbf' );

12. 怎么样管理联机日志文件

联机日志是很重要的数据库的一部分，一般情况下，可能需要对联机日志做增加 / 删除 / 改变大小位置等操作。作为管理员，必须熟悉其中的操作，以下是一些联机日志常用的操作：
增加一个日志组，在 OPS/RAC 条件下，需要注明 thread ：
Alter database add logfile [thread n] group n 'file name' size xxM;
一个简单的例子如下

代码 :

SQL > Alter database add logfile thread 2

group 4 ( '/u01/oracle/oradata/rac/redo04.log' ) size 10240K ,

group 5 ( '/u01/oracle/oradata/rac/redo05.log' ) size 10240K ,

group 6 ( '/u01/oracle/oradata/rac/redo06.log' ) size 10240k ;

在这个组上增加一个成员

代码 :

Alter database add logfile member 'file name' to group n ;

在这个组上删除一个日志成员

代码 :

Alter database drop logfile member 'file name' ;

删除整个日志组

代码 :

Alter database drop logfile group n ;

也可以对日志文件重令名

代码 :

Aalter database rename 'file one name' to 'file other name' ;

我们可以采用如下的命令来切换当前联机日志或者归档当前联机日志，注意，切换不等于归档，如果在非自动归档模式下，切换将不发生归档操作。

代码 :

SQL > alter system switch logfile ;

SQL > alter system archive log current ;

另外，在有些情况下，如果发生日志文件损坏，我们需要清除并重建该日志，我们可以采用 clear logfile 来完成。
如需要清除并重建联机日志，可以用

代码 :

Alter database clear [ unarchived ] logfile group n ;

注意其中的 unarchived ，表示如果该日志还没有归档，也强行清除。

有的时候，我们可能想获取当前联机日志文件的使用率或者是 redo block 的大小，我们可以通过如下的查询获得：
如，获得当前日志文件的使用率

代码 :

SQL > SELECT le . leseq CURRENT_LOG_SEQUENCE #,

2 100 * cp . cpodr_bno / LE . lesiz PERCENTAGE_FULL

3 from x$kcccp cp , x$kccle le

4 WHERE LE . leseq = CP . cpodr_seq ;

CURRENT_LOG_SEQUENCE # PERCENTAGE_FULL

--------------------- --------------

4366 90.1298828

三、备份与恢复
1. 数据文件丢失或者数据文件错误的处理方法

1.1 、 RAC 环境下，一个节点错误操作增加的数据文件，导致另外一个节点找不到在 RAC 的环境下，数据文件都必须等两边同时准备好环境 。

如创建裸设备，做链接之后再在一个节点上创建数据文件。但是如果不小心，只在一个节点创建裸设备之后就马上创建了表空间或者添加了数据文件，这样另外一个节点找不到新创建的数据文件，会报如下的错误：

代码 :

Sun Aug 1 10 : 44 : 46 2004

Errors in file / opt / oracle / admin / tbdb2 / bdump / tbdb2in2_dbw0_1564 . trc :

ORA - 01186 : file 39 failed verification tests

ORA - 01157 : cannot identify / lock data file 39 - see DBWR trace file

ORA - 01110 : data file 39 : '/opt/oracle/product/9.2/dbs/tbdb2/tbs_index2_2.dbf'

Sun Aug 1 10 : 44 : 46 2004

File 39 not verified due to error ORA - 01157

Sun Aug 1 10 : 44 : 46 2004

Errors in file / opt / oracle / admin / tbdb2 / bdump / tbdb2in2_dbw0_1564 . trc :

ORA - 01157 : cannot identify / lock data file 39 - see DBWR trace file

ORA - 01110 : data file 39 : '/opt/oracle/product/9.2/dbs/tbdb2/tbs_index2_2.dbf'

ORA - 27037 : unable to obtain file status

Linux Error : 2 : No such file or directory

Additional information : 3

对于这样的问题，重起数据库当然可以解决，但是如果对于 24*7 的系统，只需要利用备份与恢复的办法，在这个找不到数据文件的节点上 offline->recover datafile->online 即可，如

代码 :

alter database datafile 39 offline ;

recover datafile 39 ;

alter database datafile 39 online ;

1.2 、丢失一个数据文件但是存在其日志，没有备份怎么恢复
对于丢失一个数据文件的情况，必须要求该数据文件不能是系统的数据文件，而且控制文件记录有该数据文件的信息，并且保证有该数据文件创建以来的所有日志，则可以不利用备份恢复，只需要重新根据控制文件创建该数据文件并恢复即可。

代码 :

SQL > startup mount

SQL > Alter database create datafile 'db file name' as 'db file name' size … reuse ;

SQL > recover datafile n ;

SQL > recover datafile ' db file name' ;

1.3 、恢复过程中，因为没有权限错误创建数据文件
在数据库的恢复过程中，或者 standby 数据库的日志应用中，如果要根据日志内容来创建新的数据文件，但是却没有权限创建或者其他写错误，将在控制文件中自动生成一个 UNNAMEDxxxxx 的数据文件。对于这样的情况，首先要保证目标地点能正常的写入，然后采用如下命令更正该数据文件，如。

代码 :

SQL > alter database create datafile 'old file(UNNAMED00063)' as 'new file(correct file)' ;

SQL > recover datafile n ;

1.4 、没有备份，丢失数据文件
对于这样的情况，如果没有任何备份，可能要承担数据的丢失（除非通过 dul 或者类似工具去数据文件中抓取数据）的风险。如果该数据文件不是系统数据文件，可以强行删除再打开数据库。如，在 mount 状态下：

代码 :

-- ARCHIVELOG 模式命令

SQL > Alter database datafile 'file name' offline ;

-- NOARCHIVELOG 模式命令

SQL > Alter database datafile 'file name' offline drop ;

最后 open 数据库即可。
当然，如果有备份是可以防止数据丢失的，可以从备份进行数据文件的恢复，如

代码 :

rman > restore datafile n ;

rman > recover datafile n ;

2. 当失去媒体管理层，怎么样删除磁带备份

有这样的时候，控制文件中已经有了很多磁带备份的信息，但是现在由于某种原因，导致在 delete obsolete 的时候出错，无法处理磁带上的备份。当然，我们也可以指定操作类型只对磁盘备份进行操作。
delete noprompt obsolete redundancy = 2 device type disk;
还有的时候，我们想删除控制文件中这些磁带备份的信息，但是因为没有媒体管理层，我们除了让它自然过期之外，还可以采用如下方法：

代码 :

RUN

{

ALLOCATE CHANNEL FOO TYPE SBT PARMS

'SBT_LIBRARY=oracle.disksbt,ENV=(BACKUP_DIR=.)' ;

DELETE BACKUPPIECE 'db_off_DIWAENT2_6982_1_461253279' ;

}

注意这里的备份目录需要设置为 “.” ，可以成功的不使用媒体管理层而从备份库中删除备份信息。

3. 怎么样找回被意外删除的数据

对于意外 delete 的数据，除了利用备份来恢复外， 8i 以上可以采用 logminer 来恢复了， 9i 增加了一个 flashback query 的功能，基本可以闪回 undo_retention 时间之内的数据。对于 trunc table 的数据，除了备份， 10g 还提供 flashback dababase 可以恢复；对于 drop table ， 10g 也提供 flashback drop 来恢复。这里我们只讨论 delete 意外删除数据的恢复情况。
3.1 、利用 logmnr 来恢复数据
Oracle 的 logminer 可以从日志中恢复数据，在使用这个功能前，先确定 utl_file_dir 参数的设置，因为这个目录主要用于存放 dbms_logmnr_d.build 过程所产生的字典信息文件。
那首先，我们来生成字典文件，这里的字典文件的路径就是参数 utl_file_dir 设置过的路径，否则会因为没有权限而报错：

代码 :

SQL > exec dbms_logmnr_d . build ( dictionary_filename => 'dict.ora' , dictionary_location => '/u01/oradata/logmnr' );

然后，我们来添加要分析的日志文件：

代码 :

SQL > exec

dbms_logmnr . add_logfile ( logfilename => '/u01/archive/arch_1_197.arc' , options => dbms_logmnr .new);

PL / SQL procedure successfully completed .

SQL > exec

dbms_logmnr . add_logfile ( logfilename => '/u01/archive/arch_1_198.arc' , options => dbms_logmnr . addfile );

PL / SQL procedure successfully completed .

可以根据自己的需要选择是新创建的文件还是追加新文件，除了这两个选项之外，还有一个选项为 REMOVEFILE ，与 addfile 相反，是移除已经添加的日志文件。
如果一切完成，最后，我们就可以根据字典文件信息来分析日志了

代码 :

SQL > exec

dbms_logmnr . start_logmnr ( dictfilename => '/u01/oradata/logmnr/dict.ora' );

PL / SQL procedure successfully completed .

当然 dbms_logmnr.start_logmnr() 过程还有其它几个用于定义分析日志时间 /SCN 窗口的参数，它们分别是：
STARTSCN / ENDSCN - 定义分析的起始 / 结束 SCN 号，
STARTTIME / ENDTIME - 定义分析的起始 / 结束时间。
如：

代码 :

begin

dbms_logmnr . start_logmnr ( dictfilename => '/u01/oradata/logmnr/dict.ora' ,

starttime => to_date ( '2003-09-21 09:39:00' , 'YYYY-MM-DD HH24:MI:SS' ),

endtime => to_date ( '2003-09-21 09:45:00' , 'YYYY-MM-DD HH24:MI:SS' ));

end ;

STARTSCN 和 ENDSCN 参数使用方法类似。

如果日志分析完成，我们就可以查看分析结果了，分析结果保存在 v$logmnr_contents 中，其中有很多有用的信息，如 SESSION_INFO 保存了执行该操作的会话信息， SQL_REDO 保存了该会话的操作，这里的操作是原子级别的，也就是说一行表记录将对应一行 SQL_REDO 中的语句，如原来操作语句是 update table set a=a+1 ，如果该表有 10 条记录，则对应的 SQL_REDO 将有 10 条， SQL_UNDO 保存了该语句对应的回滚操作， TIMESTAMP 保存了操作时间戳， OPERATION 保存了操作类型。还需要注意的是，该视图是 session 级别的，别的会话看不见你的分析结果。
全部结束之后，我们可以执行 dbms_logmnr.end_logmnr 过程退出 LogMiner 分析过程，你也可以直接退出 SQL*PLUS ，它会自动终止。

Logminer 除了分析本数据库的日志文件外，还可以分析其它数据库的日志文件，主要是要求 build 出来的字典文件与日志文件是同一个数据库的，而且最好与分析的数据库有同样的 OS 版本，否则文件头信息可能会不一样。

3.2 、采用 flashback query 来找回数据
flashback query （闪回查询）必须要求 UNDO_MANAGEMENT = AUTO ，并且注意另外一个参数 undo_retention = n （秒），它决定了能往前闪回的最大时间，从参数也可以看到，这个参数决定了 Undo 最多的保存时间，越大的值，就会需要更多的 Undo 空间的支持。我们可以通过如下的命令来修改该参数

代码 :

SQL > ALTER SYSTEM SET undo_retention = 1200 ;

System altered .

以上的命令，告诉 Oracle ， Undo 信息将保持 1200 秒 (20 分钟 ) ，这个将确保任何提交后的信息在 Undo 中将保持 20 分钟，除了可以一定程度的防止 Ora-01555 错误外， Falshback query 将也直接的应用到其中的信息。如我们采用如下基于时间的查询语句，则可以直接查询到 undo_retention 之内的数据。

代码 :

SQL > select * from table as of timestamp to_timestamp ( '2006-1-24 23:26:5' , 'yyyy-mm-dd hh24:mi:ss' );

也可以使用基于 scn 的查询，如

代码 :

SQL > select * from table as of scn : scn_number ;

至于 scn 可以采用如下语句查询，在实际中， SCN 是无处不在的，也可以从归档日志中获得所想要的 SCN Number 。

代码 :

SQL > select dbms_flashback . get_system_change_number from dual ;

如果想要备份数据，用 create table as 或者 insert into 即可。
注意： Oracle 9i flashback query 有一个 5 分钟的误差延时，与 scn 同步有关系，也就是说，创建的新数据如果在 5 分钟之内删除，通过 flashback query 很可能查询不到该数据。如果在 5 分钟之后删除，也需要数据创建 5 分钟之后的时间戳或者是 SCN ， 10g 已经没有这样的限制。
另外， 10g 的 falshback 的功能更加强大，除了以上的功能，还提供的 flashback database, flashback drop,flashback table,flashback version query,flashback transaction query 等功能，不仅仅可以直接查询到误操作之前的数据， flashback transaction query 甚至可以直接获得 undo_sql 的语句。

3.3 、采用最小备份恢复法恢复
从 Oracle 8 之后的新版本中引入了基于表空间的时间点恢复 (TSPITR) ，可以单独将包含错误操作的表空间恢复到指定时间，而不必对整个数据库进行不完全恢复。但是这个操作要求有以前的备份，而且运行在归档模式下。
基本原理是，把出错的表空间（假定是误删除了数据的表空间）与系统数据文件，回滚表空间放到另外一个环境（与生产环境类似）上进行时间点恢复 ( 其它不需要的表空间全部 offline 掉 ) 。把数据库恢复到出错的时间点之前，然后 open 数据库，就可以查询该表了，然后也可以 exp 或者是 imp 。
这种恢复方法不仅仅针对 delete 的数据，对 truncate 与 drop 的表一样适用。

4. 如何在裸设备与文件系统间拷贝数据文件
数据库经常使用在裸设备上面，因为这样对数据库来说，绕过 OS 的 cache 达到连续的读写性能会更好，正因为这样，我们会经常有需求，需要将数据文件从锣设备拷贝到文件系统或者是从文件系统拷贝到裸设备。
4.1 、使用 dd 进行拷贝
dd 可以实现从裸设备到文件系统或者是文件系统到锣设备的拷贝，但是使用 dd 拷贝的时候，要注意裸设备的头部保留大小，下面是常用 UNIX 的 OS Reserved Size 列表：

代码 :

UNIX OS Reserved Size

------------  ----------------

SUN Solaris 0

HP - UX 0

IBM AIX 4k

Tru64 UNIX  64k

Linux 0

注意：在 aix 中，如果使用 big VG ，并且使用 -O T 创建的 lv ，是没有 4k 大小的头部保留区域的。
然后就是要确定 dd 的 count 数目，在从裸设备拷贝到文件系统的时候可以用到，假定数据文件的块大小为 8K 的话， count 数目可以认为是 dba_data_files 中的 blocks 加 1 ，这是因为 ORACLE 建立 DATAFILE 时，在命令中 SIZE 指定的大小之外，还要在文件头另加一个 BLOCK ，叫作 “Oracle OS Header Block” ，里面保存有这个文件的逻辑块大小和文件块数等信息。这一点并不是在 RAW DEVICE 上建 DATAFILE 特有的，如果你在文件系统上建一个 DATAFILE ，指定 SIZE 1000k 的话，你用 ls -l 或 dir 命令看到的文件大小将是 1008k (DB_BLOCK_SIZE=8K) 。如

代码 :

SQL > SELECT bytes , blocks , bytes / blocks db_block_size , bytes + bytes / blocks file_size

FROM dba_data_files WHERE file_name = '/dev/rlv_data' ;

BYTES  BLOCKSDB_BLOCK_SIZEFILE_SIZE

---------- -------- ------------- ---------

4194304 5128192 4202496

所以，从裸设备 dd 到文件系统的命令为：

代码 :

AIX $ dd if=/ dev / rlv_data of =/ u01 / oradata / test . dbf bs = 4k skip = 1 count = 1026

1026 + 0 records in

1026 + 0 records out

Tru64 $ dd if=/ dev / rlv_data of =/ u01 / oradata / test . dbf bs = 8k skip = 8 count = 513

513 + 0 records in

513 + 0 records out

Other $ dd if=/ dev / rlv_data of =/ u01 / oradata / test . dbf bs = 8k count = 513

513 + 0 records in

513 + 0 records out

与此对应的从文件系统到裸设备的命令为：

代码 :

AIX $ dd if=/ u01 / oradata / test . dbf of =/ dev / rlv_data bs = 4k seek = 1

1026 + 0 records in

1026 + 0 records out

Tru64 $ dd if=/ u01 / oradata / test . dbf of =/ dev / rlv_data bs = 64k seek = 1

64 + 1 records in

64 + 1 records out

Other $ dd if=/ u01 / oradata / test . dbf of =/ dev / rlv_data bs = 1024k

4 + 1 records in

4 + 1 records out

从文件系统到裸设备可以不指定 count 数目，但是必须保证裸设备大小大于或等于文件大小 + 一个数据块大小 + 保留空间大小 (RAW DEVICE SIZE>= BD_FILE_SIZE + OS_RESERVED_SIZE + DB_BLOCK_SIZE)
注意，如果 aix 没有头部保留区域的 big vg 的 lv ，可以参考 other 的命令（第三个命令）。

4. 2 、使用 rman 来进行拷贝
使用 rman 来拷贝数据文件相对就简单多了，可以用 Rman 来轻松搞定裸设备 / 文件系统之间数据文件的迁移。在传统方法里面，我们必须对于每个 Unix 不同的 Block 大小， OS 卷管理的 overhead 的值作计算，才能非常小心的用 dd 来做这些拷贝和移动，但是现在在 Rman 的帮助下，我们可以完全忽略这些不同的地方，在所有 Unix 平台 /NT 平台上直接在 Oracle 内部实现这种数据。
如，从文件系统拷贝到裸设备

代码 :

rman > sql 'alter tablespace test offline' ;

rman > copy datafile '/u01/test/datafile/test01.dbf' to '/dev/rlvorarbs' ;

从裸设备拷贝到文件系统

代码 :

rman > sql 'alter tablespace test offline' ;

rman > copy datafile '/dev/rlvrawtest' to '/u01/test/datafile/test01.dbf' ;

值得注意的是， rman 拷贝的一方必须是数据库，因为 rman 只有连接到数据库才能进行拷贝，另外，以上的 offline 不是必须的，但是，如果想要一致性的文件，则需要加入 offline 关键字。
在数据库的迁移方面，我们可以采用 rman 把文件从数据库中拷贝到文件系统，然后从文件系统 dd 到新的数据库；或者是采用 rman 备份，把备份 restore 到新的数据库即可。

如果仅仅是在数据库内部进行数据文件的迁移（换位置），都可以单独用 dd 或者 rman 完成，但是文件迁移完成后，别忘记用如下命令更新控制文件：

代码 :

SQL > alter database rename file 'old file' to 'new file' ;

四、性能调整
1.怎么样安装与使用statpacks

1.1、安装与删除
Statspack是Oracle 8i以上提供的一个非常好的性能监控与诊断工具，更多的信息可以参考附带文档$ORACLE_HOME/rdbms/admin/spdoc.txt。statspack在使用前，必须安装，安装文件位于$ORACLE_HOME/rdbms/admin下，与此对应的还有很多其他的文件，如

代码 :

[ oracle @ db admin ]$ ls sp *

spcpkg . sql spctab . sql spdtab . sql sppurge . sql sprepsql . sql

spup816 . sql spauto . sql spcreate . sql spcusr . lis spdrop . sql spdusr . lis

sprepins . sql sptrunc . sql spup817 . sql spcpkg . lis spctab . lis

spcusr . sql spdtab . lis spdusr . sql spreport . sql spuexp . par spup90 . sql

其中包括我们安装 statspack 需要用到的 spcreate.sql ，卸载需要用到的 spdrop.sql ，清除记录需要用到的 sptrunc.sql 等，清除一段范围内的统计信息，需要提供开始快照与结束快照号的脚本 sppurge.sql 。
下面，我们就开始安装 statspack ，在次之前我们必须用系统用户登录。

代码 :

SQL > connect sys / pass as sysdba

SQL >@?/ rdbms / admin / spcreate . sql

我们需要输入三个值：用户密码，用户默认表空间与用户临时表空间

如果需要删除刚才的安装，也必须用系统用户登陆，并断开所有的 perfstat 用户的连接，然后执行

代码 :

SQL >@?/ rdbms / admin / spdrop . sql

如果是仅仅想清除表中的统计数据，执行如下语句即可

代码 :

SQL >@?/ rdbms / admin / sptrunc . sql

或者直接删除表 STATS$SNAPSHOT; 的数据，其他的表会级联删除。

1.2 、使用 Statspack
可以采用 perfstat 用户连接进去，生成快照，如果有两个以上快照，便可以生成报表了。

代码 :

sqlplus perfstat / perfstat

SQL > exec statspack . snap ; -- 进行信息收集统计，每次运行都将产生一个快照号，必须要有两个以上的快照，才能生成报表

SQL > select SNAP_ID , SNAP_TIME from STATS$SNAPSHOT ;

SQL >@?/ rdbms / admin / spreport . sql -- 输入需要查看的开始快照号与结束快照号

当然，我们也可以采用 spauto.sql 脚本的例子来创建作业，自动产生快照与生成报表，也可以采用 shell 脚本 (linux/unix) ，放在 crontab 中自动执行，如产生快照的脚本，如：

代码 :

$more statpack . sh

#!/bin/ksh

# creator: tuolei

# function: produce statpack snapshot

date

#first you must set environment variable

export ORACLE_SID = xxxx

export ORACLE_HOME =/ u01 / oracle / product / 9.2

export PATH = $ORACLE_HOME / bin : $PATH

export NLS_LANG = american_america . zhs16gbk

$ORACLE_HOME / bin / sqlplus / nolog << EOF

connect perfstat / perfstat

exec statspack . snap

exit

EOF

这里是自动产生报表，并将报表发送到指定邮箱的脚本

代码 :

$more spreport . sh

#!/bin/ksh

# creator: tuolei

# function:get statpack report

date

#first you must set environment variable

export ORACLE_SID = xxxx

export ORACLE_HOME =/ u01 / oracle / product / 9.2

export PATH = $ORACLE_HOME / bin : $PATH

export NLS_LANG = american_america . zhs16gbk

DBHOST =` hostname `

SQLPLUS = $ORACLE_HOME / bin / sqlplus

LOGFILE =/ home / oracle / logs / spreport . txt

REPFILE =/ home / oracle / worksh / spreport . lst

$SQLPLUS - S perfstat / perfstat << EOF

set echo off

set feedback off

set heading off

set pagesize 0

set linesize 1000

set trimspool on

spool $LOGFILE

select SNAP_ID from

( select SNAP_ID from stats$snapshot where INSTANCE_NUMBER = 1

order by SNAP_TIME desc ) where rownum < 3 ;

spool off ;

set echo on

set feedback on

set heading on

exit

EOF

line1 =` tail -1 $LOGFILE `

line2 =` head -1 $LOGFILE `

echo "line1 is" $line1

echo "line2 is" $line2

$SQLPLUS - S perfstat / perfstat << EOF

define begin_snap = $line1

define end_snap = $line2

define report_name = $REPFILE

@?/ rdbms / admin / spreport . sql

exit

EOF

scp $REPFILE admin @ monitor :/ tmp /${ DBHOST }. lst

ssh admin @ monitor "mail -s " ` date +%Y%m%d `${ DBHOST } spreport " test@mail.com< /tmp/${DBHOST}.lst"

--------------------------------------------------------------------------------------------------------------

1.3 、 statpack 的级别与门限
级别
statspack 的级别用来设置收集数据的类型， Oracle8i 的 statspack 一般有三种级别设置， Oracle 9i 增加到 5 种级别，默认级别都是 5 ，对于每个级别的详细描述，可以参考 perfstat 用户的表 stats$level_description 。
如果想永久的修改收集级别，可以

代码 :

SQL > execute statspack . snap ( i_snap_level => 0 , i_modify_parameter => 'true' );

如果仅仅是想修改本次的收集级别，省略 i_modify_parameter 参数即可

代码 :

SQL > execute statspack . snap ( i_snap_level => 0 );

门限
statspack 的快照用来设置收集数据的阀值
快照门限只应用于 stats$sql_summary 中收集的 SQL 语句，因为每一个快照就会收集很多数据，特别是 stats$sql_summary ，很快就会成为最大的表。
门限存储在 stats$statspack_parameter 表中，让我们了解一下各个门限值。
Executions_th 这是 SQl 语句执行的的次数（默认 100 ）
Disk_reads_th 这是 SQL 语句磁盘读入块的数量（默认 1000 ）
Parse_calls_th 这是 SQL 语句执行解析调用的次数（默认 1000 ）
Buffer_gets_th 这是 SQL 语句执行缓冲区获取数量（默认是 10000 ）
任何超过这个门限的 SQL 语句都将被记载下来
我们可以通过 statspack.modify_statspcak_parameter 函数来修改门限值

代码 :

SQL > execute

statspack . modify_statspcak_parameter ( i_buffer_gets_th = 1000 );

以上有效设置可以通过查询 stats$statspack_parameter 获得。

如果是 10g ，可以简单的用如下方法创建快照和取得报表

代码 :

exec dbms_workload_repository . create_snapshot ;

@?/ rdbms / admin / addmrpt

2. 如何优化 WEB 分页检索的语句

2.1 、不带排序的分页语句
因为不带排序功能，所以选择 rownum 的 stopkey 是最佳的选择，使用 rownum < maxnum 这样的语句中，开始的几页有非常好的效率，但是大部分人感兴趣的正是前面部分的页面。如：

代码 :

select * from (

select rownum rn , t .* from table_name t where ...... and rownum < maxnum )

where rn >= minnum

以上语句最大的特性就是使用了 rownum 的 COUNT (STOPKEY) 特性，这种特性就是寻找到满足条件的记录，就停止下来。所以，对于越靠后面的页面，效率将越差，差的效率取决于 where 中的其他条件。
2.2 、利用索引先分页后回表
有表 table_name(a,b,c,d……) ，现在有这样的一个 where 条件需要排序分页， where a=? b=? order by c 。对于这样的情况，如果 where 条件筛选出来的记录数比较多，而且在表中可能比较离散，我们可以采用创建一个有序的索引，利用索引的有序性先分页然后回表。

代码 :

SQL > create index ind_table_a_b_c on table_name ( a , b , c );

利用分析函数或者是 rownum 都可以做分页设计，
分析函数分页

代码 :

select /*+ ordered use_nl(tmp t) */ * from

( select rid from (

select row_number () over ( order by c ) rn , t . rowid rid from table_name t

where a =? and b =?)

where rn >= minnum and rn < maxnum ) tmp ,

table_name t

where tmp . rid = t . rowid

rownum 分页

代码 :

select /*+ ordered use_nl(tmp t) */ * from

( select rid from

( select rownum rn , rid from (

select rn , t . rowid rid from table_name t

where a =? and b =? order by c ) where rownum < maxnum )

where rn >= minnum ) tmp ,

table_name t

where tmp . rid = t . rowid

这样做最大的好处就是将离散的数据创建有序的索引来分页，然后回表找本页内的数据，假定满足条件的表记录数是 10000 条，每页 20 条，按照传统的分页写法，至少要回表 10000 次，最坏的情况 10000 个逻辑读（假定每行都在一个单独的块上），而新的写法可能 20 多个逻辑读即可（因为只有 20 条数据回表）
2.3 、利用索引的 stopkey 分页后回表。
我们把情况 1 与情况 2 结合起来，情况 1 是采用了 stopkey 来优化 sql 语句，情况 2 是使用了索引分页后回表这样的优化，我们可不可以把 1 与 2 结合起来，使排序的分页 SQL 语句也可以使用 stopkey 。这样既减少逻辑读，还提高语句的响应时间。
在索引中使用 stopkey 必须有这样的条件（与跳跃式索引的使用方法一样）
a 、排序的字段尽量是索引的第一个字段
b 、如果不在第一个字段，索引中的前导列的唯一值的数量能满足一定的条件（如重复值很多）
c 、优化器要知道前导列的值分布 ( 通过分析 / 统计表得到 )
如论坛的每个子论坛假定为字段 a ，是否是需要显示的帖子是字段 b （如非删除标记的帖子等），发帖时间是字段 c ，还是问题 2 中的同样的条件， where a=? and b=? order by c desc
那为了使用索引的 stopkey ，我们需要创建索引。

代码 :

SQL > create index ind_table_a_b_c on table_name ( a , c , b ); -- 如果b的条件并不固定

SQL > create index ind_table_a_b_c on table_name ( a , b , c ); -- 如果b条件固定

然后利用特定的语句，也就是问题 2 中的 rownum 分页语句，不过多了一个 desc ，这里是因为要求不一样而增加的，不影响语句的执行。

代码 :

select /*+ ordered use_nl(tmp t) */ * from

( select rid from

( select rownum rn , rid from (

select rn , t . rowid rid from table_name t

where a =? and b =? order by c desc ) where rownum < maxnum )

where rn >= minnum ) tmp ,

table_name t

where tmp . rid = t . rowid

这个时候，假定 a=? and b=? 最终能选择出来 100,000 条记录，如果全部从索引中读出来，也可能有比较大的逻辑读，但是因为索引的有序性，可以根据 order by c 逆向扫描，到满足 maxnum 的数目就停止扫描，这样可以节约大量的逻辑读。
注意，这里的 desc 仅仅是决定了是要求索引逆向扫描，或者是要创建逆向的函数索引，如果没有 desc 的关键字，则直接是顺序扫描，更简单了。

以上 1 、 2 、 3 的三种情况可以结合灵活使用，没有一定标准，主要是针对当前环境，当前 sql 做针对性的优化。