欢迎光临新家 www.dbafan.com

有一个比较有趣的话题，你平时用什么工具计算？

我一般手边有什么就用什么。想到的这儿列举一些：
1. 笔和纸  在没有电脑的时候只能用这个，不过用的次数很少，因为电脑不离手:)

2. windows计算器 相信有不少人是用这个计算的。我不太喜欢用这个，因为要从附件中找出这个不容易，而且功能也不行，先乘除后加减都不知道，和windows一样苯。

3. google的搜索栏 在装了google的toolbar后这个蛮好用的，直接输入回车就可以了，如果当前窗口是mozilla，这是首选。

4. sqlplus 这个我相信不做DBA的人很少会知道。 如果当前我的窗口是sqlplus的话，懒得切换，直接select xxx*xxxx from dual;就出来了，功能强大，函数众多,强烈推荐dba适用。

5. bc Unix命令，如果当前窗口是在一台没有安装数据库的server下，你可以选择bc。

6. Perl 编程语言，Perl的随意在这种时候也能够显示出来，两行代码就可以给出你要的答案，功能强大，函数很多。

7. Excel 这个我比较少用，不是很熟悉。都是别人做好了我来用。

如果你的工具不在上述7种工具中，欢迎补充

这是我常用的两种PL/SQL监控运行状况的方法：

1.  使用dbms_application_info.SET_CLIENT_INFO

举例如下:
declare
  cursor cr is select rowid from test;
  delete_count number;
  total_count number;
begin
 delete_count :=0;
 total_count :=0;
 for i in cr loop
   delete from test where rowid=i.rowid;
   delete_count :=delete_count+1;
   total_count :=total_count+1;
   if (delete_count>100) then
      dbms_application_info.SET_CLIENT_INFO(' So far '||total_count||' rows has been deleted');
      delete_count :=0;
      commit;
   end if;
 end loop;
end;
/

另开一session, select client_info from v$session where client_info like 'So far%';

注意info的长度有限制，超过64字符会被截断

2. 使用dbms_system.ksdwrt， 这个可以写到300个字符

KSDWRT Procedure

This procedure prints the message to the target file (alert log and/or trace file).

Syntax

DBMS_SYSTEM.KSDWRT (
    dest IN BINARY_INTEGER,
    tst IN VARCHAR2);

Parameters

Table KSDWRT Procedure Parameters

Parameter	Description
dest	Destination is indicated by the following values: 1 - Write to trace file. 2 - Write to alertlog. 3 - Write to both.
tst	Message (not tested for max length, but output with 300 chars was successful)

举例如下:

declare
  cursor cr is select rowid from test;
  delete_count number;
  total_count number;
begin
 delete_count :=0;
 total_count :=0;
 for i in cr loop
   delete from test where rowid=i.rowid;
   delete_count :=delete_count+1;
   total_count :=total_count+1;
   if (delete_count>100) then
      dbms_system.ksdwrt (1,' So far '||total_count||' rows has been deleted');
      delete_count :=0;
      commit;
   end if;
 end loop;
end;
/

然后开一session,  tail -30f xxx.trc

首先来看一下real index的创建,便于和后面virtual index进行比较

SQL> create table test
  2  tablespace data02
  3  as
  4  select * from dba_objects;

Table created.

SQL> create index test_idx on test(object_id);

Index created.

SQL> select index_name,index_type,status from user_indexes
  2  where index_name='TEST_IDX';

INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
TEST_IDX                       NORMAL                      VALID

SQL> select object_id from dba_objects
  2  where object_name='TEST_IDX';

 OBJECT_ID
----------
      8054

SQL> select * from ind$ where obj#=8054;

      OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#        BO# INDMETHOD#       COLS   PCTFREE$   INITRANS   MAXTRANS  PCTTHRES$
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     TYPE#      FLAGS   PROPERTY     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETI SAMPLESIZE     ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ----------
   INTCOLS     DEGREE  INSTANCES   TRUNCCNT     SPARE1     SPARE2     SPARE3
