Oracle 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做针对性的优化。

posted on 2008-06-11 08:40 巍巍边疆阅读(1655) 评论(0) 编辑收藏举报