🧙🏼 🔷 👸🏻 विभाजन। स्वचालित रूप से अनुभाग जोड़ें 👍🏼 🧜🏿 👨🏾‍🌾

संस्करण 11 जी में, ओरेकल ने कई रोमांचक नई विभाजन योजनाओं को पेश किया - उदाहरण के लिए, अंतराल विभाजन का सुविधाजनक कार्य - विभाजन को सीमा से बाहर जाने के लिए स्वचालित रूप से विभाजन बनाने के लिए।
11g से पहले के संस्करणों में, आपको समय-समय पर मैन्युअल रूप से या तो अनुभागों को अग्रिम में जोड़ना होगा या डिफ़ॉल्ट अनुभाग को विभाजित करना होगा। यही है, ऐसी तालिकाओं की स्थिति की निगरानी करना लगातार आवश्यक है। इस लेख में, मैं इस तरह के विभाजन कार्यों को स्वचालित करने के लिए अपने समाधान साझा करूंगा।
पहले मैं 11 जी के लिए एक उदाहरण देता हूं:

create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );
create table res ( res_id number not null , res_date date , hotel_id number(3), guest_id number ) partition by range (res_id) interval (100) store in (users) ( partition p1 values less than (101) );

यह स्क्रिप्ट रिकॉर्ड के लिए एक p1 सेक्शन बनाता है जिसका res_id कॉलम मान 1-100 की सीमा में है। जब 101 से कम res_id स्तंभ मान वाले रिकॉर्ड्स सम्मिलित किए जाते हैं, तो उन्हें p1 अनुभाग में रखा जाता है, और जब नए रिकॉर्ड में इस स्तंभ का मान 101 से अधिक या बराबर होता है, तो Oracle डेटाबेस 11g एक नया अनुभाग बनाता है, जिसका नाम सिस्टम द्वारा उत्पन्न होता है। आप इस उदाहरण और अन्य नई विभाजन योजनाओं के बारे में अधिक जान सकते हैं जो अरुप नंदा द्वारा ओरेकल पत्रिका के रूसी संस्करण में एक लेख के अनुवाद में हैं ।
नीचे वर्णित समाधानों को अन्य DBMS पर लागू किया जा सकता है जो विभाजन के स्वचालित जोड़ने का समर्थन नहीं करते हैं।

अंतराल के बिना एक समान रूप से बढ़ती विभाजन कुंजी के लिए समाधान

एक परीक्षण तालिका बनाएँ:

टेबल टेस्ट_पार्ट बनाएं (
आईडी संख्या शून्य नहीं है ,
नाम varchar2 (100) शून्य नहीं ,
मालिक varchar2 (100) अशक्त नहीं ,
प्रकार varchar2 (100) शून्य नहीं ,
बनाई गई तारीख शून्य नहीं है ,
constraint test_part_pk
प्राथमिक कुंजी (आईडी)
)
विभाजन (आईडी) द्वारा विभाजन (विभाजन p1 मान (10000) से कम );

यह तर्कसंगत है कि यदि ऐसी तालिका में कोई अंतराल नहीं है, तो विभाजन खंड के अधिकतम खंड की सीमा से पहले नए खंड बनाने के लिए यह वांछनीय होगा। सीमा पर हमारे पास कितने प्रमुख मूल्य हैं, हम एक सरल सूत्र के अनुसार आसानी से निर्धारित कर सकते हैं: विभाजन_साइज - (की-स्टार्ट_की_इन_पार्टिशन) , जहां कुंजी वर्तमान विभाजन कुंजी है, start_key_in_partition पहली कुंजी है जो इस खंड में आती है, विभाजन_साइज अनुभाग में कुंजियों की संख्या है। और% पूर्णांक विभाजन (div) का संचालन है। आमतौर पर, इस तरह के विभाजन को समान वर्गों में किया जाता है, और इसे ध्यान में रखते हुए, हम इस सूत्र को निम्न में सरल कर सकते हैं: पार्टीशन_साइज़ - कुंजी% पार्टीशन_साइज़।
यह हमें क्या देता है: पल को जानना, हम एक ट्रिगर बना सकते हैं जो इस घटना के होने पर वर्गों को जोड़ देगा।
यह ट्रिगर बनाएं:

tr_test_part ट्रिगर बनाएं या बदलें
test_part पर डालने से पहले
प्रत्येक पंक्ति के लिए
जब (mod ( NEW .id, 10000) = 6000)
घोषित
l_part_name नंबर;
l_maxvalue संख्या;
l_exist संख्या;
l_partition_exists अपवाद ;
PRAGMA EXCEPTION_INIT (l_partition_exists, -14074);
PRAGMA AUTONOMOUS_TRANSACTION;
शुरू करना
l_part_name: = ceil (: NEW .ID / 10000) +1;
शुरू
तत्काल 'अदला-बदली तालिका xtender.test_part add विभाजन p' || l_part_name || 'मान से कम (' || l_maxvalue || ')' ?
अपवाद
जब l_partition_exists तब शून्य ;
अंत;
अंत tr_test_part;

स्वायत्त लेनदेन का उपयोग करते हुए यह ट्रिगर, स्वचालित रूप से 'P' + 10,000 की एक संख्या के नाम के साथ एक नया खंड बनाता है, जब ID हमारी विभाजन कुंजी है, अनुभाग सीमा (10000-4000 = 6000, अर्थात आईडी = 6000, तक 4,000 मान शेष हैं) 16000,26000, आदि), लेकिन पहले यह जाँच की जाती है कि क्या दिया गया खंड पहले से मौजूद है (यह हो सकता है, उदाहरण के लिए, 6000 वां रिकॉर्ड फिर से जोड़कर, या मैन्युअल रूप से अनुभाग जोड़कर)। विभाजन के पैरामीटर - 10000 और 4000, आपको अपनी विशिष्ट स्थिति के आधार पर चुनना चाहिए, लेकिन ध्यान रखें कि सीमा (उदाहरण में 4000) एक बार में जोड़े गए रिकॉर्ड की अधिकतम संख्या से अधिक होनी चाहिए, क्योंकि अन्यथा, डेटा प्रविष्टि लेन-देन के समय, लेनदेन नए अनुभाग के बारे में "पता" नहीं करेगा, क्योंकि लेन-देन की शुरुआत में, यह मौजूद नहीं था, इसलिए इस कुंजी को अनुभाग मैपिंग की अनुपस्थिति के बारे में शिकायत के साथ डेटा नहीं डाला जाएगा। यह परिवर्तन तालिका विभाजन default_partition के उपयोग से बचा जा सकता था, जिसकी मैं बाद में चर्चा करूंगा, लेकिन यह रनटाइम को प्रभावित करेगा।
अनुभाग भरकर हमारे ट्रिगर की जाँच करें:

insert into xtender.test_part select rownum, o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE, o.CREATED from all_objects o where rownum<1000;

इसके अलावा, अनुक्रम के उपयोग के मामले में कि "कदम" कैशिंग के कारण, क्रमिक रूप से ट्रिगर को बदलना संभव नहीं है, ताकि यह अनुभाग के अंत से 4000 से 3900 रिकॉर्ड तक मानों के एक सेट के लिए चलता है:
स्थिति को बदलें

  जब (mod (NEW.id, 10000) = 6000)

पर

  जब (mod (NEW.id, 10000) 6000 और 6100 के बीच)

अन्य समाधान

उन मामलों में जहां हम डिफ़ॉल्ट अनुभाग को निर्दिष्ट करते हैं, हम इसे अलग कर सकते हैं जब रिकॉर्ड पहले ही मिल चुके हैं, तो सवाल यह है कि यह स्वचालित रूप से कैसे ट्रैक करें

डेटा डिक्शनरी में हम dba_tab_partitions से चयन करके पार्टीशन टेबल के सभी सेक्शन के बारे में जानकारी प्राप्त कर सकते हैं, जिसमें पार्टीशन टेबल में सेक्शन के क्रम को इंगित करता है, और high_value सेक्शन पैरामीटर को इंगित करता है। इसलिए, हम तालिका में अंतिम अनुभाग का नाम प्राप्त कर सकते हैं और उसमें से रिकॉर्ड प्राप्त करने के लिए उसमें से एक चयन कर सकते हैं।

उन तालिका के बारे में जानकारी प्राप्त करने के बाद, जिनमें डिफ़ॉल्ट अनुभागों में रिकॉर्डिंग शुरू हुई, हमें एक सूचना भेजनी होगी। ऐसा करने के लिए, हम विकल्पों का उपयोग कर सकते हैं:

यदि आपने ई-मेल पर स्वचालित रूप से अलर्ट भेजने के लिए कॉन्फ़िगर किया है, तो अलर्ट में केवल घटना लिखें।
अधिसूचना पत्र भेजने के लिए सिर्फ एक प्रक्रिया लिखें।

पहला विकल्प dbms_system.ksdwrt प्रक्रिया का उपयोग करके लागू किया गया है, जिसमें दो पैरामीटर हैं:

पहला (BINARY_INTEGER) - जहां संभव मानों के साथ लिखने के लिए: 1 - मानक ट्रेस फ़ाइल के लिए, 2 - to alert.log, 3 - दोनों;
और दूसरा (varchar2) - वास्तव में स्ट्रिंग ही है, जिसे हम लिखते हैं।

एक उदाहरण:

 exec dbms_system.ksdwrt (2, 'टेस्ट अलर्ट संदेश');

दूसरा विकल्प utl_mail पैकेज या निचले वाले का उपयोग करना है - utl_smtp या utl_tcp ।
utl_mail utl_smtp के लिए अधिक सुविधाजनक आवरण है, लेकिन इसका उपयोग करने के लिए, आपको smtp_out_server पैरामीटर सेट करना होगा। आप इसे सत्र के लिए जैसे ही कर सकते हैं - “ALTER SESSION SET smtp_out_server =…” और सिस्टम के लिए “ALTER SYSTEM SET smtp_out_server =…” ।
अगर आपको यह पैकेज घर पर नहीं मिल रहा है तो आश्चर्यचकित न हों - शुरू में इसमें शामिल नहीं है और इसे बनाने के लिए आपको दो स्क्रिप्ट्स चलाने होंगे:

 sqlplus sys/<pwd> SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

यह सब एक साथ pkg_partitions पैकेज में लाना
पैकेज के तरीके:

फ़ंक्शन get_penultimate_maxvalue (p_table_owner varchar2, p_table_name varchar2) वापसी varchar2;
फ़ंक्शन तालिका के नाम को स्वामी पैरामीटर के रूप में स्वीकार करता है और दंडात्मक खंड की स्थिति मान (उच्च_गुण) लौटाता है। यह जानकारी आवश्यक हो सकती है, उदाहरण के लिए, उन मामलों में जहां अंतिम अनुभाग एक मैक्सवेल्यू पैरामीटर वाला एक खंड है, और तदनुसार, नए अनुभाग के पैरामीटर को निर्धारित करने के लिए पारगम्य अनुभाग पैरामीटर की आवश्यकता हो सकती है।
फ़ंक्शन get_maxvalued_partitions वापसी टेबल_प्रॉप्स_अरे पाइपलाइनबद्ध;
फ़ंक्शन तालिकाओं और उनके मालिकों के नाम लौटाता है, जिनके अंतिम अनुभाग को भरना शुरू हुआ।
उपयोग उदाहरण:
1. चुनना
2. पी। *,
3. sys.pkg_partitions.get_penultimate_maxvalue (p.table_owner, p.table_name) pre_maxvalue
4. से
5. तालिका (sys.pkg_partitions.get_maxvalued_partitions) पी
फ़ंक्शन get_maxvalued_partitions_html वापसी varchar2 ;
यह फ़ंक्शन get_maxvalued_partitions के समान ही देता है, लेकिन HTML तालिका के रूप में
प्रक्रिया send_partitions_report (मेल varchar2 );
तालिकाओं के साथ एक रिपोर्ट भेजने की प्रक्रिया जिसके लिए अंतिम अनुभाग भरना शुरू हुआ। एकमात्र पैरामीटर पता है कि किसे भेजना है।

पैकेज कोड:

पैकेज बॉडी pkg_partitions बनाएं या बदलें
/ ** समारोह अनुभाग के लिए एक पैरामीटर लौटाता है
* @ अपरम i_table_name तालिका नाम
* @ वापसी varchar2
* /
फ़ंक्शन get_penultimate_maxvalue (p_table_owner varchar2, p_table_name varchar2) रिटर्न varchar2 है
l_cursor पूर्णांक डिफ़ॉल्ट dbms_sql.open_cursor;
l_ignore नंबर;
l_long_val varchar2 (4000);
l_long_len संख्या;
l_buflen संख्या: = 4000;
l_curpos संख्या: = 0;
शुरू करना
dbms_sql.parse (l_cursor,
' all_tab_partitions p से p.high_value का चयन करें जहां p.table_owner जैसे: o और p.table_name जैसे: x और p.partition_position = (select max (p1.partition_position) -1 all_tab_partitions p1 से जहाँ p.table_owner पसंद है: o और p1। table_name जैसे: x) '
।
dbms_sql.native);
dbms_sql.bind_variable (l_cursor, ': x' , p_table_name);
dbms_sql.bind_variable (l_cursor, ': o' , p_table_owner);
dbms_sql.define_column_long (l_cursor, 1);
l_ignore: = dbms_sql निष्पादित (l_cursor);
अगर (dbms_sql.fetch_rows (l_cursor)> 0)
तो
dbms_sql.column_value_long (l_cursor, 1, l_buflen, l_curpos,
l_long_val, l_long_len);
अंत यदि ;
dbms_sql.close_cursor (l_cursor);
वापसी l_long_val;
अंत ;
/ ** फ़ंक्शन तालिकाओं और उनके मालिकों के नाम लौटाते हैं, जिनके अंतिम अनुभाग को भरना शुरू हुआ
* @ वापसी
* table_name varchar2 (4000),
* table_owner varchar2 (4000),
* विभाजन_ संख्या संख्या,
* विभाजन_नाम varchar2 (4000));
* /
फ़ंक्शन get_maxvalued_partitions वापसी टेबल_प्रॉप्स_अरे पाइपलाइनबद्ध है
l_cursor पूर्णांक डिफ़ॉल्ट dbms_sql.open_cursor;
l_count संख्या;
l_ignore पूर्णांक ;
l_data table_props;
कर्सर l_partitions है
चुनना
pl.table_owner,
pl.table_name,
गिनती (1) cnt,
अधिकतम (pl.partition_name) रखना (d.partition_position द्वारा अंतिम क्रम में रखना) (विभाजन)
dba_tab_partitions pl से
जहां pl.table_name 'BIN $%' को पसंद नहीं करता
pl.table_owner, pl.table_name द्वारा समूह
गिनती (1)> 1;
शुरू
L_partitions में भाग के लिए
लूप
dbms_sql.parse (l_cursor,
'सेलेक्ट काउंट (1) ' || part.table_owner || '।' part.table_name
|| 'विभाजन (' || part.partition_name || ')'
|| 'जहां पंक्तिम <2
।
dbms_sql.native);
dbms_sql.define_column (l_cursor, 1, l_count);
l_ignore: = dbms_sql.execute_and_fetch (l_cursor);
dbms_sql.column_value (l_cursor, 1, l_count);
अगर (l_count> 0) तो
l_data.table_name: = part.table_name;
l_data.table_owner: = part.table_owner;
l_data.partitions_count: = part.cnt;
l_data.partition_name: = part.partition_name;
पाइप पंक्ति (l_data);
अंत यदि ;
END LOOP;
अंत;
/ ** फ़ंक्शन जो HTML के रूप में तालिकाओं और उनके मालिकों के नाम लौटाता है, जिसमें अंतिम अनुभाग भरना शुरू हुआ था
* @ वापसी
* table_name varchar2 (4000),
* table_owner varchar2 (4000),
* विभाजन_ संख्या संख्या,
* विभाजन_नाम varchar2 (4000));
* /
फ़ंक्शन get_maxvalued_partitions_html वापसी varchar2 है
l_cursor पूर्णांक डिफ़ॉल्ट dbms_sql.open_cursor;
l_count संख्या;
l_ignore पूर्णांक ;
l_data varchar2 (4000);
कर्सर l_partitions है
चुनना
pl.table_owner,
pl.table_name,
गिनती (1) cnt,
अधिकतम (pl.partition_name) रखना (d.partition_position द्वारा अंतिम क्रम में रखना) (विभाजन)
dba_tab_partitions pl से
जहां pl.table_name 'BIN $%' को पसंद नहीं करता
pl.table_owner, pl.table_name द्वारा समूह
गिनती (1)> 1;
शुरू
l_data: = '<html> <body> <table बॉर्डर = 1>'
|| '<tr> <th> तालिका का नाम </ th>'
|| '<th> टेबल के मालिक </ th>'
|| '<th> विभाजन की गिनती </ th>'
|| '<th> विभाजन नाम </ th>'
|| '<th> प्री मैक्सवेलु </ th>' ;
L_partitions में भाग के लिए
लूप
dbms_sql.parse (l_cursor,
'सेलेक्ट काउंट (1) ' || part.table_owner || '।' part.table_name
|| 'विभाजन (' || part.partition_name || ')'
|| 'जहां पंक्तिम <2
।
dbms_sql.native);
dbms_sql.define_column (l_cursor, 1, l_count);
l_ignore: = dbms_sql.execute_and_fetch (l_cursor);
dbms_sql.column_value (l_cursor, 1, l_count);
अगर (l_count> 0) तो
l_data: = l_data || '<tr> <td>'
part.table_name
|| '</ td> <td>'
|| part.table_owner ||
|| '</ td> <td>'
|| भाग
|| '</ td> <td>'
|| part.partition_name
|| '</ td> </ tr>' ;
अंत यदि ;
END LOOP;
l_data: = l_data || '</ table> </ body> </ html>' ;
वापसी l_data;
अंत;
/ **
* तालिकाओं के साथ एक रिपोर्ट भेजने की प्रक्रिया जिसका अंतिम भाग भरने लगा
* /
प्रक्रिया send_partitions_report (मेल varchar2)
है
msg_body varchar2 (4000);
शुरू
pkg_partitions.get_maxvalued_partitions_html को ड्यूस से msg_body में चुनें ;
- EXECUTE IMMEDIATE 'पहले सेशन सेट smtp_out_server =' 'our_mailserver' '' ;
utl_mail.send (
प्रेषक => 'oracleDBA@dbdomain.com' ,
प्राप्तकर्ता => मेल,
विषय => 'मैक्सवेल्ड विभाजन रिपोर्ट' ,
संदेश => msg_body,
mime_type => 'text / html' );
अंत;
अंत pkg_partitions;
* इस सोर्स कोड को सोर्स कोड हाइलाइटर के साथ हाइलाइट किया गया था।

युपीडी
जैसा कि zhekappp ने सही तरीके से संकेत दिया है, आप स्वचालित सांख्यिकी संग्रह को सक्षम करते समय num_rows का उपयोग कर सकते हैं। Dbms_stats.gather_table_stats के साथ dbms_job का उपयोग करके नौकरी जोड़कर सांख्यिकी संग्रह को सक्षम किया जा सकता है।
तब आपको अनुभाग में रिकॉर्ड की संख्या के लिए अनुरोध को हटाने और अनुरोध को बदलने की आवश्यकता होगी:

चुनना
pl.table_owner,
pl.table_name,
गिनती (1) cnt,
अधिकतम (pl.num_rows) रखें (dense_rank अंतिम क्रम द्वारा (pl.partition_position)) विभाजन_,
अधिकतम (pl.partition_name) रखना (d.partition_position द्वारा अंतिम क्रम में रखना) (विभाजन)
dba_tab_partitions pl से
जहां pl.table_name 'BIN $%' को पसंद नहीं करता
pl.table_owner, pl.table_name द्वारा समूह

इस मामले के लिए पूर्ण पैकेज कोड यहां पाया जा सकता है: http://www.xt-r.com/2010/10/pkgpartitions.html

स्वचालित निष्पादन

यह केवल स्वचालित निष्पादन को कॉन्फ़िगर करने के लिए बनी हुई है। इसे dbms_job के साथ करते हैं।
उदाहरण के लिए, डेटा अधिग्रहण स्क्रिप्ट का दैनिक स्वचालित निष्पादन:

घोषित
नौकरी बाइनरी_इन्टेगर;
शुरू करना
dbms_job.submit (
नौकरी,
'pkg_partitions.send_partitions_report (' 'dba@domain.ru' ');' ' ।
sysdate,
'trunc (sysdate) +1' );
dbms_output.put_line (job);
अंत ;

विभाजन। स्वचालित रूप से अनुभाग जोड़ें

अंतराल के बिना एक समान रूप से बढ़ती विभाजन कुंजी के लिए समाधान

अन्य समाधान

स्वचालित निष्पादन

More articles: