当我们提交的PL/SQL代码具有很好的一直性、可读性和严密性时，往往会给运维DBA、其他程序员和最终用户一个惊喜。不管我们之前的业务逻辑设计的如何规范、清晰；也不管我们的业务操作流程定义多么明确，请始终坚信：最终用户在决大多数情况下的操作方法都不会符合业务规则的。

所以，我们要想编写出健壮的PL/SQL代码，就必须借助调试技术，可以在关键点上提示最终用户的错误.

很多做运维的DBA都遇到过类似的问题，因为用户的要求，当DBA在做数据维护时，偶尔会清理或修改一些比较重要的关系数据，往往会导致很多的代码执行异常，并且没有任何可明确的提示，DBA不得不打开代码去阅读、分析定位问题。所以，错误处理的细节可以确保迅速定位被破坏的数据或关系。

这里说到Oracle的错误有两种：编译时和运行时错误。

下面我先用2个小的匿名块来对比一下编译时错误和运行时错误的区别。

1）编译时错误

   SQL> declare
      2   v_num number;
      3  begin
      4   v_num:=to_number('1A')
      5  end;
      6  /       
   ORA-06550: line 5, column 1:
   PLS-00103: Encountered the symbol "END" when expecting one of the following:
    
       . ( * % & = - + ; < / > at in is mod remainder not rem
        <> or != or ~= >= <= <> and or like LIKE2_
       LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
   The symbol ";" was substituted for "END" to continue.    
   SQL> 

在这段代码的第4行，我故意少写了一个分号(;),但错误提示却是在line 5，这也是经常困惑大家的地方，在这里需要提醒大家的是，PL/SQL语法分析器指向的通常是出现错误的下一行，所以有时候我们在定位问题时，不要被表象或迷惑。再看一段代码，在下面的这段代码中，我增加了一个统计语句，但实际上non_table这个表在数据库中根本不存在，可以看到，下面的错误提示还是出现在了我故意漏写的标点符号上。

    SQL> declare
      2   v_num number; 
      3  begin
      4   select count(*) into v_num from non_table;   --non_table是一个不存在的表
      5   if v_num = 0 then
      6      v_num:=to_number('1A')
      7   end if;
      8  end;
      9  /
     end if;
     *
    ERROR at line 7:
    ORA-06550: line 7, column 2:
    PLS-00103: Encountered the symbol "END" when expecting one of the following:
    . ( * % & = - + ; < / > at in is mod remainder not rem
     <> or != or ~= >= <= <> and or like LIKE2_
    LIKE4_ LIKEC_ between || multiset member SUBMULTISET_
    The symbol ";" was substituted for "END" to continue.        
    SQL> 

为什么会出现这个现象，我想大家也可能都很明白：这是因为在调用SQL语法分析器检查内部的SQL语句之前，PL/SQL语法分析器会先对整个PL/SQL程序进行遍历检查。也就是说：PL/SQL总是会先于SQL进行语法检查，只有当PL/SQL语法按照语义上解释是正确的情况下，执行PL/SQL才会显示SQL语法错误（如果有的话）

提醒：在编写PL/SQL代码时，要时刻保持清醒，否则很容易出现编译时错误，编译时错误是不能调试的，只能借助PL/SQL语法分析器去定位编译时错误。

我经常看到有的开发人员一下子蒙头写了几百行PL/SQL代码(还很自诩，觉得自己很了不起，多插一句，往往很多负责开发的manager本身就不是特别懂技术,流程就...，看到team中的人，如此高效的开发能力，颇为赞赏，结果大家一联调测试时才发现，根本不能用，于是又花更多的时间、人力去处理这个早就应该OK的issue，实在可笑，这样的情况，我想国内很多的开发team中屡见不鲜吧)，结果发现死活调试不通，最后不得不得花更多的时间去分析、定位他的代码中的问题，甚至有些情况下，不得不重新编写。

2）运行时错误

接着再看一个运行时错误，我还以上面的代码为例，大家对比一下就可以发现不同了：

    SQL> declare
      2   v_num number; 
      3  begin
      4   v_num:=to_number('1A'); 
      5  end;
      6  /
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 4    
    SQL> 

捕获、跟踪、识别并正确处理这些异常，正是我们能写出健壮的PL/SQL代码所关心的。

3）系统预定义的标准异常

下表给出了Oracle提供了一些标准的预定义异常，绝大多数大家都面熟.

4）Oracle的两个内置函数

5）异常管理

PL/SQL代码中的异常捕获和管理也是一个经常让人犯晕的地方，在pl/sql中，除了系统支持的预定义异常外，也允许用户自定义异常，用户自定义的错误也可以有2种方式实现，一种是使用EXCEPTION_INIT指令的预编译程序，另外一种是不使用预编译程序。这部分内容网上的资料很多，也很详细，我就不再唠叨了。

如果要深入理解这部分内容，需要先了解Oracle内部的错误堆栈的管理，但错误堆栈是一块极其复杂的内容，不过我们只要始终牢记：堆栈顶部的程序触发错误，而底部的程序是发生错误的地方，问题就容易理解了。幸运的是，在Oracle 10g中，Oracle对dbms_utility这个程序包做了改进，10g中的dbms_utility.format_error_backtrace过程为记录和跟踪错误序列提供了一定的灵活性，也为Oracle PL/SQL开发者带来了便利。甲骨文公司对Oracle软件的不懈的人性化的更新和发展，是大家无法舍弃她的一个很重要的原因。

下面我们看一个如何在Exception Block中捕获异常发生所在行的例子：

在这篇小技巧中，介绍一下如何在Exception Block中捕获错误发生的所在行，以便于我们准确的定位和解决问题。在实际的pl/sql开发过程中，有的pl/sql代码非常庞大，甚至彼此嵌套了好多层，当然了，Exception部分也就变的复杂起来。

这个时候，我们在日常运维或开发调试的时候，经常会根据Exception Block中抛出的raise去分析是程序的问题还是数据的问题，以便于后期改进和处理。那么有的人就很头疼，一组上K行的pl/sql，分析起来也是很头疼的，他们就希望能在出现异常的时候，知道准确的出错SQL所在行。

以前我们一般就简单的通过SQLCODE、SQLERRM来获取一些错误代码和相关的信息，从Oralce 10g开始，我们可以借助DBMS_UTILITY.FORMAT_ERROR_BACKTRACE() 函数来实现我们的目标。

下面我通过一个简单的DEMO来说一下如何调用这个函数：

conn study/study;
create table zrp (no int);
set serveroutput on

declare
  v_no number;
begin
  select no into v_no from zrp;
exception
  when others then
     dbms_output.put_line('SQL CODE:'||sqlcode||chr(10)||
                           sqlerrm||chr(10)||
                           dbms_utility.format_error_backtrace());
end;
/

测试一下看看：

SQL> declare
  2    v_no number;
  3  begin
  4    select no into v_no from zrp;
  5  exception
  6    when others then
  7       dbms_output.put_line('SQL CODE:'||sqlcode||chr(10)||
  8                             sqlerrm||chr(10)||
  9                             dbms_utility.format_error_backtrace());
 10  end;
 11  /
SQL CODE:100
ORA-01403: no data found
ORA-06512: at line 4

PL/SQL procedure successfully completed
SQL> 　

是不是很好用？

普通cursor与REF cursor还有一些大家应该都熟悉的区别，我再浪费点唾沫。

1）PL/SQL静态光标不能返回到客户端,只有PL/SQL才能利用它.ref光标能够被返回到客户端.这就是从Oracle的存储过程返回结果集的方式

2）PL/SQL静态光标可以是全局的，而ref光标则不是。 也就是说，不能在包说明或包体中的过程或函数之外定义ref光标。 只能在定义ref光标的过程中处理它，或返回到客户端应用程序.

3）ref光标可以从子例程传递到子例程，而光标则不能。 为了共享静态光标，必须在包说明或包体中把它定义为全局光标。 因为使用全局变量通常不是一种很好的编码习惯，因此可以用ref光标来共享PL/SQL中的光标，无需混合使用全局变量。
　

最后，使用静态光标--通过静态SQL（但不用ref光标）--比使用ref光标效率高，而ref光标的使用仅限于以下几种情况

把结果集返回给客户端
在多个子例程之间共享光标（实际上与上面提到的一点非常类似）
没有其他有效的方法来达到你的目标时，则使用ref光标，正如必须用动态SQL时那样
简言之，首先考虑使用静态SQL，只有绝对必须使用ref光标时才使用ref光标，也有人建议尽量使用隐式游标，避免编写附加的游标控制代码（声明，打开，获取，关闭），也不需要声明变量来保存从游标中获取的数据。这个就因人因具体的case大家去酌定吧。

游标属性
　

%FOUND： bool - TRUE if >1 row returned
%NOTFOUND：bool - TRUE if 0 rows returned
%ISOPEN： bool - TRUE if cursor still open

%ROWCOUNT：int - number of rows affected by last SQL statement

注：NO_DATA_FOUND和%NOTFOUND的用法是有区别的，小结如下：
1）SELECT . . . INTO 语句触发 NO_DATA_FOUND；
2）当一个显式光标的 where 子句未找到时 触发 %NOTFOUND；
3）当UPDATE或DELETE 语句的where 子句未找到时 触发 SQL%NOTFOUND；
4）在光标的提取(Fetch)循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件,不要用NO_DATA_FOUND。
　

本小节就罗嗦这么多，更多进一步的资料，参考Oracle pl/sql开发手册吧。

相信不少做开发或维护的DBA在找工作的时候，遇到过类似的面视问题：请简单的描述一下光标的类型，说一下普通光标和REF光标之间的区别，以及什么时候该正确应用哪一个?

这个题目，我着实难住了不少人，其实他们在具体开发的时候，也还是比较能够把握正确的光标的使用的，但就是说不出来，当然了，这与大家自身的沟流交通能力是有关系的。有的人不善于说，但做的却很好。扯的扯就走远了，最后唠叨一下：做技术这条路，能干不能说，或者说会干不会包装，路是走不"远"的.

摘要

TSPITR是大家都比较熟悉的一项功能了，我却从来没有在实际的生产系统中用过她，不成想，今天在我自己身上碰到了.

今天很郁闷，一不小心把几个原来toms_tools和toms_dbms工具包使用的字典表全部给干掉了。而且很干净，直接purge了。其实也没什么特别重要的数据，不过重新配置一边实在是很不爽的一件事。于是想到了用TSPITR技术来恢复我的这些表(都在同一个表空间data_01上)，我这个开发库也只有我自己，恢复到我删除前的那个点，也没有什么数据丢失之类的麻烦.闲着也是闲着，穷折腾呗。

周五的时候，铿锵的分公司下面有人不小心把一个表给drop掉了(9i)，我当时还嘀咕：怎么这么不小心。没想到我一个通宵下来，也会这么晕，哈哈！

最后铿锵采用DUL找回了数据，我就不玩DUL了，来个TSPITR吧。下面的过程，是我成功找会数据后的再现过程

环境说明

   我的Oracle环境是windows xp + oracle 10.2.0.2
   db_name:ora10g
   
   我以toms_tools_def字典表的恢复为例说明。
   SQL> select * from toms_tools_def;
   
   REGION NAME       RAC_SID      RAC_NAME     STORAGE_SCALE
   ------ ---------- ------------ ------------ -------------
      300 全省       0            AB                   0.001
      530 菏泽       1            A                    0.500
      531 济南       2            B                    1.000
      532 青岛       1            B                    1.300
      533 淄博       1            A                    0.700
      534 德州       2            A                    0.400
      535 烟台       1            A                    1.000
      536 潍坊       2            A                    0.850
      537 济宁       2            A                    0.800
      538 泰安       1            A                    0.400
      539 临沂       2            B                    1.000
      543 滨州       1            B                    0.400
      546 东营       2            A                    0.400
      631 威海       2            B                    0.500
      632 枣庄       1            A                    0.400
      633 日照       2            B                    0.300
      634 莱芜       1            B                    0.100
      635 聊城       1            B                    0.500  

   SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';  
   Session altered.
      
   在2008-06-22 17:52:30前后,我不小心drop掉了这个表。
   SQL> drop table toms_tools_def purge;   
   Table dropped
   
   SQL> 

   SQL> select sysdate from dual;  
   SYSDATE
   -------------------
   2008-06-22 17:52:47
   
   SQL> 

下面我借助RMAN将表空间data_01恢复到2008-06-22 17:50:00这个时间点。这样我就自然找回了我的那张表

现实生活中，循环的影子无处不在，每天太阳的东升西落，每天的工作、学习、娱乐、休息...，我们总是在不停的重复着昨天、今天、明天.
同样是每天重复的上班、下班，同样 是每天做着相似的事情,走着走着就不一样了：有的人正确的找到了入口，也顺利的进入了下一个入口；有的
人经过更多的努力，也找到了出口，但付出了更多的代价:有的人习惯了现在的模式，就愿意这样不停的重复着，做自己份内的事情,直到有人告诉
他该换换任务了，于是他很乐意的接受了下一次的重复；有的人却一直在原地不停的打转，想找到出口，才发现迷失了方向,陷入了死循环,如果没
有人拉他一般，他必将就那样一路稀里糊涂的继续晕下去，永远找不到路；有的人走着走着，就走到了十字路口,不知道该从哪个方向走。。。

有人说：写程序如写人生，程序也是有生命的，没有生命的程序，是最大的垃圾（文字垃圾）

扯远了，继续回到今天的内容，在这一小节，简单的聊聊大家熟悉的PL/SQL代码中常用的循环控制方式，我个人的主要观点是：在写plsql代码
时,一定要控制上循环的条件和出入口.

循环控制好了，可以有效的提高工作效率（大多时候批量处理总比单个作业高效的多），但循环体内的迭代码也是一个很敏感的区域，代码的
重复运行,会导致问题升级,控制不好将导致严重的后果。

先回顾一下各种循环的用法

先回顾一下各种循环的用法

1）无条件循环

loop

循环体

end loop;

2）while循环
while (循环条件)

loop

循环体

end loop;
3）数字for 循环
for <index> in begin..end loop

循环体

end loop;

4）指针for循环
for <record> in <cursor> loop

循环体

end loop;

无条件循环总是要进入循环体，while循环必须要在条件成立的时候才进入循环体,如下面的2段码，他们在功能是等价的：

A：无条件循环

    declare
      v_times number:=1;
    begin
      loop
        if v_times>5 then
           exit;
        end if;   
        dbms_output.put_line('Looped '|| v_times ||' times');
        v_times:=v_times+1;
      end loop;
    end;
    /

    B：while循环
    declare
      v_times number:=1;
    begin
      while v_times<6 loop
         dbms_output.put_line('Looped '|| v_times ||' times');
         v_times:=v_times+1;
      end loop;
    end;
    /

如果要按照次数或记录进行代码处理，我们就需要使用for循环.下面的这个情况，就比较适合使用数字for 循环：

客户提出一个需求，要求把北京地区手机号码号段为1380001尾数为0-1000的记录，凡在朝阳区(编码为BJ.CY.01)的都调拨到海淀区(BJ.HD.01),
如果不在朝阳区就不变，那么我们可以这样实现：

    begin
      for i in 0..1000 loop
          begin
            update gsm_resource 
              set store_id='BJ.HD.01'
              where gsmno='1390001'||to_char(i,'fm0000') and store_id='BJ.CY.01';
          exception 
            when no_data_found then
                 null;
          end;
      end loop;
    end;
    /

注：当然了，这样写的后果是会增加一些不必要的update（因为有些记录可能已经不在朝阳区了），但是，这是一个上千万的表，gsmno是主键，

这么做可能效率是相对较好的.

    如果客户说，我要把目前在朝阳区手机号码为138?00???88,状态为0(未使用)的号码调拨给海淀区，那么用上面的数字for循环就合适了。因为我们
事先不知道这样的号码有多少.这个时候，就需要考虑指针for循环了.
    begin
      for rec in (select rowid from gsm_resource where  gsmno like '138%00%88' and store_id='BJ.CY.01' and status=0) loop    
          update gsm_resource 
              set store_id='BJ.HD.01'
              where rowid=rec.rowid;   
      end loop;
    end;
    /
   以上几个简单的DEMO说明了一下这几种循环的适用场景，算是回顾一下了（大家都比较熟悉，不多唠叨了）
　
   本小节我主要想给大家再重申一下使用循环的一个至理格言：one way in - one way out（OWI-OWO），这一原则在结构化编程中大家都是深有体会的，
也非常适合pl/sql循环控制。
　
   OWI-OWO的思想非常重要，否则你的代码就会非常难于理解、跟踪、调试或维护。这一点我在以前运维时深有体会，也吃了不少苦头。客户说一个程序在
调用时没有任何异常，但结果就是不对，开发人员面对几千行，若干个package彼此互相调来调去实在找不出原因，我分析了代码，从出现问题的地方开始
调式，发现随着处理条件的不同，在每个procedure中总有4-5个出口，也不记录任何异常的日志.不做跟踪，光从代码上确实很难找到到底是在处理什么数
据的时候从哪个出口退了出去。没办法，根据客户的处理意图和开发人员的控制逻辑，我针对几种类型的数据做了调试，最后跟踪到了“异常”返回的出口,
而这个出口，开发人员认为一般情况下不会出现（他把宝压在了数据必须符合他的初衷的底线上，而实际上，"垃圾"数据是经常可能出现的）.最后，开发人
员按照我提供的思路，修改了这段代码，问题得到解决.
   
    看看下面这段代码：
    create or replace procedure relocate_data is
    begin
       for rec in (select rowid,store_id,status from gsm_resource where status<>'9') loop
           if rec.status='6' then
              exit;
           elsif rec.store_id is null then
                 return;
              else
                 update gsm_resource set store_id=store_id||'.02' where rowid=rec.rowid;
           end if;
       end loop;
       return;
    end;
    /
 
   这段模拟代码是我以前实际处理过的一个case的缩影，实际上他比我这个复杂了很多倍，上面的代码共有3个出口，我原来处理过的那个比这个出口
还多，中间还 掉用了好多其他procedure，包含了几个goto语句，让你看来就象是走迷宫.
   当然了，针对这段demo code，修改起来就比较简单了，我们可以直接在数据收集的时候，就把异常的情况过滤掉。
   create or replace procedure relocate_data is
   begin
     for rec in (select rowid,store_id,status 
                   from gsm_resource 
                   where status not in ('9','6') and store_id is not null)
     loop
          update gsm_resource set store_id=store_id||'.02' where rowid=rec.rowid;       
     end loop;
     return;
    end;
    /   
   下面的这个例子（来自大师Steven Feuerstein的作品）
   CREATE OR REPLACE FUNCTION matching_row (title_list_in IN title_collection_t,
                                            title_in IN VARCHAR2) RETURN PLS_INTEGER
   IS
       l_count PLS_INTEGER;
   begin
       l_count := title_list_in.COUNT;
       for indx in 1 .. l_count loop
          if title_in = title_list_in(indx) then
             return indx;
          elsif indx is null then
                exit;
          end if;
       end loop;
       return null;
   EXCEPTION
       when Exit_Function then return null;
   end;
   /
   此过程接收一个集合，并搜索该列表以查找与title_in 中数值相区配的值。使用FOR循环查找该集合中的每一行。但是在第9行，如果找到一个匹配值
，会马上自该函数退出返回。在第11行，如果集合的索引值为NULL（空），那么我就终止该循环。我的程序返回NULL，表明没有找到匹配值.
   仅存在一条路径进行该函数（标头）和一条路径进行该循环（FOR语句）。但是，共有三条途径可退出循环（读出集合中的所有路径；找到一个匹配值
并返回；当索引为NULL时退出），还有三条退出该函数的途径（在循环内部返回索引；在可执行部分的末尾返回NULL；在异常部分返回NULL）.
   现在假定该函数返回一个错误值。为了调试此程序，需要检查三个退出点，并确定哪个点返回了这一错误值及其原因.
　
   下面的代码是对这段代码的改进版本，它在结构上更为清楚，并易于管理。请注意，现在采用了WHILE循环，因为我可能希望提早停止该循环。事实上，
如果已经用完了集合中的数值，或者已经找到了一个匹配值（设置return_value 为一个非NULL 索引值），那么该循环将结束.
   CREATE OR REPLACE FUNCTION matching_row (title_list_in IN title_collection_t,
                                            title_in IN VARCHAR2) RETURN PLS_INTEGER
   IS
       l_row PLS_INTEGER;
       return_value PLS_INTEGER;
   begin
       l_row := title_list_in.FIRST;
       while (l_row is not null and return_value is null) loop
          if title_in = title_list_in (l_row) then
             return_value := l_row
          else
             l_row := title_list_in.NEXT (l_row);
          end if;
       end loop;
       return return_value;   
   end;
   /
   按照大师们的建议，我们在PL/SQL代码中使用循环时，可以考虑如下的规则，从而写出更加健壮的代码。
   1）把 for 循环看作是对任何阅读或运行你的代码的人的承诺。"我将允许FOR循环运行到完成针对IN子句中指定的每个整数或记录的操作。我不会提前
或有条件地终止它的运行". 因此，一个for循环决不应当包括EXIT语句或RETURN语句。 
   2）while循环 应当只根据直接跟在WHILE关键字之后的布尔表达式来终止。WHILE循环也不应当依靠EXIT或RETURN来停止.最后，当你期望使循环主体
甚至一次都不运行时，则利用WHILE循环。 
   3）简单循环或者（有可能）无限循环应当利用EXIT 或EXIT WHEN 语句来终止循环的执行。你应当在一个简单循环中不使用RETURN语句；你还应当力
求仅使用一个EXIT语句。而且，如果你看到一个简单循环中甚至没有包含一个EXIT，那么可能会出现一个问题（无限循环）。最后，如果希望你的循环主体
总是执行至少一次，那么采用一个简单循环，并将EXIT语句置于循环主体的底部（这是REPEAT UNTIL在PL/SQL的等价形式).

继续忽悠。。。

我们大家都和送快递的打过交道，我们都知道快递员每天会按照客户和业务的分布，事先需要规划好行程。当快递员带着客户的东西来到一栋分为A、B、C、D四座的20多层高的写字楼下，他并不会急着去送某一个客户的东西，而是根据这座大楼里客户和货品的分布情况，先规划一下自己的路线，然后以最短的路线，最快的时间完成任务。而不会是先送到A座1楼101房间客户的单子，然后返回公司再去拿2楼的货，然后。。。，这样下去我想那个快递公司也快关门了。

大家都到ATM上取过钱吧？公司楼下周边工行、农行、招行、建行、及其他一些商业银行自助ATM机构，但分布在马路的不同方向和位置，有的离的近，有的离的远，有的支持银联有的不支持，你手上持有一张招行卡，工行离你最近，但你发现，这里经常需要排队，人较多，每次可能要等10-20分钟，农行离的稍微远一些，但一般人少，可以节省一点时间，但同样每个月跨行交易超过3笔每笔交易需要收2元的手续费，招行离你最远，需要步行10分钟，穿过一个十字路口，安全系数降低了，但通过招行取钱的话，不需要花手续费.

在这样的背景下，你几乎每个月都需要取好几次钱，每次的数目也不一样，而你又是一个精打细算、工作很忙的人，那么你不是就要好好的安排一下你的取钱计划呢？

在实际的PL/SQL代码编写过程中，和其他语言一样，如同上面的生活场景，根据实际的业务逻辑，我们很少写出那些单一的、线形控制的业务逻辑，现实情况往往是我们需要根据一些既定的或可计算的条件判断方式做出不同的处理.

在PLSQL语言中，Oracle也提供了相应的解决方案，让我们的代码和逻辑看上去透明和易于理解，那就是IF、ELSIF或CASE

看一下下面这段代码：

    create or replace procedure get_cash_cost(p_bankcode in varchar2) is
    begin
       if p_bankcode='NH' then
          dbms_output.put_line('Total Cost:200');
       end if;
       if p_bankcode='GH' then
          dbms_output.put_line('Total Cost:300');
       end if;
       if p_bankcode='ZH' then
          dbms_output.put_line('Total Cost:100');
       end if;
       if p_bankcode='JH' then
          dbms_output.put_line('Total Cost:400');
       end if;
       if p_bankcode='QT' then
          dbms_output.put_line('Total Cost:500');
       end if;
    end;
    /

根据所选的银行，get_cash_cost自动计算出到那个银行半业务的成本，可以清楚的看到，我这里list出来的条件都是相互独立的，是互相排斥的，彼此之间没有关系，但我在每次决定走哪条路线前，我需要计算每一个IF条件，这样是不是有点浪费？是不是增加了资源上的开销？

针对这种情况，改成下面的方式是不是效果要好一些呢？

    create or replace procedure get_cash_cast(p_bankcode in varchar2) is
    begin
       if p_bankcode='NH' then
          dbms_output.put_line('Total Cost:200');
       elsif p_bankcode='GH' then
             dbms_output.put_line('Total Cost:300');
          elsif p_bankcode='ZH' then
                dbms_output.put_line('Total Cost:100');
            elsif p_bankcode='JH' then
                  dbms_output.put_line('Total Cost:400');
               elsif p_bankcode='QT' then
                     dbms_output.put_line('Total Cost:500');
                  else
                     dbms_output.put_line('Unkown');
       end if;
    end;
    /

因为所有的条件都是独立的，那么现在只要有一个条件被计算为真，则所有其它条件计算都将被忽略，是不是节省了一些开销呢？很多时候资源就是被这些无谓的计算浪费掉的。很明显下面的两个调用

exec get_cash_cost('NH');

exec get_cash_cost('Unkown')

所需要的时间是不一样的，这个在通过Oracle 10g的time model statistics,可以得到一个验证。

对于上面的IF ELSIF子句，你是不是觉得看起来很费劲呢？很多时候，开发人员都有类似的通病，写一段代码，就一定要写的拐弯摸角，就是让你看不明白，显得好象自己很"牛"的样子，和你玩做迷藏。给后期的维护和代码升级带来了很大的麻烦。

所以我经常建议我的那些朋友在coding的时候，要尽量使自己的代码透明、结构清晰，易于理解。否则你不仅是让你的"接班人"头疼，大部分情况下你将自食苦果。

对于上面采用IF ELSIF子句的写法，从9i开始以后，我们可以使用更清晰的case（我在另外一篇文章中已经详细的介绍了case语句的使用方法，大家也可以参考）语句来代替，让你的代码上去很美。

    create or replace procedure get_cash_cost(p_bankcode in varchar2) is
    begin
       case p_bankcode
         when 'NH' then
           dbms_output.put_line('Total Cost:200');
         when 'GH' then
           dbms_output.put_line('Total Cost:300');
         when 'ZH' then
           dbms_output.put_line('Total Cost:100');
         when 'NH' then
           dbms_output.put_line('Total Cost:400');
         when 'QT' then
           dbms_output.put_line('Total Cost:500');

         else

           dbms_output.put_line('Unkown');
       end case;
    end;
    /

在真正的业务系统逻辑条件控制中，条件一般要复杂，有的时候甚至需要很多个条件通过一些的与|或|非来做一些复杂的判断。个人的习惯是尽量不要把这些负责的逻辑放在一层去判断，可以根据条件的优先级和过滤范围，分为多层去处理，对程序的可读性更有好处。

看下面的一段代码，理解起来是不是很痛苦：

    declare
      A boolean := TRUE;
      B boolean := FALSE;
      C boolean := FALSE;  
    begin
      if A and not (B or C) then
           dbms_output.put_line('A and not (B or C)');
      elsif A and (B or C) then
            dbms_output.put_line('A and (B or C)');
         elsif not A and not (B or C) then
                dbms_output.put_line('not A and not (B or C)');
             elsif not A and (B or C) then
                   dbms_output.put_line('not A and (B or C)');
      end if;
    end;
    /

我将上面的复杂的逻辑判断分为如下的2层，理解起来是不是要好多了：

    declare
      A boolean := TRUE;
      B boolean := TRUE;
      C boolean := FALSE;  
    begin
      if A then
         if (B or C) then
             dbms_output.put_line('A and (B or C)');
         else
             dbms_output.put_line('A and not (B or C)');
         end if;
      else
         if (B or C) then
             dbms_output.put_line('not A and (B or C)');
         else
             dbms_output.put_line('not A and not (B or C)');
         end if;
      end if;
    end;
    /  

这一小节就到这里了，健壮的PL/SQL语句不仅仅要设计合理，同时也要结构清晰、通俗易懂。往往这些小的细节方面，可以帮助我们正确的代码.

备注：我这里为了模拟场景，数据抽取出来仅仅做一个display，用来模拟其他业务。

所以，项目一开始的时候，我们的开发人员是这样设计代码的：

第一步：创建一张表zrp,并导入原始的员工信息

create table zrp
( id number,
name varchar2(32),
address varchar2(32)
);
insert into zrp values(1,'Oracle','china beijing');
insert into zrp values(2,'IBM','china beijing');
insert into zrp values(3,'HP','china beijing');
commit;

注：字段ID,NAME,ADDRESS是模块要访问的信息

第二步：定义一个package.

该package包括一个REF CURSOR类型声明和一个函数，函数用来返回zrp表中所有行的所有列.

create or replace package apk_demo IS
TYPE zrp_rc IS REF CURSOR return zrp%ROWTYPE;
function get_info return apk_demo.zrp_rc;
end apk_demo;
/
create or replace package body apk_demo IS
function get_info RETURN apk_demo.zrp_rc IS
v_ret apk_demo.zrp_rc;
begin
open v_ret for select * from zrp;
return v_ret;
end get_info;
end apk_demo;
/

第三步：编写proc_demo模块，实现数据抽取

create or replace procedure proc_demo IS
v_zrp_buf apk_demo.zrp_rc;
v_id number;
v_name varchar2(32);
v_address varchar2(32);
begin
v_zrp_buf := apk_demo.get_info;
loop
fetch v_zrp_buf INTO v_id,v_name,v_address;
exit when v_zrp_buf%NOTFOUND;
dbms_output.put_line(v_id||'#'||v_name||'#'||v_address);
end loop;
close v_zrp_buf;
end;
/

可以看到，在proc_demo模块中对列的信息使用硬编码的形式，这在特定的情况下是要出"问题".（见第一节）

注：我这里提到的问题，其实也不能算是特别大的问题，只要你能cover的住就不能叫问题。

发布后，模块工作的很好。下面是测试效果

    SQL> create table zrp
      2  ( id number,
      3    name varchar2(32),
      4    address varchar2(32)
      5  );
    
    Table created
    
    SQL> insert into zrp values(1,'Oracle','china beijing');  
    1 row inserted
    
    SQL> insert into zrp values(2,'IBM','china beijing');    
    1 row inserted
    
    SQL> insert into zrp values(3,'HP','china beijing');    
    1 row inserted
    
    SQL> commit;    
    Commit complete
    
    SQL> 
    SQL> create or replace package apk_demo IS
      2       TYPE zrp_rc IS REF CURSOR return zrp%ROWTYPE;
      3       function get_info return apk_demo.zrp_rc;
      4   end apk_demo;
      5  /
    
    Package created
    
    SQL> create or replace package body apk_demo IS
      2      function get_info RETURN apk_demo.zrp_rc IS
      3         v_ret apk_demo.zrp_rc;
      4      begin
      5         open v_ret for select id,name,address from zrp;
      6         return v_ret;
      7      end get_info;
      8   end apk_demo;
      9  /
    
    Package body created
    
    SQL> 
    SQL> create or replace procedure proc_demo IS
      2     v_zrp_buf apk_demo.zrp_rc;
      3     v_id number;
      4     v_name varchar2(32);
      5     v_address varchar2(32);
      6  begin
      7     v_zrp_buf := apk_demo.get_info;
      8     loop
      9       fetch v_zrp_buf INTO v_id,v_name,v_address;
     10         exit when v_zrp_buf%NOTFOUND;
     11         dbms_output.put_line(v_id||'#'||v_name||'#'||v_address);
     12     end loop;
     13     close v_zrp_buf;
     14  end;
     15  /
    
    Procedure created
    
    SQL> set serveroutput on 
    SQL> exec proc_demo;
    
    1#Oracle#china beijing
    2#IBM#china beijing
    3#HP#china beijing
    
    PL/SQL procedure successfully completed
    
    SQL> 

系统运行了半年后，突然有一天，一个客户说，我的另外一个报表模块中，需要提供员工的联系信息，要求在员工信息表(zrp)增加一个字段，存放员工的手机号码。而且，只有我这个一个报表需要这个手机号码字段，不影响其他现有模块。

于是，我们的需求分析人员，把客户的需求反馈到了开发team,而这个开发team是一个不太规范的team,设计人员也没有全局的分析这样一个“微小”的动作会导致什么后果，直接下单子给报表开发小组，修改了报表代码，并通知实施team按照预定的时间实施了该变更。

DBA按照change的要求，修改了该表的结构，增加了一列

    SQL> alter table zrp add phone varchar2(11);   
    Table altered
    
    SQL> select * from zrp;
    
            ID NAME          ADDRESS           PHONE
    ---------- ------------- ----------------- --------
             1 Oracle        china beijing     
             2 IBM           china beijing     
             3 HP            china beijing     
    
    SQL> select object_name,object_type,status from user_objects where status='INVALID';
    
    OBJECT_NAME           OBJECT_TYPE         STATUS
    --------------------- ------------------- -------
    APK_DEMO              PACKAGE BODY        INVALID
    APK_DEMO              PACKAGE             INVALID
    PROC_DEMO             PROCEDURE           INVALID
    SQL> alter procedure proc_demo compile;
    
    Warning: Procedure altered with compilation errors
    
    SQL> show error
    Errors for PROCEDURE STUDY.PROC_DEMO:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------------
    9/6      PLS-00394: wrong number of values in the INTO list of a FETCH statement
    9/6      PL/SQL: SQL Statement ignored
    
    SQL> 

接着，DBA发现，系统中有一些依赖表zrp的对象invalid了，然后他尝试去compile 他们，但他发现有几个对象始终无法compile成功。很明显，我上面的函数get_info在返回的时候是zrp的一行的所有列，而之前因为特定的业务需求，我指定了列名，同时一个proc_demo在fetch的时候，在修改了结构，必须要包含4个变量，而现在into到了3个变量中。所以他们变成了invalid.

这个时候，报表的那个及其他模块均测试OK了。就剩这2个东西编不过去了，没办法，只能叫开发人员过来调整了，回退是不可能的，客户要求今天必须上。于是一通电话后（在一个走流程管理和版本控制相对严格的的公司里，这个协调是个头疼的事儿），开发人员气喘吁吁的赶到了客户现场，分析了问题后修改并重新发部了该脚本。

这样的场景，不知道大家遇到的多不多，我想做运维但每权修改代码的DBA还是比较有体会的。

那么，这样的问题，我们在设计pl/sql代码的时候如何避免呢？

其实Oracle对这个问题有一个很简单的解决方案：不要使用单个变量的列表，而采用记录结构。

下面是针对这个问题的修改方案：

    create or replace package body apk_demo IS
        function get_info RETURN apk_demo.zrp_rc IS 
           v_ret apk_demo.zrp_rc; 
        begin 
           open v_ret for select * from zrp;
           return v_ret;
        end get_info; 
     end apk_demo;
    /
    
    create or replace procedure proc_demo IS 
       v_zrp_buf apk_demo.zrp_rc; 
       v_zrp_row zrp%rowtype;   
    begin
      v_zrp_buf := apk_demo.get_info;
      loop
        fetch v_zrp_buf INTO v_zrp_row;
        exit when v_zrp_buf%NOTFOUND;
        dbms_output.put_line(v_zrp_row.id||'#'||v_zrp_row.name||'#'||v_zrp_row.address);  --模拟数据处理
      end loop;
      close v_zrp_buf;
    end;
    /
    下面看看这样修改后的效果：

     SQL> create or replace package body apk_demo IS
      2      function get_info RETURN apk_demo.zrp_rc IS
      3         v_ret apk_demo.zrp_rc;
      4      begin
      5         open v_ret for select * from zrp;
      6         return v_ret;
      7      end get_info;
      8   end apk_demo;
      9  /
    
    Package body created
    
    SQL> create or replace procedure proc_demo IS
      2     v_zrp_buf apk_demo.zrp_rc;
      3     v_zrp_row zrp%rowtype;
      4  begin
      5    v_zrp_buf := apk_demo.get_info;
      6    loop
      7      fetch v_zrp_buf INTO v_zrp_row;
      8      exit when v_zrp_buf%NOTFOUND;
      9      dbms_output.put_line(v_zrp_row.id||'#'||v_zrp_row.name||'#'||v_zrp_row.address);
     10    end loop;
     11    close v_zrp_buf;
     12  end;
     13  /
    
    Procedure created
    
    SQL> set serveroutput on
    SQL> exec proc_demo
    
    1#Oracle#china beijing
    2#IBM#china beijing
    3#HP#china beijing
    
    PL/SQL procedure successfully completed
    下面的代码用来再次模拟增加的字段支撑其他功能，看看对proc_demo模块是否有影响：
    SQL> alter table zrp add zipcode varchar2(6);
    
    Table altered
    
    SQL> alter package apk_demo compile body;
    
    Package body altered
    
    SQL> alter procedure proc_demo compile;
    
    Procedure altered
    
    SQL> exec proc_demo
    
    1#Oracle#china beijing
    2#IBM#china beijing
    3#HP#china beijing
    
    PL/SQL procedure successfully completed
    
    SQL>   

    OK，到现在，proc_demo是不是显得健壮一些了呢？

在我以前做的一个系统中，有5-6个核心业务数据库，各个数据库之间因为业务需要，有很多数据需要交互，特别是一些关键的数据模型在设计上，各个数据库之间含义是一致的.典型的比如app_col(我这里用这个app_col代表某一类字段)这样的字段,在各个数据库上的很多表结构中均包含这样的关键字段。但实际情况却是很让人头疼，我们的设计人员会设计出行行色色的定义来.
比如在ora_a数据库上 table_aa.app_col number(4),
table_ab.app_col char(3),
table_ac.app_col varchar2(5),
table_ad.app_col number,
table_ae.app_col varchar2(4)
ora_b数据库上 table_ba.app_col number,
table_bb.app_col char(4),
table_bc.app_col varchar2(6)
...
等等，其实，在整个系统（所有数据库）中，这个app_col column的含义都是一样的，但因为各个模块的设计人员随意性和理解上的偏差，导致系统中出现了众多的定义方式. 最终导致的后果就是各个模块之间交换数据带来了很大的不变，同样的一段pl/sql代码，到另外一个db上就invalid了，就需要调整代码中变量的类型定义了。

可以设想一下：一个已经创建了包括 5000个表/per db和 20000 个模块的综合应用系统. 因为业务发展的需要，客户要求对其进行改造(实际上这样的需求在通信行业是很正常的),经过系统设计专家的分析，如果为了满足本次业务需要，我们需要在整个系统中的某几个相关的DB上，对一些表a.A、b.B、b.C上增加一个新列，同时需要对其中的20多个模块代码进行修改。其中有50个模块引用了这些需要被修改结构的表中的数据.

实际情况可能更糟糕，我们的系统设计人员，可能根本无法准确的给出哪些模块中引用那些本次业务支撑需要修改结构的表中的数据.这样我们就不能提前系统的修改、测试、编译并精确的发布版本了。可能要投入到运行后才发现某几个模块不work,而这几个模块又被其他的模块所引用，最终导致很多模块均invalid. 更为糟糕的时，一些在后台运行的batch job,一个月只在月底或月初跑一次，所以很容易别“遗忘”掉。等问题出现修改并正确的处理数据后，客户宝贵的时间（延迟了报表的交付时间，是要影响考核的）因此而被耽误，我想那个老板不发飑是乎是不太可能的。

当然了，最终我们也是可以发现并修改、compile所有invalid的模块的，但对于一个高可用（业务连续性）要求十分严格的业务系统，客户可能很难接受这样的事实。同时我们也要浪费掉大量的人力来排查定位问题，效率严重下降.倒行逆施的后果就是要付出惨重的代价。

下面看一段代码：
create table test
( id number primary key,
name varchar2(32),
address varchar2(32),
phone varchar2(11)
);
create or replace procedure proc_test (p_id in number) IS
v_buf VARCHAR2(200);
begin
select name||','||address||','||phone into v_buf from test where id = p_id;
/*其他的业务处理代码部分...*/
end;
/

在上面的这段代码中，我们可以说没有问题，也可以说存在一些"潜在"(需要一定条件才能触发)的问题.

假定因为业务的需要，客户希望将来支持ID中包含字符的工号信息，所以要提前做好业务支撑。那么我们就需要修改ID字段的数据类型由NUMBER变为VARCHAR2,那么这个时候我的这个proc_test程序将变为invalid，并在应用第一次调用他的时候自动重新编译（一般情况下，DBA会提前发现编译好）,而不需要程序员进行任何修改。

因为这一特性，也可能是某些经验不够丰富(没有精力去认证分析)的开发人员忽略掉一个细，就是在将来不能使用包含有字符的员工ID来调用这一过程，最终在客户面前表现出来的现象就是不work.

注：因为这个业务是将来才去支撑，所以发布的时候也不会引起任何问题，测试也没有问题，过了2个月后，客户真正使用的时候才发现这个问题，类似的case见过了太多。

如果把代码改成如下的形式，效果是不是好很多呢？
create or replace procedure proc_test (p_id in test.id%TYPE) IS
v_buf VARCHAR2(200);
begin
select name||','||address||','||phone into v_buf from test where id = p_id;
/*其他的业务处理代码部分...*/
end;
/
现在将参数p_id的数据类型直接锁定在该表的列的数据类型上。每次编译过程时，PL/SQL都将从数据字典中查询该列的数据类型，并将其用于被编译的代码中。所以，如果真的将该列设定为字母数字型的，那么也不会对此代码造成任何伤害.

对比上面的写法，是不是健壮了许多？

再看上面的一行硬编码 v_buf varchar2(200);

一般情况下，这个也不会导致什么问题，v_buf变量是一个用来存放导出数据的缓存，原来的test表结构中name,address,phone三个字段的最大长度加起来为75，所以我们给200 byte的长度是OK的了。
突然某一天，客户又提需求了：我要求我的ID对应完整的联系信息，address要精确到门牌号码。很不幸，我们又得修改我们的表结构和代码了，是不是感觉很烦？

简单的我们直接重新声明v_buf为
v_buf varchar2(1000);

如果我的应用中有上百个类似的调整呢？修改100段代码的工作量是不是让你发疯？而且你都不能保证你的修改是否考虑周全。

对于这种情况，Oracle提供了一个很好的解决方案：使用SUBTYPE。SUBTYPE是另一个已定义的数据类型的一个别名。例如，针对本案例，定义一个listbuffer_t的新数据类型，它其实就是VARCHAR2(1000)的另一个名字：
CREATE OR REPLACE PACKAGE app_def AS
SUBTYPE listbuffer_t IS VARCHAR2 (1000);
END app_def;

有了这个SUBTYPE，可以将我的声明调整为：
v_buf app_def.listbuffer_t;
这样我就从proc_test过程的声明中去除了硬编码。

如果大家在声明类似的变量时，都利用预定义的、应用程序特定的数据类型，如listbuffer_t，那么我们就可以错误点削减到代码中的一个位置，比如本案例中的app_def package中。

调整起来是不是也很方便？如果1000字节还不够用，那么我只需要修改app_def中的这一行代码，并重新编译就OK了。是不是相对比较健壮呢？

修改后的解决方案：
SQL> create table test
2 ( id number primary key,
3 name varchar2(32),
4 address varchar2(32),
5 phone varchar2(11)
6 );
Table created
SQL>
SQL> CREATE OR REPLACE PACKAGE app_def AS
2 SUBTYPE listbuffer_t IS VARCHAR2 (1000);
3 END app_def;
4 /
Package created

SQL> create or replace procedure proc_test (p_id in test.id%TYPE) IS
2 v_buf app_def.listbuffer_t;
3 begin
4 select name||','||address||','||phone into v_buf from test where id = p_id;
5 /*其他的业务处理代码部分...*/
6 end;
7 /
Procedure created
SQL>

借助上面的测试，顺便介绍一下如何move IOT MAPPING TABLES。移动IOT MAPPING TABLES的语法是：

ALTER TABLE <TABLE_NAME> MOVE MAPPING TABLE TABLESPACE <TBS_NAME>;

下面做个简单的测试

SQL> select segment_name,segment_type,tablespace_name from user_segments where segment_name like '%MAP%';

SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME
------------------------- ------------------ ---------------------
SYS_IOT_MAP_56839         TABLE              DATA_01
PK_IOT_MAPPING_TEST       INDEX              DATA_01

SQL> alter table iot_mapping_test move tablespace users;

Table altered

SQL> select segment_name,segment_type,tablespace_name from user_segments where segment_name like '%MAP%';

SEGMENT_NAME                    SEGMENT_TYPE       TABLESPACE_NAME
------------------------------- ------------------ -----------------
SYS_IOT_MAP_56839               TABLE              DATA_01
PK_IOT_MAPPING_TEST             INDEX              USERS

可以看到，常规的move操作只是表索引移了。

SQL> alter table iot_mapping_test move mapping table tablespace users;

Table altered

SQL> select segment_name,segment_type,tablespace_name from user_segments where segment_name like '%MAP%';

SEGMENT_NAME              SEGMENT_TYPE       TABLESPACE_NAME
------------------------- ------------------ ---------------------
SYS_IOT_MAP_56839         TABLE              USERS
PK_IOT_MAPPING_TEST       INDEX              USERS

SQL> 
OK，到这里mapping table被成功的移动了

SQL> create table IOT_MAPPING_TEST (name varchar2(32),
  2                                 no number,
  3                                 constraint PK_IOT_MAPPING_TEST primary key(no)
  4                                 )
  5  organization index
  6  mapping table;

Table created

SQL> 
SQL> select table_name,iot_name,iot_type from user_tables where table_name='IOT_MAPPING_TEST';

TABLE_NAME                     IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
IOT_MAPPING_TEST                                              IOT

SQL> select table_name,iot_name,iot_type from user_tables where iot_type='IOT_MAPPING';

TABLE_NAME                     IOT_NAME                       IOT_TYPE
------------------------------ ------------------------------ ------------
SYS_IOT_MAP_56839              IOT_MAPPING_TEST               IOT_MAPPING

SQL> 
SQL> alter table iot_mapping_test shrink space;

Table altered

SQL> alter table SYS_IOT_MAP_56839 shrink space;

alter table SYS_IOT_MAP_56839 shrink space

ORA-28668: cannot reference mapping table of an index-organized table

SQL> 

章节索引

Segment Shrink的基本要求
Segment Shrink在不同版本的一些限制
Segment Shrink的工作机制
一个简单的Segment Shrink的DEMO
预测索引空间的需要
Shrink 不支持IOT MAPPING TABLES的测试
如何move IOT MAPPING TABLES

附：本章中用到的主要脚本
　

在Oracle 10g之前，我想很多DBA都遇到过数据重组这样的需求，从而改善系统的性能，虽然在 9i中也提供了诸多的特性可以帮助DBA去完成类似的工作，但对一个真正的7*24的高并发OLTP业务系统，这是一个十分棘手的问题，DBA们不得不考虑诸的潜在风险，以保证对生产系统没有任何影响。比如空间的需求，是否可以停止这个表上的相关应用，如果不可以怎么办？是否会导致某些对象失效？是否会导致锁等待，等等，这是我们以前在着手一个表被重组前不得不考虑的问题。

相关参考《Oracle 9i中数据重组优化的方法》

从Oracle 10g开始，Oracle提供了Segment Shrink 选项，这是Oracle 软件在不断的走向成熟和完善的一个标志，有了这个特性，对DBA来说我们就可以更有效的管理我们的空间，从而提供更好的访问性能。

部分章节陆续在本blog上发布。。。欢迎大家仍砖头^|^

问题源至：http://www.itpub.net/thread-956623-1-1.html
内容如下：
free space 大于db file的大小

遇到一个奇怪的问题， db file free space的大小大于file的大小。导致在toad下看到的使用率为负的

select sum(bytes) from dba_free_space where file_id=8;
result: 35672489984 (这个大小已经达到了数据文件的max)

select bytes from dba_data_files where file_id=8;
result: 10171187200

这种问题，一般情况下，可以往bug上去考虑，后来和puber在msn上聊，得知是Oracle 10.2.0.2,
正好我的客户的Oracle版本都是这个，所以就去查了一下，结果和预料的一样，是一个bug所致。

Details:
The values of FILE_ID / REL_FNO may be wrong in DBA_FREE_SPACE if a table has been dropped but exists in the RECYCLE BIN

Workaround: Purge the dropped table from the recycle bin.

When the table is dropped that the freed up space is accounted for in the wrong datafile and hence we see the size of datafile bigger than its defined size. If you purge the recyclebin then the incorrect accounting is fixed.

Solution
This bug is fixed in next release of Oracle 11 and is also fixed in upcoming patchsets 10.2.0.4 & 10.1.0.6. There are few one-off patches available, please check metalink for existence of one-off patch for your patchset level and OS platform.

As a simple workaround you can purge the dropped table from the recycle bin.

最后，也是建议puber通过查询dba_recyclebin并purge后问题解决,这个版本上的bug真不是一般的少啊。

这个问题大家很熟悉了，但我从来没有设备能做这样的测试,这次终于得到一次巧合,做了一次测试,也验证了相关的一些理论.

关于32windows上内存寻址的问题，这节就不介绍了，网上到处都是.下面就我测试的具体信息show一下，或许能对部分朋友有所帮助.

测试环境：

Windows 2003 Server Enterprise (32位)

2 cpu + 8G(RAM) + Oracle 10.2.0.1(32位)

db_block_size=8192

关键Oracle init参数文件说明：

*.use_indirect_data_buffers=true
*.db_block_buffers=720896
*.java_pool_size=4194304
*.large_pool_size=4194304
*.shared_pool_size=2147483648
*.streams_pool_size=0
*.compatible='10.2.0.1.0'
*.db_block_size=8192
*.db_file_multiblock_read_count=16
*.db_name='DBTest'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=203423744
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
　

说明：为了使用AWE，在Oracle 10g中不能使用ASMM，所以需要设置sga_target=0.

测试内容和结果

准备工作

1）enabling support at the OS level

编辑Windows安装盘的根目录下的boot.ini文件，增加如下一行内容

multi(0)disk(0)rdisk(0)partition(1)WINNT="Microsoft Windows 2003 Server Enterprise" /3GB /PAE

说明：/3GB enables a single process to attach 3GB of memory.
/PAE enables access to memory above 4GB to be mapped for a process.

这两个参数可以一起使用，也可以单独使用。

if mem<=4G,就只需要设置/3GB即可

if mem> 4G,/3GB /PAE可以同时设置（这个时候最大只能使用到16G，如果要使用更多的内容，需要去掉/3GB参数）

授权：给运行Oracle数据库的操作系统帐户，授予"Lock Pages in Memory"的系统权限

重起windows系统

2）enabling AWE Support at the Database/Instance Level

设置USE_INDIRECT_DATA_BUFFERS=TRUE

设置DB_BLOCK_BUFFERS参数（不能使用db_cache_size参数）

3）修改注册表参数AWE_WINDOW_MEMORY

如果不设置这个参数，默认是1G

AWE_WINDOW_MEMORY参数有一个最小值计算公式，我们设置的value不能小于这个最小值

min(AWE_WINDOW_MEMORY)=(4096 * db_block_size * _db_block_lru_latches)/8

其中 db_block_size就是块的大小（在这个测试中8K)

_db_block_lru_latches在10g中是一个隐藏的下化线参数，可以通过如下的脚本得到：

col ksppinm format a32 col ksppstvl format a20 select ksppinm, ksppstvl from x$ksppi x, x$ksppcv y where x.indx = y.indx and x.inst_id=userenv('instance') and y.inst_id=userenv('instance') and ksppinm='_db_block_lru_latches';

_db_block_lru_latches这个参数也可以通过一个公式计算出来：

_db_block_lru_latches=(max buffer pools * sets_per_pool)

其中： max buffer pools是一个常量=8

sets_per_tool是一个变量，它的值取决于是否enable VLM(即是否设置了use_indirect_data_buffers=true)

if VLM enabled then

sets_per_tool=2*cpu_count

else

sets_per_tool=cpu_count/2

end if;

建议：建议使用前种方法直接使用Oracle自己计算出来的结果。

根据这个测试环境，我们得到的

min(AWE_WINDOW_MEMORY)=(4096 * db_block_size * _db_block_lru_latches)/8
=(4096 * 8192 * (8*2*2))/8

=134217728(byte)

我在实际测试中，注册表中设置了这个值为234217728

测试具体过程记录

第一轮：(cache size(G)=3 shared_pool_size=213909504)

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 3439329280 bytes
Fixed Size                  1247564 bytes
Variable Size             209716916 bytes
Database Buffers         3221225472 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL> show parameter db_block
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
db_block_buffers                     integer     393216
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
SQL>

