修改表结构删除列相关问题

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

查看数据库版本:

l5m@orcl(304)> @&r/ver

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

准备测试数据: 

l5m@orcl(304)> create tablespace cup datafile
  2  '/u01/oradata/orcl/cup01.dbf' size 10m autoextend on next 10m maxsize unlimited
  3  logging
  4  online
  5  extent management local autoallocate
  6  blocksize 8k
  7  segment space management manual
  8  flashback on;

Tablespace created.

l5m@orcl(304)> drop table t;

Table dropped.

l5m@orcl(304)> create table t  (id number,v1 varchar2(10),v2 varchar2(20)) tablespace cup;

Table created.

l5m@orcl(304)>  insert into t select 2,lpad('a',10,'a'),lpad('b',20,'b') from dual;

1 row created.

l5m@orcl(304)> insert into t select 3,lpad('a',10,'a'),lpad('b',20,'b') from dual;

1 row created.

l5m@orcl(304)> commit;

Commit complete.

删除数据

l5m@orcl(304)> alter table t  SET UNUSED (v1);

Table altered.

l5m@orcl(304)> alter system archive log current ;

System altered.

l5m@orcl(304)> set numw 12
l5m@orcl(304)>  select current_scn from v$database ;

 CURRENT_SCN
------------
 17672256923

l5m@orcl(304)> @&r/logfile;

GROUP# THREAD# SEQUENCE# TYPE   MEMBER                       BYTES MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME
------ ------- --------- ------ -------------------- ------------- ------- --- ---------- ------------- -------------------
     1       1        49 ONLINE /u01/oradata/orcl/re     536870912       1 YES ACTIVE       17672152817 2020-04-14 22:00:04
                                do01.log

     2       1        50 ONLINE /u01/oradata/orcl/re     536870912       1 NO  CURRENT      17672256907 2020-04-16 14:34:43
                                do02.log

     3       1        45 ONLINE /u01/oradata/orcl/re     536870912       1 YES INACTIVE     17671723949 2020-04-09 16:28:56
                                do03.log

     4       1        46 ONLINE /u01/oradata/orcl/re     536870912       1 YES INACTIVE     17671762767 2020-04-09 16:30:04
                                do04.log

     5       1        47 ONLINE /u01/oradata/orcl/re     536870912       1 YES INACTIVE     17671801548 2020-04-09 16:32:04
                                do05.log

     6       1        48 ONLINE /u01/oradata/orcl/re     536870912       1 YES INACTIVE     17671840350 2020-04-09 16:34:00
                                do06.log

GROUP# THREAD# SEQUENCE# TYPE   MEMBER                       BYTES MEMBERS ARC STATUS     FIRST_CHANGE# FIRST_TIME
------ ------- --------- ------ -------------------- ------------- ------- --- ---------- ------------- -------------------


6 rows selected.

当前的redo log是redo02.log

真正的删除列

l5m@orcl(304)> ALTER TABLE t DROP UNUSED COLUMNS ;

Table altered.

l5m@orcl(304)> select current_scn from v$database ;

 CURRENT_SCN
------------
 17672257063

l5m@orcl(304)> 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#
------------ ------------ ------------ ------------------------------ ------------ ------------
      421618            1            1 ID                                        1            2
      421618            2            2 V2                                        2            1

//v1列已删除了,数据字典已查不到v1,obj#是421618

查看转储文件

l5m@orcl(304)> alter system dump logfile '/u01/oradata/orcl/redo02.log' scn min 17672256923 scn max 17672257063;

System altered.
[oracle@qht115 udump]$ grep 421618 /u01/app/oracle/admin/orcl/udump/orcl_ora_3918.trc
ktubl redo: slt: 11 rci: 0 opc: 11.1 objn: 421618 objd: 421618 tsn: 32
CHANGE #3 TYP:2 CLS: 1 AFN:11 DBA:0x02c0000a OBJ:421618 SCN:0x0004.1d593d67 SEQ:  1 OP:11.14
ktubu redo: slt: 11 rci: 29 opc: 11.1 objn: 421618 objd: 421618 tsn: 32
CHANGE #2 TYP:0 CLS: 1 AFN:11 DBA:0x02c0000a OBJ:421618 SCN:0x0004.1d593e1d SEQ:  1 OP:11.14
ktubu redo: slt: 11 rci: 30 opc: 11.1 objn: 421618 objd: 421618 tsn: 32
CHANGE #2 TYP:0 CLS: 1 AFN:11 DBA:0x02c0000a OBJ:421618 SCN:0x0004.1d593e1d SEQ:  2 OP:11.14

看到redo里面有3次修改,和表的行数相吻合,说明删除列的操作是一个一个删除列数据的

 具体看一段转储的redo内容

REDO RECORD - Thread:1 RBA: 0x000032.00000043.0194 LEN: 0x0060 VLD: 0x01
SCN: 0x0004.1d593dc3 SUBSCN:  1 04/16/2020 14:35:48
CHANGE #1 TYP:0 CLS:29 AFN:2 DBA:0x00800069 OBJ:4294967295 SCN:0x0004.1d593dc2 SEQ:  1 OP:5.4
ktucm redo: slt: 0x0010 sqn: 0x00024822 srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x00800159.ec9e.0c ext: 2 spc: 6650 fbi: 0

REDO RECORD - Thread:1 RBA: 0x000032.00000044.0010 LEN: 0x01a0 VLD: 0x01
SCN: 0x0004.1d593dc3 SUBSCN:  2 04/16/2020 14:35:48
CHANGE #1 TYP:0 CLS:19 AFN:2 DBA:0x00800019 OBJ:4294967295 SCN:0x0004.1d593dbd SEQ:  1 OP:5.2
ktudh redo: slt: 0x000a sqn: 0x00030823 flg: 0x0412 siz: 132 fbi: 0
            uba: 0x00800bf6.1714.33    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:20 AFN:2 DBA:0x00800bf6 OBJ:4294967295 SCN:0x0004.1d593dbc SEQ:  2 OP:5.1
ktudb redo: siz: 132 spc: 1450 flg: 0x0012 seq: 0x1714 rec: 0x33
            xid:  0x0002.00a.00030823
ktubl redo: slt: 10 rci: 0 opc: 11.1 objn: 482 objd: 482 tsn: 0
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x00800bf6.1714.31
prev ctl max cmt scn:  0x0004.1d59376e  prev tx cmt scn:  0x0004.1d593773
txn start scn:  0xffff.ffffffff  logon user: 0  prev brb: 8391663  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01
op: L  itl: xid:  0x0008.00c.00030a0f uba: 0x00800a70.1c2d.15
                      flg: C---    lkc:  0     scn: 0x0004.1d593dc2
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00400eba  hdba: 0x00400eb9
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 22(0x16)
CHANGE #3 TYP:2 CLS: 1 AFN:1 DBA:0x00400eba OBJ:482 SCN:0x0004.1d593dc3 SEQ:  1 OP:11.2
KTB Redo
op: 0x01  ver: 0x01
op: F  xid:  0x0002.00a.00030823    uba: 0x00800bf6.1714.33
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x00400eba  hdba: 0x00400eb9
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 22(0x16) size/delt: 27

 --//做这个操作是实际上row directory是不动.后面字段的信息往前移动

 接着看一下datafile里面的内容

l5m@orcl(304)> @&r/rowid AABm7yAALAAAAAKAAA

ROWID                        OBJ# FILE#   BLOCK#      ROW# TEXT
-------------------- ------------ ----- -------- --------- ----------------------------------------
AAAAECAABAAAAgiAAA         421618    11       10         0 alter system dump datafile 11 block 10;
l5m@orcl(304)> @ &r/bbvi 11 10

BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 81920 -s 8192 /u01/oradata/orcl/cup01.dbf

 

[oracle@qht115 udump]$ bvi -b 81920 -s 8192 /u01/oradata/orcl/cup01.dbf
。。。。。
00015F7C  00 00 00 00 00 00 00 00 00 00 00 00 00 00 2C 02 02 02 C1 04 ..............,.....
00015F90  14 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 .bbbbbbbbbbbbbbbbbbb
00015FA4  62 62 62 62 62 62 62 62 62 62 62 62 2C 02 02 02 C1 03 14 62 bbbbbbbbbbbb,......b
00015FB8  62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 bbbbbbbbbbbbbbbbbbbb
00015FCC  62 62 62 62 62 62 62 62 62 62 2C 02 02 02 C1 02 14 62 62 62 bbbbbbbbbb,......bbb
00015FE0  62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 62 bbbbbbbbbbbbbbbbbbbb
00015FF4  62 62 62 62 62 62 62 62 01 06 24 3E                         bbbbbbbb..$>

--//可以发现前面的一串aaaaaa被覆盖了.

 

 

 

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页