---------- ---------- ---------- ---------- ---------- ---------- ----------
SPARE4
------------------------------------------------------------------------------------------------------------------------------------
SPARE5
------------------------------------------------------------------------------------------------------------------------------------
SPARE6
---------
      8054       8054          0          7          4       8053          0          1         10          2        255
         1          0          0
         1                                           1

03-JAN-06

SQL> drop index test_idx;

Index dropped.

 

我们创建了一个虚拟的index
SQL> create index test_idx on test(object_id) nosegment;

Index created.

SQL> select object_id from dba_objects
  2  where object_name='TEST_IDX';

 OBJECT_ID
----------
      8058

SQL> select segment_name,segment_type,bytes from user_segments
  2  where segment_name='TEST_IDX';

no rows selected

SQL> select * from ind$ where obj#=8058;

      OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#        BO# INDMETHOD#       COLS   PCTFREE$   INITRANS   MAXTRANS  PCTTHRES$
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
     TYPE#      FLAGS   PROPERTY     BLEVEL    LEAFCNT    DISTKEY    LBLKKEY    DBLKKEY     CLUFAC ANALYZETI SAMPLESIZE     ROWCNT
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ----------
   INTCOLS     DEGREE  INSTANCES   TRUNCCNT     SPARE1     SPARE2     SPARE3
---------- ---------- ---------- ---------- ---------- ---------- ----------
SPARE4
------------------------------------------------------------------------------------------------------------------------------------
SPARE5
------------------------------------------------------------------------------------------------------------------------------------
SPARE6
---------
      8058       8058          0          0          0       8053          0          1         10          2        255
         1       4096          0
         1                                           1

03-JAN-06

 

可见virtual index并没有真实的被创建出来,没有分配segment空间. 根据我trace的结果,它只更新了obj$,ind$,icol$三个表.

 

虚拟index的作用在于可用于optimizer,但是有几个条件
1.  需要设定隐藏参数 "_use_nosegment_indexes"=true, 因为涉及隐藏参数,所以不建议在prod环境使用,除非oracle建议

2.  hint 对virtual index有效
3.  没有hint的情况下,RBO时虚拟索引对于optimizer不可见, CBO时oracle会考虑使用虚拟索引

 

下面来看个试验:

SQL> analyze table test compute statistics;

Table analyzed.

SQL> alter session set optimizer_mode=choose;

Session altered.

SQL> explain plan for select /*+ index(test,test_idx) */ * from test where object_id=10;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |     1 |    76 |    13 |
|*  1 |  TABLE ACCESS FULL   | TEST        |     1 |    76 |    13 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TEST"."OBJECT_ID"=10)

Note: cpu costing is off

14 rows selected.

 

_use_nosegment_indexes=false时, virtual index对于optimizer来讲不可见

 

SQL> alter session set "_use_nosegment_indexes"=TRUE;

Session altered.

SQL> explain plan for select /*+ index(test,test_idx) */ * from test where object_id=10;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    76 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    76 |     2 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX    |     1 |       |     1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST"."OBJECT_ID"=10)

Note: cpu costing is off

15 rows selected.

SQL> ALTER SESSION SET OPTIMIZER_MODE=RULE;

Session altered.
SQL> explain plan for select /*+ index(test,test_idx) */ * from test where object_id=10;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    76 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    76 |     2 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX    |     1 |       |     1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST"."OBJECT_ID"=10)

Note: cpu costing is off

15 rows selected.

 

#index hint在RBO和CBO下均有效

 

SQL> EXPLAIN PLAN FOR SELECT * FROM TEST WHERE OBJECT_ID=10;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|*  1 |  TABLE ACCESS FULL   | TEST        |       |       |       |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TEST"."OBJECT_ID"=10)

Note: rule based optimization

14 rows selected.

 

#RBO下, virtual index对于optimizer来讲不可见

SQL> alter session set optimizer_mode=choose;

Session altered.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    76 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    76 |     2 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX    |     1 |       |     1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST"."OBJECT_ID"=10)

Note: cpu costing is off

15 rows selected.

 

#CBO下有效

虚拟索引有以下几个特性:

1. virtual index对实际的sql运行并没有作用,看下面的试验
SQL> alter session set "_use_nosegment_indexes"=TRUE;

Session altered.

SQL> alter session set optimizer_mode=choose;

Session altered.

SQL> EXPLAIN PLAN FOR SELECT * FROM TEST WHERE OBJECT_ID=10;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    76 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    76 |     2 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX    |     1 |       |     1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST"."OBJECT_ID"=10)

Note: cpu costing is off

15 rows selected.

SQL> set autotrace on
SQL> select * from TEST WHERE OBJECT_ID=10;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE        CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
------------------------------ ---------- -------------- ------------------ --------- --------- ------------------- ------- - - -
SYS
C_USER#
                                       10             10 CLUSTER            28-SEP-05 28-SEP-05 2005-09-28:23:10:43 VALID   N N N

 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=76)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=76)

   2    1     INDEX (RANGE SCAN) OF 'TEST_IDX' (NON-UNIQUE) (Cost=1 Ca
          rd=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         88  consistent gets
          0  physical reads
          0  redo size
       1353  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
         
虽然explain plan 和autotrace 都给出了index range scan 的路径,但是事实上还是走的full table scan

使用10046 trace:

SQL> alter session set events '10046 trace name context forever, level 8';

Session altered.

SQL> set autotrace off
SQL> select * from TEST WHERE OBJECT_ID=10;

OWNER
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE        CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S
------------------------------ ---------- -------------- ------------------ --------- --------- ------------------- ------- - - -
SYS
C_USER#
                                       10             10 CLUSTER            28-SEP-05 28-SEP-05 2005-09-28:23:10:43 VALID   N N N

PARSING IN CURSOR #1 len=37 dep=0 uid=0 oct=3 lid=0 tim=3297812446365 hv=3616964102 ad='8c804b30'
select * from TEST WHERE OBJECT_ID=10
END OF STMT
PARSE #1:c=0,e=1957,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=3297812446357
EXEC #1:c=0,e=55,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3297812446643
WAIT #1: nam='SQL*Net message to client' ela= 6 p1=1650815232 p2=1 p3=0
FETCH #1:c=0,e=406,p=0,cr=8,cu=0,mis=0,r=1,dep=0,og=4,tim=3297812447220
WAIT #1: nam='SQL*Net message from client' ela= 781 p1=1650815232 p2=1 p3=0
FETCH #1:c=10000,e=3792,p=0,cr=80,cu=0,mis=0,r=0,dep=0,og=4,tim=3297812451974
WAIT #1: nam='SQL*Net message to client' ela= 1 p1=1650815232 p2=1 p3=0
WAIT #1: nam='SQL*Net message from client' ela= 2170013 p1=1650815232 p2=1 p3=0
XCTEND rlbk=0, rd_only=0
WAIT #0: nam='log file sync' ela= 464 p1=3322 p2=0 p3=0
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=8053 op='TABLE ACCESS FULL OBJ#(8053) '
         
2. 不能使用alter index语句

SQL> alter index test_idx rebuild;
alter index test_idx rebuild
*
ERROR at line 1:
ORA-08114: can not alter a fake index

3. 相同名字的index不能被创建,但是可以创建相同列上的不同名index
SQL> create index test_idx on test(owner);
create index test_idx on test(owner)
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SQL> create index test_idx2 on test(object_id);

Index created.

4. 分析有效且起作用,但是dba_indexes中看不到统计信息
SQL> update test set object_id=10;

6783 rows updated.

SQL> commit;

SQL> explain plan for select * from test where object_id=10;

Explained.

SQL>  @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------
| Id  | Operation                   |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    76 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST        |     1 |    76 |     2 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX    |     1 |       |     1 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST"."OBJECT_ID"=10)

Note: cpu costing is off

15 rows selected.

QL> analyze table test compute statistics for all indexed columns size 100;

Table analyzed.

SQL> explain plan for select * from test where object_id=10;

Explained.

SQL>  @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  6783 |   496K|    13 |
|*  1 |  TABLE ACCESS FULL   | TEST        |  6783 |   496K|    13 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TEST"."OBJECT_ID"=10)

Note: cpu costing is off

 

 

SQL> alter table test allocate extent (size 10M datafile '/oracle/ora9i/data01/xfan/system02.dbf');

Table altered.

SQL> exec show_space('TEST','SYS');
Free Blocks.............................0
Total Blocks............................1284
Total Bytes.............................10518528
Unused Blocks...........................1283
Unused Bytes............................10510336
Last Used Ext FileId....................7
Last Used Ext BlockId...................4
Last Used Block.........................1

 

#仍然没有变化

 

SQL> alter table test allocate extent (size 10M instance 1);

Table altered.

SQL> exec show_space('TEST','SYS');
Free Blocks.............................1281
Total Blocks............................2564
Total Bytes.............................21004288
Unused Blocks...........................1282
Unused Bytes............................10502144
Last Used Ext FileId....................1
Last Used Ext BlockId...................31567
Last Used Block.........................1280

PL/SQL procedure successfully completed.

 

#HWM被调高了

 

所以,只要在allocate extent 中加入 (instance 1),才可以将新加入的blocks加入到HWM之下

 

今天中午在饭桌上，我随便问了句“你们以前有人玩过VOS吗”

注意，我指的是

Virtual Orchestra Studio

一种音乐模拟类游戏

沉默一会，还好，不是没有人知道，还是有人知道的，不过解释是

Veritas Oracle Sun

而且还真有这个联合体

狂晕

慢慢爬级吧:)

 