SQL> alter database open;
数据库已更改。

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
----------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS

SQL> create user thomas_zhang identified by thomas_zhang default tablespace users;
用户已创建。

SQL> grant connect,resource to thomas_zhang;
授权成功。

SQL> alter user thomas_zhang quota unlimited on users;
用户已更改。

SQL> conn thomas_zhang/thomas_zhang
已连接。

SQL> create table test (no int);
表已创建。

SQL> insert into test select rownum from dual connect by rownum<10;
已创建9行。

SQL> commit;
提交完成。

SQL> select *from test;
        NO
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

已选择9行。

SQL> drop table test purge;
表已删除。


SQL> conn /as sysdba
已连接。
SQL> show parameter service

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      DBTest
SQL> host tnsping dbtest

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 12-3月 -2008 20:12:14

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

已使用的参数文件:D:oracleproduct10.2.0db_2networkadminsqlnet.ora

已使用 TNSNAMES 适配器来解析别名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = IP10128325)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DBTest)))

OK (30 毫秒)

SQL> conn thomas_zhang/thomas_zhang@dbtest;
已连接。
SQL> select sysdate,user from dual;

SYSDATE        USER
-------------- -------------------
12-3月 -08     THOMAS_ZHANG

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

第二轮：(cache size(G)=3.5 shared_pool_size=213909504)

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 3976200192 bytes
Fixed Size                  1247564 bytes
Variable Size             209716916 bytes
Database Buffers         3758096384 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。

