Oracle – 第19页 – Database SOS

11GR2 Control file enqueue hold time tracking dump

标题：11GR2 Control file enqueue hold time tracking dump

如果你比较心细,可能在11.2的数据库中发现alert文件中存在存在类此下面的记录

Errors in file /oradb/diag/rdbms/offon/offon2/trace/offon2_ckpt_19660878.trc:

查看trace文件发现

*** 2012-08-01 03:36:03.520
  1: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  2: 890ms (rw) file: kcrf.c line: 10012 count: 6 total: 4266ms time: 1820928
  3: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  4: 530ms (rw) file: kcv.c line: 11783 count: 1 total: 530ms time: 3207607
Control file enqueue hold time tracking dump at time: 3376956
*** 2012-08-03 02:14:38.714
  1: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  2: 890ms (rw) file: kcrf.c line: 10012 count: 7 total: 4953ms time: 1820928
  3: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  4: 530ms (rw) file: kcv.c line: 11783 count: 1 total: 530ms time: 3207607
Control file enqueue hold time tracking dump at time: 3384212

这个类此我们在10g中看到的lgwr的警告类此,见Warning: log write time 560ms, size 3KB,其实也就是oracle对lgwr进程写入日志慢的时候的一个trace功能记录下来的.
我们这里遇到的是因为oracle对ckpt进程的trace,当control file enqueue holding time tracking size超过10的时候，就会记录到trace文件中,oracle 内部文档对于该问题的一些描述如下:

About the issue, this is the expected behavior on 11.2.
New controlfile enqueue hold time tracking statistics have been added in 11.2 to
aid diagnosis of controlfile transaction related performance related issues:
Control File Enqueue AWR Statistics:
* max cf enq hold time - The maximum amount of time in milliseconds a client has held the control file enqueue.
* total cf enq hold time - The total amount of time in milliseconds all clients have held the control file enqueue.
* total number of cf enq holders - The total number of times clients have held the control file enqueue.
Periodically, the CKPT process dumps statistics for the top N control file enqueue holders.
N defaults to 10, but can be modified with the static hidden parameter:
_controlfile_enqueue_holding_time_tracking_size.The dump looks like the following:
Preface: "Control file enqueue hold time tracking dump at time: [relative time]".
* a. Time the client has held the control file enqueue.
* b. Type of client's control file enqueue transaction - rw or ro.
* c. File name where the client obtained control file enqueue.
* d. Line number where the client obtained control file enqueue.
* e. Number of times the client has held the control file enqueue since it became a member of the top N.
* f. Total time the client has held the control file in all those times from [e].
* g. Relative time the client obtained the control file enqueue from [a].

查询数据库默认值

SQL> col value for a24
SQL> col description for a70
SQL> set linesize 180
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: _controlfile_enqueue_holding_time_tracking_size
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_controlfile_enqueue_holding_time_tracking_size%')
NAME                                               VALUE    DESCRIPTION
-------------------------------------------------- -------- ------------------------------------------------
_controlfile_enqueue_holding_time_tracking_size    10       control file enqueue holding time tracking size

虽然在alert日志中使用Error的形式显示该错误,但是这只是一个oracle作为诊断数据库Control File Enqueue性能的一个依据,大部分情况下,我们可以选择忽略或者关闭该诊断功能.屏蔽该提示方法如下:

The way to shut off is set _controlefile_enqueue_holding_time_tracking_size = 0 then restart the database
-- spfile
alter system set "_controlfile_enqueue_holding_time_tracking_size"=0 scope=spfile;
-- pfile
_controlfile_enqueue_holding_time_tracking_size=0
Restart database

ORACLE用户重命名

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE用户重命名

从oracle 11.2.0.2开始提供了用户重命名的新特性，在以前的版本中，如果想对用户重命名，一般来说是先创建一个新的用户并授权，然后将原用户下的所有对象导入，然后删除旧的用户！
数据库版本信息

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 user xifenfei identified by xifenfei;
User created.
SQL> grant connect,resource to xifenfei;
Grant succeeded.
SQL> conn xifenfei/xifenfei
Connected.
SQL> create table t_xifenfei   as select * from user_users;
Table created.
SQL> create index ind_t_xifenfei on t_xifenfei(user_id);
Index created.
SQL> conn / as sysdba
Connected.
SQL> select object_type,object_name from dba_objects where owner='XIFENFEI';
OBJECT_TYPE         OBJECT_NAME
------------------- ---------------------------------------------------------
TABLE               T_XIFENFEI
INDEX               IND_T_XIFENFEI

尝试修改用户名

SQL> alter user xifenfei rename to xff identified by xifenfei;
alter user xifenfei rename to xff identified by xifenfei
                    *
ERROR at line 1:
ORA-00922: missing or invalid option
--默认值是false
SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
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')
   and b.inst_id = USERENV ('Instance')
  4    5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _enable_rename_user
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_enable_rename_user%')
NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ ------------------------------------------------
_enable_rename_user              FALSE                    enable RENAME-clause using ALTER USER statement
SQL> startup force restrict
ORACLE instance started.
Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
--_enable_rename_user=false,在restrict模式也不能修改用户名
SQL> ALTER user XFF RENAME TO xffei IDENTIFIED BY xifenfei;
ALTER user XFF RENAME TO xffei IDENTIFIED BY xifenfei
               *
ERROR at line 1:
ORA-00922: missing or invalid option

设置隐含参数

SQL> alter system set "_enable_rename_user"=true scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.
Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
SQL> ALTER user xifenfei RENAME TO xff IDENTIFIED BY xifenfei;
User altered.

测试结果

