[20171113]修改表結構刪除列相關問題3.txt
[20171113]修改表結構刪除列相關問題3.txt
--//維護表結構刪除欄位一般都是先
ALTER TABLE <table_name> SET UNUSED (<column_name>);
--//然後等空閒時候刪除列.
ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>;
--//參考文件:
Removing Unused Columns
The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes
unused columns from the table and reclaims disk space.
In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint
to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of
undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.
ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;
--//從文件上可以看出加入CHECKPOINT關鍵字可以一定程度減少undo空間的消耗.
--//測試看看使用CHECKPOINT <n>的情況.如果執行中斷會出現什麼情況呢?會回滾嗎,oracle如何處理這些細節問題.而且這個時候sys.col$並不能修改.
--//因為這些修改沒有完成.透過測試理解這些問題.
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 table t (id number,v1 varchar2(5),v2 varchar2(10));
insert into t select rownum,lpad('a',5,'a'),lpad('b',10,'b') from xmltable('1 to 1000000');
commit;
SCOTT@book> SELECT obj#,col#, segcol#, name, intcol#, type#,PROPERTY 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# PROPERTY
------------ ------------ ------------ -------------------- ------------ ------------ ------------
90622 1 1 ID 1 2 0
90622 2 2 V1 2 1 0
90622 3 3 V2 3 1 0
SCOTT@book> ALTER TABLE t SET UNUSED (v1);
Table altered.
SCOTT@book> SELECT obj#,col#, segcol#, name, intcol#, type#,PROPERTY 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# PROPERTY
------------ ------------ ------------ ------------------------------ ------------ ------------ ------------
90622 1 1 ID 1 2 0
90622 0 2 SYS_C00002_17111311:56:47$ 2 1 32800
90622 2 3 V2 3 1 0
COTT@book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
------------ ------------ ------------------------ --------- ------ ------- ------------ --------------------------------------------------
67 93 48102 DEDICATED 48103 29 35 alter system kill session '67,93' immediate;
--// 記下程式號spid=48103
2.執行DROP UNUSED COLUMNS:
--//session 1:
ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10;
--//session 2, 在命令列執行:
$ kill -l
1) SIGHUP 2) SIGINT 3) SIGQUIT 4) SIGILL
5) SIGTRAP 6) SIGABRT 7) SIGBUS 8) SIGFPE
9) SIGKILL 10) SIGUSR1 11) SIGSEGV 12) SIGUSR2
13) SIGPIPE 14) SIGALRM 15) SIGTERM 16) SIGSTKFLT
17) SIGCHLD 18) SIGCONT 19) SIGSTOP 20) SIGTSTP
21) SIGTTIN 22) SIGTTOU 23) SIGURG 24) SIGXCPU
25) SIGXFSZ 26) SIGVTALRM 27) SIGPROF 28) SIGWINCH
29) SIGIO 30) SIGPWR 31) SIGSYS 34) SIGRTMIN
35) SIGRTMIN+1 36) SIGRTMIN+2 37) SIGRTMIN+3 38) SIGRTMIN+4
39) SIGRTMIN+5 40) SIGRTMIN+6 41) SIGRTMIN+7 42) SIGRTMIN+8
43) SIGRTMIN+9 44) SIGRTMIN+10 45) SIGRTMIN+11 46) SIGRTMIN+12
47) SIGRTMIN+13 48) SIGRTMIN+14 49) SIGRTMIN+15 50) SIGRTMAX-14
51) SIGRTMAX-13 52) SIGRTMAX-12 53) SIGRTMAX-11 54) SIGRTMAX-10
55) SIGRTMAX-9 56) SIGRTMAX-8 57) SIGRTMAX-7 58) SIGRTMAX-6
59) SIGRTMAX-5 60) SIGRTMAX-4 61) SIGRTMAX-3 62) SIGRTMAX-2
63) SIGRTMAX-1 64) SIGRTMAX
--//19 是 stop,18是cout,9 kill.我直接使用-9 kill程式.
--//先寫下命令,避免手忙腳亂.先在session 1發出ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10;,然後切換到session 2執行如下命令.
$ kill -9 48103
--//session 1:
SCOTT@book> ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10;
ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 10
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 48103
Session ID: 67 Serial number: 93
SCOTT@book> select * from t where rownum<=10;
select * from t where rownum<=10
*
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE
-- //可以發現無法select,也就是要Submit ALTER TABLE DROP COLUMNS CONTINUE.
SCOTT@book> ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 1000;
ALTER TABLE t DROP UNUSED COLUMNS CHECKPOINT 1000
*
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE
$ oerr ora 12986
12986, 00000, "columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE"
// *Cause: An attempt was made to access a table with columns in partially
// dropped state (i.e., drop column operation was interrupted).
// *Action: Submit ALTER TABLE DROP COLUMNS CONTINUE to complete the drop
// column operation before accessing the table.
--//一旦出現這樣的情況,就不能在使用CHECKPOINT引數.而是執行:
SCOTT@book> ALTER TABLE t DROP COLUMNS CONTINUE;
Table altered.
SCOTT@book> select * from t where rownum<=1;
ID V2
---------- ----------
563 bbbbbbbbbb
--ok. 問題在於oracle如何知道發生了中斷,重新產生問題跟蹤看看(步驟略).
SCOTT@book> @ &r/10046on 12
old 1: alter session set events '10046 trace name context forever, level &1'
new 1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SCOTT@book> select * from t where rownum<=1;
select * from t where rownum<=1
*
ERROR at line 1:
ORA-12986: columns in partially dropped state. Submit ALTER TABLE DROP COLUMNS CONTINUE
SCOTT@book> @ &r/10046off
Session altered.
--//看不出問題.沒有任何線索.
SYS@book> select * from sys.tab$ where obj#=90622;
...省略...
SP2-0784: Invalid or incomplete character beginning 0xEF returned
--//報SP2-0784錯誤.
SYS@book> host oerr SP2 0784
00784,0, "Invalid or incomplete character beginning 0x%02X returned\n"
// *Cause: Attempted to return a string from the database that contained
// an invalid or incomplete character.
// *Action: Replace the invalid or incomplete string in the database with
// a valid or complete string.
--//一個一個欄位查詢確定,問題在spare4欄位上.
SYS@book> select SPARE4 from sys.tab$ where obj#=90622;
SPARE4
-----------------------------------------------------------------
SP2-0784: Invalid or incomplete character beginning 0xEF returned
SCOTT@book> select dump(SPARE4,16) c30 ,spare6 from sys.tab$ where obj#=90622;
C30 SPARE6
------------------------------ --------------------
Typ=1 Len=6: 1,0,3,e1,0,ef 2017-11-13 03:56:47
--//注意後面有一個ef表示.
--//再建立一個表T1 對比看看.
SCOTT@book> create table t1 (id number,v1 varchar2(5),v2 varchar2(10));
Table created.
SCOTT@book> insert into t1 select rownum,lpad('a',5,'a'),lpad('b',10,'b') from xmltable('1 to 100000');
100000 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> SELECT obj#,col#, segcol#, name, intcol#, type#,PROPERTY 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# PROPERTY
---------- ---------- ---------- -------------------- ---------- ---------- ----------
90624 1 1 ID 1 2 0
90624 2 2 V1 2 1 0
90624 3 3 V2 3 1 0
SCOTT@book> column spare6 format a20
SCOTT@book> select OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,spare5,spare6 from sys.tab$ where obj#=90624;
OBJ# DATAOBJ# SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------------
90624 90624 736 2017-11-13 06:59:26
SCOTT@book> ALTER TABLE t1 SET UNUSED (v1);
Table altered.
SCOTT@book> select OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,spare5,spare6 from sys.tab$ where obj#=90624;
OBJ# DATAOBJ# SPARE1 SPARE2 SPARE3 SPARE4 SPARE5 SPARE6
---------- ---------- ------------ ---------- ---------- ---------- ---------- --------------------
90624 90624 736 2017-11-13 07:01:07
SCOTT@book> SELECT obj#,col#, segcol#, name, intcol#, type#,PROPERTY 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# PROPERTY
---------- ---------- ---------- ------------------------------ ---------- ---------- ----------
90624 1 1 ID 1 2 0
90624 0 2 SYS_C00002_17111315:01:08$ 2 1 32800
90624 2 3 V2 3 1 0
--//你可以發現在ALTER TABLE t1 DROP UNUSED COLUMNS CHECKPOINT 10;前,sys.tab$的spare4為null.
--//session 1:
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
54 203 49380 DEDICATED 49381 28 74 alter system kill session '54,203' immediate;
--//確定轉儲檔案: /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_49381.trc
$ cat x1.sql
@ &r/10046on 12
select current_scn from v$database ;
ALTER TABLE t1 DROP UNUSED COLUMNS CHECKPOINT 10;
select current_scn from v$database ;
@ &r/10046off
SCOTT@book> select OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,dump(spare4,16) c30,spare5,spare6 from sys.tab$ where obj#=90624;
OBJ# DATAOBJ# SPARE1 SPARE2 SPARE3 SPARE4 C30 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- --------------------
90624 90624 736 寠 k Typ=1 Len=6: 1,0,8c,8a,0,6b 2017-11-13 07:01:07
--// 檢查跟蹤檔案,發現如下:(注sql語句我做了格式化處理.)
PARSING IN CURSOR #140630994050912 len=532 dep=1 uid=0 oct=6 lid=0 tim=1510556663952810 hv=685354830 ad='7bc57128' sqlid='b5cr4hhndmbuf'
UPDATE tab$
SET ts# = :2,
file# = :3,
block# = :4,
bobj# = decode(:5,
0,
null,
:5),
tab# = decode(:6,
0,
null,
:6),
intcols = :7,
kernelcols = :8,
clucols = decode(:9,
0,
null,
:9),
audit$ = :10,
flags = :11,
pctfree$ = :12,
pctused$ = :13,
initrans = :14,
maxtrans = :15,
rowcnt = :16,
blkcnt = :17,
empcnt = :18,
avgspc = :19,
chncnt = :20,
avgrln = :21,
analyzetime = :22,
samplesize = :23,
cols = :24,
property = :25,
degree = decode(:26,
1,
null,
:26),
instances = decode(:27,
1,
null,
:27),
dataobj# = :28,
avgspc_flb = :29,
flbcnt = :30,
trigflag = :31,
spare1 = :32,
spare2 = decode(:33,
0,
null,
:33),
spare4 = :34,
spare6 = :35
WHERE obj# = :1
END OF STMT
*** 2017-11-13 15:04:23.953
BINDS #140630995900744:
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
kxsbbbfp=7fe73485db08 bln=22 avl=02 flg=05
value=4
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=7fe73485db20 bln=22 avl=02 flg=01
value=3
...
Bind#38
oacdty=01 mxl=32(06) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=7c1eef40 bln=32 avl=06 flg=09
value="^A"
~~~~~~~~~~~~~~~~~~
Bind#39
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=10 fl2=0001 frm=00 csi=00 siz=8 off=0
kxsbbbfp=7c1eef5e bln=07 avl=07 flg=09
value="11/13/2017 7:1:7"
Bind#40
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fe734950188 bln=22 avl=04 flg=05
value=90624
--//注意看下劃線就是插入sprae4的值.
SCOTT@book> select OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,dump(spare4,16) c30,spare5,spare6 from sys.tab$ where obj#=90624;
OBJ# DATAOBJ# SPARE1 SPARE2 SPARE3 SPARE4 C30 SPARE5 SPARE6
---------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ---------- --------------------
90624 90624 736 寠 k Typ=1 Len=6: 1,0,8c,8a,0,6b 2017-11-13 07:01:07
--//不知道spare4的插入值的具體含義.可以知道僅僅與drop 欄位有關.轉儲日誌分析看看:
alter system dump logfile '/mnt/ramdisk/book/redo02.log' scn min 13277923577 scn max 13277943577;
$ egrep "col 33: \[ 6\] 01 00|col 0: \[ 4\] c3 0a 07 19" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_49641.trc > /tmp/aa.txt
--//注 c3 0a 07 18 對應的是obj#欄位
SCOTT@book> select dump(90624,16) from dual ;
DUMP(90624,16)
----------------------
Typ=2 Len=4: c3,a,7,19
--//檢查/tmp/aa.txt文字,可以發現如下資訊.
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 00 0a
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 00 0a
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 00 14
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 00 14
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 00 1e
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 00 1e
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 00 28
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 00 28
col 0: [ 4] c3 0a 07 19
...
--//0x0a=10,0x14=20,1e=30,0x28=40.哈哈看出來了嗎?這個就是每次提交的記錄行號. 我是每10條一個提交,這樣就很好猜測rowid之類的資訊.
--//spare4記錄下一條操作的記錄rowid,我估計.
--//如果繼續往下看
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cb 01 0e
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cc 00 01
col 0: [ 4] c3 0a 07 19
--//這裡跨塊了,0x10e=270.
--//好了修復看看該表的rowid一切就清楚了.
SCOTT@book> ALTER TABLE t1 DROP COLUMNS CONTINUE;
Table altered.
SCOTT@book> select OBJ#,DATAOBJ#,spare1,spare2,spare3,spare4,dump(spare4,16) c30,spare5,spare6 from sys.tab$ where obj#=90624;
OBJ# DATAOBJ# SPARE1 SPARE2 SPARE3 SPARE4 C30 SPARE5 SPARE6
------------ ------------ ---------- ---------- ---------- ---------- ------------------------------ ---------- --------------------
90624 90624 736 NULL 2017-11-13 07:54:14
--//完成後spare4內容清空.
SCOTT@book> select rowid,t1.* from t1 where rownum<=2;
ROWID ID V2
------------------ ------------ ----------
AAAWIAAAEAAAHrLAAA 563 bbbbbbbbbb
AAAWIAAAEAAAHrLAAB 564 bbbbbbbbbb
SCOTT@book> @ &r/rowid AAAWIAAAEAAAHrLAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
90624 4 31435 0 0x1007ACB 4,31435 alter system dump datafile 4 block 31435
--//注意看ROWID_DBA,就是塊地址.與前面的轉儲內容一直.可以猜測這塊279條記錄.因為下一個記錄是"01 00 7a cc 00 01".
--//塊dba=0x01007acc有1條已經提交了+加上剩下9條作為一個事務提交.驗證看看.
SCOTT@book> select count(*) from t1 where rowid between 'AAAWIAAAEAAAHrLAAA' and 'AAAWIAAAEAAAHrLBBB';
COUNT(*)
------------
279
--//繼續看下面也驗證了猜測.
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cc 01 0f
col 0: [ 4] c3 0a 07 19
col 33: [ 6] 01 00 7a cd 00 02
col 0: [ 4] c3 0a 07 19
總結:
1.ALTER TABLE <table_name> DROP UNUSED COLUMNS CHECKPOINT <n>;
In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint
to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of
undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.
--//雖然減少undo的使用,但是如果中斷或者中途退出,表無法查詢與使用,必須執行ALTER TABLE <table_name> DROP COLUMNS CONTINUE;修復.
--//並且再使用checkpoint 引數.
--//oracle是透過修改相應sys.tab$表的spare4的值來確定這個工作是否完成,並且這裡記錄的是下一次要操作記錄的rowid.
2.測試很幸運正好sys.tab$顯示pare4線索異常, 最佳的方式應該是直接跟蹤ALTER TABLE t1 DROP UNUSED COLUMNS CHECKPOINT 10;操作.走了一個彎路.^_^.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2147196/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20171113]修改表結構刪除列相關問題.txt
- [20171113]修改表結構刪除列相關問題2.txt
- [20171113]修改表結構刪除列相關問題4.txt
- oracle修改表增加列刪除列修改列Oracle
- iptables刪除命令中的相關問題
- Oracle 增加 修改 刪除 列Oracle
- 關於修改分割槽表的問題總結
- 常見問題--oracle10g修改表結構Oracle
- [MYSQL][1]建立，修改，刪除表MySql
- 關於javascript中陣列元素刪除問題的討論 (轉)JavaScript陣列
- mysql相關問題總結MySql
- Laravel佇列相關問題記錄Laravel佇列
- 線性表的相關操作-初始化、增添、刪除
- 在Oracle 9i中修改表的結構的相關sql語句OracleSQL
- iOS上架4.3相關問題修改建議iOS
- 臨時表空間資料刪除問題
- ORACLE表空間的建立修改刪除Oracle
- 【體系結構】與Checkpoint相關的問題解決思路
- 資料結構C語言|佇列相關資料結構C語言佇列
- 刪除 AP 發票相關指令碼指令碼
- 結構體相關結構體
- Oracle表 列欄位的增加、刪除、修改以及重新命名操作sqlOracleSQL
- [20191223]關於共享記憶體段相關問題3.txt記憶體
- SQL SERVER 不能修改表結構、列型別 解決方法SQLServer型別
- Hadoop/Spark相關面試問題總結HadoopSpark面試
- HTTPS總結+相關面試問題解答HTTP面試
- 關於刪除資料的快慢問題的分析
- 【專案問題總結】5：樹形結構節點的級聯刪除邏輯
- 演算法問題總結-連結串列相關演算法
- 表管理之二：表資料的插入修改與刪除
- [20190110]rlwrap sqlplus tee相關問題3.txtSQL
- Docker 相關問題Docker
- django相關問題Django
- electron相關問題
- 【實戰】使用“基表修改法”恢復被“set unused”方式誤刪除的列
- 在迴圈陣列時使用splice()方法刪除陣列遇到的問題陣列
- 使用全表掃描快取大表的相關問題快取
- 資料結構——RMQ（ST表）問題資料結構MQ