SQL> conn thomas_zhang/thomas_zhang@dbtest;
已连接。
SQL> create table a (a int, b int);
表已创建。

SQL> insert into a values(1,1);
已创建 1 行。

SQL> commit;
提交完成。

SQL> select *from a;

         A          B
---------- ----------
         1          1

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

第三轮：(cache size(G)=4 shared_pool_size=213909504)

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 4513071104 bytes
Fixed Size                  1247564 bytes
Variable Size             209716916 bytes
Database Buffers         4294967296 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。

SQL> conn thomas_zhang/thomas_zhang@dbtest;
已连接。
SQL> select *from a;

         A          B
---------- ----------
         1          1

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

第四轮：(cache size(G)=4.5 shared_pool_size=213909504)

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 5049942016 bytes
Fixed Size                  1247564 bytes
Variable Size             209716916 bytes
Database Buffers         4831838208 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL>
SQL> alter database open;
数据库已更改。

SQL> conn thomas_zhang/thomas_zhang@dbtest
已连接。

SQL> select *from a;
         A          B
---------- ----------
         1          1

SQL> insert into a values(3,3);
已创建 1 行。

SQL> commit;
提交完成。

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

第五轮：(cache size(G)=5 shared_pool_size=283458240)第七轮：(cache size(G)=6 shared_pool_size=283458240)

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 5679087616 bytes
Fixed Size                  1247828 bytes
Variable Size             301991340 bytes
Database Buffers         5368709120 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。

