उत्पादन में Oracle टेबल को DBMS_REDEFINITION के साथ सहजता से पुनर्परिभाषित करें

ऑनलाइन टेबल पुनर्परिभाषा आपको अपने Oracle® टेबल को उत्पादन में पुनर्गठित करने की अनुमति देती है बिना डेटा को अनुपलब्ध किए। आप डेटा को इधर-उधर ले जाने के लिए टेम्प टेबल का उपयोग करने में सहज हो सकते हैं, लेकिन एक बेहतर समाधान है।

परिचय

डेटा को स्टेज करना और अपने टेबल को पुनर्गठित करते समय इसे इधर-उधर ले जाना टेबल और डेटा दोनों को एक निश्चित अवधि के लिए अनुपलब्ध कर देता है, जो व्यवसायों के लिए कम अनुकूल स्थिति है। यह तब होता है जब DBMS_REDEFINITION पैकेज स्थिति को बचाता है, जैसा कि निम्नलिखित छवि में दिखाया गया है:

उद्देश्य

आवधिक रूप से, आपको निम्नलिखित कारणों से Oracle टेबल की तार्किक या भौतिक संरचना को संशोधित करने की आवश्यकता होती है:

• क्वेरी या डेटा मैनिपुलेशन लैंग्वेज (DML) प्रदर्शन को बढ़ाने के लिए
• एप्लिकेशन परिवर्तनों को समायोजित करने के लिए
• स्टोरेज प्रबंधित करने के लिए

Oracle Database टेबल संरचना संशोधन करने के लिए एक तंत्र प्रदान करता है बिना टेबल की उपलब्धता को महत्वपूर्ण रूप से प्रभावित किए, जिसे ऑनलाइन टेबल पुनर्परिभाषा के रूप में जाना जाता है। ऑनलाइन टेबल पुनर्परिभाषा पारंपरिक टेबल पुनर्परिभाषा विधियों की तुलना में पर्याप्त प्रदर्शन वृद्धि प्रदान करती है।

जब एक टेबल को ऑनलाइन पुनर्परिभाषित किया जाता है, तो यह पुनर्परिभाषा प्रक्रिया के अधिकांश भाग के दौरान क्वेरी और DML दोनों के लिए सुलभ होता है। टेबल केवल थोड़े समय के लिए विशेष मोड में लॉक होता है, जो टेबल के आकार और पुनर्परिभाषा की जटिलता से स्वतंत्र होता है। पुनर्परिभाषा प्रक्रिया उपयोगकर्ताओं के लिए पूरी तरह से पारदर्शी होती है।

ऑनलाइन टेबल पुनर्परिभाषा के लिए मुक्त स्थान की आवश्यकता होती है जो पुनर्परिभाषित की जा रही टेबल द्वारा वर्तमान में उपयोग किए जा रहे स्थान के लगभग बराबर हो।

टेबल को पुनर्गठित करने के कई तरीके हैं। जब डाउनटाइम एक चुनौती है, तो इस कार्य के लिए DBMS_REDEFINITION पैकेज सबसे अच्छा विकल्प है।

ऑनलाइन टेबल पुनर्परिभाषित करें

ऑनलाइन टेबल पुनर्परिभाषित करने के लिए निम्नलिखित चरणों का उपयोग करें।

1. पुनर्परिभाषा विधि चुनें, या तो कुंजी द्वारा या rowids द्वारा।

   कुंजी द्वारा: पुनर्परिभाषा के लिए उपयोग करने के लिए एक प्राथमिक कुंजी या छद्म-प्राथमिक कुंजी चुनें। छद्म-प्राथमिक कुंजियाँ अद्वितीय कुंजियाँ हैं जहां सभी घटक कॉलम में NOT NULL प्रतिबंध होते हैं। इस विधि के लिए, पुनर्परिभाषा से पहले और बाद में तालिकाओं के संस्करणों में समान प्राथमिक कुंजी कॉलम होने चाहिए। यह पुनर्परिभाषा की पसंदीदा और डिफ़ॉल्ट विधि है।

   rowid द्वारा: यदि कोई कुंजी उपलब्ध नहीं है तो इस विधि का उपयोग करें। इस विधि में, M_ROW$$ नामक एक छिपा हुआ कॉलम, तालिका के पुनर्परिभाषित संस्करण में जोड़ा जाता है। पुनर्परिभाषा पूरी होने के बाद इस कॉलम को ड्रॉप या अनयूज्ड के रूप में चिह्नित किया जाना चाहिए। यदि COMPATIBLE 10.2.0 या उच्च पर सेट है, तो पुनर्परिभाषा पूरी होने पर यह छिपा हुआ कॉलम स्वचालित रूप से UNUSED पर सेट हो जाता है। आप तब ALTER TABLE ... DROP UNUSED COLUMNS स्टेटमेंट का उपयोग करके इसे ड्रॉप कर सकते हैं। आप इस विधि का उपयोग इंडेक्स-संगठित तालिकाओं पर नहीं कर सकते।

2. CAN_REDEF_TABLE प्रक्रिया को आमंत्रित करके सत्यापित करें कि टेबल को ऑनलाइन पुनर्परिभाषित किया जा सकता है। यदि टेबल ऑनलाइन पुनर्परिभाषा के लिए उम्मीदवार नहीं है, तो यह प्रक्रिया एक त्रुटि उठाती है जो दर्शाती है कि टेबल को ऑनलाइन पुनर्परिभाषित क्यों नहीं किया जा सकता।

3. एक खाली अंतरिम टेबल बनाएं (उसी स्कीमा में जिसमें पुनर्परिभाषित की जाने वाली टेबल है) सभी वांछित तार्किक और भौतिक विशेषताओं के साथ।

4. अंतरिम टेबल को पुनर्परिभाषित की जा रही टेबल के सभी इंडेक्स, प्रतिबंध, अनुदान, और ट्रिगर के साथ बनाना आवश्यक नहीं है। यह COPY_TABLE_DEPENDENTS प्रक्रिया का उपयोग करके स्वचालित रूप से किया जा सकता है।

5. बड़ी टेबल में प्रदर्शन में सुधार के लिए, आप इसे समानांतर में सेट कर सकते हैं निम्नलिखित कमांड का उपयोग करके:

    ALTER SESSION force parallel dml parallel degree-of-parallelism; ALTER SESSION force parallel query parallel degree-of-parallelism; 

6. FINISH_REDEF_TABLE कमांड टेबल की पुनर्परिभाषा को पूरा करता है। इस प्रक्रिया के दौरान, मूल टेबल को थोड़े समय के लिए विशेष मोड में लॉक किया जाता है, जो मूल टेबल में डेटा की मात्रा से स्वतंत्र होता है। हालांकि, FINISH_REDEF_TABLE पुनर्परिभाषा पूरी करने से पहले सभी लंबित DML ऑपरेशन के प्रतिबद्ध होने की प्रतीक्षा करता है।

7. यदि आपने पुनर्परिभाषा के लिए rowids का उपयोग किया है और आपका COMPATIBLE इनिशियलाइज़ेशन पैरामीटर 10.1.0 या उससे कम पर सेट है, तो आपको पुनर्परिभाषित टेबल में जोड़े गए छिपे हुए कॉलम M_ROW$$ को ड्रॉप करना होगा। आप निम्नलिखित कमांड का उपयोग करके कॉलम को "UNUSED" पर भी सेट कर सकते हैं:

    ALTER TABLE <table_name> SET UNUSED (M_ROW$$); 

   यदि COMPATIBLE 10.2.0 या उच्च है, तो यह छिपा हुआ कॉलम पुनर्परिभाषा पूरी होने पर स्वचालित रूप से UNUSED पर सेट हो जाता है। आप तब ALTER TABLE ... DROP UNUSED COLUMNS स्टेटमेंट के साथ कॉलम ड्रॉप कर सकते हैं। अंतरिम टेबल के विरुद्ध लंबे समय तक चलने वाली क्वेरी के पूरा होने की प्रतीक्षा करें और फिर अंतरिम टेबल को ड्रॉप करें।

नमूना टेबल पुनर्परिभाषा

निम्नलिखित अनुभाग नमूना टेबल पुनर्परिभाषा के लिए विभिन्न कमांड और आउटपुट के उदाहरण दिखाता है।

sqlplus प्रारंभ करें

निम्नलिखित नमूना sqlplus शुरू करने को दर्शाता है:

[oracle@vm215 ~]$ sqlplus amit/amit SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 29 05:44:44 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 

एक डेमो टेबल बनाएं

निम्नलिखित नमूना AMIT स्कीमा के तहत TEST1 नामक एक डेमो टेबल बनाने को दर्शाता है।

SQL> CREATE TABLE TEST1 ( ID NUMBER(10) , ENAME VARCHAR2(10), SAL NUMBER(10) ) ; 

बल्क पंक्तियाँ डालें

निम्नलिखित नमूना बल्क पंक्तियाँ डालने और AMIT स्कीमा में PPA_AGGGREGATE_TARGET को अधिकतम मान पर सेट करने को दर्शाता है।

SQL> INSERT INTO AMIT.TEST1 SELECT ROWNUM, 'T'|| ROWNUM, DBMS_RANDOM.VALUE(100000, 999999) FROM DUAL CONNECT BY LEVEL < 1000000; 999999 ROWS CREATED. SQL> COMMIT; COMMIT COMPLETE. 

परीक्षण के लिए आश्रित ऑब्जेक्ट बनाएं

निम्नलिखित नमूना टेबल TEST1 पंक्तियों से संबंधित आश्रित ऑब्जेक्ट बनाने को दर्शाता है, ताकि आप देख सकें कि ऑनलाइन पुनर्परिभाषा के दौरान क्या होता है।

व्यू निर्माण

SQL> CREATE VIEW TEST1_VW AS SELECT * FROM TEST1 ; VIEW CREATED. 

सीक्वेंस निर्माण

SQL> CREATE SEQUENCE TEST_SEQ ; SEQUENCE CREATED. 

प्रक्रिया निर्माण

CREATE OR REPLACE PROCEDURE PROC1 (P_ID IN NUMBER) AS V_ID NUMBER ; BEGIN SELECT SAL INTO V_ID FROM TEST1 WHERE ID = P_ID; END; / PROCEDURE CREATED. 

DML ट्रिगर निर्माण

SQL> CREATE OR REPLACE TRIGGER AMIT_TRIG BEFORE INSERT OR UPDATE ON TEST1 FOR EACH ROW DECLARE X NUMBER; BEGIN SELECT COUNT(*) INTO X FROM TEST1 WHERE ID = :NEW.ID; IF X > 0 THEN RAISE_APPLICATION_ERROR(-20501, 'ID' || :NEW.ID || ' ALREADY EXISTS'); END IF; END; / TRIGGER CREATED. 

प्राथमिक कुंजी निर्माण

SQL> ALTER TABLE TEST1 ADD CONSTRAINT TEST1_ID_PK PRIMARY KEY (ID) ; TABLE ALTERED. 

पुनर्परिभाषा से पहले स्थिति प्राप्त करें

SQL> COLUMN OBJECT_NAME FORMAT A20 SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM USER_OBJECTS ORDER BY OBJECT_NAME;SQL> OBJECT_NAME OBJECT_TYPE STATUS -------------------- ------------------- ------- AMIT_TRIG TRIGGER VALID PROC1 PROCEDURE VALID TEST1 TABLE VALID TEST1_ID_PK INDEX VALID TEST1_VW VIEW VALID TEST_SEQ SEQUENCE VALID 6 ROWS SELECTED. 

पुनर्परिभाषा के लिए टेबल जांचें

निम्नलिखित नमूना दर्शाता है कि टेबल को या तो rowids या प्राथमिक कुंजी का उपयोग करके ऑनलाइन पुनर्परिभाषित किया जा सकता है:

प्राथमिक कुंजी का उपयोग करके

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_PK); PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. 

rowid का उपयोग करके

SQL> EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE ('AMIT','TEST1',DBMS_REDEFINITION.CONS_USE_ROWID); PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. 

अंतरिम टेबल की प्रतिकृति बनाएं

निम्नलिखित नमूना किसी भी आश्रित ऑब्जेक्ट के बिना नई अंतरिम टेबल की प्रतिकृति बनाने को दर्शाता है:

SQL> CREATE TABLE TEST1_REORG AS SELECT * FROM TEST1 WHERE ROWNUM=5 ; TABLE CREATED. SQL> SELECT COUNT(*) FROM TEST1_REORG ; COUNT(*) ---------- 0 SQL> SELECT COUNT(*) FROM TEST1; COUNT(*) ---------- 999999 

डेटाबेस से कनेक्ट करें

निम्नलिखित नमूना टेबल पुनर्परिभाषा कार्य को निष्पादित करने के लिए विशेषाधिकार प्राप्त उपयोगकर्ता का उपयोग करके कनेक्ट करने को दर्शाता है:

[oracle@vm215 ~]$ sqlplus / as sysdba Sql*plus: release 11.2.0.3.0 production on sat oct 29 05:16:48 2016 Copyright (c) 1982, 2011, oracle. All rights reserved. Connected to: Oracle database 11g enterprise edition release 11.2.0.3.0 - 64bit production With the partitioning, olap, data mining and real application testing options 

पुनर्परिभाषा प्रारंभ करें

निम्नलिखित नमूना प्राथमिक कुंजी का उपयोग करके पुनर्परिभाषा शुरू करने को दर्शाता है:

SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('AMIT','TEST1', 'TEST1_REORG'); PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. 

आश्रित ऑब्जेक्ट कॉपी करें

निम्नलिखित नमूना स्वचालित रूप से mview, प्राथमिक कुंजी, व्यू, सीक्वेंस, और ट्रिगर जैसे आश्रित ऑब्जेक्ट्स को कॉपी करने को दर्शाता है। COPY_TABLE_DEPENDENTS कमांड के साथ प्राथमिक कुंजी उल्लंघन से बचने के लिए IGNORE_ERROR को TRUE पर सेट किया गया है।

SQL> DECLARE N PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('AMIT', 'TEST1','TEST1_REORG', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, N); END; / PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. 

त्रुटियों की जांच करें

निम्नलिखित नमूना DBA_REDEFINITION_ERRORS व्यू में त्रुटियों की जांच करने को दर्शाता है:

SQL> COL OBJECT_NAME FOR A25 SET LIN200 PAGES 200 COL DDL_TEXT FOR A60 SELECT OBJECT_NAME, BASE_TABLE_NAME, DDL_TXT FROM DBA_REDEFINITION_ERRORS; NO ROWS SELECTED 

दोनों टेबल मान्य करें

निम्नलिखित नमूना दोनों टेबल पर पंक्ति गिनती मान्य करने और अंतरिम टेबल के साथ सिंक करने को दर्शाता है:

SQL> SELECT COUNT(*) FROM AMIT.TEST1_REORG ; COUNT(*) ---------- 999999 SQL> SELECT COUNT(*) FROM AMIT.TEST1 ; COUNT(*) ---------- 999999 SQL> EXEC DBMS_REDEFINITION.SYNC_INTERIM_TABLE('AMIT', 'TEST1', 'TEST1_REORG'); PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. 

पुनर्परिभाषा समाप्त करें

निम्नलिखित नमूना पुनर्परिभाषा समाप्त करने को दर्शाता है:

SQL> EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE ('AMIT', 'TEST1', 'TEST1_REORG'); PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. SQL> COLUMN OBJECT_NAME FORMAT A40 SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT'; OBJECT_NAME OBJECT_TYPE STATUS --------------------- ------------------- ------- TEST1_VW VIEW INVALID TEST_SEQ SEQUENCE VALID PROC1 PROCEDURE VALID TEST1 TABLE VALID TEST1_REORG TABLE VALID TEST1_ID_PK INDEX VALID TMP$$_TEST1_ID_PK0 INDEX VALID TMP$$_AMIT_TRIG0 TRIGGER INVALID AMIT_TRIG TRIGGER INVALID 9 ROWS SELECTED. 

त्रुटि जांचें और स्कीमा रीकंपाइल करें

निम्नलिखित नमूना पूर्ण निर्भरता के साथ स्कीमा को रीकंपाइल करने को दर्शाता है, जो पिछले चरण में अमान्य ट्रिगर के कारण आवश्यक है:

SQL> EXEC UTL_RECOMP.RECOMP_SERIAL('AMIT') ; PL/SQL PROCEDURE SUCCESSFULLY COMPLETED. SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='AMIT'; OBJECT_NAME OBJECT_TYPE STATUS ---------------------------------------- ------------------- ------- TEST1_VW VIEW VALID TEST_SEQ SEQUENCE VALID PROC1 PROCEDURE VALID TEST1 TABLE VALID TEST1_REORG TABLE VALID TEST1_ID_PK INDEX VALID TMP$$_TEST1_ID_PK0 INDEX VALID TMP$$_AMIT_TRIG0 TRIGGER VALID AMIT_TRIG TRIGGER VALID 9 ROWS SELECTED. 

अंतरिम टेबल ड्रॉप करें

निम्नलिखित नमूना अंतरिम टेबल ड्रॉप करने को दर्शाता है:

SQL> DROP TABLE AMIT.TEST1_REORG; TABLE DROPPED. 

निष्कर्ष

यदि टेबल संरचना को संशोधित करने की आवश्यकता है और साथ ही अंतिम उपयोगकर्ताओं द्वारा एक्सेस किया जाता है, तो DBMS_REFDEFINITION का उपयोग करें।

यह सुविधा डेटा को बिना किसी डाउनटाइम के पुनर्गठित करने में मदद करती है, इस प्रकार ऑनलाइन ट्रांजैक्शन प्रोसेसिंग (OLTP) वातावरण में ग्राहकों के लिए डाउनटाइम के कारण होने वाली चुनौतियों से बचाती है।

टिप्पणी करने या प्रश्न पूछने के लिए फीडबैक टैब का उपयोग करें।