這篇文章主要介紹“分析Oracle能否在2小時內(nèi)在線完成表結(jié)構(gòu)字段類型變更”，在日常操作中，相信很多人在分析Oracle能否在2小時內(nèi)在線完成表結(jié)構(gòu)字段類型變更問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”分析Oracle能否在2小時內(nèi)在線完成表結(jié)構(gòu)字段類型變更”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！
黃梅ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)建站的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18982081108（備注：SSL證書合作）期待與您的合作！
摘要：Oracle 12c 能否在2小時內(nèi)在線完成一張14億條記錄的表結(jié)構(gòu)字段類型變更
前面分享過Oracle大表在線修改的腳本（在線重定義），經(jīng)過幾輪的測試發(fā)現(xiàn)，都存在些缺陷，效率始終不是很滿意。這次把索引和統(tǒng)計信息拆出來后發(fā)現(xiàn)效率相對算是最高的。
在線重定義的目標(biāo)，是對在線業(yè)務(wù)影響最小，通過最短的鎖表時間來實(shí)現(xiàn)表結(jié)構(gòu)的變更，鎖表只發(fā)生在finish_redef_table過程中，正式切換前先執(zhí)行sync_interim_table過程異步同步數(shù)據(jù)，以盡可能減少業(yè)務(wù)影響。
由于是要對客戶的核心業(yè)務(wù)變更，按管理要求沒辦法提前執(zhí)行finish_redef_table前的過程，且維護(hù)窗口時間有限，業(yè)務(wù)又不能完全停掉，才有了這次的測試。
主要測試常見的2種場景，如下:
場景1：
復(fù)制全部依賴 - COPY_TABLE_DEPENDENTS（索引 + 約束 + 統(tǒng)計信息），觸發(fā)器和權(quán)限這種基本沒有，就沒有復(fù)制。
優(yōu)點(diǎn)：操作方便，腳本直接把原表所有依賴全部復(fù)制過去，改后的表直接使用，不需要額外處理，適合百萬或千萬的表，且對效率沒要求可用。
缺點(diǎn)：上億的表測試發(fā)現(xiàn)效率非常低。
場景2：
有主鍵的表只復(fù)制約束 - COPY_TABLE_DEPENDENTS（會復(fù)制主鍵和唯一索引），其它索引和統(tǒng)計信息等重定義完成后再開并行重建和收集，這里要補(bǔ)充說明一下為什么要復(fù)制約束，因?yàn)閯?chuàng)建主鍵不能并行操作，等重定義完成數(shù)據(jù)轉(zhuǎn)換后，相當(dāng)于在普通大表上創(chuàng)建主鍵，效率非常低。
優(yōu)點(diǎn)：目前針對10億以上的表測試發(fā)現(xiàn)效率是最高的，14億的表全部弄完約2小時左右。
缺點(diǎn)：操作過程稍微麻煩一點(diǎn)，別的還好。
復(fù)制規(guī)則，如下：
copy_indexes => 0, copy_triggers => FALSE, copy_constraints => TRUE, copy_privileges => FALSE, ignore_errors => FALSE, num_errors => num_errors, copy_statistics => FALSE);
硬軟配置一般，如下：
CPU：Intel? Xeon? CPU E7-4820 v3 @ 1.90GHz（物理4個，10核心，80個邏輯cpu）
內(nèi)存：500 GB
存儲：華為某型號
數(shù)據(jù)庫軟件：Oracle 12.2 Nocdb RAC，未打補(bǔ)丁。
我們先看一下原表數(shù)據(jù)行數(shù),接近14億條，人工造的，表實(shí)際大小和生產(chǎn)相差1倍以上。
SQL> select /*+ parallel(40) */ count(*) from OM_OFFERING_INST_TEST; COUNT(*)----------1399999996 Elapsed: 00:00:17.39
創(chuàng)建臨時表，有35個分區(qū)，部份省略了，主鍵、索引等都不要建。
CREATE TABLE "CUSTINFO"."INT_OM_OFFERING_INST_TEST" ( "BUSINESS_SEQ" VARCHAR2(20), "PROD_ID" NUMBER(20, 0), "OFFERING_INST_ID" NUMBER(20, 0), "OFFERING_ID" NUMBER(20, 0), "OFFERING_NAME" VARCHAR2(256), "OFFERING_CODE" VARCHAR2(50), "CUST_TYPE" VARCHAR2(20), "CUST_ID" NUMBER(20, 0), "BRAND" VARCHAR2(50), ...... "RECORD_STATUS" NUMBER(3, 0) DEFAULT 1) PARTITION BY LIST ( "BE_ID" ) ( PARTITION "P_000" VALUES ( '000' ), PARTITION "P_001" VALUES ( '001' ), PARTITION "P_002" VALUES ( '002' ) , PARTITION "P_100" VALUES ( '100' ) , PARTITION "P_200" VALUES ( '200' ) , ..........
定義參數(shù)，設(shè)置并行和行遷移
define USERNAME = 'CUSTINFO'; --用戶名define SOURCE_TAB = 'OM_OFFERING_INST_TEST';-- 原表名define INT_TAB = 'INT_OM_OFFERING_INST_TEST';-- 臨時表名，需要手工提前創(chuàng)建define PARALLELS = 35; --并行數(shù)，這里設(shè)的分區(qū)數(shù)alter session enable parallel dml ;alter session force parallel dml parallel &PARALLELS;alter session force parallel query parallel &PARALLELS;alter table &INT_TAB enable row movement; --臨時表開啟行遷移
檢查原表是否支持在線重定義，比較快，僅用了1秒不到。
SQL> begin 2 dbms_redefinition.can_redef_table(uname => '&USERNAME', 3 tname => '&SOURCE_TAB', 4 options_flag => DBMS_REDEFINITION.CONS_USE_PK); 5 end; 6 / PL/SQL procedure successfully completed Executed in 0.027 seconds
映射字段類型，啟動重定義進(jìn)程，用了近10分鐘，稍微有點(diǎn)慢。從這里開始到結(jié)束， 如果中途有錯誤，想要重來，需要調(diào)abort_redef_table過程取消任務(wù)。
SQL> set timing on; SQL> begin 2 DBMS_REDEFINITION.START_REDEF_TABLE(uname => '&USERNAME', 3 orig_table => '&SOURCE_TAB', 4 int_table => '&INT_TAB', 5 col_mapping => 'to_number(owner_party_role_id) owner_party_role_id, 7 to_number(offering_inst_id) offering_inst_id, 8 to_number(subs_id) subs_id, 9 to_number(group_id) group_id, 10 to_number(apply_obj_id) apply_obj_id', --這里只列舉了需要變更的字段類型 11 options_flag => DBMS_REDEFINITION.CONS_USE_PK); 12 end; 13 / PL/SQL procedure successfully completed Executed in 576.565 seconds
復(fù)制依賴對象，這里只復(fù)制了主鍵約束，耗時54分鐘，如果全部復(fù)制，我在測試跑了3個小時沒有結(jié)果，只接Kill了。
SQL> DECLARE 2 num_errors PLS_INTEGER; 3 BEGIN 4 DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname => '&USERNAME', 5 orig_table => '&SOURCE_TAB', 6 int_table => '&INT_TAB', 7 copy_indexes => 0, 8 copy_triggers => FALSE, 9 copy_constraints => TRUE, 10 copy_privileges => FALSE, 11 ignore_errors => FALSE, 12 num_errors => num_errors, 13 copy_statistics => FALSE); 14 END; 15 / PL/SQL procedure successfully completed Executed in 3230.441 seconds
異步同步數(shù)據(jù)，耗時28秒，比較快。
SQL> begin 2 dbms_redefinition.sync_interim_table(uname => '&USERNAME', 3 orig_table => '&SOURCE_TAB', 4 int_table => '&INT_TAB'); 5 end; 6 / PL/SQL procedure successfully completed Executed in 27.908 seconds
完成在線重定義，結(jié)束任務(wù),耗時73秒，也是比較快。
SQL> begin 2 dbms_redefinition.finish_redef_table(uname => '&USERNAME', 3 orig_table => '&SOURCE_TAB', 4 int_table => '&INT_TAB'); 5 end; 6 / PL/SQL procedure successfully completed Executed in 72.302 seconds
創(chuàng)建索引，這個分區(qū)表上的索引不多，就3個普通索引，開53個并行，平均每個耗時4分鐘左右，累計13分鐘。
重慶分公司
重慶分公司
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_CUSTID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("CUST_ID") online parallel 35;
Index created
Executed in 257.138 seconds
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_GROUPID" ON "CUSTINFO"."OM_OFFERING_INST_TEST" ("GROUP_ID") online parallel 35;
Index created
Executed in 244.853 seconds
SQL> CREATE INDEX "CUSTINFO"."INX_OM_OFFERING_INST_TEST_SUBSID" ON"CUSTINFO"."OM_OFFERING_INST_TEST" ("SUBS_ID") online parallel 35;
Index created
Executed in 261.665 seconds收集統(tǒng)計信息,同樣也是開35個并行，耗時4分鐘左右。CASCADE => true表示收集表、列、索引等。
SQL> exec dbms_stats.gather_table_stats(ownname => 'CUSTINFO',tabname => 'OM_OFFERING_INST_TEST',CASCADE => true,degree => 35); PL/SQL procedure successfully completed. Elapsed: 00:04:18.35
取消表、索引上的并行度，檢查字段是否修改成功，刪除臨時表，至此整個修改過程結(jié)束，這里耗時約10分鐘左右。
--取消表上的并行alter table &SOURCE_TAB noparallel;--取消索引上的并行alter index INX_OM_OFFERING_INST_TEST_CUSTID noparallel;alter index INX_OM_OFFERING_INST_TEST_GROUPID noparallel;alter index INX_OM_OFFERING_INST_TEST_SUBSID noparallel; --刪除臨時表drop table &INT_TAB;
到此，關(guān)于“分析Oracle能否在2小時內(nèi)在線完成表結(jié)構(gòu)字段類型變更”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！