SQL> conn thomas_zhang/thomas_zhang@dbtest;
已连接。
SQL> select *from a;

         A          B
---------- ----------
         3          3
         1          1

SQL> insert into a values(5,5);
已创建 1 行。

SQL> commit;
提交完成。

SQL> conn /as sysdba
已连接。
SQL> show parameter db_block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
db_block_buffers                     integer     655360
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。

SQL>

第六轮：(cache size(G)=5.5 shared_pool_size=283458240)
SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 6215958528 bytes
Fixed Size                  1247828 bytes
Variable Size             301991340 bytes
Database Buffers         5905580032 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 5928M
sga_target                           big integer 0
SQL> conn thomas_zhang/thomas_zhang@dbtest;
已连接。
SQL> select *from a;
         A          B
---------- ----------
         3          3
         1          1
         5          5

SQL> insert into a values(6,6);
已创建 1 行。

SQL> commit;
提交完成。

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 6752829440 bytes
Fixed Size                  1247828 bytes
Variable Size             301991340 bytes
Database Buffers         6442450944 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 6440M
sga_target                           big integer 0
SQL> conn thomas_zhang/thomas_zhang@dbtest;
已连接。
SQL> select *from a;

         A          B
---------- ----------
         3          3
         6          6
         1          1
         5          5

