oracle判断数据出现交叉,Oracle move table分析
在move操作的時(shí)候，進(jìn)行的是block之間的數(shù)據(jù)copy，所以table所位于的block的區(qū)域會(huì)發(fā)生變化；所有行物理存儲(chǔ)的順序不會(huì)發(fā)生變化，但行號(hào)會(huì)變成按AAA增1的順子，所以block里行會(huì)挪緊，實(shí)現(xiàn)了消除HWM和行碎片(包括行遷移問(wèn)題)，但不會(huì)釋放申請(qǐng)的空間(其他segment不能使用此部分的空閑空間，只能本segment增加數(shù)據(jù)時(shí)使用)，得到了空閑空間但不釋放，更說(shuō)明是block之間的copy，只是block內(nèi)行會(huì)挪緊。當(dāng)前的tablespace中需要有1倍于table的空閑空間以供使用。所有行的rowid都發(fā)生了變化，index是通過(guò)rowid來(lái)fetch數(shù)據(jù)行的，所以，table上的index是必須要rebuild的。table在進(jìn)行move操作時(shí)，table上加了exclusive?lock，我們只能對(duì)它進(jìn)行select的操作。反過(guò)來(lái)說(shuō)，當(dāng)我們的一個(gè)session對(duì)table進(jìn)行DML操作且沒(méi)有commit時(shí)，在另一個(gè)session中是不能對(duì)這個(gè)table進(jìn)行move操作的，否則oracle會(huì)返回這樣的錯(cuò)誤信息：ORA-00054:?資源正忙，要求指定?NOWAIT。最后重新編譯數(shù)據(jù)庫(kù)所有失效的對(duì)象，收集新的table統(tǒng)計(jì)分析數(shù)據(jù)。
實(shí)驗(yàn)：
1.建表并插入數(shù)據(jù)：
SQL>?create?table?sjh.test1(id?int)?tablespace?users;
表已創(chuàng)建。
SQL>?insert?into?sjh.test1?values(1);
已創(chuàng)建?1?行。
SQL>?insert?into?sjh.test1?values(2);
已創(chuàng)建?1?行。
SQL>?insert?into?sjh.test1?values(3);
已創(chuàng)建?1?行。
SQL>?insert?into?sjh.test1?values(4);
已創(chuàng)建?1?行。
SQL>?insert?into?sjh.test1?values(5);
已創(chuàng)建?1?行。
SQL>?insert?into?sjh.test1?values(6);
已創(chuàng)建?1?行。
SQL>?insert?into?sjh.test1?values(7);
已創(chuàng)建?1?行。
SQL>?insert?into?sjh.test1?values(8);
已創(chuàng)建?1?行。
SQL>?commit;
提交完成。
SQL>?select?*?from?sjh.test1;
ID
----------
1
2
3
4
5
6
7
8
已選擇8行。
2.查看表的rowid信息和block?id信息：
SQL>?select?rowid,id?from?sjh.test1;
ROWID??????????????????????ID
------------------?----------
AAAMlQAAEAAAABHAAA??????????1
AAAMlQAAEAAAABHAAB??????????2
AAAMlQAAEAAAABHAAC??????????3
AAAMlQAAEAAAABHAAD??????????4
AAAMlQAAEAAAABHAAE??????????5
AAAMlQAAEAAAABHAAF??????????6
AAAMlQAAEAAAABHAAG??????????7
AAAMlQAAEAAAABHAAH??????????8
已選擇8行。
SQL>?select?EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2???from?dba_extents?where?segment_name='TEST1';
EXTENT_ID????FILE_ID?RELATIVE_FNO???BLOCK_ID?????BLOCKS
----------?----------?------------?----------?----------
0??????????4????????????4?????????65??????????8
--8條記錄都在一個(gè)塊上(AAAABH)
這里簡(jiǎn)單介紹一下ROWID的知識(shí)：ROWID?在磁盤(pán)上需要10個(gè)字節(jié)的存儲(chǔ)空間并使用18個(gè)字符來(lái)顯示它包含下列組件：
數(shù)據(jù)對(duì)象編號(hào)：每個(gè)數(shù)據(jù)對(duì)象如表或索引在創(chuàng)建時(shí)都分配有此編號(hào)，并且此編號(hào)在數(shù)據(jù)庫(kù)中是唯一的；
相關(guān)文件編號(hào)：此編號(hào)對(duì)于一個(gè)表空間中的每個(gè)文件是唯一的；
塊編號(hào)：表示包含此行的塊在文件中的位置；
行編號(hào)：標(biāo)識(shí)塊頭中行目錄位置的位置；
在內(nèi)部數(shù)據(jù)對(duì)象編號(hào)需要32?位，相關(guān)文件編號(hào)需要10?位，塊編號(hào)需要22，位行編號(hào)需要16?位，加起來(lái)總共是80?位或10?個(gè)字節(jié)，ROWID?使用以64?為基數(shù)的編碼方案來(lái)顯示該方案將六個(gè)位置用于數(shù)據(jù)對(duì)象，編號(hào)三個(gè)位置用于相關(guān)文件編號(hào)六個(gè)位置用于塊編號(hào)三個(gè)位置用于行編號(hào)以64?為基數(shù)的編碼方案使用字符A-Z?a-z?0-9?+?和/共64?個(gè)字符。
如下例所示：AAAMlQ?AAE?AAAABH?AAA
在本例中
AAAMlQ????是數(shù)據(jù)對(duì)象編號(hào)
AAE???????是相關(guān)文件編號(hào)
AAAABH???是塊編號(hào)
AAA??????是行編號(hào)
3.做一些DML操作，再觀察ROWID有沒(méi)有發(fā)生變化：
SQL>?delete?from?sjh.test1?where?id=1;
已刪除?1?行。
SQL>?delete?from?sjh.test1?where?id=3;
已刪除?1?行。
SQL>?delete?from?sjh.test1?where?id=5;
已刪除?1?行。
SQL>?commit;
提交完成。
SQL>?select?rowid,id?from?sjh.test1;
ROWID??????????????????????ID
------------------?----------
AAAMlQAAEAAAABHAAB??????????2
AAAMlQAAEAAAABHAAD??????????4
AAAMlQAAEAAAABHAAF??????????6
AAAMlQAAEAAAABHAAG??????????7
AAAMlQAAEAAAABHAAH??????????8
--我們看到ROWID保持不變。
4.做MOVE操作，然后觀察ROWID的情況：
SQL>?alter?table?sjh.test1?move;
表已更改。
SQL>?select?rowid,id?from?sjh.test1;
ROWID??????????????????????ID
------------------?----------
AAAMlRAAEAAAABMAAA??????????2
AAAMlRAAEAAAABMAAB??????????4
AAAMlRAAEAAAABMAAC??????????6
AAAMlRAAEAAAABMAAD??????????7
AAAMlRAAEAAAABMAAE??????????8
SQL>?select?EXTENT_ID,FILE_ID,RELATIVE_FNO,BLOCK_ID,BLOCKS
2???from?dba_extents?where?segment_name='TEST1';
EXTENT_ID????FILE_ID?RELATIVE_FNO???BLOCK_ID?????BLOCKS
----------?----------?------------?----------?----------
0??????????4????????????4?????????73??????????8
--ROWID發(fā)生變化BLOCK_ID由原來(lái)的65變?yōu)?3，BLOCK的編號(hào)由原來(lái)的AAAABH變?yōu)锳AAABM
5.move對(duì)HWM的影響：
SQL>?create?table?my_objects?tablespace?HWM
2???as?select?*?from?all_objects;
SQL>?delete?from?my_objects?where?rownum<10000;
9999?rows?deleted
SQL>?select?count(*)?from?my_objects;
COUNT(*)
----------
21015
SQL>?exec?show_space(p_segname?=>?'MY_OBJECTS',p_owner?=>?'DLINGER',p_type?=>?'TABLE');
Total?Blocks............................425
Total?Bytes.............................3481600
Unused?Blocks...........................3
Unused?Bytes............................24576
Last?Used?Ext?FileId....................11
Last?Used?Ext?BlockId...................1294
Last?Used?Block.........................2
這里HWM=425?-?3?+?1?=?423
然后對(duì)table?MY_OBJECTS進(jìn)行move操作：
SQL>?alter?table?MY_OBJECTS?move;
表已更改。
SQL>?exec?show_space(p_segname?=>?'MY_OBJECTS',p_owner?=>?'DLINGER',p_type?=>?'TABLE');
Total?Blocks............................290
Total?Bytes.............................2375680
Unused?Blocks...........................1
Unused?Bytes............................8192
Last?Used?Ext?FileId....................11
Last?Used?Ext?BlockId...................1584
Last?Used?Block.........................4
我們可以看到，table?MY_OBJECTS的HWM從423移動(dòng)到290，table的HWM降低了！(show_space是自定義的一個(gè)過(guò)程)。
Move的一些用法:
以下是alter?table?中move子句的完整語(yǔ)法，我們介紹其中的幾點(diǎn)：
MOVE?[ONLINE]
[segment_attributes_clause]
[data_segment_compression]
[index_org_table_clause]
[?{?LOB_storage_clause?|?varray_col_properties?}
[?{?LOB_storage_clause?|?varray_col_properties?}?]...
]
[parallel_clause]
a.?我們可以使用move將一個(gè)table從當(dāng)前的tablespace上移動(dòng)到另一個(gè)tablespace上，如：
alter?table?t?move?tablespace?tablespace_name;
b.?我們還可以用move來(lái)改變table已有的block的存儲(chǔ)參數(shù),如：
alter?table?t?move?storage?(initial?30k?next?50k);
c.另外，move操作也可以用來(lái)解決table中的行遷移的問(wèn)題。
使用move的一些注意事項(xiàng):
a.?table上的index需要rebuild：
在前面我們討論過(guò)，move操作后，數(shù)據(jù)的rowid發(fā)生了改變，我們知道，index是通過(guò)rowid來(lái)fetch數(shù)據(jù)行的，所以，table上的index是必須要rebuild的。
SQL>?create?index?i_my_objects?on?my_objects?(object_id);
Index?created
SQL>?alter?table?my_objects?move;
Table?altered
SQL>?select?index_name,status?from?user_indexes?where?index_name='I_MY_OBJECTS';
INDEX_NAME?????????????????????STATUS
------------------------------?--------
I_MY_OBJECTS???????????????????UNUSABLE
從這里可以看到，當(dāng)table?MY_OBJECTS進(jìn)行move操作后，該table?上的inedx的狀態(tài)為UNUSABLE，這時(shí)，我們可以使用alter?index?I_MY_OBJECTS?rebuild?online的命令，對(duì)index?I_MY_OBJECTS進(jìn)行在線rebuild。
b.?move時(shí)對(duì)table的鎖定
當(dāng)我們對(duì)table?MY_OBJECTS進(jìn)行move操作時(shí)，查詢(xún)v$locked_objects視圖可以發(fā)現(xiàn)，table?MY_OBJECTS上加了exclusive?lock：
SQL>select?OBJECT_ID,?SESSION_ID,ORACLE_USERNAME,LOCKED_MODE?from?v$locked_objects;
OBJECT_ID?SESSION_ID?ORACLE_USERNAME????LOCKED_MODE
----------?----------?------------------?-----------
32471??????????9?DLINGER??????????????????????6
SQL>?select?object_id?from?user_objects?where?object_name?=?'MY_OBJECTS';
OBJECT_ID
----------
32471
這就意味著，table在進(jìn)行move操作時(shí)，我們只能對(duì)它進(jìn)行select的操作。反過(guò)來(lái)說(shuō)，當(dāng)我們的一個(gè)session對(duì)table進(jìn)行DML操作且沒(méi)有commit時(shí)，在另一個(gè)session中是不能對(duì)這個(gè)table進(jìn)行move操作的，否則oracle會(huì)返回這樣的錯(cuò)誤信息：ORA-00054:?資源正忙，要求指定?NOWAIT。
c.?關(guān)于move時(shí)空間使用的問(wèn)題：
當(dāng)我們使用alter?table?move來(lái)降低table的HWM時(shí)，有一點(diǎn)是需要注意的，這時(shí)，當(dāng)前的tablespace中需要有1倍于table的空閑空間以供使用：
SQL>?CREATE?TABLESPACE?TEST1
2???DATAFILE?'D:\ORACLE\ORADATA\ORACLE9I\TEST1.dbf'?SIZE?5M
3???UNIFORM?SIZE?128K?;
SQL>?create?table?my_objects?tablespace?test1?as?select?*?from?all_objects;
表已創(chuàng)建。
SQL>?select?bytes/1024/1024?from?user_segments?where?segment_name='MY_OBJECTS';
BYTES/1024/1024
---------------
3.125
SQL>?alter?table?MY_OBJECTS?move;
alter?table?MY_OBJECTS?move
*
ERROR?位于第?1?行:
ORA-01652:?無(wú)法通過(guò)16(在表空間TEST1中)擴(kuò)展?temp?段
SQL>?ALTER?DATABASE
2??DATAFILE?'D:\ORACLE\ORADATA\ORACLE9I\TEST1.DBF'?RESIZE?7M;
數(shù)據(jù)庫(kù)已更改。
SQL>?alter?table?MY_OBJECTS?move;
表已更改。
總結(jié)
以上是生活随笔為你收集整理的oracle判断数据出现交叉,Oracle move table分析的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 外部中断实验 编写程序学习外部中断的电平
- 下一篇： 升级计算机方案,关于计算机升级方案