[20171113]修改表结构删除列相关问题2.txt

[20171113]修改表结构删除列相关问题2.txt

--//测试看看修改表结构删除列产生的redo向量,对这些操作细节不了解,分析redo看看.

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE TEA DATAFILE
  '/mnt/ramdisk/book/tea01.dbf' SIZE 40M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;

SCOTT@book> create table t  (id number,v1 varchar2(10),v2 varchar2(20)) tablespace tea;
Table created.

SCOTT@book> insert into t select rownum,lpad('a',10,'a'),lpad('b',20,'b') from xmltable('1 to 3');
3 rows created.

SCOTT@book> commit ;
Commit complete.

2.删除列:
SCOTT@book> alter table t  SET UNUSED (v1);
Table altered.

SCOTT@book> alter system archive log current ;
System altered.

SCOTT@book> set numw 12
SCOTT@book> select current_scn from v$database ;
CURRENT_SCN
------------
13277730224

SCOTT@book> @ &r/logfile
GROUP# STATUS     TYPE       MEMBER                           IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1            ONLINE     /mnt/ramdisk/book/redo01.log     NO       1       1       791    52428800       512       1 NO  CURRENT      13277730193 2017-11-13 10:11:22 2.814750E+14
     2            ONLINE     /mnt/ramdisk/book/redo02.log     NO       2       1       789    52428800       512       1 YES INACTIVE     13277727924 2017-11-13 09:48:48  13277728003 2017-11-13 09:48:53
     3            ONLINE     /mnt/ramdisk/book/redo03.log     NO       3       1       790    52428800       512       1 YES ACTIVE       13277728003 2017-11-13 09:48:53  13277730193 2017-11-13 10:11:22
     4            STANDBY    /mnt/ramdisk/book/redostb01.log  NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log  NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log  NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log  NO
7 rows selected.

--//当前redo是/mnt/ramdisk/book/redo01.log.

SCOTT@book> ALTER TABLE t DROP UNUSED COLUMNS ;
Table altered.

SCOTT@book> select current_scn from v$database ;
CURRENT_SCN
------------
13277730365

SCOTT@book> alter system dump logfile '/mnt/ramdisk/book/redo01.log' scn min 13277730224 scn max 13277730365;
System altered.

3.分析转储看看:

SCOTT@book> SELECT OBJ#,col#, segcol#, name, intcol#, type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T' AND owner = user);
        OBJ#         COL#      SEGCOL# NAME INTCOL#        TYPE#
------------ ------------ ------------ ---- ------- ------------
       90617            1            1 ID         1            2
       90617            2            2 V2         2            1

$ grep OBJ:90617 /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_47196.trc
CHANGE #3 TYP:2 CLS:1 AFN:6 DBA:0x01800081 OBJ:90617 SCN:0x0003.176a14f9 SEQ:5 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800081 OBJ:90617 SCN:0x0003.176a162e SEQ:1 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800081 OBJ:90617 SCN:0x0003.176a162e SEQ:2 OP:11.14 ENC:0 RBL:0

--//看到3次修改.
SCOTT@book> select rowid,t.* from t ;
ROWID                        ID V2
------------------ ------------ --------------------
AAAWH5AAGAAAACBAAA            1 bbbbbbbbbbbbbbbbbbbb
AAAWH5AAGAAAACBAAB            2 bbbbbbbbbbbbbbbbbbbb
AAAWH5AAGAAAACBAAC            3 bbbbbbbbbbbbbbbbbbbb

SCOTT@book> @ &r/rowid AAAWH5AAGAAAACBAAA
      OBJECT         FILE        BLOCK          ROW ROWID_DBA            DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
       90617            6          129            0  0x1800081           6,129                alter system dump datafile 6 block 129 ;

--//摘取其中1段:

REDO RECORD - Thread:1 RBA: 0x000317.0000009c.0084 LEN: 0x017c VLD: 0x01
SCN: 0x0003.176a162e SUBSCN:  1 11/13/2017 10:13:57
CHANGE #1 TYP:0 CLS:35 AFN:3 DBA:0x00c00110 OBJ:4294967295 SCN:0x0003.176a1592 SEQ:1 OP:5.2 ENC:0 RBL:0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ktudh redo: slt: 0x0009 sqn: 0x0000532a flg: 0x0012 siz: 132 fbi: 0
            uba: 0x00c1cd3c.10c6.10    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:36 AFN:3 DBA:0x00c1cd3c OBJ:4294967295 SCN:0x0003.176a1591 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 132 spc: 3916 flg: 0x0012 seq: 0x10c6 rec: 0x10
            xid:  0x000a.009.0000532a
ktubl redo: slt: 9 rci: 0 opc: 11.1 [objn: 90617 objd: 90617 tsn: 7]
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00c1cd3c.10c6.0f
prev ctl max cmt scn:  0x0003.176a1392  prev tx cmt scn:  0x0003.176a1396
txn start scn:  0xffff.ffffffff  logon user: 83  prev brb: 12700985  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DSC row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01800081  hdba: 0x01800080
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0)
piece relative column number: 1
CHANGE #3 TYP:2 CLS:1 AFN:6 DBA:0x01800081 OBJ:90617 SCN:0x0003.176a14f9 SEQ:5 OP:11.14 ENC:0 RBL:0
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
KTB Redo
op: 0x11  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x000a.009.0000532a    uba: 0x00c1cd3c.10c6.10
Block cleanout record, scn:  0x0003.176a162e ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0003.176a14f9
KDO Op code: DSC row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01800081  hdba: 0x01800080
itli: 2  ispac: 0  maxfr: 4863
tabn: 0 slot: 0(0x0)
piece relative column number: 1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

--//突然想起我以前的一次测试,链接http://blog.itpub.net/267265/viewspace-2081924/=>[20160414]分析drop column.txt
--//做这个操作是实际上row directory是不动.后面字段的信息往前移动
SCOTT@book> @ &r/bbvi  6          129
BVI_COMMAND
---------------------------------------------------
bvi -b 1056768 -s 8192 /mnt/ramdisk/book/tea01.dbf

$ bvi -b 1056768 -s 8192 /mnt/ramdisk/book/tea01.dbf
...
00103F80  5F 51 01 01 2A 02 00 01 FA 12 2C 02 02 02 C1 02 _Q..*.....,.....
00103F90  14 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 .bbbbbbbbbbbbbbb
00103FA0  62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 bbbbbbbbbbbbbbbb
00103FB0  2C 02 02 02 C1 03 14 62 62 62 62 62 62 62 62 62 ,......bbbbbbbbb
00103FC0  62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 bbbbbbbbbbbbbbbb
00103FD0  62 62 62 62 62 62 2C 02 02 02 C1 04 14 62 62 62 bbbbbb,......bbb
00103FE0  62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 bbbbbbbbbbbbbbbb
00103FF0  62 62 62 62 62 62 62 62 62 62 62 62 01 06 35 16 bbbbbbbbbbbb..5.
00104000
--//可以发现前面的一串aaaaaa被覆盖了.

3.重复测试:
SCOTT@book> create table t1 (id number,v1 varchar2(10),v2 varchar2(10),v3 varchar2(10),v4 varchar2(10),v5 varchar2(50)) tablespace tea;
Table created.

SCOTT@book> insert into t1 select rownum,lpad('c',10,'c'),lpad('d',10,'d'),lpad('e',10,'e'),lpad('f',10,'f'),lpad('e',50,'e') from xmltable('1 to 4');
4 rows created.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> SELECT obj#,col#, segcol#, name, intcol#, type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T1' AND owner = user);
OBJ# COL#      SEGCOL# NAME INTCOL#        TYPE#
----- ---- ------------ ---- ------- ------------
90620    1            1 ID         1            2
90620    2            2 V1         2            1
90620    3            3 V2         3            1
90620    4            4 V3         4            1
90620    5            5 V4         5            1
90620    6            6 V5         6            1
6 rows selected.

SCOTT@book> select rowid,t1.* from t1 where rownum=1 ;
ROWID                        ID V1         V2         V3         V4         V5
------------------ ------------ ---------- ---------- ---------- ---------- --------------------------------------------------
AAAWH8AAGAAAACJAAA            1 cccccccccc dddddddddd eeeeeeeeee ffffffffff eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee

SCOTT@book> @ &r/rowid AAAWH8AAGAAAACJAAA
      OBJECT         FILE        BLOCK          ROW ROWID_DBA            DBA                  TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
       90620            6          137            0  0x1800089           6,137                alter system dump datafile 6 block 137 ;

2.删除列:
$ cat a.sql
alter table t1  SET UNUSED (v2,v4);
SELECT obj#,col#, segcol#, name, intcol#, type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T1' AND owner = user);
alter system archive log current ;
@ &r/logfile
select current_scn from v$database ;
ALTER TABLE t1 DROP UNUSED COLUMNS ;
select current_scn from v$database ;
SELECT obj#,col#, segcol#, name, intcol#, type# FROM sys.col$ WHERE obj# IN (SELECT object_id FROM dba_objects WHERE object_name = 'T1' AND owner = user);

SCOTT@book> @a.sql
Table altered.

      OBJ#       COL#    SEGCOL# NAME                    INTCOL#      TYPE#
---------- ---------- ---------- -------------------- ---------- ----------
     90620          1          1 ID                            1          2
     90620          2          2 V1                            2          1
     90620          0          3 SYS_C00003_17111311:          3          1
                                 01:01$

     90620          3          4 V3                            4          1
     90620          0          5 SYS_C00005_17111311:          5          1
                                 01:01$

     90620          4          6 V5                            6          1
6 rows selected.

System altered.
GROUP# STATUS     TYPE       MEMBER                           IS_ GROUP# THREAD# SEQUENCE#       BYTES BLOCKSIZE MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- -------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------- ------------------- ------------ -------------------
     1            ONLINE     /mnt/ramdisk/book/redo01.log     NO       1       1       791    52428800       512       1 YES INACTIVE     13277730193 2017-11-13 10:11:22  13277732831 2017-11-13 10:46:55
     2            ONLINE     /mnt/ramdisk/book/redo02.log     NO       2       1       792    52428800       512       1 YES ACTIVE       13277732831 2017-11-13 10:46:55  13277734072 2017-11-13 11:01:01
     3            ONLINE     /mnt/ramdisk/book/redo03.log     NO       3       1       793    52428800       512       1 NO  CURRENT      13277734072 2017-11-13 11:01:01 2.814750E+14
     4            STANDBY    /mnt/ramdisk/book/redostb01.log  NO
     5            STANDBY    /mnt/ramdisk/book/redostb02.log  NO
     6            STANDBY    /mnt/ramdisk/book/redostb03.log  NO
     7            STANDBY    /mnt/ramdisk/book/redostb04.log  NO

7 rows selected.

CURRENT_SCN
------------
13277734075

Table altered.

CURRENT_SCN
------------
13277734087

        OBJ#         COL#      SEGCOL# NAME                      INTCOL#        TYPE#
------------ ------------ ------------ -------------------- ------------ ------------
       90620            1            1 ID                              1            2
       90620            2            2 V1                              2            1
       90620            3            3 V3                              3            1
       90620            4            4 V5                              4            1

--//可以发现OBJ#没有变化.

SYS@book> alter system dump logfile '/mnt/ramdisk/book/redo03.log' scn min 13277734075 scn max 13277734087;
System altered.

$ grep "OBJ:90620" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_47804.trc
CHANGE #3 TYP:2 CLS:1 AFN:6 DBA:0x01800089 OBJ:90620 SCN:0x0003.176a2294 SEQ:5 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800089 OBJ:90620 SCN:0x0003.176a24bc SEQ:1 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800089 OBJ:90620 SCN:0x0003.176a24bc SEQ:2 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800089 OBJ:90620 SCN:0x0003.176a24bc SEQ:3 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800089 OBJ:90620 SCN:0x0003.176a24bc SEQ:4 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800089 OBJ:90620 SCN:0x0003.176a24bc SEQ:5 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800089 OBJ:90620 SCN:0x0003.176a24bc SEQ:6 OP:11.14 ENC:0 RBL:0
CHANGE #2 TYP:0 CLS:1 AFN:6 DBA:0x01800089 OBJ:90620 SCN:0x0003.176a24bc SEQ:7 OP:11.14 ENC:0 RBL:0
--//可以看到改动8次,我实际上SET UNUSED (v2,v4),2个字段.

$ grep "piece relative column number:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_47804.trc
piece relative column number: 2
piece relative column number: 2
piece relative column number: 3
piece relative column number: 3

piece relative column number: 2
piece relative column number: 2
piece relative column number: 3
piece relative column number: 3

piece relative column number: 2
piece relative column number: 2
piece relative column number: 3
piece relative column number: 3

piece relative column number: 2
piece relative column number: 2
piece relative column number: 3
piece relative column number: 3

--//注:我故意加回车分开.
--//注意piece relative column number的变化,说明oracle是一条一条记录修改,修改表T1的第2字段做删除操作,然后删除V4字段(注这个是否片的相对行号就是3,而不是4)
--//出现16次,其中一半是undo的记录.

时间： 2024-07-30 15:52:36

[20171113]修改表结构删除列相关问题2.txt的相关文章

[20171113]修改表结构删除列相关问题4.txt

[20171113]修改表结构删除列相关问题4.txt --//连续写了3篇修改表结构删除列的相关问题,链接如下: http://blog.itpub.net/267265/viewspace-2147158/ http://blog.itpub.net/267265/viewspace-2147163/ http://blog.itpub.net/267265/viewspace-2147196/ --//从redo记录日志内容看,日志仅仅记录偏移位置 (piece relative colu

[20171113]修改表结构删除列相关问题3.txt

[20171113]修改表结构删除列相关问题3.txt --//维护表结构删除字段一般都是先 ALTER TABLE <table_name> SET UNUSED (<column_name>); --//然后等空闲时候删除列. ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>; --//参考文档: https://docs.oracle.com/cd/E11882_01/server.

[20171113]修改表结构删除列相关问题.txt

[20171113]修改表结构删除列相关问题.txt --//维护表结构删除字段一般都是先 ALTER TABLE <table_name> SET UNUSED (<column_name>); --//然后等空闲时候删除列. ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>; --//参考文档: https://docs.oracle.com/cd/E11882_01/server.1

mysql修改表结构方法实例详解_Mysql

本文实例讲述了mysql修改表结构方法.分享给大家供大家参考.具体如下: mysql修改表结构使用ALTER TABLE语句,下面就为您详细介绍mysql修改表结构的语句写法,希望对您学习mysql修改表结构方面能有所帮助. ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_nam

必须会的SQL语句(二) 创建表、修改表结构、删除表_MsSql

1.创建数据库表 --使用哪个数据库,如果不写这一句是默认的数据库,也可以用鼠标选当前数据库 use testDB --创建表 Create Table tablename ( --id表示字段名 --int 数据类型 --primary key 主键 --not null 非空 --identity(1,1)初始值是1 每次自增长1 id int primary key not null identity(1,1), --unique 唯一 name varchar(20) not null

看实例学VFP：用sql命令修改表结构

在看实例学VFP:用sql语句修改数据表记录一文中介绍过用sql语句修改表记录的值,实际vfp中也可以用sql语言中的Alter语句动态的修改表结构.虽然可以实现这样的操作,但是我觉得尽可能还是不要在程序中动态修改表结构,因为这样有可能会造成程序的不稳定以及其它的意外情况. vfp中对表结构的操作主要是增加字段.修改字段.重命名字段和删除字段这四项,sql中修改表结构的语句是Alter语句,那么和这四项操作相对应的Alter语句格式可以总结如下: 增加字段:alter talbe 表名 add

mysql alter table命令修改表结构实例详解_php实例

mysql alter table语句可以修改表的基本结构,例如添加字段.删除字段.添加主键.添加索引.修改字段数据类型.对表重命名等等操作,本文章通过两个简单的实例向大家介绍mysql alter table的使用方法.  实例一:使用ALTER TABLE命令向表中添加字段.修改字段类型以及设置主键. 首先创建一个表,SQL语句如下: mysql> CREATE TABLE myTable( -> ID SMALLINT -> ); 使用desc命令查看表结构: mysql>

mysql alter table命令修改表结构实例_Mysql

mysql实例之使用alter table命令修改表结构 mysql alter table语句可以修改表的基本结构,例如添加字段.删除字段.添加主键.添加索引.修改字段数据类型.对表重命名等等操作,本文章通过两个简单的实例向大家介绍mysql alter table的使用方法  实例一:使用ALTER TABLE命令向表中添加字段.修改字段类型以及设置主键. 首先创建一个表,SQL语句如下: mysql> CREATE TABLE myTable( -> ID SMALLINT ->

sqlserver 2008手工修改表结构,表不能保存的问题与解决方法_mssql2005

如果点击"保存文本文件"将会弹出保存文本文件的对话框口, 保存的文本文件中的内容是如下: /*    2010年4月5日0:34:53    用户:    服务器: LONGGEL    数据库: longgel    应用程序: */ ChildCaiClass 保存的对于我来说简直是没用的信息,只是记录了事务的发生时间和一些相关信息,结果这样操作了数据库的结构还是没能修改并保存,而是继续弹出上面的那个窗口,这下我就郁闷了. 点击"取消"却弹出 同样也是没有完成表