1.展示列属性

[oracle@saas-xunzhi-db1 ~]$ sqlplus testga/testga
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 12 16:26:56 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> desc t_xifenfei;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROCESS_INSTANCE_ID                       NOT NULL NUMBER(10)
 STATUS_NEW                                         NUMBER
 PACKAGE_NAME                                       VARCHAR2(50)
SQL> desc wf_proc_inst@oldmoa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(10)
 NAME                                               VARCHAR2(500)
 PROC_ID                                   NOT NULL NUMBER(10)
 PARE_INST_ID                              NOT NULL NUMBER(10)
 PARE_ACTI_ID                              NOT NULL NUMBER(10)
 INST_CREATOR                                       VARCHAR2(40)
 CREAT_DATE                                         DATE
 STATUS                                             CHAR(1)
 ATT1                                               NUMBER(10)
 ATT2                                               VARCHAR2(255)
 SEQVALUE                                           VARCHAR2(50)

从这里可以看出，原表的status列是char，而目标表的STATUS_NEW列是number类型

2.通过dblink查询列值

SQL> select t.inst_id as ID,
  2         case w.status
  3           when '0' then
  4            4
  5           when '2' then
  6            3
  7           else
  8            1
  9         end as  status_new,
 10         p.name as PACKAGE_NAME
 11    from wf_proc_inst@oldmoa i
 12    left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id
 13    left join wf_proc_type@oldmoa p on t.type_id = p.id
 14    left join wf_proc_inst@oldmoa w on t.inst_id = w.id
 15   where t.inst_id = i.id
 16     and t.proc_id <> 53
 17     and t.is_ok = 1
 18     AND t.inst_id <= 4837
 19     AND t.inst_id>=4735;
        ID STATUS_NEW PACKAGE_NAME
---------- ---------- --------------------------
      4755          3 呈批处理
      4836          3 公文处理

从这里可以看出来，case工作正常，传入的char类型，通过case转换为number类型

3.使用dblink插入数据并查询

SQL> INSERT INTO t_xifenfei
  2  select t.inst_id as ID,
  3         case w.status
  4           when '0' then
  5            4
  6           when '2' then
  7            3
  8           else
  9            1
 10         end as  status_new,
 11         p.name as PACKAGE_NAME
 12    from wf_proc_inst@oldmoa i
 13    left join wf_proc_info_inst@oldmoa t on t.inst_id = i.id
 14    left join wf_proc_type@oldmoa p on t.type_id = p.id
 15    left join wf_proc_inst@oldmoa w on t.inst_id = w.id
 16   where t.inst_id = i.id
 17     and t.proc_id <> 53
 18     and t.is_ok = 1
 19     AND t.inst_id <= 4837
 20     AND t.inst_id>=4735;
2 rows created.
SQL> commit;
Commit complete.
SQL> col package_name for a10
SQL> select * from t_xifenfei;
PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
               4755          1 呈批处理
               4836          1 公文处理

这里出现异常，怀疑case中传入的值，都走到else那边了，怀疑是在使用dblink 的时候，char的类型发生的改变，转为了number，导致case匹配失败，所以走到else。

4.在dblink端创建视图，插入数据

SQL> CREATE VIEW GZZJ.v_xifenfei
  2  AS
  3  select t.inst_id as ID,
  4         case to_char(w.status)
  5           when '0' then
  6            4
  7           when '2' then
  8            3
  9           else
 10            1
 11         end as  status_new,
 12         p.name as PACKAGE_NAME
 13    from GZZJ.wf_proc_inst i
 14    left join GZZJ.wf_proc_info_inst t on t.inst_id = i.id
 15    left join GZZJ.wf_proc_type p on t.type_id = p.id
 16    left join GZZJ.wf_proc_inst w on t.inst_id = w.id
 17   where t.inst_id = i.id
 18     and t.proc_id <> 53
 19     and t.is_ok = 1
 20     AND t.inst_id <= 4837
 21     AND t.inst_id>=4735;
View created.
SQL> insert into t_xifenfei
  2  select * from v_xifenfei@oldmoa;
2 rows created.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
PROCESS_INSTANCE_ID STATUS_NEW PACKAGE_NA
------------------- ---------- ----------
               4755          3 呈批处理
               4836          3 公文处理

通过远程视图，接触数据类型发生改变异常(怀疑是dblink 在insert时候，char数据类型变成了number，但是未得到官方或者权威的确认)

1.试验前提

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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> select FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,
   2 SUPPLEMENTAL_LOG_DATA_MIN from v$database;
FOR SUP SUP SUPPLEME
--- --- --- --------
NO  NO  NO  NO
SQL> conn / as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/archivelog/chf
Oldest online log sequence     1879
Next log sequence to archive   1881
Current log sequence           1881
SQL> select count(*) from t_xifenfei_move;
  COUNT(*)
----------
   7432085
SQL> select bytes/1024/1024 from user_segments where segment_name='T_XIFENFEI_MOVE';
BYTES/1024/1024
---------------
            832

从上面信息可以看到数据库处于归档模式，强制日志和辅助日志为开启，试验测试表t_xifenfei_move有7432085条记录，占用硬盘空间832M

2.常规CAST

SQL>  alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> SET TIMING ON
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            100028
undo change vector size                                               16172
Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_CAST tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:01:58.10
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         873150548
undo change vector size                                              131384
Elapsed: 00:00:00.01
SQL> select 873150548-100028 "redo size" from dual;
 redo size
----------
 873050520