SQL> insert into a values(7,7);
已创建 1 行。

SQL> commit;
提交完成。

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

第八轮：(cache size(G)=6.5 shared_pool_size=283458240)

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 7289700352 bytes
Fixed Size                  1247828 bytes
Variable Size             301991340 bytes
Database Buffers         6979321856 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。

SQL> conn thomas_zhang/thomas_zhang@dbtest;
已连接。
SQL> select *from a;

         A          B
---------- ----------
         3          3
         6          6
         7          7
         1          1
         5          5

SQL> insert into a values(8,8);
已创建 1 行。

SQL> commit;
提交完成。

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

第九轮：(cache size(G)=6.5 shared_pool_size=1073741824)

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 8078229504 bytes
Fixed Size                  1250092 bytes
Variable Size            1090522324 bytes
Database Buffers         6979321856 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 7704M
sga_target                           big integer 0
SQL> show parameter shared_pool_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
shared_pool_size                     big integer 1G
SQL> conn thomas_zhang/thomas_zhang@dbtest
已连接。
SQL> select * from a;

         A          B
---------- ----------
         3          3
         6          6
         7          7
         8          8
         1          1
         5          5

已选择6行。

SQL> insert into a values(9,9);
已创建 1 行。

SQL> commit;
提交完成。

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL>

第十轮：(cache size(G)=5.5 shared_pool_size=2147483648)

SQL> startup mount pfile='d:zrp.ora';
ORACLE 例程已经启动。

Total System Global Area 8078229504 bytes
Fixed Size                  1253172 bytes
Variable Size            2164261068 bytes
Database Buffers         5905580032 bytes
Redo Buffers                7135232 bytes
数据库装载完毕。
SQL> alter database open;
数据库已更改。

SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 7704M
sga_target                           big integer 0
SQL> show parameter shared_pool_size
NAME