SQL> startup force
ORACLE instance started.
Total System Global Area  230162432 bytes
Fixed Size                  1344088 bytes
Variable Size              88083880 bytes
Database Buffers          134217728 bytes
Redo Buffers                6516736 bytes
Database mounted.
Database opened.
SQL> select object_type,object_name from dba_objects where owner='XIFENFEI';
no rows selected
SQL> select object_type,object_name from dba_objects where owner='XFF';
OBJECT_TYPE         OBJECT_NAME
------------------- ----------------------------------------------------
TABLE               T_XIFENFEI
INDEX               IND_T_XIFENFEI
SQL> conn xff/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
         1

相关文档和上面的测试,得出结论:数据库版本在11.2.0.2及其以上版本,_enable_rename_user设置为true,数据库启动到restrict模式可以修改用户名

bbed 删除 cluster table 记录

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：bbed 删除 cluster table 记录

对应cluster table使用bbed删除其中的部分记录,一直没有被攻克的难关,今天突发灵感,解决了cluster table 通过bbed删除记录后验证不能通过的难题.主要修改操作:oracle在index cluster中删除一条记录实际上只是把这条记录的行头由0x6c修改为0x7c,并且把这条记录所对应的聚簇键所在行的行头中记录的comc减1;修改验证信息
模拟cluster table 环境

SQL> create cluster clu_xff(id number(4));
Cluster created.
SQL> create table t_xifenfei
  2  (id number(4)
  3  ,name      varchar2(25)
  4  )CLUSTER clu_xff (id);
Table created.
SQL> create index ind_clu_xff ON CLUSTER clu_xff;
Index created.
SQL> insert into t_xifenfei values(1,'xifenfei');
1 row created.
SQL> insert into t_xifenfei values(2,'www.xifenfei.com');
1 row created.
SQL> insert into t_xifenfei values(3,'XIFENFEI');
1 row created.
SQL> insert into t_xifenfei values(4,'WWW.XIFENFEI.COM');
1 row created.
SQL> insert into t_xifenfei values(2,'WWW.xifenfei.COM');
1 row created.
SQL> insert into t_xifenfei values(3,'XFF_CHF');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> select t.*,
  2  dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location
  3  from t_xifenfei t;
        ID NAME                      LOCATION
---------- ------------------------- ----------
         2 www.xifenfei.com          4_171
         2 WWW.xifenfei.COM          4_171
         3 XIFENFEI                  4_172
         3 XFF_CHF                   4_172    <----需要删除记录
         4 WWW.XIFENFEI.COM          4_174
         1 xifenfei                  4_175
6 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

bbed删除记录操作

[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 09:33:58 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 172
        FILE#           4
        BLOCK#          172
BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172                                   Dba:0x010000ac
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[2], 8 bytes                    @114
 sb2 kdbr[3]                                @122
 ub1 freespace[8013]                        @128
 ub1 rowdata[47]                            @8141
 ub4 tailchk                                @8188
--查看该块的相关数据值
BBED> p kdbr
sb2 kdbr[0]                                 @122      8066
sb2 kdbr[1]                                 @124      8053
sb2 kdbr[2]                                 @126      8041
BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25]                             @8166     0xac
BBED> x /rn
rowdata[25]                                 @8166
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    2
mref@8171:    2
hrid@8173:0x010000ac.0
nrid@8179:0x010000ac.0
col    0[2] @8185: 3
BBED> p *kdbr[1]
rowdata[12]
-----------
ub1 rowdata[12]                             @8153     0x6c
BBED> x /rc
rowdata[12]                                 @8153
-----------
flag@8153: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8154: 0x00
cols@8155:    1
col    0[8] @8157: XIFENFEI
BBED> p *kdbr[2]
rowdata[0]
----------
ub1 rowdata[0]                              @8141     0x6c
BBED> x /rc
rowdata[0]                                  @8141
----------
flag@8141: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8142: 0x02
cols@8143:    1
col    0[7] @8145: XFF_CHF   <----需要删除记录
BBED> m /x 7c offset 8141
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets: 8141 to 8191           Dba:0x010000ac
------------------------------------------------------------------------
 7c020100 07584646 5f434846 6c000100 08584946 454e4645 49ac0001 02000200
 010000ac 00000100 00ac0000 02c10402 066c1d
 <32 bytes per line>
BBED> m /x 01 offset 8171
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets: 8171 to 8191           Dba:0x010000ac
------------------------------------------------------------------------
 01000100 00ac0000 010000ac 000002c1 0402066c 1d
 <32 bytes per line>
BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25]                             @8166     0xac
BBED> x /rn
rowdata[25]                                 @8166
-----------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    2
mref@8171:    1
hrid@8173:0x010000ac.0
nrid@8179:0x010000ac.0
col    0[2] @8185: 3
BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f87, required = 0x8f87
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172
Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: the amount of space used is not equal to block size   <----数据块使用空间错误
        used=67 fsc=0 avsp=8013 dtl=8088
Block 172 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes                       @100
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      2
   sb2 kdbhnrow                             @102      3
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      28
   sb2 kdbhfseo                             @108      8041
   sb2 kdbhavsp                             @110      8013
   sb2 kdbhtosp                             @112      8013
BBED> m /x 551f offset 110
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets:  110 to  126           Dba:0x010000ac
------------------------------------------------------------------------
 551f4d1f 00000100 01000200 821f751f
 <32 bytes per line>
BBED>  p kdbh
struct kdbh, 14 bytes                       @100
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      2
   sb2 kdbhnrow                             @102      3
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      28
   sb2 kdbhfseo                             @108      8041
   sb2 kdbhavsp                             @110      8021
   sb2 kdbhtosp                             @112      8013
BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f9f, required = 0x8f9f
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172
Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: avsp(8021) > tosp(8013)           <----avsp 不能大于tosp
Block 172 failed with check code 6128
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED>  m /x 551f offset 112
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets:  112 to  128           Dba:0x010000ac
------------------------------------------------------------------------
 551f0000 01000100 0200821f 751f691f
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f87, required = 0x8f87
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172
Block Checking: DBA = 16777388, Block Type = KTB-managed data block
data header at 0xb53ed264
kdbchk: space available on commit is incorrect
        tosp=8021 fsc=0 stb=4 avsp=8021        <----tosp值不合适
Block 172 failed with check code 6111
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> set count 64
        COUNT           64
BBED> m /x 591f offset 112
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 172              Offsets:  112 to  175           Dba:0x010000ac
------------------------------------------------------------------------
 591f0000 01000100 0200821f 751f691f 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 172:
current = 0x8f8b, required = 0x8f8b
--修改块工作完成
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 172
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

启动库验证

SQL> startup
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             260049780 bytes
Database Buffers           46137344 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------------
         2 www.xifenfei.com
         2 WWW.xifenfei.COM
         3 XIFENFEI
         4 WWW.XIFENFEI.COM
         1 xifenfei
--XFF_CHF记录被删除
SQL> insert into t_xifenfei values(3,'惜分飞');
1 row created.
SQL> delete from t_xifenfei where name='XIFENFEI';
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from t_xifenfei;
        ID NAME
---------- -------------------------
         2 www.xifenfei.com
         2 WWW.xifenfei.COM
         3 惜分飞
         4 WWW.XIFENFEI.COM
         1 xifenfei
--证明XFF_CHF所在数据块其他dml操作正常,证明修改正确

利用bbed找回ORACLE更新前值

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：利用bbed找回ORACLE更新前值

模拟数据块更新

SQL> create table t_xifenfei(id number,name varchar2(10));
Table created.
SQL> insert into t_xifenfei values(1,'XFF');
1 row created.
SQL> insert into t_xifenfei values(2,'CHF');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select id,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_xifenfei;
        ID ROWID                 REL_FNO    BLOCKNO      ROWNO
---------- ------------------ ---------- ---------- ----------
         1 AAASc+AAEAAAACvAAA          4        175          0
         2 AAASc+AAEAAAACvAAB          4        175          1
SQL> select dump(1,'16') from dual;
DUMP(1,'16')
-----------------
Typ=2 Len=2: c1,2
SQL> select dump(2,'16') from dual;
DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3
SQL> select dump('XFF','16') FROM DUAL;
DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46
SQL> SELECT DUMP('CHF','16') FROM DUAL;
DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46
SQL> update t_xifenfei set name='XIFENFEI' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select dump('XIFENFEI','16') from dual;
DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49
SQL> alter system checkpoint;
System altered.
SQL> select * from t_xifenfei;
        ID NAME
---------- ----------
         1 XIFENFEI
         2 CHF

这里我们对数据库进行了一次更新操作,并且dump出来对应值,为了方便定位到相应记录

bbed查看相关值

