Oracle 9i 10g编程艺术 —— 读书笔记(一)

        在上班的时间看书真是一件快乐的事情， 最近我很快乐啊，因为正在读《Oracle 9i & 10g编程艺术》这本书，真是厚，就留在办公室天天看，刚好也没什么活干。
        不知道是不是自己速度太慢还是看得太仔细，看了好多天只看了三章，期间做了些笔记，整理了一下。
        第一章讲得还挺杂滴，举了些例.
        1.2关于位图索引，对某字段建位图索引后，会将该字段拥有相同列值的行锁定，如

create   table  test(
test_id  varchar2 ( 20 ), test_flag  varchar2 ( 1 )
);
create  bitmap  index  test_index  on  test(test_flag);
-- 对test_flag建位图索引
-- 现插入一行数据, 但不commit
insert   into  test  values ( ' 1 ' ,  ' N ' );
-- 这时就会锁定所有列值为N的行，并且在其他会话中也不能插入值为N的行
-- 因为其他会话也会要求锁定，只能排队。
-- 现开始另一个会话，同样插入
insert   into  test  values ( ' 2 ' ,  ' N ' );
-- 这句话就会挂起。

        1.3 windows xp是单进程体系结构，会限制Oracle数据库服务器总共只有大约1.75G的ram。每个oracle连接要同时处理多条语句，所以oracle连接通常比sql server连接占用更多的ram。看了这节有点明白上次面试时别人为什么会问操作系统相关的问题了。

        关于绑定变量，虽然一直是这样用的，但明白了为什么，Oracle会将已解析，已编译的sql连同其他内容存储在共享池中，这是系统全局区中一个共享内存结构。
在查询中若使用常量 select * from test where test_id = '123'; 不管原先是否查询过123这行，这都是一条全新的查询，也就是会重新编译(硬解析)。
若使用绑定变量 select * from test where test_id = :testId ; testId在查询时提供，这个查询只会编译一次，随后会把查询计划存储在一个共享池中，以便以后获取和重用这个查询计划(软解析)。

        顺带一提：如果只有一个小应用没有使用绑定变量，即使其他应用设计的很好，能适当的将已解析的sql放在共享池中重用，但因为这个小应用，过一段时间就会从共享池中删除已存储的sql。就使得其他应用也必须再次硬解析sql。在1.3.5中有个参数CURSOR_SHARING=FORCE，这个特性会实现一个自动绑定器，它会自动把硬解析改写成软解析，不过这个对性能上会有副作用。

runstats安装和使用方法,参看 http://blog.oracle.com.cn/index.php/165800/viewspace-5542.html
以sys用户登录，忘记密码可用alter user sys identified by <new password>修改

        1.3.2 简单了说了点关于并发，锁定，我记录了些书中总结：
1.Oracle只在修改时才对数据加行级锁。正常情况下不会升级到块级锁或表级锁(不过两段提交期间的一段很短的时间内除外，这是一个不常见的操作)。
2如果只是读数据，Oracle绝不会对数据锁定。不会因为简单的读操作在数据行上锁定。
3.写入器（writer）不会阻塞读取器（reader）。换种说法：读（read）不会被写（write）阻塞。这一点几乎与其他所有数据库都不一样。在其他数据库中，读往往会被写阻塞。尽管听上去这个特性似乎很不错（一般情况下确实如此），但是，如果你没有充分理解这个思想，而且想通过应用逻辑对应用施加完整性约束，就极有可能做得不对。
4.写入器想写某行数据，但另一个写入器已经锁定了这行数据，此时该写入器才会被阻塞。读取器绝对不会阻塞写入器。
Oracle的无阻塞方法有一个副作用，特别对资源进行调配时，会出现问题。我一般做法时对资源的调配加标识位，调用之前先查询标识位，可用时代表调用，然后修改标识位。但因为Oracle的一个特性写入器不会阻塞读取器，所以当并发情况发生时，即使某一个会话读取某个可用资源时并锁定时，修改还未提交(所做的修改在提交之前对其他会话来说是不可见的)，而此时另一个会话也读取到该行资源可用，并也进行修改。这样两个会话就都获得了该资源。与书上的例子大同小异。解决办法就是加for update 。查询可用资源时加上for update
如：select * from resource where resource = :resource for update;
这个是表锁定，即每个会话进来先进行表锁定，若无法锁定则阻塞并等待。

        1.3.3 关于游标，又是一点点，书上在这节通过例子说明一个情况，对某个表打开一个游标，Oracle并不会马上复制这张表的数据，只是在你获取数据时它才从表中读数据。还有就是对某张表作delete命令，Oracle会把数据保留下来，把它放在一个称为undo段(undo segment)的数据区，这个数据区也称为回滚段(rollback segment)。

        1.3.3 闪回

variable myscn  number ; 
exec  :myscn : =  dbms_flashback.get_system_change_number;
-- 设置变量myscn，获得当前系统修改号(每次发生提交时，这个就会递增)
-- 此时删除表test中所有数据。
delete  test;
commit ;

-- 然后使用闪回查询依然可获得原来的时间点的数据。
select   *   from  test  as   of  scn :myscn;

-- 在Oracle 10g中还可以
flashback  table  test  to  scn :myscn;

-- 若提示错误： cannot flashback the table because row movement is not enabled using the FLASHBACK command
-- 则先执行 alter table test enable row movement;
-- flashback其实是对表重新插入的记录，所以rowid与原来的并不相同。

闪回数据库应遵循：
1.在数据库控制文件恢复或重建以前执行。
2.需要恢复的对象不能位于已经被删除的表空间中。
3.包含了将要查询的对象的数据库的数据文件不能够被压缩。
4.在resetlos命令使用之前。

这里我对闪回做了个稍微详细点的了解，Oracle中有回收站的概念，当某个表被drop时，他就移动到了回收站中。该对象一直保存在回收站中，直到清除回收站为止。若不想保存在回收站中，则 drop table test purge; 回收站其实是个数据字典表。被删除和移动到回收站中的对象并没有释放空间，所以这些对象依然耗费空间，直到从回收站中清除为止。这句 purge tablespace all_users; 删除该表空间相关的回收站中所有对象。
闪回已drop的表，其实是将回收站里的表恢复。恢复时同时也将回收站的清除掉
flashback table test to before drop;
flashback table test to before drop rename to mytest;  //闪回同时重命名
若要查看回收站 select * from user_recyclebin;

关于闪回查询，我看到一个不错的例子，是关于汇率的。现有一表rates, 分别有货币种类和当前汇率两个字段。

create   table  rates(currency  varchar2 ( 8 ), rate  number ( 15 , 10 ));
-- 由于汇率是不断更新的，有时就需要过去某个时段的汇率。
-- 如现有一项在下午15:00发生但在上午11:00生效的交易。其有效汇率是上午11:00的汇率，而不是现在的。
-- 对rates表有如下操作
insert   into  rates  values ( ' 人民币 ' ,  1.25 ); 
commit ;
update  rates  set  rate  =   1.20 ;
commit ;
delete  rates;
commit ;
insert   into  rates  values ( ' 人民币 ' , 1.8 );
commit ;

汇率变化很频繁。
在Oracle 10g中的版本闪回查询维护了历史表和存储开始和结束时间。(只能在10g中用)

select  versions_starttime, versions_endtime, versions_xid, versions_startscn, versions_endscn, versions_operation, rate
from  rates versions  between   timestamp  minvalue  and  maxvalue;

-- 得到结果如下
VERSIONS_STARTTIME            VERSIONS_ENDTIME              VERSIONS_XID         VERSIONS_STARTSCN  VERSIONS_ENDSCN   VERSIONS_OPERATION       RATE
-- ------------------------- --------------------------- -------------------- ----------------- ----------------- ------------------ -----------------
21 - 11月 - 07   02.12 . 09  下午                                   09000000FB870000         58848306                        I             1.8000000000
21 - 11月 - 07   02.11 . 18  下午                                   07000100C6870000         58848258                        D             1.2000000000
21 - 11月 - 07   02.10 . 57  下午       21 - 11月 - 07   02.11 . 18  下午     030013008C880000         58848223           58848258       U             1.2000000000
21 - 11月 - 07   02.09 . 15  下午       21 - 11月 - 07   02.10 . 57  下午     06002600DC860000         58848153           58848223       I             1.2500000000

这里除了rate字段，其他都是伪列，time是时间，scn代表系统号，VERSIONS_OPERATION代表操作类型，xid表示更改该行的事务标识符，可以通过xid来显示实际的操作语句：
select undo_sql from flashback_transaction_query where xid = '0A0001007B880000';
若要恢复表中被删除的数据，现通过刚才获得要闪回的时间
flashback table rates to timestamp to_timestamp('2007-11-21 02.11.18','yyyy-mm-dd hh24:mi:ss');
则可以闪回定点的时间。
若报 cannot flashback the table because row movement is not enabled 错误
原因就是因为表没有row movement,改变下表的属性：
alter table rates enable row movement;

        1.3.3 多版本，书中举了两个例子可谓把多版本讲得很具体了，让我印象深刻。
第一个例子，现有一家帐户余额表accounts(id, money...)，里面记录了客户的当前余额。现在对要求所有客户的总余额：select sum(money) from accounts;  假设有4个客户，sum函数会自上而下的求和，当求得前2个客户的余额总额时，这时在ATM机发生了一个转帐事务，将400元从第一个帐户转到第四个帐户，因为Oracle的一个特点，写入器不会阻塞读取器，所以不会阻塞对余额的求和。而此时sum函数计算的第四个帐户会比实际情况多处400元。细细一想确实如此，之前在Oracle锁定策略也说了，读（read）不会被写（write）阻塞。Oracle对此的解决方法确实很棒，即是通过事务机制，Oracle在修改数据时，会创建undo条目，这些undo条目记录的是修改前的数据，这些undo条目写至undo段，即回滚段，用来回滚时恢复数据的。那么刚才sum在求和时Oracle会看数据是否有undo段，即是否发生改变，像上面所说的情况，只需在回滚段获取原来的值。

第二个例子

begin  
   for  x  in  ( select   *   from  test)
  loop
       insert   into  test  values (x.test_id, x.test_info);
   end  loop;
end ;

猛一看感觉像是死循环样，不停的查询表test，然后把查到的记录再插入test，这样下一次查询时不是可以查询刚刚插入的那条吗？
Oracle的情况是这样，在select * from test的结果在查询开始执行时就已经确定了，这个select并不看insert生成的任何新数据。

        这章剩下的都是一些很杂的东西：
Oracle则是按ANSI SQL的要求来实现null值。
基于这些规则的要求，null与null的比较结果既不为true也不为false，也就是说，实际上，它是未知的(unknown)。

select   *   from  test  where   null   =   null ;
select   *   from  test  where   null   <>   null ;
select   *   from  test  where   null   is   null ;
-- 前面两句都查不到记录，

-- 确保用户在数据库中只有一个会话的方法：
create  profile one_session limit session_per_user  1 ;
alter   user  scott profile one_session;
alter  system  set  resource_limit = true;

         第二章开始扯概念了，挑了点记录了下：
关于数据库和实例：实例是一组后台进程和共享内存。数据库是磁盘上存储的数据集合。实例"一生"只能装载并打开一个数据库。数据库可以由一个或多个实例(使用RAC)装载和打开。

专用服务器和共享服务器：
专用服务器：
对于每个会话，都会出现一个新的专用服务器线程(或进程)，客户进程(也就是想要连接数据库的程序)。会通过某种网络通道(如TCP/IP socket)与这个专用服务器直接通信，并由这个服务器进程接收和执行SQL。
共享服务器：
不会对每条用户连接创建另外的线程(或进程)，使用一个"共享进程"池为大量用户提供服务。共享服务器实际上就是一种连接池机制。客户进程不会直接与服务器进程对话，在共享服务器有个"调度器"的进程，同时在SGA中会有个一个请求队列，一个响应队列。客户进程会直接与这个"调度器"对话,调度器会将请求放入请求队列，当有可用的共享服务器从请求队列中取出这个请求并处理，把结果放入响应队列中，调度器会一直监听这个响应队列，一有结果便返回给客户。

一个Oracle实例可以同时使用这两类连接(实际上，即使配置为使用共享服务器连接，Oracle数据库也总是支持专用服务器连接)。

         第三章讲文件，看得我挺烦躁滴，大部分可以说与开发无关，DBA倒可看看。
若是SPFILE文件被损坏，数据库就无法启动。
修复办法：
在unix平台上，strings spfile$ORACLE_SID.ora
在windows平台上，用写字板打开，在内容copy到init<ORACLE_SID>.ora中，就能创建启动实例的pfile。
若SPFILE丢失，还可以从数据库的警告日志恢复参数文件的信息。

查看跟踪文件的位置

select  name, value  from  v$parameter  where  name  like   ' %dump_dest% ' ;

NAME                                                                             VALUE
-- ------------------------------------------------------------------------------ -------------------------------
background_dump_dest                                                              / opt / ora10 / admin / orcl / bdump
user_dump_dest                                                                    / opt / ora10 / admin / orcl / udump
core_dump_dest                                                                    / opt / ora10 / admin / orcl / cdump

如果使用Oracle的共享服务器连接，就会使用一个后台进程；因此，跟踪文件的位置由BACKGROUND_DUMP_DEST确定。如果使用的是专用服务器连接，则会使用一个用户或前台进程与Oracle交互；所以跟踪文件会放在USER_DUMP_DEST参数指定的目录中。如果出现严重的Oracle内部错误（如UNIX上的“segmentation fault”错误），或者如果Oracle Support要求你生成一个跟踪文件来得到额外的调试信息，
CORE_DUMP_DEST参数则定义了此时这个“内核”文件应该放在哪里。
若要准确查到跟踪文件的位置，需要用到四个视图
v$parameter :找到USER_DUMP_DEST指定的跟踪文件位置。v$parameter:找到USER_DUMP_DEST指定的跟踪文件位置。
v$process   :查找进程ID。
v$session   :正确地标识其他视图中的会话信息。
v$instance  :得到ORACLE_SID。

select  c.value  ||   ' / '   ||  d.instance_name  ||   ' _ora_ '   ||  a.SPID  ||   ' .trc '   from  v$process a, v$session b, v$parameter c, v$instance d 
where  a.ADDR  =  b.PADDR  and  b.audsid  =  userenv( ' sessionid ' )  and  c.name  =   ' user_dump_dest '

现在oracle会自动处理死锁，但锁等待经常会遇到，至少我是，对表记录进行锁定修改时，若发生掉线，断电等情况，就会发生锁等待，而且是始终等待。现有测试表test，开启一个会话，然后查询select * from test for update;
若这时断电或掉线，则test就被锁定，最近公司网络不好，经常遇到掉线导致锁等待的情况。测试一下，再开启另一个会话，同样执行select * from test for update;这句查询会始终处于等待状态，因为test被刚才的会话锁定了。
需要两个视图就可得知锁定test的session，v$session, v$lock

select  a.sid, a.serial#, a.userName, b.type  from  v$session a, v$lock b  where  a.sid = b.sid  and  a.userName  is   not   null ;
-- 根据userName就可知道锁定的sid, serial#
-- 然后kill掉session
alter  system  kill  session  ' sid,serial# ' ;
-- 则锁被释放。

待续。。。