1 、DBMS_REPAIR包的使用 Oracle提供的DBMS_REPAIR包可以發(fā)現(xiàn)、標(biāo)識(shí)并修改數(shù)據(jù)文件中的壞塊。 DBMS_REPAIR包的工作原理比較簡單——將檢查到的壞塊標(biāo)注出來，使隨后的dml操作跳過該塊。同時(shí)，DBMS_REPAIR包還提供了幾個(gè)過程，可以用來保存索引的鍵值（這些鍵值指向被標(biāo)注為壞塊的block）的過程，以及修復(fù)freelists和segment bitmap的過程。 DBMS_REPAIR包不但可以檢測(cè)出壞塊，根據(jù)表被索引的情況，還可以用來在一定程度上恢復(fù)壞塊中的數(shù)據(jù)。 需要注重，DBMS_REPAIR包沒有進(jìn)行授權(quán)，默認(rèn)情況下，只有sys用戶可以執(zhí)行。 下面通過一個(gè)完整的例子來說明DBMS_REPAIR包的使用。 第一步：構(gòu)造測(cè)試環(huán)境 首先建立一個(gè)測(cè)試用表空間，由于需要用UltraEdit打開數(shù)據(jù)文件修改部分內(nèi)容來模擬錯(cuò)誤，因此數(shù)據(jù)文件要建的小一些。 SQL> CREATE TABLESPACE TEST DATAFILE 'E:ORACLEORADATATESTTEST.DBF' SIZE 1M 2 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL; 表空間已創(chuàng)建。 SQL> CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(30)) TABLESPACE TEST; 表已創(chuàng)建。 SQL> INSERT INTO TEST SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS; 已創(chuàng)建6232行。 SQL> COMMIT; 提交完成。 SQL> CREATE INDEX IND_TEST_ID ON TEST (ID); 索引已創(chuàng)建。 SQL> CREATE INDEX IND_TEST_NAME ON TEST (NAME); 索引已創(chuàng)建。 為了確保數(shù)據(jù)庫已經(jīng)把剛才插入的數(shù)據(jù)寫到數(shù)據(jù)文件中，現(xiàn)在重起數(shù)據(jù)庫。 SQL> CONN /@TEST AS SYSDBA 已連接。 SQL> SHUTDOWN 數(shù)據(jù)庫已經(jīng)關(guān)閉。 已經(jīng)卸載數(shù)據(jù)庫。 ORACLE 例程已經(jīng)關(guān)閉。 SQL> STARTUP ORACLE 例程已經(jīng)啟動(dòng)。 Total System Global Area 89201304 bytes Fixed Size 453272 bytes Variable Size 62914560 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes 數(shù)據(jù)庫裝載完畢。 數(shù)據(jù)庫已經(jīng)打開。 第二步：模擬錯(cuò)誤的產(chǎn)生 用UltraEdit打開數(shù)據(jù)文件,只要修改了數(shù)據(jù)文件中任意的一個(gè)位置，都會(huì)造成數(shù)據(jù)文件錯(cuò)誤。但我們測(cè)試需要將錯(cuò)誤發(fā)生位置定位在TEST表中。 SQL> CONN YANGTK/YANGTK@TEST 已連接。 SQL> SELECT SUBSTR(ROWID, 10, 6), ID, NAME FROM TEST WHERE ID = 123; SUBSTR(ROWID ID NAME ------------ ---------- ------------------------------ AAAAAG 123 ALL_REPCONFLICT 如何在數(shù)據(jù)文件中找到TEST表的數(shù)據(jù)呢？可以通過ROWID來定位的記錄在數(shù)據(jù)文件中的位置。任意選擇一條記錄（如上面ID = 123），取得它的ROWID，我們知道，ROWID中10～15位表示這條記錄所在的BLOCK是數(shù)據(jù)文件的第幾個(gè)BLOCK。 A表示0，B為1，G表示6。這說明這條記錄在數(shù)據(jù)文件的第六個(gè)block中。 SQL> SHOW PARAMETER DB_BLOCK_SIZE NAME TYPE VALUE ------------------------------------ ----------- --------------- db_block_size integer 16384 BLOCK的大小是16k。 SQL> SELECT TO_CHAR(6*16384, 'XXXXXX') FROM DUAL; TO_CHAR ------- 18000 SQL> SELECT TO_CHAR(7*16384, 'XXXXXX') FROM DUAL; TO_CHAR ------- 1C000 用UltraEdit打開數(shù)據(jù)文件，將文件定位18000h處（以二進(jìn)制方式打開，假如沒有用二進(jìn)制打開，可以使用CTRL+H快捷鍵切換）。根據(jù)上面的計(jì)算，可以得出，我們要找到記錄在18000h和1C000h之間。 Number類型123在數(shù)據(jù)庫存放方式為03C20218，03表示占有三位，C2表示最高位是百位，02表示最高位上是1，18表示低位上是23。（假如對(duì)Oracle的基本數(shù)據(jù)類型的存儲(chǔ)格式感愛好，可以參考我在論壇上的帖子） 具體的數(shù)值可以通過下面的查詢得到: SQL> SELECT DUMP(123, 16) FROM DUAL; DUMP(123,16) -------------------- Typ=2 Len=3: c2,2,18 下面使用UltraEdit的搜索功能，查找到03C20218，將其修改為03C20216，并保存。 上面是通過Oracle的ROWID在文件中定位，這相對(duì)來說要復(fù)雜一些。下面可以直接使用UltraEdit的搜索功能達(dá)到相同的目的。 根據(jù)上面的查詢可以得到，ID = 123時(shí)，NAME的值是ALL_REPCONFLICT。 下面用UltraEdit打開文件，使用CTRL+H方式切換到文本格式，直接查找ALL_REPCONFLICT字符串。找到后，CTRL+H切換回二進(jìn)制格式。向前跳過一個(gè)長度字節(jié)（本例中為0F），就可以看到123的值03C20218，進(jìn)行修改后，保存并退出。 SQL> SELECT * FROM TEST WHERE ID = 123; ID NAME ---------- ------------------------------ 123 ALL_REPCONFLICT 這時(shí)候查詢?nèi)匀豢梢缘玫秸_結(jié)果，因?yàn)閛racle使用了db_cache中的結(jié)果。為了讓oracle“看”到修改，必須重起數(shù)據(jù)庫。 SQL> CONN /@TEST AS SYSDBA 已連接。 SQL> SHUTDOWN 數(shù)據(jù)庫已經(jīng)關(guān)閉。 已經(jīng)卸載數(shù)據(jù)庫。 ORACLE 例程已經(jīng)關(guān)閉。 SQL> STARTUP ORACLE 例程已經(jīng)啟動(dòng)。 Total System Global Area 89201304 bytes Fixed Size 453272 bytes Variable Size 62914560 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes 數(shù)據(jù)庫裝載完畢。數(shù)據(jù)庫已經(jīng)打開。 SQL> CONN YANGTK/YANGTK@TEST 已連接。 SQL> SELECT * FROM TEST WHERE ID = 123; SELECT * FROM TEST WHERE ID = 123 * ERROR 位于第 1 行: ORA-01578: ORACLE 數(shù)據(jù)塊損壞（文件號(hào)7，塊號(hào)6） ORA-01110: 數(shù)據(jù)文件 7: 'E:ORACLEORADATATESTTEST.DBF' 已經(jīng)模擬成功了壞塊，開始進(jìn)入正題部分，使用DBMS_REPAIR表來處理壞塊。 第三步：使用DBMS_REPAIR包處理壞塊。 1.建立REPAIR_TABLE和ORPHAN_KEY_TABLE表 SQL> BEGIN 2 DBMS_REPAIR.ADMIN_TABLES ( 3 TABLE_NAME => 'REPAIR_TABLE', 4 TABLE_TYPE => dbms_repair.repair_table, 5 ACTION => dbms_repair.create_action, 6 TABLESPACE => 'YANGTK'); 7 END; 8 / PL/SQL 過程已成功完成。 SQL> BEGIN 2 DBMS_REPAIR.ADMIN_TABLES ( 3 TABLE_NAME => 'ORPHAN_KEY_TABLE', 4 TABLE_TYPE => dbms_repair.orphan_table, 5 ACTION => dbms_repair.create_action, 6 TABLESPACE => 'YANGTK'); 7 END; 8 / PL/SQL 過程已成功完成。 REPAIR_TABLE用來記錄錯(cuò)誤檢查結(jié)果，ORPHAN_KEY_TABLE用來記錄表壞塊中記錄在索引中的對(duì)應(yīng)鍵值。 這兩個(gè)表的刪除可以通過下列存儲(chǔ)過程完成 BEGIN DBMS_REPAIR.ADMIN_TABLES ( TABLE_NAME => 'REPAIR_TABLE', TABLE_TYPE => dbms_repair.repair_table, ACTION => dbms_repair.drop_action); END; / BEGIN DBMS_REPAIR.ADMIN_TABLES ( TABLE_NAME => 'ORPHAN_KEY_TABLE', TABLE_TYPE => dbms_repair.orphan_table, ACTION => dbms_repair.drop_action); END; / 2.使用CHECK_OBJECT過程檢測(cè)壞塊。 SQL> SET SERVEROUTPUT ON SQL> DECLARE 2 num_corrupt INT; 3 BEGIN 4 num_corrupt := 0; 5 DBMS_REPAIR.CHECK_OBJECT ( 6 SCHEMA_NAME => 'YANGTK', 7 OBJECT_NAME => 'TEST', 8 REPAIR_TABLE_NAME => 'REPAIR_TABLE', 9 CORRUPT_COUNT => num_corrupt); 10 DBMS_OUTPUT.PUT_LINE('number corrupt: ' TO_CHAR (num_corrupt)); 11 END; 12 / number corrupt: 1 PL/SQL 過程已成功完成。 SQL> SELECT OBJECT_NAME, BLOCK_ID, CORRUPT_TYPE, MARKED_CORRUPT, 2 CORRUPT_DESCRIPTION, REPAIR_DESCRIPTION 3 FROM REPAIR_TABLE; OBJECT_NAM BLOCK_ID CORRUPT_TYPE MARKED_COR CORRUPT_DE REPAIR_DESCRIPTION ---------- ---------- ------------ ---------- ---------- ---------------------- TEST 6 6148 TRUE mark block software corrupt 這里和Oracle文檔上面有點(diǎn)出入，根據(jù)Oracle的文檔執(zhí)行完這一步操作MARKED_CORRUPT列的值是FALSE，只有執(zhí)行了FIX_CORRUPT_BLOCKS過程才會(huì)使MARKED_CORRUPT列的值變?yōu)門RUE。懷疑Oracle在CHECK的同時(shí)，會(huì)自動(dòng)進(jìn)行FIX_CORRUPT_BLOCKS的操作。 SQL> DECLARE 2 num_fix INT; 3 BEGIN 4 num_fix := 0; 5 DBMS_REPAIR.FIX_CORRUPT_BLOCKS ( 6 SCHEMA_NAME => 'YANGTK', 7 OBJECT_NAME=> 'TEST', 8 OBJECT_TYPE => dbms_repair.table_object, 9 REPAIR_TABLE_NAME => 'REPAIR_TABLE', 10 FIX_COUNT=> num_fix); 11 DBMS_OUTPUT.PUT_LINE('num fix: ' TO_CHAR(num_fix)); 12 END; 13 / num fix: 0 PL/SQL 過程已成功完成。 果然，執(zhí)行FIX_CORRUPT_BLOCKS過程發(fā)現(xiàn)FIX了0個(gè)壞塊，Oracle已經(jīng)在檢查的時(shí)候自動(dòng)標(biāo)識(shí)了壞塊，這一步操作可以省略不用執(zhí)行。 3.使用DUMP_ORPHAN_KEYS過程來保存壞塊中的索引鍵值。 這時(shí)還存在著一個(gè)潛在的問題。表出現(xiàn)了壞塊，但是索引沒有損壞，通過表掃描會(huì)出現(xiàn)錯(cuò)誤，但是通過索引掃描，仍然可以返回結(jié)果，這會(huì)造成數(shù)據(jù)的不一致。 SQL> SELECT * FROM YANGTK.TEST WHERE ID = 123; SELECT * FROM YANGTK.TEST WHERE ID = 123 * ERROR 位于第 1 行: ORA-01578: ORACLE 數(shù)據(jù)塊損壞（文件號(hào)7，塊號(hào)6） ORA-01110: 數(shù)據(jù)文件 7: 'E:ORACLEORADATATESTTEST.DBF' SQL> SELECT ID FROM YANGTK.TEST WHERE ID = 123; ID ---------- 123 通過使用DUMP_ORPHAN_KEYS過程來保存壞塊中數(shù)據(jù)對(duì)應(yīng)的索引鍵值，這樣當(dāng)執(zhí)行完SKIP_CORRUPT_BLOCKS操作后，就可以重新建立索引了。 SQL> DECLARE 2 num_orphans INT; 3 BEGIN 4 num_orphans := 0; 5 DBMS_REPAIR.DUMP_ORPHAN_KEYS ( 6 SCHEMA_NAME => 'YANGTK', 7 OBJECT_NAME => 'IND_TEST_ID', 8 OBJECT_TYPE => dbms_repair.index_object, 9 REPAIR_TABLE_NAME => 'REPAIR_TABLE', 10 ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE', 11 KEY_COUNT => num_orphans); 12 DBMS_OUTPUT.PUT_LINE('orphan key count: ' TO_CHAR(num_orphans)); 13 END; 14 / orphan key count: 549 PL/SQL 過程已成功完成。 SQL> DECLARE 2 num_orphans INT; 3 BEGIN 4 num_orphans := 0; 5 DBMS_REPAIR.DUMP_ORPHAN_KEYS ( 6 SCHEMA_NAME => 'YANGTK', 7 OBJECT_NAME => 'IND_TEST_NAME', 8 OBJECT_TYPE => dbms_repair.index_object, 9 REPAIR_TABLE_NAME => 'REPAIR_TABLE', 10 ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE', 11 KEY_COUNT => num_orphans); 12 DBMS_OUTPUT.PUT_LINE('orphan key count: ' TO_CHAR(num_orphans)); 13 END; 14 / orphan key count: 549 PL/SQL 過程已成功完成。 注重對(duì)每個(gè)索引都要執(zhí)行DUMP_ORPHAN_KEYS過程。 4.使用REBUILD_FREELISTS過程來修改FREELISTS。 假如壞塊發(fā)生在FREELIST列表中的中部，則FREELIST列表后面的塊都無法訪問。在這個(gè)例子中，模擬產(chǎn)生的錯(cuò)誤的位置不在FREELIST中，因此可以跳過這一步驟，一般情況下，由于無法定位確定壞塊的位置，則需要執(zhí)行此過程。 SQL> BEGIN 2 DBMS_REPAIR.REBUILD_FREELISTS ( 3 SCHEMA_NAME => 'YANGTK', 4 OBJECT_NAME => 'TEST', 5 OBJECT_TYPE => dbms_repair.table_object); 6 END; 7 / PL/SQL 過程已成功完成。 5.執(zhí)行SKIP_CORRUPT_BLOCKS過程，使后續(xù)的DML操作跳過壞塊 SQL> BEGIN 2 DBMS_REPAIR.SKIP_CORRUPT_BLOCKS ( 3 SCHEMA_NAME => 'YANGTK', 4 OBJECT_NAME => 'TEST', 5 OBJECT_TYPE => dbms_repair.table_object, 6 FLAGS => dbms_repair.skip_flag); 7 END; 8 / PL/SQL 過程已成功完成。 SQL> SELECT OWNER, TABLE_NAME, SKIP_CORRUPT FROM DBA_TABLES 2 WHERE OWNER = 'YANGTK'; OWNER TABLE_NAME SKIP_COR ---------------------------- ---------------------------- -------- YANGTK TEST ENABLED YANGTK TEST1 DISABLED YANGTK TEST_AAA DISABLED YANGTK TEST_PART DISABLED 已選擇4行。 6.重建索引 由于數(shù)據(jù)和索引仍然存在不一致的問題，因此必須重建索引。 SQL> SELECT * FROM YANGTK.TEST WHERE ID = 123; 未選定行 SQL> SELECT ID FROM YANGTK.TEST WHERE ID = 123; ID ---------- 123 SQL> ALTER INDEX YANGTK.IND_TEST_ID REBUILD; 索引已更改。 SQL> SELECT ID FROM YANGTK.TEST WHERE ID = 123; ID ---------- 123 注重：重建索引一定要先DROP INDEX，然后CREATE INDEX的方式。假如使用了REBUILD的方式，那么重建索引時(shí)的數(shù)據(jù)源是原來的索引，重建后仍然會(huì)保留著不一致的數(shù)據(jù)。 SQL> DROP INDEX YANGTK.IND_TEST_ID; 索引已丟棄。 SQL> DROP INDEX YANGTK.IND_TEST_NAME; 索引已丟棄。 SQL> CREATE INDEX YANGTK.IND_TEST_ID ON YANGTK.TEST(ID); 索引已創(chuàng)建。 SQL> CREATE INDEX YANGTK.IND_TEST_NAME ON YANGTK.TEST(NAME); 索引已創(chuàng)建。 SQL> SELECT ID FROM YANGTK.TEST WHERE ID = 123; 未選定行 SQL> SELECT MIN(ID) FROM YANGTK.TEST; MIN(ID) ---------- 550 包含ID ＝ 123的塊已經(jīng)別標(biāo)識(shí)為壞塊。現(xiàn)在可以看到，最小的ID是550，也就是說，這個(gè)壞塊中包含了549條記錄。 SQL> SELECT COUNT(*) FROM ORPHAN_KEY_TABLE; COUNT(*) ---------- 1098 繼續(xù)查詢ORPHAN_KEY_TABLE表，可以發(fā)現(xiàn)，這些記錄的索引（2個(gè)）已經(jīng)被保存到了ORPHAN_KEY_TABLE表中。2 恢復(fù)數(shù)據(jù) 使用DBMS_REPAIR包的目的不僅是為了使表重新可以訪問，而且使用這個(gè)包還能在一定程度上恢復(fù)被因壞塊而無法讀取的數(shù)據(jù)。 由于壞塊產(chǎn)生在表上，因此索引是可以訪問，所有被索引的列的數(shù)據(jù)都可以恢復(fù)。遺憾的是，Oracle的文檔并沒有給出恢復(fù)的方法，我查詢了METALINK和ASKTOM也沒有找到相應(yīng)的答案，所以，恢復(fù)的工作只能靠自己摸索進(jìn)行。這部分的內(nèi)容完全是建立在我對(duì)Oracle數(shù)據(jù)類型理解的基礎(chǔ)上的，雖然我已經(jīng)對(duì)我的程序進(jìn)行過測(cè)試，但是由于沒有文檔可以參考，而且測(cè)試環(huán)境相對(duì)比較單一，因此，我并不能確保我提供的包和函數(shù)一定沒有錯(cuò)誤。假如想將這種方法應(yīng)用的正式系統(tǒng)中，請(qǐng)首先做好備份工作。 言歸正傳，在上面的步驟中，使用DUMP_ORPHAN_KEYS過程保存了壞塊中的索引鍵值，下面就通過這些保存的鍵值來進(jìn)行數(shù)據(jù)的恢復(fù)。 先看一下ORPHAN_KEY_TABLE的表結(jié)構(gòu)： SQL> DESC ORPHAN_KEY_TABLE 名稱 是否為空? 類型 -------------------------------------- -------- -------------- SCHEMA_NAME NOT NULL VARCHAR2(30) INDEX_NAME NOT NULL VARCHAR2(30) IPART_NAME VARCHAR2(30) INDEX_ID NOT NULL NUMBER TABLE_NAME NOT NULL VARCHAR2(30) PART_NAME VARCHAR2(30) TABLE_ID NOT NULL NUMBER KEYROWID NOT NULL ROWID KEY NOT NULL ROWID DUMP_TIMESTAMP NOT NULL DATE 由于字段名基本上都是自解釋的，這里就不再過多描述了，需要說明的是KEYROWID和KEY兩個(gè)字段。 KEYROWID存放的是該索引鍵值對(duì)應(yīng)的表中的ROWID，而KEY保存的就是索引的鍵值。 但是查詢KEY的值發(fā)現(xiàn)，并非像想象中一樣，存放的是1、2、3……或ALL_TABLES、Access$……等值，而是以邏輯ROWID方式存放的。 SQL> SELECT KEY FROM ORPHAN_KEY_TABLE WHERE INDEX_NAME = 'IND_TEST_ID' AND ROWNUM = 1; KEY --------------------------------------------------------------- *BAAAAAACwQL+ SQL> SELECT KEY FROM ORPHAN_KEY_TABLE WHERE INDEX_NAME = 'IND_TEST_NAME' AND ROWNUM = 1; KEY --------------------------------------------------------------- *BAAAAAAHQUNDRVNTJP4 如何將邏輯ROWID轉(zhuǎn)化為NUMBER或VARCHAR2類型呢？Oracle的文檔并沒有找到相應(yīng)的解決方法。 于是抱著嘗試的想法對(duì)這個(gè)字段DUMP一下。 SQL> SELECT DUMP(KEY) FROM ORPHAN_KEY_TABLE WHERE INDEX_NAME = 'IND_TEST_ID' AND ROWNUM < 6; DUMP(KEY) ---------------------------------------------------------------- Typ=208 Len=10: 2,4,0,0,0,0,2,193,2,254 Typ=208 Len=10: 2,4,0,0,0,0,2,193,3,254 Typ=208 Len=10: 2,4,0,0,0,0,2,193,4,254 Typ=208 Len=10: 2,4,0,0,0,0,2,193,5,254 Typ=208 Len=10: 2,4,0,0,0,0,2,193,6,254 這回看到希望了。還記得上面修改數(shù)據(jù)文件時(shí)123的編碼嗎？是不是和第一個(gè)查詢中的結(jié)果很相似？ 2,4,0,0,0,0前幾位是不變的，最后一位254也是不變的。中間的部分就是有意義的數(shù)字了。其中第一個(gè)2表示長度是2，193表示最高位是個(gè)位，2表示最高位上的值是1，也就是說，第一個(gè)鍵值是數(shù)字1。（可以參考我對(duì)邏輯ROWID的存儲(chǔ)格式進(jìn)行分析的帖子） SQL> SELECT DUMP(1) FROM DUAL; DUMP(1) ------------------ Typ=2 Len=2: 193,2 Oracle把數(shù)據(jù)在文件中的存儲(chǔ)格式保存在ROWID字段中了。根據(jù)這個(gè)假設(shè)，我們看看字符串是不是以同樣方式存儲(chǔ)的。 SQL> SELECT DUMP(KEY) FROM ORPHAN_KEY_TABLE WHERE INDEX_NAME = 'IND_TEST_NAME' AND ROWNUM < 6; DUMP(KEY) ---------------------------------------------------------------- Typ=208 Len=15: 2,4,0,0,0,0,7,65,67,67,69,83,83,36,254 Typ=208 Len=17: 2,4,0,0,0,0,9,65,71,71,88,77,76,73,77,80,254 Typ=208 Len=23: 2,4,0,0,0,0,15,65,71,71,88,77,76,73,78,80,85,84,84,89,80,69,254 Typ=208 Len=22: 2,4,0,0,0,0,14,65,76,76,95,65,76,76,95,84,65,66,76,69,83,254 Typ=208 Len=17: 2,4,0,0,0,0,9,65,76,76,95,65,80,80,76,89,254 顯然7代表長度，后面跟著的明顯就是ASCII編碼。 SQL> SELECT CHR(65)CHR(67)CHR(67)CHR(69)CHR(83)CHR(83)CHR(36) FROM DUAL; CHR(65) ------- ACCESS$ 知道這個(gè)規(guī)律，就可以著手進(jìn)行恢復(fù)了。我寫了一個(gè)包，用來恢復(fù)各種數(shù)據(jù)類型的數(shù)據(jù)。 SQL> CREATE OR REPLACE PACKAGE P_DUMPROWID 2 AS 3 --內(nèi)部函數(shù)，由于去掉DUMP結(jié)果中的開頭描述部分 4 FUNCTION F_GET_VALUE(P_STR IN VARCHAR2) RETURN VARCHAR2; 5 PRAGMA RESTRICT_REFERENCES (F_GET_VALUE, WNDS, WNPS, RNDS, RNPS); 6 7 --整理DUMP結(jié)果中16進(jìn)制數(shù)，一位數(shù)的情況前面補(bǔ)0，并過濾逗號(hào) 8 FUNCTION F_ADD_PREFIX_ZERO (P_STR IN VARCHAR2, P_POSITION IN NUMBER) RETURN VARCHAR2; 9 PRAGMA RESTRICT_REFERENCES (F_ADD_PREFIX_ZERO, WNDS, WNPS, RNDS, RNPS); 10 11 --根據(jù)輸入ROWID、方案名、索引名和索引中列的位置，將ROWID中的列的值提取出來 12 FUNCTION F_DUMP_FROM_ROWID 13 ( 14 P_DUMP_ROWID IN UROWID, 15 P_OWNER IN VARCHAR2, 16 P_INDEX_NAME IN VARCHAR2, 17 P_COLUMN_POSITION IN NUMBER DEFAULT 1 18 ) 19 RETURN VARCHAR2; 20 PRAGMA RESTRICT_REFERENCES (F_DUMP_FROM_ROWID, WNDS, WNPS); 21 22 END P_DUMPROWID; 23 / 程序包已創(chuàng)建。 SQL> CREATE OR REPLACE PACKAGE BODY P_DUMPROWID AS 2 3 FUNCTION F_GET_VALUE(P_STR IN VARCHAR2) RETURN VARCHAR2 AS 4 BEGIN 5 RETURN (SUBSTR(P_STR, INSTR(P_STR, ':') + 2)); 6 END F_GET_VALUE; 7 8 FUNCTION F_ADD_PREFIX_ZERO (P_STR IN VARCHAR2, P_POSITION IN NUMBER) RETURN VARCHAR2 9 AS 10 V_STR VARCHAR2(30000) := P_STR; 11 V_POSITION NUMBER := P_POSITION; 12 V_STR_PART VARCHAR2(2); 13 V_RETURN VARCHAR2(30000); 14 BEGIN 15 WHILE (V_POSITION != 0) LOOP 16 V_STR_PART := SUBSTR(V_STR, 1, V_POSITION - 1); 17 V_STR := SUBSTR(V_STR, V_POSITION + 1); 18 19 IF V_POSITION = 2 THEN 20 V_RETURN := V_RETURN '0' V_STR_PART; 21 ELSIF V_POSITION = 3 THEN 22 V_RETURN := V_RETURN V_STR_PART; 23 ELSE 24 RAISE_APPLICATION_ERROR(-20002, 'DUMP ERROR CHECK THE INPUT ROWID'); 25 END IF; 26 27 V_POSITION := INSTR(V_STR, ','); 28 END LOOP; 29 RETURN REPLACE(V_RETURN , ','); 30 END F_ADD_PREFIX_ZERO; 31 32 FUNCTION F_DUMP_FROM_ROWID 33 ( 34 P_DUMP_ROWID IN UROWID, 35 P_OWNER IN VARCHAR2, 36 P_INDEX_NAME IN VARCHAR2, 37 P_COLUMN_POSITION IN NUMBER DEFAULT 1 38 ) 39 RETURN VARCHAR2 AS 40 V_COLUMN_TYPE DBA_TAB_COLUMNS.DATA_TYPE%TYPE; 41 42 V_LENGTH_STR VARCHAR2(10); 43 V_LENGTH NUMBER DEFAULT 7; 44 V_DUMP_ROWID VARCHAR2(30000); 45 46 V_DATE_STR VARCHAR2(100); 47 TYPE T_DATE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; 48 V_DATE T_DATE; 49 50 BEGIN 51 52 --根據(jù)SCHEMA、INDEX_NAME和COLUMN_NAME得到COLUMN的數(shù)據(jù)類型 53 SELECT T.DATA_TYPE 54 INTO V_COLUMN_TYPE 55 FROM DBA_IND_COLUMNS I, DBA_TAB_COLUMNS T 56 WHERE I.TABLE_NAME = T.TABLE_NAME 57 AND I.TABLE_OWNER = T.OWNER 58 AND I.COLUMN_NAME = T.COLUMN_NAME 59 AND I.TABLE_OWNER = P_OWNER 60 AND I.INDEX_NAME = P_INDEX_NAME 61 AND I.COLUMN_POSITION = P_COLUMN_POSITION; 62 63 --根據(jù)COLUMN在索引中的位置，循環(huán)找到這個(gè)COLUMN對(duì)應(yīng)的ROWID 64 FOR I IN 1..P_COLUMN_POSITION LOOP 65 66 --假如COLUMN的長度超過127，即表示長度的位超過7f，則開始用兩位來存儲(chǔ)長度，其中第一位以8開始。 67 SELECT F_GET_VALUE(DUMP(P_DUMP_ROWID, 16, V_LENGTH, 1)) INTO V_LENGTH_STR FROM DUAL; 68 IF SUBSTR(V_LENGTH_STR, 1, 1) = '8' THEN 69 SELECT SUBSTR(F_GET_VALUE(DUMP(P_DUMP_ROWID, 16, V_LENGTH, 2)), 2) INTO V_LENGTH_STR FROM DUAL; 70 V_LENGTH_STR := TO_CHAR(TO_NUMBER(REPLACE(V_LENGTH_STR, ','), 'XXXX')); 71 SELECT F_GET_VALUE(DUMP(P_DUMP_ROWID, 16, V_LENGTH + 2, TO_NUMBER(V_LENGTH_STR))) INTO V_DUMP_ROWID 72 FROM DUAL; 73 V_LENGTH := V_LENGTH + TO_NUMBER(V_LENGTH_STR) + 2; 74 ELSE 75 V_LENGTH_STR := TO_CHAR(TO_NUMBER(V_LENGTH_STR, 'XXX')); 76 SELECT F_GET_VALUE(DUMP(P_DUMP_ROWID, 16, V_LENGTH + 1, TO_NUMBER(V_LENGTH_STR))) INTO V_DUMP_ROWID 77 FROM DUAL; 78 V_LENGTH := V_LENGTH + TO_NUMBER(V_LENGTH_STR) + 1; 79 END IF; 80 END LOOP; 81 82 IF V_COLUMN_TYPE = 'VARCHAR2' OR V_COLUMN_TYPE = 'CHAR' THEN 83 84 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID ',', INSTR(V_DUMP_ROWID, ',')); 85 86 RETURN(UTL_RAW.CAST_TO_VARCHAR2(V_DUMP_ROWID)); 87 ELSIF V_COLUMN_TYPE = 'NUMBER' THEN 88 89 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID ',', INSTR(V_DUMP_ROWID, ',')); 90 91 RETURN(TO_CHAR(UTL_RAW.CAST_TO_NUMBER(V_DUMP_ROWID))); 92 ELSIF V_COLUMN_TYPE = 'DATE' THEN 93 94 V_DUMP_ROWID := ',' V_DUMP_ROWID ','; 95 96 FOR I IN 1..7 LOOP 97 V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1, 98 INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX'); 99 END LOOP; 100 101 V_DATE(1) := V_DATE(1) - 100; 102 V_DATE(2) := V_DATE(2) - 100; 103 104 IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN 105 V_DATE_STR := '-' LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) LTRIM(TO_CHAR(ABS(V_DATE(2)), '00')); 106 ELSE 107 V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) LTRIM(TO_CHAR(ABS(V_DATE(2)),'00')); 108 END IF; 109 110 V_DATE_STR := V_DATE_STR '-' TO_CHAR(V_DATE(3)) '-' TO_CHAR(V_DATE(4)) ' ' 111 TO_CHAR(V_DATE(5) - 1) ':' TO_CHAR(V_DATE(6) - 1) ':' TO_CHAR(V_DATE(7) - 1); 112 RETURN (V_DATE_STR); 113 ELSIF V_COLUMN_TYPE LIKE 'TIMESTAMP(_)' THEN 114 115 V_DUMP_ROWID := ',' V_DUMP_ROWID ','; 116 117 FOR I IN 1..11 LOOP 118 V_DATE(I) := TO_NUMBER(SUBSTR(V_DUMP_ROWID, INSTR(V_DUMP_ROWID, ',', 1, I) + 1, 119 INSTR(V_DUMP_ROWID, ',', 1, I + 1) - INSTR(V_DUMP_ROWID, ',', 1, I) - 1), 'XXX'); 120 END LOOP; 121 122 V_DATE(1) := V_DATE(1) - 100; 123 V_DATE(2) := V_DATE(2) - 100; 124 125 IF ((V_DATE(1) < 0) OR (V_DATE(2) < 0)) THEN 126 V_DATE_STR := '-' LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) LTRIM(TO_CHAR(ABS(V_DATE(2)), '00')); 127 ELSE 128 V_DATE_STR := LTRIM(TO_CHAR(ABS(V_DATE(1)), '00')) LTRIM(TO_CHAR(ABS(V_DATE(2)),'00')); 129 END IF; 130 131 V_DATE_STR := V_DATE_STR '-' TO_CHAR(V_DATE(3)) '-' TO_CHAR(V_DATE(4)) ' ' 132 TO_CHAR(V_DATE(5) - 1) ':' TO_CHAR(V_DATE(6) - 1) ':' TO_CHAR(V_DATE(7) - 1) '.' 133 SUBSTR(TO_CHAR(V_DATE(8) * POWER(256, 3) + V_DATE(9) * POWER(256, 2) + V_DATE(10) * 256 + V_DATE(11)), 134 1, TO_NUMBER(SUBSTR(V_COLUMN_TYPE, 11, 1))); 135 RETURN (V_DATE_STR); 136 ELSIF V_COLUMN_TYPE = 'RAW' THEN 137 138 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID ',', INSTR(V_DUMP_ROWID, ',')); 139 140 RETURN(V_DUMP_ROWID); 141 ELSIF V_COLUMN_TYPE = 'ROWID' THEN 142 143 V_DUMP_ROWID :=F_ADD_PREFIX_ZERO(V_DUMP_ROWID ',', INSTR(V_DUMP_ROWID, ',')); 144 145 RETURN (DBMS_ROWID.ROWID_CREATE( 146 1, 147 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 1, 8), 'XXXXXXXXXXX'), 148 TRUNC(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), 'XXXXXX')/64), 149 TO_NUMBER(MOD(TO_NUMBER(SUBSTR(V_DUMP_ROWID, 9, 4), 'XXXXXX'), 64) 150 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 13, 4), 'XXXXXXXXXXX')), 151 TO_NUMBER(SUBSTR(V_DUMP_ROWID, 17, 4), 'XXXXXX'))); 152 ELSE 153 RAISE_APPLICATION_ERROR(-20001, 'TYPE NOT VALID OR CAN''T TRANSALTE ' V_COLUMN_TYPE ' TYPE'); 154 END IF; 155 EXCEPTION 156 WHEN NO_DATA_FOUND THEN 157 RAISE_APPLICATION_ERROR(-20003, 158 'INVALID SCHEMA_NAME OR INVALID INDEX_NAME OR INVALID COLUMN_NAME OR INVALID COLUMN_POSITION'); 159 END F_DUMP_FROM_ROWID; 160 161 END P_DUMPROWID; 162 /程序包主體已創(chuàng)建。 這個(gè)包主要對(duì)外提供一個(gè)函數(shù)F_DUMP_FROM_ROWID，另外兩個(gè)函數(shù)是為了包中內(nèi)部調(diào)用的。這個(gè)函數(shù)的輸入?yún)?shù)分別是邏輯ROWID，表所在的SCHEMA，索引名稱和抽取的列在索引中的位置。前三個(gè)參數(shù)分別由ORPHAN_KEY_TABLE表的KEY、SCHEMA_NAME和INDEX_NAME三個(gè)字段的值提供，最后一個(gè)參數(shù)的默認(rèn)值為1，對(duì)于單列索引或復(fù)合索引的第一個(gè)字段的抽取，可以省略這個(gè)參數(shù)。 由于dbms_repair包一般是由DBA執(zhí)行的，因此一般把這個(gè)包也建立在SYS帳戶中，假如用普通用戶建立，需要對(duì)DBA_TAB_COLUMNS和DBA_IND_COLUMNS的單獨(dú)授權(quán)。下面測(cè)試一下函數(shù)的功能。 SQL> SELECT P_DUMPROWID.F_DUMP_FROM_ROWID(KEY, SCHEMA_NAME, INDEX_NAME) DUMP FROM ORPHAN_KEY_TABLE 2 WHERE INDEX_NAME = 'IND_TEST_ID' AND ROWNUM < 6; DUMP ------------------------------------------- 1 2 3 4 5 SQL> SELECT P_DUMPROWID.F_DUMP_FROM_ROWID(KEY, SCHEMA_NAME, INDEX_NAME, 1) DUMP FROM ORPHAN_KEY_TABLE 2 WHERE INDEX_NAME = 'IND_TEST_NAME' AND ROWNUM < 6; DUMP ------------------------------------------- ACCESS$ AGGXMLIMP AGGXMLINPUTTYPE ALL_ALL_TABLES ALL_APPLY 好了，剩下的事情就簡單了。我們將ORPHAN_KEY_TABLE表中的記錄轉(zhuǎn)變后，重新插入到TEST表中即可。 SQL> INSERT INTO YANGTK.TEST (ID, NAME) 2 SELECT 3 P_DUMPROWID.F_DUMP_FROM_ROWID(A.KEY, A.SCHEMA_NAME, A.INDEX_NAME), 4 P_DUMPROWID.F_DUMP_FROM_ROWID(B.KEY, B.SCHEMA_NAME, B.INDEX_NAME) 5 FROM ORPHAN_KEY_TABLE A, ORPHAN_KEY_TABLE B 6 WHERE A.KEYROWID = B.KEYROWID 7 AND A.INDEX_NAME = 'IND_TEST_ID' 8 AND B.INDEX_NAME = 'IND_TEST_NAME'; 已創(chuàng)建549行。 SQL> SELECT * FROM YANGTK.TEST WHERE ID = 1; ID NAME ---------- ------------------------------ 1 ACCESS$ SQL> SELECT * FROM YANGTK.TEST WHERE ID = 123; ID NAME ---------- ------------------------------ 123 ALL_REPCONFLICT SQL> COMMIT; 至此，已經(jīng)成功的恢復(fù)了數(shù)據(jù)。 3 使用P_DUMPROWID恢復(fù)數(shù)據(jù)的例子 下面再簡單說明一下這個(gè)程序包是如何恢復(fù)其他數(shù)據(jù)類型的。 通過查詢ORPHAN_KEY_TABLE可以發(fā)現(xiàn)，Oracle把索引的值保存在邏輯ROWID中，我這個(gè)包實(shí)現(xiàn)的其實(shí)就是將邏輯ROWID中的值還原為索引鍵值。下面的例子為了簡單起見，通過建立索引組織表，然后使用這個(gè)包還原索引組織表的ROWID信息作為參考的例子。 例1：對(duì)NUMBER類型、VARCHAR2類型和TIME類型的恢復(fù)，對(duì)復(fù)合索引的恢復(fù)。 SQL> create table test_index (id number, name varchar2(30), time date, 2 constraint pk_test_index primary key (id, name, time)) 3 organization index; 表已創(chuàng)建。 SQL> insert into test_index select rownum, object_name, created from user_objects; 已創(chuàng)建64行。 SQL> commit; 提交完成。 SQL> col dump format a30 SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss'; 會(huì)話已更改。 SQL> select id, p_dumprowid.f_dump_from_rowid(ROWID, 'YANGTK', 'PK_TEST_INDEX') dump 2 from test_index where rownum < 5; ID DUMP ---------- ------------------------------ 1 1 2 2 3 3 4 4 SQL> select name, p_dumprowid.f_dump_from_rowid(ROWID, 'YANGTK', 'PK_TEST_INDEX', 2) dump 2 from test_index where rownum < 5; NAME DUMP ------------------------------ ------------------------------ AA AA IND_Q1_ID IND_Q1_ID IND_Q1_ID IND_Q1_ID IND_Q1_ID IND_Q1_ID SQL> select time, p_dumprowid.f_dump_from_rowid(ROWID, 'YANGTK', 'PK_TEST_INDEX', 3) dump 2 from test_index where rownum < 5; TIME DUMP ------------------- ------------------------------ 2004-12-19 02:36:33 2004-12-19 2:36:33 2004-12-18 23:17:56 2004-12-18 23:17:56 2004-12-18 23:17:56 2004-12-18 23:17:56 2004-12-18 23:18:33 2004-12-18 23:18:33 例二：對(duì)TIMESTAMP類型的恢復(fù)。 SQL> create table test_stamp (time timestamp constraint pk_test_stamp primary key) 2 organization index; 表已創(chuàng)建。 SQL> insert into test_stamp values (systimestamp); 已創(chuàng)建 1 行。 SQL> alter session set nls_timestamp_format = 'yyyy-mm-dd hh24:mi:ss.ff'; 會(huì)話已更改。 SQL> col dump format a40 SQL> col time format a40 SQL> select time, p_dumprowid.f_dump_from_rowid(rowid, 'YANGTK', 'PK_TEST_STAMP') dump 2 from test_stamp; TIME DUMP ---------------------------------------- ---------------------------------------- 2005-01-13 00:44:27.392000 2005-1-13 0:44:27.392000 SQL> alter table test_stamp modify (time timestamp(9)); 表已更改。 SQL> insert into test_stamp values (to_timestamp('2005-1-1 12:23:23.334282122', 2 'yyyy-mm-dd hh24:mi:ss.ff')); 已創(chuàng)建 1 行。 SQL> select time, p_dumprowid.f_dump_from_rowid(rowid, 'YANGTK', 'PK_TEST_STAMP') dump 2 from test_stamp; TIME DUMP ---------------------------------------- ---------------------------------------- 2005-01-01 12:23:23.334282122 2005-1-1 12:23:23.334282122 2005-01-13 00:44:27.392000000 2005-1-13 0:44:27.392000000 這個(gè)包可以支持普通的單列BTREE索引和復(fù)合BTREE索引，不支持reverse key索引和BITMAP索引。 目前支持的數(shù)據(jù)類型包括CHAR、VARCHAR2、NUMBER、DATE、TIMESTAMP、RAW和物理ROWID類型。這個(gè)包不支持的類型包括NCHAR、NVARCHAR2、TIMESTAMP WITH LOCAL TIME ZONE和TIMESTAMP WITH TIME ZONE等幾種不很常用的類型(LONG、LONG RAW、邏輯ROWID和LOB類型不支持索引)。 假如VARCHAR2（CHAR）類型中包含中文，在ZHS16GBK字符集下我測(cè)試通過，其他字符集沒有測(cè)試，但估計(jì)對(duì)于一般中文字符集都不會(huì)有問題，但是假如用單字節(jié)字符集表示中文可能會(huì)有問題。 任何一個(gè)工具也不可能只有好的方面而沒有任何缺點(diǎn)。使用DBMS_REPAIR包的同時(shí)會(huì)帶來數(shù)據(jù)丟失、表和索引返回?cái)?shù)據(jù)不一致，完整性約束破壞等問題。因此當(dāng)出現(xiàn)錯(cuò)誤時(shí)，首先應(yīng)當(dāng)考慮用物理備份或邏輯備份進(jìn)行恢復(fù)，DBMS_REPAIR包應(yīng)該只是在沒有備份的情況下使用的一種手段。DMBS_REPAIR包無法恢復(fù)表中沒有被索引的列，因此使用這種方式一般都會(huì)造成數(shù)據(jù)的丟失。 參考文檔： 1.Oracle9i Administrator’s Guide 2.Oracle9i Database Concepts 3.Supplied PL/SQL Packages and Types Reference 我在ITPUB上的這篇帖子包括了對(duì)Oracle基本數(shù)據(jù)類型的描述： http://www.itpub.net/308317.Html