كيفية تبسيط أبحاث قاعدة بيانات أوراكل: مجموعة من النصوص اللطيفة

مساء الخير نحن فريق من محللي النظام في أحد أقسام إدارة البيانات في Rostelecom. تمتلك شركتنا أكثر من 300 مصدر بيانات غير متجانسة - مثل هذا التنوع ضروري لدعم عمل Rostelecom في جميع المجالات العديدة. نحن ندرس مصادر البيانات ، وإذا لزم الأمر ، نقوم بالتحميل جزئيًا إلى حلقة التخزين.


الفضول روفر على السطح. كما أن لديها العديد من مصادر البيانات غير المتجانسة. الصورة مأخوذة من therahnuma.com.

في هذه العملية ، يتم تمييز مهمتين فرعيتين: تحديد استراتيجية لجمع البيانات من جداول المصدر وفقًا لخصائصها وإعداد الجداول التي تعتبر "مستقبلات" لمستودع البيانات. للقيام بذلك ، نستخدم العديد من واجهات المستخدم الرسومية وأدوات الهندسة العكسية. بالإضافة إلى ذلك ، عند جمع المعلومات ، يبدأ محلل النظام في الحصول على مجموعة من الاستعلامات المساعدة لجداول معلومات DBMS (أوراكل بشكل أساسي). في هذه المقالة سوف أشارك "مجموعة السيد" من هذه النصوص التي يستخدمها فريقنا.

للبدء ، شرح صغير لجميع النصوص المسرودة:

• تستخدم العديد من البرامج النصية xmlagg لتجميع السلاسل ، حيث لا تستطيع listagg معالجة السلاسل الطويلة جدًا الناتجة عن السلاسل.
  
• في جميع البرامج النصية باستثناء "الإجراءات والوظائف والحزم" ، يتم تحديد الجداول المستهدفة من خلال جدول الترشيح في كتلة "مع". يتم ملء اسم المخطط واسم الجدول.
  
• يكون كل برنامج نصي مصحوبًا بحالة استخدام واحدة أو أكثر ، ووصف للمواصفات (مجموعة النتائج) ، وقائمة بجداول النظام المستخدمة (لتقييم إمكانية الاستخدام في قاعدة بيانات معينة).
  

النصي "معلومات حول الجداول"

المواصفات :

جداول النظام المستخدمة: 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_constrict ، all_cons_columns ، all_tab_columns ، all_col_comments ، v $ nls_parameters.

سيكون هذا البرنامج النصي مفيدًا لإعداد جداول "المتلقي" في مستودع البيانات عندما تكون هناك حاجة إلى معلومات مفصلة حول الجدول ، وعلاقاته مع الجداول الأخرى ، وكذلك تكوين السمة الكامل. يتم استخدام الجدول filter2 لتصفية الجداول التي يتم البحث عن الارتباطات (من وإلى). بشكل افتراضي ، يتم أخذ الجداول من جميع المخططات ، باستثناء أنظمة النظام.

طلب الجسم:

 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

عند تحليل المصدر ، قد تنشأ مهمة لدراسة تدفق البيانات المتداولة في النظام. يكاد يكون من المستحيل الاستغناء عن قاعدة الكود للحزم والوظائف والإجراءات ، خاصة مع الوثائق غير المكتملة أو المفقودة. للراحة ، يمكن تمثيل الكائنات المدرجة من خلال برنامج نصي في جدول. يمكن إخراج نتيجة الاستعلام باستخدام الأداة المساعدة لوحدة التحكم وإعادة توجيهها بواسطة معالج بسيط (نص bash) إلى الملفات لمزيد من الدراسة بواسطة المحرر المفضل لديك. بالإضافة إلى ذلك ، يمكن تعليق "معالجات" مختلفة على تدفق الإخراج - تجميل ، فك ، الخ

طلب الجسم:

 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 ; 

الخاتمة

تساعد البرامج النصية الموضحة أعلاه محللي النظام لدينا على التخلص من العديد من المهام الروتينية الخاصة بجمع المعلومات حول قاعدة البيانات والتركيز على أشياء أكثر إبداعًا ، مثل استراتيجية التحميل وهيكل جداول المتلقي. آمل أن تكون البرامج النصية مفيدة لك أيضًا. سيكون من المثير للاهتمام معرفة كيفية أتمتة هذه المهام وما شابهها.