SQL> select 131384-16172 "undo size" from dual;
 undo size
----------
    115212

通过这个可以得出结论，产生redo为873050520，undo为115212

3.普通INSERT

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:00.06
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.01
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.18
SQL> CREATE TABLE t_xifenfei_move_INSERT
  2  AS
  3  SELECT * FROM T_XIFENFEI_MOVE WHERE 1=0;
Table created.
Elapsed: 00:00:00.32
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22712
undo change vector size                                                6512
Elapsed: 00:00:00.02
SQL> INSERT INTO t_xifenfei_move_INSERT
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:01:59.47
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862261580
undo change vector size                                            27980508
--redo
SQL> select 22712-732 "create redo size" from dual;
create redo size
----------------
           21980
SQL> select 862261580-22712 "insert redo size" from dual;
insert redo size
----------------
       862238868
--undo
SQL> select 6512-136 "create undo size" from dual;
create undo size
----------------
            6376
SQL> select 27980508-6512 "insert undo size" from dual;
insert undo size
----------------
        27973996

通过这个可以得出CREATE TABLE 过程中产生redo:21980,undo:6376,而INSERT 过程中产生的redo:862238868,undo:27973996,整个过程总的产生redo:862260848(862238868+21980),undo:27980372(27973996+6376)

4.INSERT+APPEND

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:25.19
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_INSERT_A tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.18
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             21892
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+ append */INTO t_xifenfei_move_INSERT_A
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:26.37
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         872792032
undo change vector size                                               87764
--redo
SQL> select 21892-732 "create redo size" from dual;
create redo size
----------------
           21160
SQL> select 872792032-21892 "insert redo size" from dual;
insert redo size
----------------
       872770140
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 87764-6308 "insert undo size" from dual;
insert undo size
----------------
           81456

这个过程可以得到结论,create 表的过程产生的redo:21160,undo:6172;insert 表的过程redo:872770140,undo:81456;整个过程产生的redo:872791300(21160+872770140),undo:87628(6172+81456)

5.INSERT+NOLOGGING

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:02.21
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.06
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136
Elapsed: 00:00:00.06
SQL> create table chf.t_xifenfei_move_INSERT_N tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.22
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22056
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+ NOLOGGING */INTO t_xifenfei_move_INSERT_N
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:30.33
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862123984
undo change vector size                                            27982876
--redo
SQL> select 22056-780 "create redo size" from dual;
create redo size
----------------
           21276
SQL> select 862123984-22056 "insert redo size" from dual;
insert redo size
----------------
       862101928
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 27982876-6308 "insert undo size" from dual;
insert undo size
----------------
        27976568

这个试验过程得出,create table得到redo:21276,undo:6172,insert table 得到redo:862101928,undo:27976568,整个过程redo:862123204,undo:27982740(27976568+6172)

6.INSERT+NOLOGGING(TABLE)

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:23.68
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               800
undo change vector size                                                 136
Elapsed: 00:00:00.18
SQL> create table chf.t_xifenfei_move_INSERT_N_new nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.71
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             23296
undo change vector size                                                6756
Elapsed: 00:00:00.00
SQL> INSERT INTO t_xifenfei_move_INSERT_N_new
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:02:37.51
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         862031304
undo change vector size                                            27982776
SQL> select 23296-800 "create redo size" from dual;
create redo size
----------------
           22496
SQL> select 862031304-23296 "insert redo size" from dual;
insert redo size
----------------
       862008008
SQL> select 6756-136 "create undo size" from dual;
create undo size
----------------
            6620
SQL> select 27982776-6756 "insert undo size" from dual;
insert undo size
----------------
        27976020

通过该试验得出,create table 产生redo:22496,undo:6620;insert into 产生redo:862008008,undo:27976020;整个过程产生redo:862030504(22496+862008008),undo:27982640(6620+27976020)

7.APPEND+NOLOGGING(TABLE)

SQL> alter system flush buffer_cache;
System altered.
Elapsed: 00:00:23.59
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.05
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               780
undo change vector size                                                 136
Elapsed: 00:00:00.04
SQL> create table chf.t_xifenfei_move_INSERT_NA nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move where 1=0;
Table created.
Elapsed: 00:00:00.42
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                             22000
undo change vector size                                                6308
Elapsed: 00:00:00.00
SQL> INSERT /*+APPEND */ INTO t_xifenfei_move_INSERT_NA
  2  SELECT * FROM T_XIFENFEI_MOVE;
7432085 rows created.
Elapsed: 00:01:08.92
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            602352
undo change vector size                                               82756
--redo
SQL> select 22000-780 "create redo size" from dual;
create redo size
----------------
           21220
SQL> select 602352-22000 "insert redo size" from dual;
insert redo size
----------------
          580352
--undo
SQL> select 6308-136 "create undo size" from dual;
create undo size
----------------
            6172
SQL> select 82756-6308 "insert undo size" from dual;
insert undo size
----------------
           76448

在这个试验中，create table产生redo:21220,undo:6172,insert into产生redo:580352,undo:76448;整个过程产生的redo:601572(
21220+580352),undo:82620(6172+76448)

8.CAST+NOLOGGING(TABLE)

SQL>  alter system flush buffer_cache;
System altered.
Elapsed: 00:00:03.35
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.04
SQL>
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               732
undo change vector size                                                 136
Elapsed: 00:00:00.05
SQL> create table chf.t_xifenfei_move_cast_N nologging tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:00:56.41
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            769848
undo change vector size                                              124944
SQL> select 769848-732 "redo size" from dual;
 redo size
----------
    769116
SQL> select 124944-136 "undo  size" from dual;
undo  size
----------
    124808

这个试验产生的redo:769116,undo:124808

9.通过试验得出结论
1)sql hint中的nologgging无效
2)普通的cast(不含hint)，其本质是append，无nologgging
3)nologgging(表级别)可以使得cast效率较高
4)nologgging(表级别)+append(hint)可以使得insert效率较高

本篇文章是MOVE和CAST比较的续篇，主要是对于第一篇中没有涉及到的redo和undo的情况加以叙述
1.查询move产生redo和undo量

SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                          VALUE
---------------------------------------- ----------
redo size                                       844
undo change vector size                         136
SQL> ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE TEST_OCP;
Table altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                          VALUE
---------------------------------------- ----------
redo size                                 873074928
undo change vector size                      110748
--产生redo
SQL> select 873074928-844 "redo size" from dual;
 redo size
----------
 873074084
--产生undo
SQL> select 110748-136 "undo size" from dual;
 undo size
----------
    110612

2.查询cast产生redo和undo 大小

SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
and lower(a.name) in
  4    5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                               776
undo change vector size                                                 136
SQL> create table chf.t_xifenfei_move_new tablespace users
  2  as
  3  select * from chf.t_xifenfei_move;
Table created.
SQL> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) in
  5  ('redo size','undo change vector size');
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         873017580
undo change vector size                                              115340
--产生redo
SQL> select 873017580-776 "redo size" from dual;
 redo size
----------
 873016804
--产生undo
SQL> select 115340-136 "undo size" from dual;
 undo size
----------
    115204

3.两次实验比较

--redo(分母使用cast操作产生redo)
SQL> select 873074084-873016804 "redo" from dual;
      redo
----------
     57280
SQL> select 57280/873074084 from dual;
57280/873074084
---------------
     .000065607
--undo(分母使用cast操作产生undo)
SQL> select 110612-115204 undo from dual;
         undo
-------------
        -4592
SQL> select 4592/115204 from dual;
4592/115204
-----------
 .039859727

通过这两个的比较可以知道move操作产生的redo多了万分之七不到，undo少了百分之四，这些也是在实验允许的误差范围内，再说move操作还包括了cast的一些后续步骤在其中，所以通过这个验证和上一篇试验(MOVE和CAST比较)，基本上可以大胆操作move操作的本质就是全表扫描+append插入数据，操作过程中产生的redo大小几乎和表本身大小(872415232)相等,这个证明,move和cast都是以logging模式运行(数据库本身是非force logging模式)

1.创建模拟表

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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 chf.t_xifenfei_move
  2  as
  3  select * from dba_objects;
Table created.
SQL> select count(*) from chf.t_xifenfei_move;
  COUNT(*)
----------
     73585
SQL> create table chf.t_xifenfei_move
  2  as
  3  select * from dba_objects;
Table created.
SQL> select count(*) from chf.t_xifenfei_move;
  COUNT(*)
----------
     73585
SQL> DECLARE
  2     i NUMBER;
  3      BEGIN
  4        FOR i IN 1..100 LOOP
  5          INSERT INTO chf.t_xifenfei_move
  6          select * from dba_objects;
  7          END LOOP;
  8          COMMIT;
  9      END;
 10      /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('CHF','T_XIFENFEI_MOVE');
PL/SQL procedure successfully completed.
SQL> select bytes from dba_segments where segment_name='T_XIFENFEI_MOVE';
     BYTES
----------
 872415232

2.测试move
2.1)执行move操作，记录时间

SQL> alter system flush buffer_cache;
System altered.
SQL> SET TIMING ON;
SQL> alter session set events
  2  '10046 trace name context forever,level 1';
Session altered.
Elapsed: 00:00:00.00
SQL> ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE USERS;
Table altered.
Elapsed: 00:02:11.77
SQL> alter session set events
  2  '10046 trace name context off';
Session altered.
Elapsed: 00:00:00.04
SQL> select d.value||'/'||lower(rtrim(i.instance,chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name from
  2  (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
  3  (select t.instance from v$thread t,v$parameter v where v.name = 'thread' and(v.value = 0 or t.thread# = to_number(v.value))) i,
  4  (select value from v$parameter where name = 'user_dump_dest') d;
TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_4765.trc

从这里可以看出，move操作执行了00:02:11.77

2.2)查看trace内容

[oracle@node1 ~]$ tkprof  /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_4765.trc /tmp/xifenfei_move.txt
TKPROF: Release 11.2.0.3.0 - Development on Tue Jan 10 10:57:59 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
********************************************************************************
SQL ID: c1yk5pv0v1wg1 Plan Hash: 2931676921
ALTER TABLE CHF.T_XIFENFEI_MOVE MOVE TABLESPACE USERS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.05          7          8          0           0
Execute      1     11.29     131.23     105584     106275     115654     7432085
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     11.30     131.29     105591     106283     115654     7432085
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  (cr=117799 pr=105602 pw=105585 time=131351005 us)
   7432085    7432085    7432085   TABLE ACCESS FULL T_XIFENFEI_MOVE (cr=105591 pr=105586 pw=0 time=4735560 us cost=23453 size=720912245 card=7432085)
********************************************************************************

从这里可以看出执行move，其实本质是全表扫描表，然后append方式插入数据，而不是真的数据块拷贝

3.测试CAST
3.1).CAST插入数据过程

SQL> alter system flush buffer_cache;
System altered.
SQL> SET TIMING ON;
SQL> alter session set events
2 ‘10046 trace name context forever,level 1’;
Session altered.
Elapsed: 00:00:00.01
SQL> create table chf.t_xifenfei_move_new tablespace test_ocp
2 as
3 select * from chf.t_xifenfei_move;
Table created.
Elapsed: 00:01:59.22
SQL> alter session set events
2 ‘10046 trace name context off’;
Session altered.
Elapsed: 00:00:00.00
SQL> select d.value||’/’||lower(rtrim(i.instance,chr(0)))||’_ora_’||p.spid||’.trc’ trace_file_name from
2 (select p.spid from v$mystat m, v$session s,v$process p where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr ) p,
3 (select t.instance from v$thread t,v$parameter v where v.name = ‘thread’ and(v.value = 0 or t.thread# = to_number(v.value))) i,
4 (select value from v$parameter where name = ‘user_dump_dest’) d;
TRACE_FILE_NAME
——————————————————————————–
/opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_5121.trc

从这里看出cast操作用时:00:01:59.22，比move稍微少，但是cast要实现move完全的功能，还需要表重命名，表授权，编译无效对象等。

3.2)查看trace内容

[oracle@node1 ~]$ tkprof  /opt/oracle/diag/rdbms/chf/chf/trace/chf_ora_5121.trc /tmp/xifenfei_create.txt
TKPROF: Release 11.2.0.3.0 - Development on Tue Jan 10 11:08:19 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
********************************************************************************
create table chf.t_xifenfei_move_new tablespace test_ocp
as
select * from chf.t_xifenfei_move
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          1          4          0           0
Execute      1      9.85     118.37     105587     106097     112387     7432085
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      9.85     118.40     105588     106101     112387     7432085
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD AS SELECT  (cr=106631 pr=105592 pw=105585 time=118338607 us)
   7432085    7432085    7432085   TABLE ACCESS FULL T_XIFENFEI_MOVE (cr=105591 pr=105586 pw=0 time=2935008 us cost=23453 size=720912245 card=7432085)
********************************************************************************

通过这个可以看出，CAST其实本质也是全表扫描，然后append方式插入数据

4.比较move和cast
4.1)通过比较执行时间，cast稍微少，但是还有后续操作需要时间
4.2)通过比较执行计划，两者是一样的
4.3)move操作在整个过程中都会锁表,而cast不会锁住原表(select+where可以减少停业务时间)
4.4)move操作会一次性处理好权限,plsql/view等有效,而cast在rename之后，相关对象可能需要重新编译，重新授权等操作
4.5)cast操作index需要新建(create),而move操作index需要重建(rebuild)
4.6)cast完成后，需要对表重命名，删除原表操作操作，而这个操作move不用

5.选择使用谁
5.1)如果停业务时间够长，建议使用move操作
5.2)如果停业务时间不能太长，可以使用cast+where实现
5.3)如果数据库版本>=10g,且表空间使用local管理，那么可以考虑在不停业务的情况下使用shrink实现类此功能

至于MOVE和CAST在执行过程中，关于产生的redo和undo的比较，请见下篇:MOVE和CAST比较(续)

1.数据库版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

2.查询DBA_DATA_FILES视图

SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(MAXBYTES)/1024
--------------- ------------------
EXAMPLE                   33554416
OGG                        5242880
SYSAUX                    33554416
SYSTEM                    33554416
TEMP                      33554416
TS_INDEX_BASE             15728640
TS_PUB_BASE               15728640
UNDOTBS1                  33554416
USERS                     33554416
9 rows selected.

3.查询DBA_HIST_TBSPC_SPACE_USAGE视图

SQL> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;
NAME            TABLESPACE_MAXSIZE
--------------- ------------------
EXAMPLE                    4194302
OGG                         655360
SYSAUX                     4194302
SYSTEM                     4194302
TEMP                       4194302
TS_INDEX_BASE              1966080
TS_PUB_BASE                1966080
UNDOTBS1                   8388604
USERS                      4194302
9 rows selected.

观察者两个视图的运行结果，DBA_HIST_TBSPC_SPACE_USAGE视图收集到的统计大小和实际大小都存在一定的误差，但是UNDO表空间出入太明显(UNDOTBS1),特别是最大值和当前值，几乎是真实大小的两倍

4.排除原因
4.1)收集信息是否是最新

SQL> select MAX(rtime) FROM DBA_HIST_TBSPC_SPACE_USAGE;
MAX(RTIME)
-------------------------
01/09/2012 15:00:50

4.2)statistics_level是否被设置为basic

SQL> show parameter statistics_level;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

通过这两个查询证明，收集信息和statistics_level都是符合要求，那么为什么undo空间的空间信息还是正常的两倍呢？

5.怀疑bug，查询mos

6.查询11.2.0.3中DBA_HIST_TBSPC_SPACE_USAGE是否正常

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit 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> col name for a15
SQL> SELECT NAME, TABLESPACE_MAXSIZE
  2    FROM DBA_HIST_TBSPC_SPACE_USAGE A, V$TABLESPACE B
  3   WHERE A.TABLESPACE_ID = B.TS#
  4     AND SNAP_ID = (select MAX(snap_id) FROM DBA_HIST_TBSPC_SPACE_USAGE)
  5   ORDER BY NAME ;
NAME            TABLESPACE_MAXSIZE
--------------- ------------------
DRSYS_1                    4194302
EXAMPLE                    4194302
ODU                        8139262
SYSAUX                     4194302
SYSTEM                     4194302
TEMP                       4194302
TEST_OCP                   4194302
UNDOTBS01                  3938560
USERS                      4194302
9 rows selected.
SQL> col tablespace_name for a15
SQL>  SELECT TABLESPACE_NAME,
  2          SUM(MAXBYTES) / 1024  3
  5     FROM DBA_DATA_FILES
  6    GROUP BY TABLESPACE_NAME
  7    UNION
  8     SELECT TABLESPACE_NAME,
  9          SUM(MAXBYTES) / 1024
 12     FROM DBA_TEMP_FILES
 13    GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(MAXBYTES)/1024
--------------- ------------------
DRSYS_1                   33554416
EXAMPLE                   33554416
ODU                       65114096
SYSAUX                    33554416
SYSTEM                    33554416
TEMP                      33554416
TEST_OCP                  33554416
UNDOTBS01                 31457280
USERS                     33554416
9 rows selected.

通过对比，发现基本误差不大，确定在该版本，bug7578292已经被修复

一.系统当前状态

[root@node1 ora11g]#  cat /proc/meminfo | grep -i hugepages
HugePages_Total:     0
HugePages_Free:      0
HugePages_Rsvd:      0
Hugepagesize:     2048 kB
[root@node1 ora11g]# uname -sr
Linux 2.6.18-238.19.1.el5
cat /proc/meminfo|grep PageT
PageTables:      44748 kB

二.计算nr_hugepages值

#!/bin/bash
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support
# http://support.oracle.com
# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments. Before proceeding with the execution please make sure
that:
 * Oracle Database instance(s) are up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not setup
   (See Doc ID 749851.1)
 * The shared memory segments can be listed by command:
     # ipcs -m
Press Enter to proceed..."
read
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
# Initialize the counter
NUM_PG=0
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | awk '{print $5}' | grep "[0-9][0-9]*"`
do
   MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
   if [ $MIN_PG -gt 0 ]; then
      NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
   fi
done
RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`
# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
   echo "***********"
   echo "** ERROR **"
   echo "***********"
   echo "Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:
   # ipcs -m
of a size that can match an Oracle Database SGA. Please make sure that:
 * Oracle Database instance is up and running
 * Oracle Database 11g Automatic Memory Management (AMM) is not configured"
   exit 1
fi
# Finish with results
case $KERN in
   '2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
          echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
   '2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
    *) echo "Unrecognized kernel version $KERN. Exiting." ;;
esac
# End

1.Configuring the Script

Create a text file named hugepages_settings.sh
Copy the contents below in the file
Run
	$ chmod +x hugepages_settings.sh

2.Running the Script

Be sure that all applications that are meant to use HugePage / HugeTLB are running at the time the script is to be run.
This includes the Oracle RDBMS instances and ASM instances in addition to other applications.
Be sure that you have /bin and /usr/bin in $PATH
Run
	$ ./hugepages_settings.sh

三.修改系统配置

1./etc/sysctl.conf
vm.nr_hugepages = 770
2./etc/security/limits.conf
oracle soft memlock 102400
oracle hard memlock 102400
计算公式为：>=HugePages_Total*Hugepagesize

四.重启系统检查配置是否生效

[root@node1 ~]# cat /proc/meminfo | grep -i hugepages
HugePages_Total:   770
HugePages_Free:    770
HugePages_Rsvd:      0
Hugepagesize:     2048 kB

五.启动数据库并验证

[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jan 4 16:06:48 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             486539784 bytes
Database Buffers         1107296256 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.
SQL> exit
[oracle@node1 ~]$ grep Huge /proc/meminfo
HugePages_Total:   770
HugePages_Free:    591
HugePages_Rsvd:    590
Hugepagesize:     2048 kB
[oracle@node1 ~]$ cat /proc/meminfo|grep PageT
PageTables:      13216 kB

HugePages_Free<>HugePages_Total,PageTables变小
证明HugePages配置成功

六.补充说明
1.在数据库内存服务器内存比较大的时候，可以考虑使用该功能，提高系统性能
2.对于Oracl 11g,如果需要使用此功能，需要关闭AMM功能(需要衡量)

一、定位数据块
1.bbed查看数据块

--第一个数据块
BBED> set block 1
        BLOCK#          1
BBED> dump count 16
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:    0 to   15           Dba:0x00000000
------------------------------------------------------------------------
 0ba20000 01004001 00000000 00000104
 <32 bytes per line>
BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 0ba20000 01004001 00000000 00000104 387a0000 00000000 0003200b 74684acd
 43484600 00000000 7d4a0000 00320000 00200000 05000300 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 d28a0900 00000000 1ab5d72c f7b4d72c c5320900 00000000 00000000
 <32 bytes per line>
--第二个数据块
BBED> set block 2
        BLOCK#          2
BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 2                Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 1da20000 02004001 08dee400 00000204 f6b80000 05000000 08000000 00320000
 09000000 50000000 feff3f00 07000000 00320000 7f000000 fa010000 07dee400
 00000000 00000000 00000000 00000000 b1220000 08000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--第100个数据块
BBED> set block 100
        BLOCK#          100
BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 100              Offsets:    0 to  127           Dba:0x00000000
------------------------------------------------------------------------
 06a20000 64004001 343f0900 00000104 a38d0000 02002000 e1c90000 343f0900
 0000e81f 021f3200 61004001 00000000 00000000 00000000 00000000 00000000
 00000000 05000900 2c010000 52008000 c8005900 00800000 a3350900 00000000
 00000000 00008001 00000000 6b00fa00 5e1a6419 00000000 00000000 00000000
 <32 bytes per line>

2.UltraEdit查看数据块

--第0个数据块
--(为了便于和bbed的block一致，称为0比较合适，因为这个块在bbed中看不到)
00000000h: 00 A2 00 00 00 00 C0 FF 00 00 00 00 00 00 00 00 ; .?...?........
00000010h: 66 C8 00 00 00 20 00 00 00 32 00 00 7D 7C 7B 7A ; f?.. ...2..}|{z
00000020h: A0 81 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; 爜..............
00000030h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
……………………………………………………
00001fa0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fb0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fc0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fd0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001fe0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
00001ff0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--第一个数据块（bbed中的block 1）
00002000h: 0B A2 00 00 01 00 40 01 00 00 00 00 00 00 01 04 ; .?...@.........
00002010h: 38 7A 00 00 00 00 00 00 00 03 20 0B 74 68 4A CD ; 8z........ .thJ?
……………………………………………………………
000021c0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
000021d0h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--第二个数据块(bbed中的block 2)
00004000h: 1D A2 00 00 02 00 40 01 08 DE E4 00 00 00 02 04 ; .?...@..掬.....
00004010h: F6 B8 00 00 05 00 00 00 08 00 00 00 00 32 00 00 ; 龈...........2..
00004020h: 09 00 00 00 50 00 00 00 FE FF 3F 00 07 00 00 00 ; ....P...??.....
00004030h: 00 32 00 00 7F 00 00 00 FA 01 00 00 07 DE E4 00 ; .2.....?...掬.
00004040h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--第100个数据块(bbed中的block 100)
000c8000h: 06 A2 00 00 64 00 40 01 34 3F 09 00 00 00 01 04 ; .?.d.@.4?......
000c8010h: A3 8D 00 00 02 00 20 00 E1 C9 00 00 34 3F 09 00 ; .... .嵘..4?..
000c8020h: 00 00 E8 1F 02 1F 32 00 61 00 40 01 00 00 00 00 ; ..?..2.a.@.....
000c8030h: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
000c8040h: 00 00 00 00 05 00 09 00 2C 01 00 00 52 00 80 00 ; ........,...R.€.
000c8050h: C8 00 59 00 00 80 00 00 A3 35 09 00 00 00 00 00 ; ?Y..€..?......
000c8060h: 00 00 00 00 00 00 80 01 00 00 00 00 6B 00 FA 00 ; ......€.....k.?
000c8070h: 5E 1A 64 19 00 00 00 00 00 00 00 00 00 00 00 00 ; ^.d.............

3.结论:因为2000(16进制)=8192(10进制),而每行又是16，所以UltraEdit定位块的规则是
SELECT to_char(‘2*块数’,’xxxxxxx’) xff FROM dual;(xff*1000)

SQL> SELECT TO_number('2000','xxxxxx') FROM dual;
TO_NUMBER('2000','XXXXXX')
--------------------------
                      8192

二、UltraEdit定位块内内容
1.bbed查看内容

--第一块偏移量为484数据
BBED> set block 1
        BLOCK#          1
BBED> set offset 484
        OFFSET          484
BBED> dump count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets:  484 to  611           Dba:0x00000000
------------------------------------------------------------------------
 c052e700 00000000 ec82ba2d 01000000 2e060000 39020000 10008984 02000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--第一块偏移量为8188数据
BBED> dump offset 8188 count 128
 File: /opt/oracle/oradata/chf/example01.dbf (0)
 Block: 1                Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 010b0000
 <32 bytes per line>

2.UltraEdit中内容

--offset 484(kscnbas)
000021e0h: 00 00 00 00 C0 52 E7 00 00 00 00 00 EC 82 BA 2D ; ....繰?....靷?
000021f0h: 01 00 00 00 2E 06 00 00 39 02 00 00 10 00 89 84 ; ........9.....墑
00002200h: 02 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ; ................
--offset 8188(tailchk)
00003ff0h: 00 00 00 00 00 00 00 00 00 00 00 00 01 0B 00 00 ; ................

3.结论:10进制的便宜量转换为16进制，然后对照你块开始行号+偏移量(16进制),得到对应偏移量开始位置

SQL> SELECT to_char('484','xxxxxxx') FROM dual;
TO_CHAR('484','X
----------------
     1e4
SQL> SELECT to_char('8188','xxxxxxx') FROM dual;
TO_CHAR('8188','
----------------
    1ffc

三.补充说明
1.本实验是拷贝linux下11g数据文件到win上使用UltraEdit操作得出
2.使用UltraEdit,需要熟悉对一些关键数据的偏移量比较清楚

现在有个需求，需要数据库字符集从ZHS16GBK修改为AL32UTF8，因为他们没有子集的关系，所以在转换前，需要先检测库中的数据内容是否全库可以转换为AL32UTF8字符集，检测使用oracle提供的csscan工具实现
一、csscan使用
1.安装csscan相关数据字典

SQL> @?/rdbms/admin/csminst.sql

2.csscan使用说明

[oracle@node1 ~]$ csscan help=y
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:30:42 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
You can let Scanner prompt you for parameters by entering the CSSCAN
command followed by your username/password:
  Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"
Or, you can control how Scanner runs by entering the CSSCAN command
followed by various parameters. To specify parameters, you use keywords:
  Example:
    CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3
Keyword    Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID             yes    username/password
FULL       N       yes    scan entire database
USER               yes    owner of tables to be scanned
TABLE              yes    list of tables to scan
COLUMN             yes    list of columns to scan
EXCLUDE                   list of tables to exclude from scan
TOCHAR             yes    new database character set name
FROMCHAR                  current database character set name
TONCHAR                   new national character set name
FROMNCHAR                 current national character set name
ARRAY      1024000 yes    size of array fetch buffer
PROCESS    1       yes    number of concurrent scan process
MAXBLOCKS                 split table if block size exceed MAXBLOCKS
CAPTURE    N              capture convertible data
SUPPRESS                  maximum number of exceptions logged for each table
FEEDBACK                  report progress every N rows
BOUNDARIES                list of column size boundaries for summary report
LASTRPT    N              generate report of the last database scan
LOG        scan           base file name of report files
PARFILE                   parameter file name
PRESERVE   N              preserve existing scan results
LCSD       N       no     enable language and character set detection
LCSDDATA   LOSSY   no     define the scope of the detection
HELP       N              show help screen (this screen)
QUERY      N              select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.

3.使用csscan检测当前数据库

[oracle@node1 ~]$ csscan userid="'"sys/xifenfei as sysdba"'" full=y \
fromchar=ZHS16GBK tochar=AL32UTF8 log=/tmp/check.log capture=y array=1000000 process=4
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:36:37 2011
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
Enumerating tables to scan...
. process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA]
. process 2 scanning XFF.T1[AAATjjAAEAAAC7AAAA]
. process 3 scanning TEST.T_XFF[AAAT+6AAEAAAAIQAAA]
. process 2 scanning SYS.METHOD$[AAAAHvAABAAAA0QAAA]
. process 4 scanning SYS.ATTRIBUTE$[AAAAHvAABAAAA0QAAA]
…………………………………………
. process 4 scanning CTXSYS.DR$INDEX_CDI_COLUMN
. process 2 scanning CTXSYS.DR$SDATA_UPDATE
. process 1 scanning EXFSYS.RLM$RULESETSTCODE
. process 3 scanning EXFSYS.RLM$RULESET
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.

4.查看csscan检查日志

[oracle@node1 ~]$ ll /tmp/check.log.*
-rw-r--r-- 1 oracle oinstall  14526 12-21 12:37 /tmp/check.log.err
-rw-r--r-- 1 oracle oinstall 100235 12-21 12:37 /tmp/check.log.out
-rw-r--r-- 1 oracle oinstall   8265 12-21 12:37 /tmp/check.log.txt
--err是错误日志
--out是执行过程
--txt是执行结果汇总
[oracle@node1 tmp]$ cat /tmp/check.log.txt
Database Scan Summary Report
Time Started  : 2011-12-21 12:36:37
Time Completed: 2011-12-21 12:37:13
Process ID         Time Started       Time Completed
---------- -------------------- --------------------
         1  2011-12-21 12:36:43  2011-12-21 12:37:12
         2  2011-12-21 12:36:43  2011-12-21 12:37:12
         3  2011-12-21 12:36:43  2011-12-21 12:37:12
         4  2011-12-21 12:36:43  2011-12-21 12:37:12
---------- -------------------- --------------------
[Database Size]
Tablespace                           Used            Free           Total       Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM                            723.63M           6.38M         730.00M            .00K
SYSAUX                            686.56M         103.44M         790.00M            .00K
UNDOTBS1                           18.31M         126.69M         145.00M            .00K
TEMP                                 .00K            .00K            .00K            .00K
USERS                             138.69M          22.56M         161.25M            .00K
EXAMPLE                           310.13M          35.50M         345.63M            .00K
------------------------- --------------- --------------- --------------- ---------------
Total                           1,877.31M         294.56M       2,171.88M            .00K
[Database Scan Parameters]
Parameter                      Value
------------------------------ ------------------------------------------------
CSSCAN Version                 v2.1
Instance Name                  ora11g
Database Version               11.2.0.3.0
Scan type                      Full database
Scan CHAR data?                YES
Database character set         ZHS16GBK
FROMCHAR                       ZHS16GBK
TOCHAR                         AL32UTF8
Scan NCHAR data?               NO
Array fetch buffer size        1000000
Number of processes            4
Capture convertible data?      YES
------------------------------ ------------------------------------------------
[Scan Summary]
All character type data in the data dictionary are convertible to the new character set
All character type application data remain the same in the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     6,061,403              152                0                0
CHAR                             4,988                0                0                0
LONG                           252,530                0                0                0
VARRAY                          50,812                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        6,369,733              152                0                0
Total in percentage             99.998%           0.002%           0.000%           0.000%
The data dictionary can not be safely migrated using the CSALTER script
XML CSX Dictionary Tables:
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                           711                0                0                0
CHAR                                 0                0                0                0
LONG                                 0                0                0                0
VARRAY                               0                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                              711                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%
[Application Data Conversion Summary]
Datatype                    Changeless      Convertible       Truncation            Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2                     8,142,707                0                0                0
CHAR                            63,085                0                0                0
LONG                                 0                0                0                0
VARRAY                             583                0                0                0
--------------------- ---------------- ---------------- ---------------- ----------------
Total                        8,206,375                0                0                0
Total in percentage            100.000%           0.000%           0.000%           0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE                                            76                0                0
CTXSYS.DR$STOPWORD                                               76                0                0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE                                              Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
CTXSYS.DR$INDEX_VALUE|IXV_VALUE                                  76                0                0
CTXSYS.DR$STOPWORD|SPW_WORD                                      76                0                0
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN                                       Convertible       Truncation            Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
--因为检测结果没有Truncation(截断数据)/Lossy(丢失数据)都不存在记录，所以不用查看错误日志
--如果发现不为0，需要检查err日志，然后先处理丢这些记录，然后再转换

二、修改数据库字符集

[oracle@node1 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 12:59:55 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> select value$ from props$ where name='NLS_CHARACTERSET';
VALUE$
------------------------------------------------------
ZHS16GBK
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
SQL> alter system enable restricted session;
System altered.
SQL> alter system set job_queue_processes=0 scope=memory;
System altered.
SQL> alter system set aq_tm_processes=0  scope=memory;
System altered.
SQL> alter database open;
Database altered.
SQL> alter database character set internal_use AL32UTF8;
Database altered.
SQL> shutdown  immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size                  2230072 bytes
Variable Size            1493174472 bytes
Database Buffers          637534208 bytes
Redo Buffers                4947968 bytes
Database mounted.
Database opened.
SQL> select value$ from props$ where name='NLS_CHARACTERSET';
VALUE$
--------------------------------------------------------
AL32UTF8

1.create job

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
  job_name =>'xifenfei_job',
  job_type =>'STORED_PROCEDURE',
  job_action =>'p_schedule',
  repeat_interval =>'FREQ=DAILY; BYHOUR=18,20,22',
  enabled => true,
  comments => 'XIFENFEI');
END;
--每天18/20/22点执行p_schedule过程

2.disable job

BEGIN
    DBMS_SCHEDULER.DISABLE(name => 'xifenfei_job');
END;

3.enable job

BEGIN
    DBMS_SCHEDULER.ENABLE(name => 'xifenfei_job');
END;

4.select job

select * from USER_SCHEDULER_JOBS;

5.query logs

SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS
WHERE job_name='XIFENFEI_JOB';

6.delete logs

--ALL_SCHEDULER_JOB_RUN_DETAILS视图
CREATE OR REPLACE VIEW ALL_SCHEDULER_JOB_RUN_DETAILS
(log_id, log_date, owner, job_name, job_subname, status, error#,
req_start_date, actual_start_date, run_duration, instance_id,
session_id, slave_pid, cpu_used, credential_owner, credential_name,
destination_owner, destination, additional_info)
AS
(SELECT
     j.LOG_ID, j.LOG_DATE, e.OWNER,
     DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1)),
     DECODE(instr(e.NAME,'"'),0,NULL,substr(e.NAME,instr(e.NAME,'"')+1)),
     e.STATUS, j.ERROR#, j.REQ_START_DATE, j.START_DATE, j.RUN_DURATION,
     j.INSTANCE_ID, j.SESSION_ID, j.SLAVE_PID, j.CPU_USED,
     decode(e.credential, NULL, NULL,
        substr(e.credential, 1, instr(e.credential, '"')-1)),
     decode(e.credential, NULL, NULL,
        substr(e.credential, instr(e.credential, '"')+1,
           length(e.credential) - instr(e.credential, '"'))),
     decode(bitand(e.flags, 1), 0, NULL,
        substr(e.destination, 1, instr(e.destination, '"')-1)),
     decode(bitand(e.flags, 1), 0, e.destination,
        substr(e.destination, instr(e.destination, '"')+1,
           length(e.destination) - instr(e.destination, '"'))),
     j.ADDITIONAL_INFO
   FROM scheduler$_job_run_details j, scheduler$_event_log e
   WHERE j.log_id = e.log_id
   AND e.type# = 66 and e.dbid is null
   AND ( e.owner = SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
         or  /* user has object privileges */
            ( select jo.obj# from obj$ jo, user$ ju where
                DECODE(instr(e.NAME,'"'),0, e.NAME,substr(e.NAME,1,instr(e.NAME,'"')-1))
                = jo.name and e.owner = ju.name and jo.owner# = ju.user#
                and jo.subname is null and jo.type# = 66
            ) in
            ( select oa.obj#
                from sys.objauth$ oa
                where grantee# in ( select kzsrorol from x$kzsro )
            )
         or /* user has system privileges */
            (exists ( select null from v$enabledprivs
                       where priv_number = -265 /* CREATE ANY JOB */
                   )
             and e.owner!='SYS')
        )
  );
--从这个视图中可以发现，日志有存在SCHEDULER$_JOB_RUN_DETAILS和
--SCHEDULER$_EVENT_LOG两张表中,所以要删除日志，就需要处理这两张表
######################删除日志操作#################
--删除SYS.SCHEDULER$_JOB_RUN_DETAILS中数据
DELETE FROM SYS.SCHEDULER$_JOB_RUN_DETAILS A
 WHERE EXISTS (SELECT 1
          FROM SYS.SCHEDULER$_EVENT_LOG B
         WHERE B.NAME = 'XIFENFEI_JOB'
           AND A.LOG_ID = B.LOG_ID);
--删除SYS.SCHEDULER$_EVENT_LOG中数据
DELETE FROM SYS.SCHEDULER$_EVENT_LOG B
 WHERE B.NAME = 'XIFENFEI_JOB';
--提交
 COMMIT;
#####################################################

7.delete jobs

BEGIN
    DBMS_SCHEDULER.DROP_JOB(job_name => 'xifenfei_job');
END;

参考:http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm