ओरेकल डेटाबेस अनुसंधान को सरल कैसे करें: लिपियों का "सज्जन सेट"

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


सतह पर जिज्ञासा रोवर। इसके कई विषम डेटा स्रोत भी हैं। Therahnuma.com से ली गई छवि।

इस प्रक्रिया में, दो उप-प्रकारों को प्रतिष्ठित किया जाता है: अपने गुणों के आधार पर स्रोत तालिकाओं से डेटा एकत्र करने और डेटा वेयरहाउस के "रिसीवर्स" तैयार करने वाली रणनीति को परिभाषित करना। ऐसा करने के लिए, हम विभिन्न GUI और रिवर्स इंजीनियरिंग टूल का उपयोग करते हैं। इसके अलावा, जानकारी एकत्र करते समय, एक सिस्टम विश्लेषक DBMS सूचना तालिकाओं (मुख्य रूप से ओरेकल) के सहायक प्रश्नों का एक पूल हासिल करना शुरू कर देता है। इस लेख में मैं हमारी टीम द्वारा उपयोग की जाने वाली ऐसी लिपियों के "सज्जन सेट" को साझा करूंगा।

आरंभ करने के लिए, सूचीबद्ध सभी लिपियों की एक छोटी व्याख्या:

• कई स्क्रिप्ट xmlagg का उपयोग एग्रीगेट स्ट्रिंग्स के लिए करती हैं, क्योंकि लिस्टागेशन के परिणामस्वरूप लिस्टैग बहुत लंबे स्ट्रिंग्स को संसाधित नहीं कर सकता है।
  
• "प्रक्रिया, कार्य और पैकेज" को छोड़कर सभी लिपियों में, लक्ष्य तालिका को "तालिका" ब्लॉक में फ़िल्टर तालिका के माध्यम से निर्दिष्ट किया जाता है। योजना का नाम और तालिका का नाम भरा जाता है।
  
• प्रत्येक स्क्रिप्ट एक या अधिक उपयोग के मामलों के साथ होती है, विनिर्देश का विवरण (परिणाम सेट), और प्रयुक्त सिस्टम तालिकाओं की सूची (किसी विशेष डेटाबेस पर उपयोग की संभावना का आकलन करने के लिए)।
  

स्क्रिप्ट "तालिकाओं के बारे में जानकारी"

विशिष्टता :

प्रयुक्त सिस्टम टेबल: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns।

स्रोत सिस्टम से डेटा अपलोड करने के लिए रणनीति निर्धारित करने के लिए क्वेरी उपयोगी है। यदि प्राथमिक कुंजी को विचार के तहत टेबल पर बनाया गया है, तो उस पर "वेतन वृद्धि" के बाद के आवंटन के साथ अनलोडिंग को व्यवस्थित करना संभव है। यदि कोई समय टिकट है - उदाहरण के लिए, डेटा डालने या अद्यतन करने की जानकारी वाले तकनीकी क्षेत्रों में - आप केवल कुछ समय के लिए परिवर्तित / जोड़े गए रिकॉर्ड अपलोड कर सकते हैं। विभाजन की संरचना के बारे में जानकारी समान तालिका बनाते समय काम आ सकती है, "रिसीवर"।

अनुरोध निकाय:

with filter (owner, table_name) as (    select 'SCHEMA_NAME_1', t.*    from table(        sys.odcivarchar2list(            'TABLE_NAME_1'            , 'TABLE_NAME_2'        )    ) t    union all    select        owner        , table_name    from        all_tables    where owner = 'SCHEMA_NAME_2' ) select    a.owner as schema_name    , a.table_name    , e.comments    , b.height    , c.width    , d.datetime_columns    , b.avg_row_len    , p.part_key    , s.subpart_key from    filter a    left join (        select            owner            , table_name            , num_rows as height            , avg_row_len        from all_tab_statistics        where object_type = 'TABLE'    ) b        on            a.table_name = b.table_name            and a.owner = b.owner    left join (        select            owner            , table_name            , count(1) as width        from all_tab_columns        group by            owner            , table_name    ) c        on            a.table_name = c.table_name            and a.owner = c.owner    left join (        select            owner            , table_name            , listagg(                column_name || ' (' || data_type || ')'                , ', '            ) within group (order by column_id) as datetime_columns        from all_tab_columns        where            data_type = 'DATE'            or data_type like 'TIMESTAMP%'            or data_type like 'INTERVAL%'            or lower(column_name) like '%period%'            or lower(column_name) like '%date%'            or lower(column_name) like '%time%'        group by            owner            , table_name    ) d        on            a.table_name = d.table_name            and a.owner = d.owner    left join (        select            owner            , table_name            , comments        from all_tab_comments        where table_type = 'TABLE'    ) e        on            a.table_name = e.table_name            and a.owner = e.owner    left join (        select            owner            , name as table_name            , listagg(                column_name                , ', '            ) within group (order by column_position) as part_key        from all_part_key_columns        where object_type = 'TABLE'        group by            owner            , name    ) p        on            a.owner = p.owner            and a.table_name = p.table_name    left join (        select            owner            , name as table_name            , listagg(                column_name                , ', '            ) within group (order by column_position) as subpart_key        from all_subpart_key_columns        where object_type = 'TABLE'        group by            owner            , name    ) s        on            a.owner = s.owner            and a.table_name = s.table_name order by    e.owner    , e.table_name ; 

स्क्रिप्ट "विभाजन और उपप्रकार"

विशिष्टता:


प्रयुक्त सिस्टम टेबल: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns।

स्क्रिप्ट विभाजन की विशेषताओं (नाम, आकार) को प्राप्त करने के लिए उपयोगी होगी जब उन्हें सीधे डेटा स्रोतों के रूप में उपयोग किया जाता है।

अनुरोध निकाय:

 with filter (owner, table_name) as (   select 'SCHEMA_NAME_1', t.*   from table(       sys.odcivarchar2list(           'TABLE_NAME_1'           , 'TABLE_NAME_2'       )   ) t   union all   select       owner       , table_name   from       all_tables   where owner = 'SCHEMA_NAME_2' ) select   f.owner as schema_name   , f.table_name   , p.part_key   , pc.partition_name   , pc.partition_position   , pc.num_rows as partition_height   , s.subpart_key   , sc.subpartition_name   , sc.subpartition_position   , sc.num_rows as subpartition_height from   filter f   join (       select           owner           , name as table_name           , listagg(               column_name               , ', '           ) within group (order by column_position) as part_key       from all_part_key_columns       where object_type = 'TABLE'       group by           owner           , name   ) p       on           f.owner = p.owner           and f.table_name = p.table_name   left join all_tab_partitions pc       on           p.table_name = pc.table_name           and p.owner = pc.table_owner   left join (       select           owner           , name as table_name           , listagg(               column_name               , ', '           ) within group (order by column_position) as subpart_key       from all_subpart_key_columns       where object_type = 'TABLE'       group by           owner           , name   ) s       on           p.owner = s.owner           and p.table_name = s.table_name   left join all_tab_subpartitions sc       on           f.owner = sc.table_owner           and f.table_name = sc.table_name           and pc.partition_name = sc.partition_name   order by       f.owner       , f.table_name ; 

स्क्रिप्ट "तालिकाओं की रचना रचना"

विशिष्टता:


प्रयुक्त सिस्टम टेबल: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v $ nls_parameters।

यह स्क्रिप्ट डेटा वेयरहाउस में "रिसीवर" तालिकाओं को तैयार करने के लिए उपयोगी होगी जब तालिका के बारे में विस्तृत जानकारी, अन्य तालिकाओं के साथ-साथ पूर्ण विशेषता संरचना की आवश्यकता होती है। फ़िल्टर 2 टेबल का उपयोग टेबल को फ़िल्टर करने के लिए किया जाता है जिसके लिए लिंक (से और) खोजे जाते हैं। डिफ़ॉल्ट रूप से, सिस्टम स्कीमों को छोड़कर, सभी योजनाओं से तालिकाएं ली जाती हैं।