[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Wed Aug 8 20:50:47 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 4 block 175
        FILE#           4
        BLOCK#          175
BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175                                   Dba:0x010000af
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[2]                                @118
 ub1 freespace[8031]                        @122
 ub1 rowdata[35]                            @8153
 ub4 tailchk                                @8188
BBED> p kdbr
sb2 kdbr[0]                                 @118      8053
sb2 kdbr[1]                                 @120      8068
BBED> p *kdbr[1]
rowdata[15]
-----------
ub1 rowdata[15]                             @8168     0x2c
BBED> x /rnc
rowdata[15]                                 @8168
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    2
col    0[2] @8171: 2
col    1[3] @8174: CHF
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8153     0x2c
BBED> x /rnc
rowdata[0]                                  @8153
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155:    2
col    0[2] @8156: 1
col    1[8] @8159: XIFENFEI
BBED> set count 64
        COUNT           64
 <32 bytes per line>
BBED> d /v
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175     Offsets: 8153 to 8191  Dba:0x010000af
-------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
 000202c1 03034348 462c0002 02c10203 l ......CHF,......
 58464602 068de8                     l XFF....
 <16 bytes per line>

使用bbed找回历史值

--准备工作,通过dump出来的值,推算出来第一条记录的起点02c10203584646,
--在这个值的基础上offset-3得到offset值为8078
BBED> p kdbr
sb2 kdbr[0]                                 @118      8053
sb2 kdbr[1]                                 @120      8068
--修改row directory指针位置
BBED> m /x 8e1f
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  118 to  181           Dba:0x010000af
------------------------------------------------------------------------
 8e1f841f 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> p kdbr
sb2 kdbr[0]                                 @118      8078
sb2 kdbr[1]                                 @120      8068
BBED> sum apply
Check value for File 4, Block 175:
current = 0xdff8, required = 0xdff8
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: xaction header lock count mismatch
        trans=2 ilk=1 nlo=0     --提示事务锁错误
Block 175 failed with check code 6108
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p *kdbr[0]
rowdata[25]
-----------
ub1 rowdata[25]                             @8178     0x2c
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8178 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c000202 c1020358 46460206 8de8
 <32 bytes per line>
BBED> x /rnc
rowdata[25]                                 @8178
-----------
flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x00  --被更新前的记录事务锁标识为0,而更新后的事务锁标识为2
cols@8180:    2
col    0[2] @8181: 1
col    1[3] @8184: XFF
--修改事务锁标识为2
BBED> m /x 02 offset 8179
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8179 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 020202c1 02035846 4602068d e8
 <32 bytes per line>
BBED> set offset 8153
        OFFSET          8153
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8153 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c 000202c1 03034348 462c0202 02c10203
 58464602 068de8
 <32 bytes per line>
--把更新后值的事务锁标识改为0
BBED> m /x 00 offset +1
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8154 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 000202c1 02085849 46454e46 45492c00 0202c103 03434846 2c020202 c1020358
 46460206 8de8
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddfa, required = 0xddfa
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
        used=42 fsc=0 avsp=8041 dtl=8088   -->提示块的空间使用不正确
Block 175 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p ktbbh
struct ktbbh, 72 bytes                      @20
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24
      ub4 ktbbhsg1                          @24       0x0001273e
      ub4 ktbbhod1                          @24       0x0001273e
   struct ktbbhcsc, 8 bytes                 @28
      ub4 kscnbas                           @28       0x0000e88a
      ub2 kscnwrp                           @32       0x0002
   sb2 ktbbhict                             @36       2
   ub1 ktbbhflg                             @38       0x32 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x010000a8
   struct ktbbhitl[0], 24 bytes             @44
      struct ktbitxid, 8 bytes              @44
         ub2 kxidusn                        @44       0x0006
         ub2 kxidslt                        @46       0x001e
         ub4 kxidsqn                        @48       0x000002c6
      struct ktbituba, 8 bytes              @52
         ub4 kubadba                        @52       0x00c000d9
         ub2 kubaseq                        @56       0x0086
         ub1 kubarec                        @58       0x2a
      ub2 ktbitflg                          @60       0x8000 (KTBFCOM)
      union _ktbitun, 2 bytes               @62
         sb2 _ktbitfsc                      @62       2
         ub2 _ktbitwrp                      @62       0x0002
      ub4 ktbitbas                          @64       0x0000e550
   struct ktbbhitl[1], 24 bytes             @68
      struct ktbitxid, 8 bytes              @68
         ub2 kxidusn                        @68       0x0006
         ub2 kxidslt                        @70       0x0008
         ub4 kxidsqn                        @72       0x000002c7
      struct ktbituba, 8 bytes              @76
         ub4 kubadba                        @76       0x00c000da
         ub2 kubaseq                        @80       0x0086
         ub1 kubarec                        @82       0x12
      ub2 ktbitflg                          @84       0x2001 (KTBFUPB)
      union _ktbitun, 2 bytes               @86
         sb2 _ktbitfsc                      @86       0
         ub2 _ktbitwrp                      @86       0x0000
      ub4 ktbitbas                          @88       0x0000e88d
--所有的_ktbitfsc修改为0
BBED> m /x 00 offset 62
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:   62 to  125           Dba:0x010000af
------------------------------------------------------------------------
 000050e5 00000600 0800c702 0000da00 c0008600 12000120 00008de8 00000000
 00000000 00000001 0200ffff 1600751f 691f691f 00000200 8e1f841f 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
Block Checking: DBA = 16777391, Block Type = KTB-managed data block
data header at 0xb53cd264
kdbchk: the amount of space used is not equal to block size
        used=42 fsc=0 avsp=8041 dtl=8088
Block 175 failed with check code 6110
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED
BBED> p kdbh
struct kdbh, 14 bytes                       @100
   ub1 kdbhflag                             @100      0x00 (NONE)
   sb1 kdbhntab                             @101      1
   sb2 kdbhnrow                             @102      2
   sb2 kdbhfrre                             @104     -1
   sb2 kdbhfsbo                             @106      22
   sb2 kdbhfseo                             @108      8053
   sb2 kdbhavsp                             @110      8045
   sb2 kdbhtosp                             @112      8045
--修改kdbhtosp和kdbhavsp值
BBED>  m /x 6e1f  offset 112
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  112 to  175           Dba:0x010000af
------------------------------------------------------------------------
 6e1f0000 02008e1f 841f0000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x  6e1f  offset 110
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets:  110 to  173           Dba:0x010000af
------------------------------------------------------------------------
 6e1f6e1f 00000200 8e1f841f 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 175:
current = 0xddf8, required = 0xddf8
--数据块验证通过
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 175
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

重启数据库

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             251661172 bytes
Database Buffers           54525952 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.
--找回更新前值
SQL> select * from chf.t_xifenfei;
        ID NAME
---------- ----------
         1 XFF
         2 CHF

ORACLE update 操作内部原理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE update 操作内部原理

对于oracle的update操作,在数据块中具体是如何出来,是直接更新原来值,还是通过插入新值修改指针的方法实现.下面通过证明:
模拟表插入数据

SQL> create table t_xifenfei(id number,name varchar2(10));
Table created.
SQL> insert into t_xifenfei values(1,'XFF');
1 row created.
SQL> insert into t_xifenfei values(2,'CHF');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> select id,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_xifenfei;
        ID ROWID                 REL_FNO    BLOCKNO      ROWNO
---------- ------------------ ---------- ---------- ----------
         1 AAASc+AAEAAAACvAAA          4        175          0
         2 AAASc+AAEAAAACvAAB          4        175          1
SQL> alter system dump datafile 4 block 175;
System altered.
SQL>  select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_24625.trc

数据存储对应16进制值

SQL> select dump(1,'16') from dual;
DUMP(1,'16')
-----------------
Typ=2 Len=2: c1,2
SQL> select dump(2,'16') from dual;
DUMP(2,'16')
-----------------
Typ=2 Len=2: c1,3
SQL> select dump('XFF','16') FROM DUAL;
DUMP('XFF','16')
----------------------
Typ=96 Len=3: 58,46,46
SQL> SELECT DUMP('CHF','16') FROM DUAL;
DUMP('CHF','16')
----------------------
Typ=96 Len=3: 43,48,46

得出第一条记录对应值为:02c10203584646;第二条记录对应值为:02c10303434846

dump 数据块得到记录

bdba: 0x010000af
data_block_dump,data header at 0xb683c064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0xb683c064
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f84
avsp=0x1f6e
tosp=0x1f6e
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f8e    ---->8078
0x14:pri[1]     offs=0x1f84    ---->8068
block_row_dump:
tab 0, row 0, @0x1f8e
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [ 3]  58 46 46
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  43 48 46
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175

bbed查看相关记录

BBED> p kdbr
sb2 kdbr[0]                                 @118      8078 <--第一条row directory指针位置
sb2 kdbr[1]                                 @120      8068 <--第二条row directory指针位置
BBED> p *kdbr[0]
rowdata[10]
-----------
ub1 rowdata[10]                             @8178     0x2c
BBED> x /rnc
rowdata[10]                                 @8178
-----------
flag@8178: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8179: 0x01
cols@8180:    2
col    0[2] @8181: 1
col    1[3] @8184: XFF
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8168     0x2c
BBED> x /rnc
rowdata[0]                                  @8168
----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x01
cols@8170:    2
col    0[2] @8171: 2
col    1[3] @8174: CHF
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175              Offsets: 8168 to 8191           Dba:0x010000af
------------------------------------------------------------------------
 2c010202 c1030343 48462c01 0202c102 03584646 010650e5
 <32 bytes per line>

这里可以得到结论如下:
1.数据是从块的底部开始往上存储
2.在每一条记录的头部分别有flag/lock/cols对应这里的2c0102
3.这里的偏移量和dump出来的数据可以看出来两条记录是连续在一起(偏移量分别为:8168和8178)

更新一条记录

SQL> update t_xifenfei set name='XIFENFEI' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 175;
System altered.
SQL> select dump('XIFENFEI','16') from dual;
DUMP('XIFENFEI','16')
-------------------------------------
Typ=96 Len=8: 58,49,46,45,4e,46,45,49

我们可以但看到值有XFF改变为XIFENFEI,存储长度变大

dump数据块信息

bdba: 0x010000af
data_block_dump,data header at 0xb683c064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0xb683c064
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f75
avsp=0x1f69
tosp=0x1f69
0xe:pti[0]      nrow=2  offs=0
0x12:pri[0]     offs=0x1f75    ---->8053
0x14:pri[1]     offs=0x1f84    ---->8068
block_row_dump:
tab 0, row 0, @0x1f75
tl: 15 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 02
col  1: [ 8]  58 49 46 45 4e 46 45 49
tab 0, row 1, @0x1f84
tl: 10 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 03
col  1: [ 3]  43 48 46
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 175 maxblk 175

通过对比第一次dump出来的数据块发现:row 0的值和偏移量发生了变化

bbed查看相关记录

BBED> set file 4 block 175
        FILE#           4
        BLOCK#          175
BBED> map
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175                                   Dba:0x010000af
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[2]                                @118
 ub1 freespace[8031]                        @122
 ub1 rowdata[35]                            @8153
 ub4 tailchk                                @8188
BBED> p kdbr
sb2 kdbr[0]                                 @118      8053   <--第一条row directory指针位置
sb2 kdbr[1]                                 @120      8068   <--第二条row directory指针位置
BBED> p *kdbr[1]
rowdata[15]
-----------
ub1 rowdata[15]                             @8168     0x2c
BBED> x /rnc
rowdata[15]                                 @8168
-----------
flag@8168: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8169: 0x00
cols@8170:    2
col    0[2] @8171: 2
col    1[3] @8174: CHF
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8153     0x2c
BBED> x /r
rowdata[0]                                  @8153
----------
flag@8153: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8154: 0x02
cols@8155:    2
col    0[2] @8156:  0xc1  0x02
col    1[8] @8159:  0x58  0x49  0x46  0x45  0x4e  0x46  0x45  0x49
BBED> set count 64
        COUNT           64
 <32 bytes per line>
BBED> d /v
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 175     Offsets: 8153 to 8191  Dba:0x010000af
-------------------------------------------------------
 2c020202 c1020858 4946454e 4645492c l ,......XIFENFEI,
 000202c1 03034348 462c0002 02c10203 l ......CHF,......
 58464602 068de8                     l XFF....
 <16 bytes per line>

从这里可以看到
1.这里可以看到三个值(XFF,CHF,XIFENFEI)均存在,但是通过p kdbr和dump block不能看到,因为row directory中无指针指定到该值上
2.也是通过row directory指针使得我们从原先看到的第一条记录处于数据块最底部变成了现在相对而言的数据部分最上层,
3.绝大多数情况:数据库更新一条记录,不是直接修改数据值,而是重新插入一条新记录,然后修改row directory指针指定到新的offset上
4.不是直接update,而是insert+指针来实现,这样做的好处:1)如果修改记录update值的长度发生变化(变大或者变小)那么该值之前的数据都要发生变动,对数据库来说成本太高.2)如果直接更新值可能导致其他数据变动,使得其他行受到影响.
5.由于是修改row directory指针,所以该处理方法的rowid值不会发生变化

