DB2中产生唯一值三种方式

一.Genearate_unique函数

[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_gu(custno char(13) for bit data,
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$  db2 "insert into xifenfei.t_gu values
> (generate_unique(),'www.xifenfei.com'),(generate_unique(),'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_gu"
CUSTNO                        CUSTNAME
----------------------------- ----------------
x'20120403054630527862000000' www.xifenfei.com
x'20120403054630527940000000' xifenfei
  2 record(s) selected.

generate_unique是按照国际标准时间(UTC)生成的当前时间戳加上当前数据库分区号，包含13个字节的字符串。如果调整了系统时间可能会出现重复

二.序列(Sequence)

[db2inst1@xifenfei ~]$ db2 "create sequence xifenfei.xff_seq
> as bigint
> start with 1
> increment by 1
> no maxvalue
> cycle
> cache 10"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_seq(xff_id bigint,
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_seq values(nextval
> for xifenfei.xff_seq,'www.xifenfei.com'), (nextval for xifenfei.xff_seq,'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_seq"
XFF_ID               CUSTNAME
-------------------- ----------------
                   1 www.xifenfei.com
                   2 xifenfei
  2 record(s) selected.

和ORACLE的sequence基本相同，只是在oracle中是sequence.nextval这里改为了nextnvl for seqence

三.自增字段

--1.generated always as identity方式(不能人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_a(xff_id bigint not null generated always
> as identity(start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(custname) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
--指定值插入失败
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(xff_id,custname) values(11,'XIFENFEI')"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0798N  A value cannot be specified for column "XFF_ID" which is defined as
GENERATED ALWAYS.  SQLSTATE=428C9
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_a"
XFF_ID               CUSTNAME
-------------------- ----------------
                  10 www.xifenfei.com
  1 record(s) selected.
--1.generated by default as identity方式(可以人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_d(xff_id bigint not null generated by default
> as identity (start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(custname) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
--指定值插入成功
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(xff_id,custname) values(11,'XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_d"
XFF_ID               CUSTNAME
-------------------- ----------------
                  10 www.xifenfei.com
                  11 XIFENFEI
  2 record(s) selected.

这个功能和sql server/mysql的自增长列很相似，给出了两种方式选择，使得比它们更加灵活

ORACLE 十进制与二进制互转函数

十进制转换二进制

CREATE OR REPLACE FUNCTION NUMBER_TO_BIT(V_NUM NUMBER)
RETURN VARCHAR IS V_RTN VARCHAR(8);--注意返回列长度
  V_N1  NUMBER;
  V_N2  NUMBER;
BEGIN
V_N1 := V_NUM;
    LOOP
      V_N2  := MOD(V_N1, 2);
      V_N1  := ABS(TRUNC(V_N1 / 2));
      V_RTN := TO_CHAR(V_N2) || V_RTN;
      EXIT WHEN V_N1 = 0;
    END LOOP;
--返回二进制长度
 SELECT lpad(V_RTN,8,0)
    INTO   V_RTN
    FROM dual;
return V_RTN;
end;
SQL> select NUMBER_TO_BIT(208) from dual;
NUMBER_TO_BIT(208)
-----------------------------
11010000

二进制转换十进制

CREATE OR REPLACE FUNCTION BIT_TO_NUMBER(P_BIN IN VARCHAR2) RETURN NUMBER AS
  V_SQL    VARCHAR2(30000) := 'SELECT BIN_TO_NUM(';
  V_RETURN NUMBER;
BEGIN
  IF LENGTH(P_BIN) >= 256 THEN
    RAISE_APPLICATION_ERROR(-20001, 'INPUT BIN TOO LONG!');
  END IF;
  IF LTRIM(P_BIN, '01') IS NOT NULL THEN
    RAISE_APPLICATION_ERROR(-20002, 'INPUT STR IS NOT VALID BIN VALUE!');
  END IF;
  FOR I IN 1 .. LENGTH(P_BIN) LOOP
    V_SQL := V_SQL || SUBSTR(P_BIN, I, 1) || ',';
  END LOOP;
  V_SQL := RTRIM(V_SQL, ',') || ') FROM DUAL';
  EXECUTE IMMEDIATE V_SQL
    INTO V_RETURN;
  RETURN V_RETURN;
END;
SQL> SELECT BIT_TO_NUMBER('11010000') FROM DUAL;
BIT_TO_NUMBER('11010000')
-------------------------
                      208

ORACLE ROWID 分析

ROWID知识补充
Rowid改为：OOOOOOFFFBBBBBBRRR，占用10个字节（32bit data_object_id +10bit rfile# +22bit block +16bit）。其中，O是对象ID，F是文件ID，B是块ID，R是行ID。

Rowid对应值对应10十进制值

A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)

模拟环境

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create table t_xff (id number,name varchar2(100));
Table created.
SQL> insert into t_xff values(1,'www.xifenfei.com');
1 row created.
SQL> commit;
Commit complete.
SQL>  alter table t_xff move;
Table altered.
SQL> select rowid,a.* from t_xff a;
ROWID                      ID NAME
------------------ ---------- ------------------------------
AADye6AAEAAAtCcAAA          1 www.xifenfei.com

相关值计算

Data Object number = AADye6
File               = AAE
Block              = AAAtCc
ROW                = AAA
DATA_OBJECT_ID
AADye6
58*64^0+30*64^1+50*64^2+3*64^3+0*64^4+0*64^5
58+1920+204800+786432+0+0=993210
RFILE#
AAE
4*64^0+0*64^1+0*64^2
4+0+0=4
BLOCK
AAAtCc
28*64^0+2*64^1+45*64^2+0*64^3+0*64^4+0*64^5
28+128+184320+0+0+0=184476

验证结果

SQL> select object_id,data_object_id from dba_objects where object_name='T_XFF';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    993209         993210
SQL> select dbms_rowid.rowid_object('AADye6AAEAAAtCcAAA') data_object_id#,
  2         dbms_rowid.rowid_relative_fno('AADye6AAEAAAtCcAAA') rfile#,
  3         dbms_rowid.rowid_block_number('AADye6AAEAAAtCcAAA') block#,
  4     dbms_rowid.rowid_row_number('AADye6AAEAAAtCcAAA') row# from dual;
DATA_OBJECT_ID#     RFILE#     BLOCK#       ROW#
--------------- ---------- ---------- ----------
         993210          4     184476          0

dump方式分析

SQL> select rowid,dump(rowid) from t_xff;
ROWID              DUMP(ROWID)
------------------ ------------------------------------------
AADye6AAEAAAtCcAAA Typ=69 Len=10: 0,15,39,186,1,2,208,156,0,0
DATA_OBJECT_ID
0,15,39,186
15*256*256+39*256+186=993210
RFILE#
1,2(取前10位)
000000001 00 =4
BLOCK
2,208,156(取后22位)
000010 11010000 10011100
2^17+208*256+156=184476

DB2远程登录配置

1.服务器端操作

[db2inst2@xifenfei ~]$ netstat -nap |grep db2sysc
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:55554               0.0.0.0:*                   LISTEN      8072/db2sysc 0
[db2inst2@xifenfei ~]$  db2 list db directory
 System Database Directory
 Number of entries in the directory = 1
Database 1 entry:
 Database alias                       = XIFENFEI
 Database name                        = XIFENFEI
 Local database directory             = /home/db2inst2/xifenfei
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =
--配置监听协议
[db2inst2@xifenfei ~]$ db2set db2comm=tcpip
--配置实例端口
[db2inst2@xifenfei ~]$ db2 update dbm cfg using svcename 5000
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.
--重启生效
[db2inst2@xifenfei ~]$ db2stop
04/02/2012 12:33:36     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst2@xifenfei ~]$ db2start
04/02/2012 12:33:41     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.
[db2inst2@xifenfei ~]$ netstat -nap |grep db2sysc
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 0.0.0.0:50000               0.0.0.0:*                   LISTEN      8462/db2sysc 0

2.客户端配置

C:\Windows\system32>db2 catalog tcpip node vm_xff remote 192.168.1.90 server 50000
DB21061E  未初始化命令行环境。
--DB21061E错误解决
C:\Windows\system32>db2cmd
--编目节点
C:\Windows\system32>db2 catalog tcpip node vm_xff remote 192.168.1.90 server 50000
DB20000I  CATALOG TCPIP NODE命令成功完成。
DB21056W  直到刷新目录高速缓存之后，目录更改才生效。
--编目数据库
C:\Windows\system32>db2 catalog db xifenfei as xifenfei at node vm_xff
DB20000I  CATALOG DATABASE命令成功完成。
DB21056W  直到刷新目录高速缓存之后，目录更改才生效。
C:\Windows\system32> db2 list node directory
 节点目录
 目录中的条目数 = 1
节点 1 条目：
 节点名                            = VM_XFF
 注释                            =
 目录条目类型                    = LOCAL
 协议                            = TCPIP
 主机名                          = 192.168.1.90
 服务名称             = 50000
--terminate使操作生效
C:\Windows\system32>db2 terminate
DB20000I  TERMINATE命令成功完成。

3.验证登陆

[db2inst2@xifenfei sqldbdir]$ db2 connect to xifenfei user db2inst2 using xifenfei
   Database Connection Information
 Database server        = DB2/LINUX 9.5.0
 SQL authorization ID   = DB2INST1
 Local database alias   = XIFENFEI
[db2inst1@xifenfei ~]$ db2 list applications
Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST2 db2bp.exe      45         192.168.1.1.38849.120411160214                                 XIFENFEI   1
DB2INST2 db2bp          50         *LOCAL.db2inst1.120402072313                                   XIFENFEI   1

oracle 9i数据库存在大量ora_j0**进程

1.发现多个ora_j0**进程
可以发现进程重启非常频繁,大概1分钟重启一次，启动ora_j0**的个数为20个

[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep
oracle 213120 1 0 11:01:55 - 0:00 ora_j019_prmdb2
oracle 282808 1 0 11:01:55 - 0:00 ora_j012_prmdb2
oracle 299178 1 0 11:01:54 - 0:00 ora_j004_prmdb2
oracle 434212 1 0 11:01:55 - 0:00 ora_j014_prmdb2
oracle 475238 1 0 11:01:54 - 0:00 ora_j011_prmdb2
oracle 487562 1 0 11:01:54 - 0:00 ora_j008_prmdb2
oracle 614612 1 0 11:01:55 - 0:00 ora_j017_prmdb2
oracle 717008 1 0 11:01:54 - 0:00 ora_j009_prmdb2
oracle 721012 1 0 11:01:54 - 0:00 ora_j006_prmdb2
oracle 749618 1 0 11:01:54 - 0:00 ora_j007_prmdb2
oracle 770268 1 0 11:01:54 - 0:00 ora_j005_prmdb2
oracle 811114 1 0 11:01:55 - 0:00 ora_j015_prmdb2
oracle 831550 1 0 11:01:55 - 0:00 ora_j016_prmdb2
oracle 847940 1 0 11:01:55 - 0:00 ora_j013_prmdb2
oracle 938076 1 0 11:01:54 - 0:00 ora_j010_prmdb2
oracle 942224 1 0 11:01:54 - 0:00 ora_j002_prmdb2
oracle 974968 1 0 11:01:55 - 0:00 ora_j018_prmdb2
oracle 434602 1 0 11:01:54 - 0:00 ora_j000_prmdb2
oracle 668104 1 0 11:01:54 - 0:00 ora_j003_prmdb2
oracle 983424 1 0 11:01:54 - 0:00 ora_j001_prmdb2
[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep
oracle 454676 1 1 11:02:56 - 0:00 ora_j012_prmdb2
oracle 696366 1 1 11:02:56 - 0:00 ora_j011_prmdb2
oracle 749628 1 1 11:02:56 - 0:00 ora_j019_prmdb2
oracle 770276 1 1 11:02:56 - 0:00 ora_j017_prmdb2
oracle 794824 1 1 11:02:56 - 0:00 ora_j010_prmdb2
oracle 827464 1 1 11:02:55 - 0:00 ora_j009_prmdb2
oracle 831560 1 1 11:02:56 - 0:00 ora_j016_prmdb2
oracle 847946 1 1 11:02:56 - 0:00 ora_j014_prmdb2
oracle 888972 1 1 11:02:55 - 0:00 ora_j007_prmdb2
oracle 934064 1 1 11:02:55 - 0:00 ora_j008_prmdb2
oracle 938080 1 1 11:02:56 - 0:00 ora_j013_prmdb2
oracle 942232 1 0 11:02:55 - 0:00 ora_j001_prmdb2
oracle 962760 1 1 11:02:55 - 0:00 ora_j006_prmdb2
oracle 434608 1 1 11:02:55 - 0:00 ora_j004_prmdb2
oracle 528810 1 2 11:02:56 - 0:00 ora_j015_prmdb2
oracle 549228 1 1 11:02:55 - 0:00 ora_j005_prmdb2
oracle 668112 1 1 11:02:55 - 0:00 ora_j003_prmdb2
oracle 709090 1 1 11:02:55 - 0:00 ora_j002_prmdb2
oracle 905720 1 2 11:02:56 - 0:00 ora_j018_prmdb2
oracle 971040 1 1 11:02:55 - 0:00 ora_j000_prmdb2
[prmsvr2:/home/oraeye]ps -ef|grep ora_j0|grep -v grep |wc -l
      20

2.其他参数

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
SQL> show parameter job;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     20
SQL> select count(*) from DBA_JOBS_RUNNING;
  COUNT(*)
----------
         0
SQL> SELECT count(*) FROM v$session_wait where event='jobq slave wait';
  COUNT(*)
----------
        20
SQL> select count(*) from dba_jobs;
  COUNT(*)
----------
         2
SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _JOB_QUEUE_INTERVAL
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_JOB_QUEUE_INTERVAL%')
NAME                             VALUE
-------------------------------- ------------------------
DESCRIPTION
----------------------------------------------------------------------
_job_queue_interval              5
Wakeup interval in seconds for job queue co-ordinator

3.对cjq进程做10046

[prmsvr2:/oracle]ps -ef|grep cjq
  oracle  327870       1   0   Feb 10      - 770:39 ora_cjq0_prmdb2
  oracle  929872  794774   0 13:24:59  pts/2  0:00 grep cjq
SQL> oradebug setospid 327870
Oracle pid: 19, Unix process pid: 327870, image: oracle@prmsvr2 (CJQ0)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever, level 12
Statement processed.
SQL>  oradebug event 10046 trace name context off;
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/admin/prmdb/bdump/prmdb2_cjq0_327870.trc

4.查看cjq的10046文件
发现大量的process startup等待，而且两次批量运行之间的时间间隔在1分钟左右。

WAIT #0: nam='process startup' ela= 59247 p1=74 p2=0 p3=0
WAIT #0: nam='process startup' ela= 51486 p1=74 p2=1 p3=0
WAIT #0: nam='process startup' ela= 51629 p1=74 p2=2 p3=0
WAIT #0: nam='process startup' ela= 48205 p1=74 p2=3 p3=0
WAIT #0: nam='process startup' ela= 47625 p1=74 p2=4 p3=0
WAIT #0: nam='process startup' ela= 55945 p1=74 p2=5 p3=0
WAIT #0: nam='process startup' ela= 47633 p1=74 p2=6 p3=0
WAIT #0: nam='process startup' ela= 51809 p1=74 p2=7 p3=0
WAIT #0: nam='process startup' ela= 57371 p1=74 p2=8 p3=0
WAIT #0: nam='process startup' ela= 50249 p1=74 p2=9 p3=0
WAIT #0: nam='process startup' ela= 51683 p1=74 p2=10 p3=0
WAIT #0: nam='process startup' ela= 47933 p1=74 p2=11 p3=0
WAIT #0: nam='process startup' ela= 47699 p1=74 p2=12 p3=0
WAIT #0: nam='process startup' ela= 47800 p1=74 p2=13 p3=0
WAIT #0: nam='process startup' ela= 47947 p1=74 p2=14 p3=0
WAIT #0: nam='process startup' ela= 52071 p1=74 p2=15 p3=0
WAIT #0: nam='process startup' ela= 47776 p1=74 p2=16 p3=0
WAIT #0: nam='process startup' ela= 47764 p1=74 p2=17 p3=0
WAIT #0: nam='process startup' ela= 47684 p1=74 p2=18 p3=0
WAIT #0: nam='process startup' ela= 47790 p1=74 p2=19 p3=0

通过O记的大力帮助,终于找出了该问题的原因:Bug 4339922: CJQ PROCESS WAKE UP JOB QUEUE PROCESSES EVERY 1 MINUTES.(THERE IS NO JOBS).因为9i的版本oracle不再提供新补丁支持,ora_j0**相关进程不停重启不太占用系统和数据库资源,在不能升级数据库的情况下,可以考虑设置job_queue_processes到一个合适值,然后忽略该问题。

DB2表空间管理(2)—DMS空间管理篇

db2的DMS表空间管理主要有Exent/Reduce/Resize/Add/begin new stripe set,下面分别对这几项进行介绍
Exent扩展已有容器

[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 3200
 Useable pages                        = 3168
 Accessible                           = Yes
SQL> select 3200*32/1024 from dual;
3200*32/1024
------------
         100
[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data extend (file
'/home/db2inst2/dms/tbs_data01.dbf' 20M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 3840
 Useable pages                        = 3808
 Accessible                           = Yes
SQL> select 3840*32/1024 from dual;
3840*32/1024
------------
         120

Reduce缩减已有容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data reduce (file
'/home/db2inst2/dms/tbs_data01.dbf' 40M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 2560
 Useable pages                        = 2528
 Accessible                           = Yes
SQL> select 2560*32/1024 from dual;
2560*32/1024
------------
          80

Resize重设容器大小

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data resize (file
'/home/db2inst2/dms/tbs_data01.dbf' 30M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
SQL> select 960*32/1024 from dual;
960*32/1024
-----------
         30

Add增加新容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data add (file
'/home/db2inst2/dms/tbs_data02.dbf' 30M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /home/db2inst2/dms/tbs_data02.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes

Add增加新容器后，会在容器件进行数据Rebalance

begin new stripe set增加新容器

[db2inst2@xifenfei ~]$ db2 "alter tablespace tbs_data begin new stripe set (file
'/home/db2inst2/dms/tbs_data03.dbf' 10M)"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 list tablespace containers for 3 show detail
            Tablespace Containers for Tablespace 3
 Container ID                         = 0
 Name                                 = /home/db2inst2/dms/tbs_data01.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 1
 Name                                 = /home/db2inst2/dms/tbs_data02.dbf
 Type                                 = File
 Total pages                          = 960
 Useable pages                        = 928
 Accessible                           = Yes
 Container ID                         = 2
 Name                                 = /home/db2inst2/dms/tbs_data03.dbf
 Type                                 = File
 Total pages                          = 320
 Useable pages                        = 288
 Accessible                           = Yes

begin new stripe set当已有容器使用完之后，再使用新增加容器

DB2表空间管理(1)—基础篇

1.创建数据库

[db2inst2@xifenfei ~]$ db2 "create database xifenfei automatic storage yes
on /home/db2inst2/dbauto dbpath on /home/db2inst2/xifenfei
using codeset utf-8 territory cn collate using system"
DB20000I  The CREATE DATABASE command completed successfully.

DBPATH ON表示数据库目录
USING CONDESET codeset TERRITORY territory指定数据库编码集(Codeset)和区域(Territory)
automatic storage yes on /home/db2inst2/dbauto 表示启用自动存储管理，on指定路径

2.查看数据库

[db2inst2@xifenfei ~]$ db2 list db directory
 System Database Directory
 Number of entries in the directory = 1
Database 1 entry:
 Database alias                       = XIFENFEI
 Database name                        = XIFENFEI
 Local database directory             = /home/db2inst2/xifenfei
 Database release level               = c.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

3.连接数据库

[db2inst2@xifenfei ~]$ db2 connect to xifenfei
   Database Connection Information
 Database server        = DB2/LINUX 9.5.0
 SQL authorization ID   = DB2INST2
 Local database alias   = XIFENFEI

4.创建缓冲池

[db2inst2@xifenfei ~]$ db2 "create bufferpool bp32k size 100 pagesize 32k"
DB20000I  The SQL command completed successfully.

DB2默认创建库指创建4k的bufferpool，因为下面需要创建32k的表空间，所以需要先创建32k的bufferpool

5.创建数据库管理(DMS)的数据表空间

[db2inst2@xifenfei ~]$ db2 "create large tablespace tbs_data pagesize 32k managed by database
using (file '/home/db2inst2/dms/tbs_data01.dbf' 100M) extentsize 32 prefetchsize automatic
bufferpool bp32k no file system caching"
DB20000I  The SQL command completed successfully.

no file system caching关闭文件系统缓存,默认选项

6.创建系统管理(SMS)的临时表空间

[db2inst2@xifenfei ~]$ db2 "create temporary tablespace tbs_temp pagesize 32k
managed by system using ('/home/db2inst2/xifenfei/temp')  bufferpool bp32k"
DB20000I  The SQL command completed successfully.

7.创建系统管理(SMS)的用户临时表空间

[db2inst2@xifenfei ~]$ db2 "create user temporary tablespace tbs_user_temp pagesize 32k
managed by system using ('/home/db2inst2/xifenfei/user_temp')  bufferpool bp32k"
DB20000I  The SQL command completed successfully.

8.自动存储管理(Automatic Storage)的表空间

[db2inst2@xifenfei ~]$ db2 "create tablespace tbs_index pagesize 32k bufferpool bp32k"
DB20000I  The SQL command completed successfully.
[db2inst2@xifenfei ~]$ db2 "create tablespace tbs_data2 initialsize 10M increasesize 10M maxsize 10G"
DB20000I  The SQL command completed successfully.

数据表空间选择DMS,临时表空间选择SMS

9.查看表空间状态

[db2inst2@xifenfei ~]$ db2 list tablespaces show detail
           Tablespaces for Current Database
 Tablespace ID                        = 0
 Name                                 = SYSCATSPACE
 Type                                 = Database managed space
 Contents                             = All permanent data. Regular table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 16384
 Useable pages                        = 16380
 Used pages                           = 10500
 Free pages                           = 5880
 High water mark (pages)              = 10500
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 4
 Prefetch size (pages)                = 4
 Number of containers                 = 1
 Tablespace ID                        = 1
 Name                                 = TEMPSPACE1
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 2
 Name                                 = USERSPACE1
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 8192
 Useable pages                        = 8160
 Used pages                           = 96
 Free pages                           = 8064
 High water mark (pages)              = 96
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 3
 Name                                 = TBS_DATA
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 3200
 Useable pages                        = 3168
 Used pages                           = 96
 Free pages                           = 3072
 High water mark (pages)              = 96
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 4
 Name                                 = TBS_TEMP
 Type                                 = System managed space
 Contents                             = System Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 5
 Name                                 = TBS_USER_TEMP
 Type                                 = System managed space
 Contents                             = User Temporary data
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1
 Useable pages                        = 1
 Used pages                           = 1
 Free pages                           = Not applicable
 High water mark (pages)              = Not applicable
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 6
 Name                                 = TBS_INDEX
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 1024
 Useable pages                        = 992
 Used pages                           = 96
 Free pages                           = 896
 High water mark (pages)              = 96
 Page size (bytes)                    = 32768
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1
 Tablespace ID                        = 7
 Name                                 = TBS_DATA2
 Type                                 = Database managed space
 Contents                             = All permanent data. Large table space.
 State                                = 0x0000
   Detailed explanation:
     Normal
 Total pages                          = 2560
 Useable pages                        = 2528
 Used pages                           = 96
 Free pages                           = 2432
 High water mark (pages)              = 96
 Page size (bytes)                    = 4096
 Extent size (pages)                  = 32
 Prefetch size (pages)                = 32
 Number of containers                 = 1

10.查看表空间和容器相关信息

[db2inst2@xifenfei ~]$ db2 list tablespace containers for 6 show detail
            Tablespace Containers for Tablespace 6
 Container ID                         = 0
 Name                                 = /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000006/C0000000.LRG
 Type                                 = File
 Total pages                          = 1024
 Useable pages                        = 992
 Accessible                           = Yes

11.显示表空间配置信息/使用信息/容器信息

[db2inst2@xifenfei ~]$ db2pd -d xifenfei -tablespaces
Database Partition 0 -- Database XIFENFEI -- Active -- Up 0 days 01:07:23
Tablespace Configuration:
Address    Id    Type Content PageSz ExtentSz Auto Prefetch BufID BufIDDisk FSC NumCntrs MaxStripe  LastConsecPg Name
0x9F43E060 0     DMS  Regular 4096   4        Yes  4        1     1         Off 1        0          3            SYSCATSPACE
0x9F43E6B0 1     SMS  SysTmp  4096   32       Yes  32       1     1         On  1        0          31           TEMPSPACE1
0x9F442EB0 2     DMS  Large   4096   32       Yes  32       1     1         Off 1        0          31           USERSPACE1
0x9F73B2E0 3     DMS  Large   32768  32       Yes  32       2     2         Off 1        0          31           TBS_DATA
0x9FAFE320 4     SMS  SysTmp  32768  32       Yes  32       2     2         On  1        0          31           TBS_TEMP
0x9FB029B0 5     SMS  UsrTmp  32768  32       Yes  32       2     2         On  1        0          31           TBS_USER_TEMP
0x9FB05420 6     DMS  Large   32768  32       Yes  32       2     2         Off 1        0          31           TBS_INDEX
0x9FB0BB20 7     DMS  Large   4096   32       Yes  32       1     1         Off 1        0          31           TBS_DATA2
Tablespace Statistics:
Address    Id    TotalPgs   UsablePgs  UsedPgs    PndFreePgs FreePgs    HWM        State      MinRecTime NQuiescers
0x9F43E060 0     16384      16380      10500      0          5880       10500      0x00000000 0          0
0x9F43E6B0 1     1          1          1          0          0          0          0x00000000 0          0
0x9F442EB0 2     8192       8160       96         0          8064       96         0x00000000 0          0
0x9F73B2E0 3     3200       3168       96         0          3072       96         0x00000000 0          0
0x9FAFE320 4     1          1          1          0          0          0          0x00000000 0          0
0x9FB029B0 5     1          1          1          0          0          0          0x00000000 0          0
0x9FB05420 6     1024       992        96         0          896        96         0x00000000 0          0
0x9FB0BB20 7     2560       2528       96         0          2432       96         0x00000000 0          0
Tablespace Autoresize Statistics:
Address    Id    AS  AR  InitSize    IncSize     IIP MaxSize     LastResize                 LRF
0x9F43E060 0     Yes Yes 33554432    -1          No  None        None                       No
0x9F43E6B0 1     Yes No  0           0           No  0           None                       No
0x9F442EB0 2     Yes Yes 33554432    -1          No  None        None                       No
0x9F73B2E0 3     No  No  0           0           No  0           None                       No
0x9FAFE320 4     No  No  0           0           No  0           None                       No
0x9FB029B0 5     No  No  0           0           No  0           None                       No
0x9FB05420 6     Yes Yes 33554432    -1          No  None        None                       No
0x9FB0BB20 7     Yes Yes 10485760    10485760    No  -2147483648 None                       No
Containers:
Address    TspId ContainNum Type    TotalPgs   UseablePgs StripeSet  Container
0x989BDE10 0     0          File    16384      16380      0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000000/C0000000.CAT
0x9F43ED00 1     0          Path    1          1          0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000001/C0000000.TMP
0x9F443500 2     0          File    8192       8160       0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000002/C0000000.LRG
0x9F73B930 3     0          File    3200       3168       0          /home/db2inst2/dms/tbs_data01.dbf
0x9F73DE90 4     0          Path    1          1          0          /home/db2inst2/xifenfei/temp
0x9FB05020 5     0          Path    1          1          0          /home/db2inst2/xifenfei/user_temp
0x9FB0B4A0 6     0          File    1024       992        0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000006/C0000000.LRG
0x9FB0C170 7     0          File    2560       2528       0          /home/db2inst2/dbauto/db2inst2/NODE0000/XIFENFEI/T0000007/C0000000.LRG

DB2实例管理

1.创建新实例

[root@xifenfei ~]# cd /opt/db2/V9.5/instance/
[root@xifenfei instance]# ./db2icrt -h
DBI1001I  Usage:
 db2icrt [-h|-?]
         [-d]
         [-a AuthType]
         [-p PortName]
         [-s InstType]
         -u FencedID InstName
[root@xifenfei instance]# useradd Fence2
[root@xifenfei instance]# passwd Fence2
Changing password for user Fence2.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@xifenfei instance]# useradd db2inst2
[root@xifenfei instance]# passwd db2inst2
Changing password for user db2inst2.
New UNIX password:
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@xifenfei instance]# ./db2icrt -a server -p 55555 -s ese -u Fence2 db2inst2
DBI1070I  Program db2icrt completed successfully.
[root@xifenfei instance]# su - db2inst2
[db2inst2@xifenfei ~]$ db2 get instance
 The current database manager instance is:  db2inst2
[db2inst2@xifenfei ~]$ db2ilist
db2inst1
db2inst2
[db2inst2@xifenfei ~]$ db2greg -dump
S,DB2,9.5.0.0,/opt/db2/V9.5,,,0,0,,1332659153,0
S,DAS,9.5.0.0,/opt/db2/V9.5/das,lib/libdb2dasgcf.so,,,, ,,
I,DAS,9.5.0.0,dasusr1,/home/dasusr1/das,,1,,/opt/db2/V9.5/das,,
I,DB2,9.5.0.0,db2inst1,/home/db2inst1/sqllib,,1,0,/opt/db2/V9.5,,
I,DB2,9.5.0.0,db2inst2,/home/db2inst2/sqllib,,1,0,/opt/db2/V9.5,,

2.启动关闭实例

[db2inst2@xifenfei ~]$ db2stop
03/31/2012 11:57:48     0   0   SQL1064N  DB2STOP processing was successful.
SQL1064N  DB2STOP processing was successful.
[db2inst2@xifenfei ~]$ db2start
03/31/2012 11:57:52     0   0   SQL1063N  DB2START processing was successful.
SQL1063N  DB2START processing was successful.

3.删除实例

--先需要关闭实例
[root@xifenfei instance]# ./db2idrop db2inst2
DBI1070I  Program db2idrop completed successfully.

hint指定index的深入理解

模拟环境
创建一个表，含有位图index和b-tree index

SQL> create table t_xifenfei as
  2  select object_id,object_name from dba_objects;
Table created.
SQL> create index b_tree_t_xifenfei on t_xifenfei(object_id);
Index created.
SQL> CREATE BITMAP INDEX  bitmap_t_xifenfei on t_xifenfei(object_name);
Index created.
SQL> BEGIN
  2  dbms_stats.gather_table_stats(USER,'T_XIFENFEI',cascade => true);
  3  END;
  4  /
PL/SQL procedure successfully completed.

无index hint

SQL> SET AUTOT TRACE EXPL STAT
SQL> SELECT OBJECT_ID FROM t_xifenfei;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 548923532
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   841K|  4109K|   886   (3)| 00:00:11 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI |   841K|  4109K|   886   (3)| 00:00:11 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      60525  consistent gets
          0  physical reads
          0  redo size
   15543305  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，预料之中事件

index hint b_tree_t_xifenfei

SQL> SET  LINESIZE 150
SQL> SELECT /*+ INDEX(T b_tree_t_xifenfei) */object_id from t_xifenfei t;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里因为object_id列可能有null值，所以不会使用b_tree_t_xifenfei索引，这里的疑惑是：
就算不会使用b_tree_t_xifenfei index也不应该会使用BITMAP_T_XIFENFEI index，因为使用这个的cost会大于全表扫描

index hint 一个无效index

SQL> SELECT /*+ INDEX(T abc) */object_id from t_xifenfei t;
845708 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1935372603
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |   841K|  4109K| 18940   (1)| 00:03:48 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_XIFENFEI        |   841K|  4109K| 18940   (1)| 00:03:48 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                   |       |       |            |          |
|   3 |    BITMAP INDEX FULL SCAN    | BITMAP_T_XIFENFEI |       |       |            |          |
--------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      91537  consistent gets
          0  physical reads
          0  redo size
   42362633  bytes sent via SQL*Net to client
     620649  bytes received via SQL*Net from client
      56382  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     845708  rows processed

这里使用了一个无效的index，也使用了BITMAP_T_XIFENFEI，让人更加的感觉奇怪

原因分析
If the INDEX hint specifies no indexes, then the optimizer considers the cost of a scan on each available index on the table and then performs the index scan with the lowest cost. The database can also choose to scan multiple indexes and merge the results, if such an access path has the lowest cost. The optimizer does not consider a full table scan.
如果我们使用hint指定了一个无效的index，优化器会扫描表中所有可以使用的index，然后选择cost最小的index或者index组合，而不会选择全表扫描。
因为我们hint指定b_tree_t_xifenfei index的时候，因为object_id可能有值为空(列没定义为not null)，所以不能使用该index，从而也就是相当于一个无效的index，从而扫描该表的其他可以使用的index，导致使用了位图索引(该类型index不排除null)，而不是全表扫描.
温馨提示:使用hint指定index的时候需要慎重，如果不合适或者无效，可能导致程序效率更低

通过dump分析undo镜像内容

一.模拟表并插入数据

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xff(id number,name varchar2(10));
Table created.
SQL> insert into t_xff values(1,'a');
1 row created.
SQL> insert into t_xff values(2,'b');
1 row created.
SQL> insert into t_xff values(3,'c');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL>  select   rowid,
  2   dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3   dbms_rowid.rowid_block_number(rowid)blockno,
  4   dbms_rowid.rowid_row_number(rowid) rowno
  5   from t_xff;
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASfUAAEAAAACvAAA          4        175          0
AAASfUAAEAAAACvAAB          4        175          1
AAASfUAAEAAAACvAAC          4        175          2

二.dump当前表数据

SQL> alter system dump datafile 4 block 175;
System altered.
--表中数据
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  62     <---注意原始值(b)
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  63

三.修改表中数据(新建会话并不提交)

SQL> select * from t_xff;
        ID NAME
---------- --------------------
         1 a
         2 b
         3 c
SQL> update t_xff set name='F' where id=2;
1 row updated.
SQL> select * from t_xff;
        ID NAME
---------- --------------------
         1 a
         2 F
         3 c

四.dump修改后数据块

SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 175;
System altered.
block_row_dump:
tab 0, row 0, @0x1f90
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 02
col  1: [ 1]  61
tab 0, row 1, @0x1f88
tl: 8 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 03
col  1: [ 1]  46     <--数据内容已经修改(由b改为了F)
tab 0, row 2, @0x1f80
tl: 8 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 04
col  1: [ 1]  63
end_of_block_dump

五.找出本次更新操作对应undo块
1.通过v$transaction视图找出

SQL> select xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec from v$transaction;
    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         2         31        750       8155          3          6

2.通过更新块的XID信息找出

Block header dump:  0x010000af
 Object id on Block? Y
 seg/obj: 0x127d4  csc: 0x00.11216d  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10000a8 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.003.000001ff  0x00c01748.009f.10  C---    0  scn 0x0000.00112130
0x02   0x0002.01f.000002ee  0x00c01fdb.00f5.06  ----    1  fsc 0x0000.00000000
bdba: 0x010000af
data_block_dump,data header at 0xb6ce9664
--这里可以看出Itl=0x02为锁信息
SQL> select name from v$rollname where usn=2;
NAME
------------------------------------------------------------
_SYSSMU2_4228238222$
SQL> alter system dump undo header "_SYSSMU2_4228238222$";
System altered.
 index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
   0x00    9    0x00  0x02ee  0x0019  0x0000.0010cc90  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x01    9    0x00  0x02ee  0x0018  0x0000.0010cf00  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333662985
   0x02    9    0x00  0x02ee  0x0000  0x0000.0010cc84  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x03    9    0x00  0x02ee  0x0011  0x0000.00112094  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670810
   0x04    9    0x00  0x02ee  0x0012  0x0000.0010ccc1  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x05    9    0x00  0x02ee  0x0017  0x0000.0010cd13  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x06    9    0x00  0x02ee  0x0004  0x0000.0010ccb9  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x07    9    0x00  0x02ee  0xffff  0x0000.00112119  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x08    9    0x00  0x02ee  0x0006  0x0000.0010ccab  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x09    9    0x00  0x02ee  0x000a  0x0000.0010ccf4  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0a    9    0x00  0x02ee  0x0014  0x0000.0010ccf8  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0b    9    0x00  0x02ee  0x001a  0x0000.0010d061  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333663886
   0x0c    9    0x00  0x02ee  0x0009  0x0000.0010ccdc  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x0d    9    0x00  0x02ee  0x0001  0x0000.0010ce1f  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333662386
   0x0e    9    0x00  0x02ee  0x001d  0x0000.00112113  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x0f    9    0x00  0x02ed  0x0002  0x0000.0010cc79  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x10    9    0x00  0x02ee  0x001e  0x0000.00112017  0x00000000  0x0000.000.00000000  0x00000000   0x00000000  1333670781
   0x11    9    0x00  0x02ed  0x000e  0x0000.001120dd  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670813
   0x12    9    0x00  0x02ee  0x000c  0x0000.0010ccd3  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x13    9    0x00  0x02ee  0x0016  0x0000.0010cd2e  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x14    9    0x00  0x02ee  0x0005  0x0000.0010cd0b  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x15    9    0x00  0x02ed  0x0020  0x0000.0010cc9d  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x16    9    0x00  0x02ee  0x000d  0x0000.0010cd33  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x17    9    0x00  0x02ee  0x0013  0x0000.0010cd20  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x18    9    0x00  0x02ee  0x000b  0x0000.0010d051  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333663886
   0x19    9    0x00  0x02ed  0x0015  0x0000.0010cc96  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x1a    9    0x00  0x02ed  0x001b  0x0000.0010d102  0x00c01fda  0x0000.000.00000000  0x00000002   0x00000000  1333664305
   0x1b    9    0x00  0x02ee  0x0010  0x0000.0010d13e  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333664453
   0x1c    9    0x00  0x02c5  0x000f  0x0000.0010cc72  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x1d    9    0x00  0x02ee  0x0007  0x0000.00112115  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  1333670830
   0x1e    9    0x00  0x02ee  0x0021  0x0000.00112035  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670797
   0x1f   10    0x80  0x02ee  0x0003  0x0000.00112157  0x00c01fdb  0x0000.000.00000000  0x00000001   0x00000000  0
   0x20    9    0x00  0x02ed  0x0008  0x0000.0010cca3  0x00c01fd3  0x0000.000.00000000  0x00000001   0x00000000  1333661786
   0x21    9    0x00  0x02ec  0x0003  0x0000.00112052  0x00c01fda  0x0000.000.00000000  0x00000001   0x00000000  1333670803
SQL> select to_number('00c01fdb','xxxxxxxxxxx') from dual;
TO_NUMBER('00C01FDB','XXXXXXXXXXX')
-----------------------------------
                           12591067
SQL> select dbms_utility.data_block_address_file(12591067) file#,
  2  dbms_utility.data_block_address_block(12591067) block  from dual;
     FILE#      BLOCK
---------- ----------
         3       8155

3.通过更新块的Uba信息找出

00c01fdb 对应的2进制为:
0000 0000  11 | 00 0000 0001 1111 1101 1011
2+1=3           4096+2048+1024+512+256+128+64+16+8+2+1=8155

六.dump 对应undo数据块

SQL> alter system dump datafile 3 block 8155;
System altered.
uba: 0x00c01fdb.00f5.04 ctl max scn: 0x0000.0010cc60 prv tx scn: 0x0000.0010cc6e
txn start scn: scn: 0x0000.00112028 logon user: 84
 prev brb: 12591059 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
Array Update of 1 rows:
tabn: 0 slot: 1(0x1) flag: 0x2c lock: 0 ckix: 0
ncol: 2 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x010000af  hdba: 0x010000aa
itli: 2  ispac: 0  maxfr: 4858
vect = 3
col  1: [ 1]  62   <---以前的值(b)

试验说明:数据库的undo只是保存修改值的前镜像，而非修改数据块或者行记录的镜像