[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的記錄.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2147163/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20171113]修改表結構刪除列相關問題.txt
- [20171113]修改表結構刪除列相關問題3.txt
- [20171113]修改表結構刪除列相關問題4.txt
- oracle修改表增加列刪除列修改列Oracle
- iptables刪除命令中的相關問題
- Oracle 增加 修改 刪除 列Oracle
- 關於修改分割槽表的問題總結
- 常見問題--oracle10g修改表結構Oracle
- [MYSQL][1]建立，修改，刪除表MySql
- 關於javascript中陣列元素刪除問題的討論 (轉)JavaScript陣列
- mysql相關問題總結MySql
- [20180413]熱備模式相關問題2.txt模式
- 線性表的相關操作-初始化、增添、刪除
- Laravel佇列相關問題記錄Laravel佇列
- 在Oracle 9i中修改表的結構的相關sql語句OracleSQL
- iOS上架4.3相關問題修改建議iOS
- 臨時表空間資料刪除問題
- ORACLE表空間的建立修改刪除Oracle
- 【體系結構】與Checkpoint相關的問題解決思路
- 資料結構C語言|佇列相關資料結構C語言佇列
- 刪除 AP 發票相關指令碼指令碼
- 結構體相關結構體
- Oracle表 列欄位的增加、刪除、修改以及重新命名操作sqlOracleSQL
- SQL SERVER 不能修改表結構、列型別 解決方法SQLServer型別
- 關於刪除資料的快慢問題的分析
- Hadoop/Spark相關面試問題總結HadoopSpark面試
- HTTPS總結+相關面試問題解答HTTP面試
- 【專案問題總結】5：樹形結構節點的級聯刪除邏輯
- 演算法問題總結-連結串列相關演算法
- 表管理之二：表資料的插入修改與刪除
- Docker 相關問題Docker
- django相關問題Django
- electron相關問題
- 【實戰】使用“基表修改法”恢復被“set unused”方式誤刪除的列
- 在迴圈陣列時使用splice()方法刪除陣列遇到的問題陣列
- 使用全表掃描快取大表的相關問題快取
- 資料結構——RMQ（ST表）問題資料結構MQ
- [文件教程]解決SAE下本地除錯相關問題除錯