v$datafile.enabled相关值说明

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：v$datafile.enabled相关值说明

自认为对v$datafile视图算比较了解,但是今天看到一网友的v$datafile.enabled=DISABLED,我一时没有想出来是为什么,这里通过试验对v$datafile.enabled各种情况进行了试验并简单说明scn的变化情况
模拟环境(READ WRITE)

SQL> alter tablespace users add datafile '/u01/oracle/oradata/XFF/users02.dbf' size 10m;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
         1          0             456727 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             456727 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458322 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
QL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              456727
         2 ONLINE              456727
         3 ONLINE              456727
         4 ONLINE              456727
         5 ONLINE              458322

加入数据文件scn不一样是因为:加入数据文件的时候,使用是当前scn,而数据库未做checkpoint,所以出现数据文件scn不一致现象

datafile offline(READ WRITE)

SQL> alter database datafile 5 offline;
Database altered.
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458392
         2 ONLINE              458392
         3 ONLINE              458392
         4 ONLINE              458392
         5 OFFLINE             458322
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ----------------------------------------
         1          0             458392 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458322 READ WRITE RECOVER /u01/oracle/oradata/XFF/users02.dbf

这里可以看出来数据文件offline之后,v$datafile.enabled依然是READ WRITE,但是该数据文件的scn不再变化