अनुरोध निकाय:

 with filter (owner, table_name) as (   select 'SCHEMA_NAME_1', t.*   from table(       sys.odcivarchar2list(           'TABLE_NAME_1'           , 'TABLE_NAME_2'       )   ) t   union all   select       owner       , table_name   from       all_tables   where owner = 'SCHEMA_NAME_2' ) , filter2 (owner, table_name) as (   select owner, table_name   from all_tables   where owner not in ('MDSYS', 'CTXSYS', 'SYSTEM', 'XDB', 'SYS') ) , refs as (   select       b.constraint_type as from_constraint_type       , b.constraint_name as from_constraint_name       , d.position as from_position       , d.column_name as from_column_name       , b.table_name as from_table_name       , b.owner as from_owner       , a.owner as to_owner       , a.table_name as to_table_name       , c.column_name as to_column_name       , c.position as to_position       , a.constraint_name as to_constraint_name       , a.constraint_type as to_constraint_type   from       all_constraints a       left join all_constraints b           on               a.r_constraint_name = b.constraint_name               and a.r_owner = b.owner       left join all_cons_columns c           on               a.constraint_name = c.constraint_name               and a.table_name = c.table_name               and a.owner = c.owner       left join all_cons_columns d           on               b.constraint_name = d.constraint_name               and b.table_name = d.table_name               and b.owner = d.owner       where           a.constraint_type = 'R'           and b.constraint_type in ('P', 'U')           and c.position = d.position ) , depends as (   select       rtrim(           xmlagg(               xmlelement(                   e                   , to_owner || '.' || to_table_name || '.' || to_column_name                   , ', '               ).extract('//text()')               order by to_owner           ).getclobval()           , ', '       ) as val       , from_owner as owner       , from_table_name as table_name       , from_column_name as column_name   from refs   where (to_owner, to_table_name) in (select * from filter2)   group by       from_table_name       , from_column_name       , from_owner ) , impacts as (   select       rtrim(           xmlagg(               xmlelement(                   e                   , from_owner || '.' || from_table_name || '.' || from_column_name                   , ', '               ).extract('//text()')               order by from_owner           ).getclobval()           , ', '       ) as val       , to_owner as owner       , to_table_name as table_name       , to_column_name as column_name   from refs   where (from_owner, from_table_name) in (select * from filter2)   group by       to_table_name       , to_column_name       , to_owner ) select   f.owner as schema_name   , f.table_name   , a.column_id   , a.column_name   , a.data_type   , b.comments as column_comment   /*         precision  ,    38       (      )       ,    scale,    0 (  ). */   , decode (       a.data_type       , 'NUMBER', nvl(a.data_scale, 0)       , ''   ) as scale   , decode (       a.data_type       , 'NUMBER', nvl(a.data_precision, 38)       , ''   ) as precision   /*             CHAR, VARCHAR2           DDL-   ,    NCHAR or NVARCHAR2        .*/   , a.data_length as byte_length   , case       when a.data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')       then d.value   end as encoding   , case       when a.data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2')       then a.char_length --a.char_col_decl_length   end as char_length   , decode(a.nullable, 'Y', 'N', 'Y') as not_null   , decode(c.is_primary, 1, 'Y', 'N') as is_primary   , a.data_default as default_value   , impacts.val as column_impact   , depends.val as column_depend from   filter f   left join all_tab_columns a       on           f.owner = a.owner           and f.table_name = a.table_name   left join all_col_comments b       on           a.owner = b.owner           and a.table_name = b.table_name           and a.column_name = b.column_name   left join (       select           1 as is_primary           , owner           , table_name           , column_name       from all_cons_columns       where (owner, constraint_name) in (           select owner, constraint_name           from all_constraints           where constraint_type = 'P'       )   ) c       on           a.owner = c.owner           and a.table_name = c.table_name           and a.column_name = c.column_name   left join v$nls_parameters d       on decode (           a.character_set_name           , 'CHAR_CS', 'NLS_CHARACTERSET'           , 'NCHAR_CS', 'NLS_NCHAR_CHARACTERSET'           , a.character_set_name       ) = d.parameter   left join depends       on           a.owner = depends.owner           and a.table_name = depends.table_name           and a.column_name = depends.column_name   left join impacts       on           a.owner = impacts.owner           and a.table_name = impacts.table_name           and a.column_name = impacts.column_name order by   f.owner   , f.table_name   , a.column_id ; 

स्क्रिप्ट "प्रक्रियाएं, कार्य और पैकेज"

विशिष्टता:

प्रयुक्त सिस्टम टेबल: all_source

स्रोत का विश्लेषण करते समय, सिस्टम में परिसंचारी डेटा प्रवाह का अध्ययन करने के लिए एक कार्य उत्पन्न हो सकता है। संकुल, कार्यों और प्रक्रियाओं के कोड आधार के बिना ऐसा करना लगभग असंभव है, विशेष रूप से अपूर्ण या लापता प्रलेखन के साथ। सुविधा के लिए, स्क्रिप्ट के माध्यम से सूचीबद्ध वस्तुओं को तालिका के रूप में दर्शाया जा सकता है। कंसोल उपयोगिता का उपयोग करके क्वेरी का परिणाम एक स्ट्रीम के लिए आउटपुट हो सकता है और आपके पसंदीदा संपादक द्वारा आगे के अध्ययन के लिए एक साधारण हैंडलर (बैश स्क्रिप्ट) द्वारा रीडायरेक्ट किया जा सकता है। इसके अलावा, विभिन्न हैंडलर आउटपुट स्ट्रीम पर "त्रिशंकु" हो सकते हैं - सौंदर्यीकरण, अलंकृत करना, आदि।

अनुरोध निकाय:

 select   t.owner as schema_name   , t.name as name   , xmlagg(       xmlelement(           e           , t.text           , ''       ).extract('//text()')       order by t.line asc   ).getclobval() as body   , f.wrapped   , t.type as type from (   select       owner, name, type       , case           when lower(text) like '%wrapped%' then 1           else 0       end as wrapped   from all_source   where type in (       'PACKAGE BODY'       , 'PACKAGE'       , 'FUNCTION'       , 'PROCEDURE'   )   and line = 1   and owner not in ('MDSYS', 'CTXSYS', 'SYSTEM', 'XDB', 'SYS') ) f join all_source t on   f.owner = t.owner   and f.name = t.name   and f.type = t.type group by   t.owner   , t.name   , t.type   , f.wrapped order by   t.owner   , t.name   , t.type ; 

निष्कर्ष

ऊपर वर्णित स्क्रिप्ट हमारे सिस्टम विश्लेषकों को डेटाबेस के बारे में जानकारी इकट्ठा करने के कई नियमित कार्यों से छुटकारा पाने में मदद करती हैं और अधिक रचनात्मक चीजों पर ध्यान केंद्रित करती हैं, जैसे लोडिंग रणनीति और "रिसीवर" तालिकाओं की संरचना। मुझे उम्मीद है कि स्क्रिप्ट आपके लिए भी काम आएगी। यह जानना दिलचस्प होगा कि आप इन और इसी तरह के कार्यों को कैसे स्वचालित करते हैं।