等级：通过条件

4  ：通过弹奏歌曲获得40以上的G币
8  ：房间中拥有5件以上的道具
12 ：在组队模式下进行一次游戏
16 ：5级或以上的歌曲，游戏顺利过关的同时打出50个以上的连击（Combo）
20 ：6级或以上的歌曲，游戏顺利过关的同时打出70%以上的Cool
24 ：8级或以上的歌曲，游戏顺利过关的同时打出100个连击（Combo）
28 ：10级或以上的歌曲，游戏顺利过关的同时打出10个以上的Jam
32 ：11级或以上的歌曲，游戏顺利过关的同时打出70%以上的Cool
36 ：12级或以上的歌曲，游戏顺利过关的同时Miss的数量少于10个
40 ：14级或以上的歌曲，游戏顺利过关的同时打出20个以上的Jam
44 ：15级或以上的歌曲，使用游戏道具（失落手镯)顺利过关
48 ：16级或以上的歌曲，使用1倍速度顺利过关
52 ：17级或以上的歌曲，使用6倍速度顺利过关
56 ：18级或以上的歌曲，游戏顺利过关的同时打出80%以上的Cool
60 ：17级或以上的歌曲，使用游戏道具（幻镜之戒）0.5倍速度顺利过关
64 ：17级或以上的歌曲，使用游戏道具（闪灵手镯）顺利过关的同时打出70%以上的Cool
68 ：18级或以上的歌曲，使用游戏道具（失落手镯）顺利过关的同时打出80%以上的Cool
72 ：18级或以上的歌曲，使用游戏道具（狂风之戒）游戏顺利过关的同时全连（All Combo）通过
76 ：19级或以上的歌曲，使用游戏道具（混乱之戒）游戏顺利过关的同时打出0个Jam
80 ：18级或以上的歌曲，使用1倍速度顺利过关的同时打出全连（All Combo）
84 ：19级或以上的歌曲，使用游戏道具（失落手镯）1倍速度顺利过关
88 ：20级或以上的歌曲，游戏顺利过关的同时打出95%以上的Cool
92 ：25级或以上的歌曲，游戏顺利过关的同时打出全连（All Combo）
96 ：18级或以上的歌曲，游戏顺利过关的同时打出100%的Good
100 ：31级或以上的歌曲，游戏顺利过关的同时打出全连（All Combo)

如果你和我一样每天要开很多secureCRT窗口并且需要sudo（或者其他的事情），并且sudo还设置了timeout，那么一定要阅读这篇文章

一开始没每开一个secureCRT窗口总是要重复下面的事情：
sudo su - oracle
Password:
在等待sudo su 的时候经常会timeout，后来到google，baidu上去找自动sudo 的脚本也没有找到，最后还是乖乖的翻secureCRT的help文档解决了这个问题。

在secureCRT安装目录下C:\Program Files\SecureCRT\scripts
打开example1.vbs

#$language = "VBScript"
#$interface = "1.0"

Sub main
  ' turn on synchronous mode so we don't miss any data
  crt.Screen.Synchronous = True

  ' Wait for a string that looks like "login: " or "Login: "
  crt.Screen.WaitForString "ogin: "

  ' Send your username followed by a carriage return
  crt.Screen.Send "username" & VbCr

  ' Wait for a tring that looks like "password: " or "Password: "
  crt.Screen.WaitForString "assword:"

  ' Send your password followed by a carriage return
  crt.Screen.Send "password" & VbCr

  ' turn off synchronous mode to restore normal input processing
  crt.Screen.Synchronous = False
End Sub

修改username 为sudo su - oracle
修改password 为你自己的password
然后在session options里面设置logon script为该文件

最后记得把windows里面的权限设定为尽量小

hash partition的分区数需要是power(2)数，否则可能会出现各个分区间数据量的不平衡。但到底是怎样的不平衡，加分区后oracle如何操作以及coalesce如何进行，下面给出一个试验过程:

  1  create table test_hash(
  2  id number)
  3  partition by hash(id)
  4* partitions 6
SQL> /

Table created.

SQL> begin    
  2   for i in 1..8000 loop
  3    insert into test_hash values(i);
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select table_name,partition_name from dba_tab_partitions
  2  where table_name='TEST_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST_HASH                      SYS_P1
TEST_HASH                      SYS_P2
TEST_HASH                      SYS_P3
TEST_HASH                      SYS_P4
TEST_HASH                      SYS_P5
TEST_HASH                      SYS_P6

SQL> select count(*) from test_hash partition (sys_p1);

  COUNT(*)
----------
      1013

SQL> c/p1/p2
  1* select count(*) from test_hash partition (sys_p2)
SQL>
SQL> /

  COUNT(*)
----------
       980

SQL> c/p2/p3
  1* select count(*) from test_hash partition (sys_p3)
SQL> /

  COUNT(*)
----------
      2043

SQL> c/p3/p4
  1* select count(*) from test_hash partition (sys_p4)
SQL> /

  COUNT(*)
----------
      1988

SQL> c/p4/p5
  1* select count(*) from test_hash partition (sys_p5)
SQL> /

  COUNT(*)
----------
       938

SQL> c/p5/p6
  1* select count(*) from test_hash partition (sys_p6)
SQL> /

  COUNT(*)
----------
      1038

发现P1,P2,P5,P6差不多都是1000,而P3,P4为2000行，差不多是两倍的关系。

而P5-P1=4(2的二次方），P6-P2=4，所以结构大约如下(practical Oracle8i上解释为何如此):

P1       P5   
P2       P6
P3
P4

这时我们添加一个partition P7
SQL> alter table test_hash
  2  add partition ;

Table altered.

SQL> select table_name,partition_name from dba_tab_partitions
  2  where table_name='TEST_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST_HASH                      SYS_P1
TEST_HASH                      SYS_P2
TEST_HASH                      SYS_P3
TEST_HASH                      SYS_P4
TEST_HASH                      SYS_P5
TEST_HASH                      SYS_P6
TEST_HASH                      SYS_P7

SQL> select count(*) from test_hash partition (sys_p1);     

  COUNT(*)
----------
      1013

SQL>  c/p1/p2
  1* select count(*) from test_hash partition (sys_p2)
SQL> /

  COUNT(*)
----------
       980

SQL> c/p2/p3
  1* select count(*) from test_hash partition (sys_p3)
SQL> /

  COUNT(*)
----------
      1021

SQL> c/p3/p4
  1* select count(*) from test_hash partition (sys_p4)
SQL> /

  COUNT(*)
----------
      1988

SQL> c/p4/p5
  1* select count(*) from test_hash partition (sys_p5)
SQL> /

  COUNT(*)
----------
       938

SQL> c/p5/p6
  1* select count(*) from test_hash partition (sys_p6)
SQL> /

  COUNT(*)
----------
      1038

SQL> c/p6/p7
  1* select count(*) from test_hash partition (sys_p7)
SQL> /

  COUNT(*)
----------
      1022

发现原来的P3被一分为二成为新的P3和P7 (2043=1021+1022),结构变为如下：
P1    P5
P2    P6
P3    P7
P4

此时我们coalesce partition:

SQL> alter table test_hash coalesce partition;

Table altered.

SQL> select table_name,partition_name from dba_tab_partitions
  2  where table_name='TEST_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST_HASH                      SYS_P1
TEST_HASH                      SYS_P2
TEST_HASH                      SYS_P3
TEST_HASH                      SYS_P4
TEST_HASH                      SYS_P5
TEST_HASH                      SYS_P6

6 rows selected.

SQL> alter table test_hash coalesce partition;

Table altered.

SQL> select table_name,partition_name from dba_tab_partitions
  2  where table_name='TEST_HASH';

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
TEST_HASH                      SYS_P1
TEST_HASH                      SYS_P2
TEST_HASH                      SYS_P3
TEST_HASH                      SYS_P4
TEST_HASH                      SYS_P5

SQL> select count(*) from test_hash partition (sys_p1);     

  COUNT(*)
----------
      1013

SQL> c/p1/p2
  1* select count(*) from test_hash partition (sys_p2)
SQL> /

  COUNT(*)
----------
      2018

SQL> c/p2/p3
  1* select count(*) from test_hash partition (sys_p3)
SQL> /

  COUNT(*)
----------
      2043

SQL> c/p3/p4
  1* select count(*) from test_hash partition (sys_p4)
SQL> /

  COUNT(*)
----------
      1988

第一次coalesce, P3和P7 merge成为新P3, 第二次coalesce, P2和P6 merge成为新P2，结构如下：

P1   P5
P2
P3
P4

以前写有一个purge几个月前记录的脚本。但是今天在一个新的表上测试的时候确得到错误：
PLS-00386: type mismatch found at 'REC_TAB' between FETCH cursor and INTO
variables

fetch这段的pl/sql为
       declare
        cursor rec is select /*+ index(test,test_a) */ rowid from TEST where a>=mindate and a<mindate+1;
        type recstartype1 is table of rowid index by BINARY_INTEGER;
        rec_tab recstartype1;
        busy_inline exception;
       begin
        open rec;
        loop
         fetch rec bulk collect into rec_tab limit 1000;
          FORALL i in 1..rec_tab.count                       
           delete from TEST WHERE rowid = rec_tab(i);

类型不匹配，自然想到是logical rowid的问题。去查看表类型，果然是IOT表。对IOT table执行select rowid 返回的为logical rowid（因为记录的physical rowid 对于IOT表是可能变化的）。于是将
type recstartype1 is table of rowid index by BINARY_INTEGER;
改为
type recstartype1 is table of urowid index by BINARY_INTEGER;
就可以了。

oracle文档如下:

Oracle uses a ROWID datatype to store the address (rowid) of every row in the database.

• Physical rowids store the addresses of rows in ordinary tables (excluding index-organized tables), clustered tables, table partitions and subpartitions, indexes, and index partitions and subpartitions.
• Logical rowids store the addresses of rows in index-organized tables.

A single datatype called the universal rowid, or UROWID, supports both logical and physical rowids, as well as rowids of foreign tables such as non-Oracle tables accessed through a gateway.

A column of the UROWID datatype can store all kinds of rowids. The value of the COMPATIBLE initialization parameter must be set to 8.1 or higher to use UROWID columns.