tablespace offline(DISABLED)

SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
         1          0             458392 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458392 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458430 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458392
         2 ONLINE              458392
         3 ONLINE              458392
         4 ONLINE              458392
         5 ONLINE              458430
SQL> alter system checkpoint;
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- ---------------------------------------
         1          0             458443 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458443 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458443
         2 ONLINE              458443
         3 ONLINE              458443
         4 ONLINE              458443
         5 ONLINE              458443
SQL> alter tablespace users offline;
Tablespace altered.
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------
         1          0             458497 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458497 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458497 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users01.dbf
         5          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458497
         2 ONLINE              458497
         3 ONLINE              458497
         4 OFFLINE                  0
         5 OFFLINE                  0
SQL> alter system checkpoint;
System altered.
SQL> /
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458512 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users01.dbf
         5          4             458457 DISABLED   OFFLINE /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458512
         2 ONLINE              458512
         3 ONLINE              458512
         4 OFFLINE                  0
         5 OFFLINE                  0
SQL> alter tablespace users online;
Tablespace altered.
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458512
         2 ONLINE              458512
         3 ONLINE              458512
         4 ONLINE              458526
         5 ONLINE              458526
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458512 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458512 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458526 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458526 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> alter system checkpoint;
System altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458551 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458551
         2 ONLINE              458551
         3 ONLINE              458551
         4 ONLINE              458551
         5 ONLINE              458551

以上部分证明：
1.online datafile也不触发database checkpoint
2.tablespace offline后v$datafile.enabled为DISABLED
3.控制文件对应表空间scn不再变化,datafile header scn变为0
4.tablespace online不需要recover

tablespace read only(READ ONLY)

SQL> alter tablespace users read only;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458551 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458551 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458551
         2 ONLINE              458551
         3 ONLINE              458551
         4 ONLINE              458567
         5 ONLINE              458567
SQL> alter system checkpoint;
System altered.
SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458581 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458567 READ ONLY  ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458581
         2 ONLINE              458581
         3 ONLINE              458581
         4 ONLINE              458567
         5 ONLINE              458567
SQL> alter tablespace users read write;
Tablespace altered.
SQL> select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458581 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458581 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458635 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458635 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458581
         2 ONLINE              458581
         3 ONLINE              458581
         4 ONLINE              458635
         5 ONLINE              458635
SQL> alter system checkpoint;
System altered.
SQL>  select file#,ts#,CHECKPOINT_CHANGE#,enabled,status,name from v$datafile;
     FILE#        TS# CHECKPOINT_CHANGE# ENABLED    STATUS  NAME
---------- ---------- ------------------ ---------- ------- --------------------------------------------------
         1          0             458649 READ WRITE SYSTEM  /u01/oracle/oradata/XFF/system01.dbf
         2          1             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/undotbs01.dbf
         3          2             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/sysaux01.dbf
         4          4             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users01.dbf
         5          4             458649 READ WRITE ONLINE  /u01/oracle/oradata/XFF/users02.dbf
SQL> select file#,status, CHECKPOINT_CHANGE# from v$datafile_header;
     FILE# STATUS  CHECKPOINT_CHANGE#
---------- ------- ------------------
         1 ONLINE              458649
         2 ONLINE              458649
         3 ONLINE              458649
         4 ONLINE              458649
         5 ONLINE              458649

以上部分证明：
1.tablespace read only 对应的v$datafile.enabled为READ ONLY
2.tablespace read only与tablespace read write之间的转换也不会触发database checkpoint

补充说明
官方文档给出来的v$datafile.enabled有以下几种
DISABLED – No SQL access allowed
READ ONLY – No SQL updates allowed
READ WRITE – Full access allowed
UNKNOWN – should not occur unless the control file is corrupted
但是对于UNKNOWN我通过模拟重建控制文件,缺少数据文件(为READ WRITE),通过在线删除数据文件(为READ WRITE),都不能出现UNKNOWN状态,如果知道的朋友请告知我.

kill oracle session相关描述

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：kill oracle session相关描述

近期看到不少朋友都对kill session相关的参数不太熟悉,下面是摘自Oracle® Database SQL Reference

语法参考

DISCONNECT SESSION Clause

Use the DISCONNECT SESSION clause to disconnect the current session by destroying the dedicated server process
(or virtual circuit if the connection was made by way of a Shared Sever).
To use this clause, your instance must have the database open.
You must identify the session with both of the following values from the V$SESSION view:
•	For integer1, specify the value of the SID column.
•	For integer2, specify the value of the SERIAL# column.
If system parameters are appropriately configured, then application failover will take effect.
•	The POST_TRANSACTION setting allows ongoing transactions to complete before the session is disconnected.
        If the session has no ongoing transactions, then this clause has the same effect described for as KILL SESSION.
•	The IMMEDIATE setting disconnects the session and recovers the entire session state immediately,
        without waiting for ongoing transactions to complete.
o	If you also specify POST_TRANSACTION and the session has ongoing transactions, then the IMMEDIATE keyword is ignored.
o	If you do not specify POST_TRANSACTION, or you specify POST_TRANSACTION but the session has no ongoing transactions,
        then this clause has the same effect as described for KILL SESSION IMMEDIATE.

KILL SESSION Clause

The KILL SESSION clause lets you mark a session as terminated, roll back ongoing transactions,
release all session locks, and partially recover session resources.
To use this clause, your instance must have the database open.
Your session and the session to be terminated must be on the same instance unless you specify integer3.
You must identify the session with the following values from the V$SESSION view:
•	For integer1, specify the value of the SID column.
•	For integer2, specify the value of the SERIAL# column.
•	For the optional integer3, specify the ID of the instance where the target session to be killed exists.
        You can find the instance ID by querying the GV$ tables.
If the session is performing some activity that must be completed,
such as waiting for a reply from a remote database or rolling back a transaction,
then Oracle Database waits for this activity to complete, marks the session as terminated,
and then returns control to you.
If the waiting lasts a minute, then Oracle Database marks the session to be terminated
and returns control to you with a message that the session is marked to be terminated.
The PMON background process then marks the session as terminated when the activity is complete.
Whether or not the session has an ongoing transaction, Oracle Database does not recover
the entire session state until the session user issues a request to the session
and receives a message that the session has been terminated.

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing transactions,
release all session locks, recover the entire session state, and return control to you immediately.

执行案例

--Oracle 级别kill
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;
--Linux/Unix kill
kill -9 spid
--Windows kill
orakill ORACLE_SID spid

因非常规操作导致删除表空间提示ORA-01561解决办法

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：因非常规操作导致删除表空间提示ORA-01561解决办法

今天测试手工通过bbed修改undo$中回滚段状态(从status$=5修改为1)[NEEDS RECOVERY 修改为 DELETE],然后尝试删除表空间,发现不能删除
删除表空间提示ORA-01561

SQL> drop tablespace undotbs;
drop tablespace undotbs
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified
SQL> drop tablespace undotbs including contents;
drop tablespace undotbs including contents
*
ERROR at line 1:
ORA-01561: failed to remove all objects in the tablespace specified

查询相关信息

SQL> select ts#,name from v$tablespace;
       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 SYSAUX
         4 USERS
         6 UNDOTBS
         3 TEMP
SQL> select name,ts#,status$ from undo$;
NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
SYSTEM                                  0          3
_SYSSMU1_3138885392$                    2          1
_SYSSMU2_4228238222$                    2          1
_SYSSMU3_2210742642$                    2          1
_SYSSMU4_1455318006$                    2          1
_SYSSMU5_3787622316$                    2          1
_SYSSMU6_2460248069$                    2          1
_SYSSMU7_1924883037$                    2          1
_SYSSMU8_1909280886$                    2          1
_SYSSMU9_3593450615$                    2          1
_SYSSMU10_2490256178$                   2          1
NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
_SYSSMU11_253524401$                    6          1
_SYSSMU12_842775869$                    6          1
_SYSSMU13_2794767139$                   6          1
_SYSSMU14_2067649841$                   6          1
_SYSSMU15_3270221471$                   6          1
_SYSSMU16_4094338609$                   6          1
_SYSSMU17_709661646$                    6          1
_SYSSMU18_699588262$                    6          1
_SYSSMU19_718640828$                    6          1
_SYSSMU20_3516920665$                   6          1
_SYSSMU21_793796797$                    6          1
NAME                                  TS#    STATUS$
------------------------------ ---------- ----------
_SYSSMU22_3988785920$                   6          1
_SYSSMU23_1828333848$                   6          1
_SYSSMU24_1223218862$                   6          1
_SYSSMU25_2939844199$                   6          1
_SYSSMU26_1317300205$                   6          1
_SYSSMU27_1654033223$                   6          1
_SYSSMU28_3748619502$                   6          1
_SYSSMU29_1868765904$                   6          1
_SYSSMU30_3379578723$                   6          1
31 rows selected.
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE

通过这里可以看出,通过bbed的修改,除system回滚段外,其他均已经被标志为delete状态,对于这样的情况,很本能的怀疑是extent或者segment未被清理掉导致

查询EXTENT和SEGMENT

SQL> select SEGMENT_NAME from dba_extents where TABLESPACE_NAME='UNDOTBS';
no rows selected
SQL> select segment_name from dba_segments where  TABLESPACE_NAME='UNDOTBS';
no rows selected
SQL> select count(*) from seg$ where ts#=6;
  COUNT(*)
----------
        10
SQL> select count(*) from seg$ where ts#=2;
  COUNT(*)
----------
         0
SQL> select file#,type# from  seg$ where ts#=6;
     FILE#      TYPE#
---------- ----------
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
         3         10
10 rows selected.

通过查询我们发现SEG$中含有10条记录,而通过dbms_metadata.get_ddl分析DBA_SEGMENTS是的,得出type为10恰好是TYPE2 UNDO信息.

解决办法
删除掉这些因为手工修改undo$信息导致遗留下来的后遗症对象

SQL> delete from seg$ where ts#=6;
10 rows deleted.
SQL> commit;
Commit complete.
SQL> drop tablespace undotbs ;
Tablespace dropped.

这样的直接修改基表的做法,在一般的情况下非常不建议使用,可能带来系统不稳定.但是在数据库异常处理过程中,可能将成为一个法宝

ALERT_QUE表重建方法

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ALERT_QUE表重建方法

最近关注了下网络上,出现了很多AQ$_ALERT_QT_N的index SYS_IOT_TOP_NNNN坏块引起的数据库异常(主要是SYSAUX表空间),因为他们是IOT表和主键的关系,不能简单的rebuild.查询了一些资料,得到一些信息

ALERT_QUE表用途
The ALERT_QUE is used by the Grid Control and DB Control Management Agents to monitor server-generated alerts

ALERT_QUE表重建方法

--方法1
SQL> connect / as sysdba
SQL>alter system enable restricted session;
To drop server alert schema.
SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
To re-create tables, sequence, type and queue for server alert
SQL>@$ORACLE_HOME/rdbms/admin/dbmsslrt.sql
SQL>@$ORACLE_HOME/rdbms/admin/catalrt.sql
To recompile the invalid objects
SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> alter system disable restricted session;
--方法2
SQL> connect / as sysdba
SQL>alter system enable restricted session;
To drop server alert schema.
SQL>@$ORACLE_HOME/rdbms/admin/catnoalr.sql
Rerun catproc.sql
SQL>@$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> alter system disable restricted session;

补充说明

By running the script up, the queue tables will be recreated and the messages
in the queue will be lost.
For 11g you can use catmwin.sql which has the steps to recreate the ALERT_QT.
Alternatively, for 11g you can use the catproc.sql to recreate.
If this option may leave DBSNMP.MGMT_BSLN_INTERNAL invalid.
To validate the same run catsnmp.sql [NOTE:603289.1]

Oracle 11g增加列,并带默认值的新特性

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 11g增加列,并带默认值的新特性

在11g以前,如果要在一个大表中增加一列,并设置默认值,那将是一个非常悲剧的事情.有些时候不得不选择在线重定义功能来实现该需求.而在11g中增加新列并设置默认值,只是简单的修改数据字典来实现该功能,大大提供效率
10g加列(默认值)

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
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_xifenfei
  2  as select object_id,object_name from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     49827
SQL> desc t_xifenfei
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                                        VARCHAR2(128)
SQL> set timing on
SQL> alter table t_xifenfei add c_xff varchar2(100) default 'www.xifenfei.com' not null;
Table altered.
Elapsed: 00:00:06.13
--使用了6秒钟
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_xifenfei where object_name='OBJ$';
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAAMwJAAEAAAAB8AAr          4        124         43
SQL> alter system dump datafile 4 block 124;
System altered.
Elapsed: 00:00:00.08

11g增加列(默认值)

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_xifenfei
  2  as select object_id,object_name from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     74605
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_xifenfei where object_name='OBJ$';
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASpRAAEAAAACrAAu          4        171         46
SQL> alter system dump datafile 4 block 171;
System altered.
SQL> set timing on
SQL> alter table t_xifenfei add c_xff varchar2(100) default 'www.xifenfei.com' not null;
Table altered.
Elapsed: 00:00:00.19
--只是使用了0.19秒
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_xifenfei where object_name='OBJ$';
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASpRAAEAAAACrAAu          4        171         46
Elapsed: 00:00:00.04
SQL> alter system dump datafile 4 block 171;
System altered.

通过10g和11g的增加相同列和默认值的对比可以发现,11g的速度要比10g快很多很多,下面我们通过上面dump出来相关的数据块来分析原因
dump分析
11g增加列之前dump

tab 0, row 0, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
tab 0, row 1, @0x1f66
tl: 14 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 2f
col  1: [ 7]  49 5f 55 53 45 52 31
tab 0, row 2, @0x1f5b
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 1d
col  1: [ 4]  43 4f 4e 24

11g增加列之后dump

tab 0, row 0, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
tab 0, row 1, @0x1f66
tl: 14 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 2f
col  1: [ 7]  49 5f 55 53 45 52 31
tab 0, row 2, @0x1f5b
tl: 11 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 1d
col  1: [ 4]  43 4f 4e 24
tab 0, row 3, @0x1f4f
tl: 12 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 2]  c1 10
col  1: [ 5]  55 4e 44 4f 24

10g增加列之后dump
因为10g在没有增加列之前的dump和11g未增加列之前类似,所以未dump出来

tab 0, row 0, @0x1f63
tl: 29 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x1f44
tl: 31 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 2d
col  1: [ 7]  49 5f 55 53 45 52 31
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x1f28
tl: 28 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 1d
col  1: [ 4]  43 4f 4e 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x1f0b
tl: 29 fb: --H-FL-- lb: 0x2  cc: 3
col  0: [ 2]  c1 10
col  1: [ 5]  55 4e 44 4f 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

对比发现11g在增加列之后,以前的数据dump出来的内容未有任何改变.也就是说:在10g中,我们增加一个列和默认值,会自动的增加到真实的数据中,而在11g中增加列和默认值并未真的加到11g的表中已经存在的数据中.
11g中插入新数据dump测试

SQL>  insert into chf.t_xifenfei(object_id,object_name)
  2  select object_id,object_name FROM DBA_OBJECTS;
74605 rows created.
SQL> commit;
Commit complete.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> /
System altered.
--建议刷新
SQL>
SQL>
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 chf.t_xifenfei where object_name='OBJ$';
ROWID                 REL_FNO    BLOCKNO      ROWNO
------------------ ---------- ---------- ----------
AAASpRAAEAAAACrAAu          4        171         46
AAASpRAAEAAAB5TAAu          4       7763         46
SQL> ALTER SYSTEM DUMP DATAFILE 4 BLOCK 7763;
System altered.
--dump内容
tab 0, row 0, @0x4e3
tl: 29 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 15
col  1: [ 5]  49 43 4f 4c 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 1, @0x500
tl: 31 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 2f
col  1: [ 7]  49 5f 55 53 45 52 31
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 2, @0x51f
tl: 28 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 1d
col  1: [ 4]  43 4f 4e 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d
tab 0, row 3, @0x53b
tl: 29 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 2]  c1 10
col  1: [ 5]  55 4e 44 4f 24
col  2: [16]  77 77 77 2e 78 69 66 65 6e 66 65 69 2e 63 6f 6d

通过这里看看出:在11g中后续插入的数据,默认值也插入到数据文件中

分类目录归档：Oracle