Oracleデータベースの調査を簡素化する方法：「紳士用」スクリプトセット

こんにちは 私たちは、ロステレコムのデータ管理部門のシステムアナリストチームです。 当社には、300を超える異種データソースがあります。さまざまな分野でRostelecomの作業をサポートするには、このような多様性が必要です。 データソースを調査し、必要に応じて、ストレージループに部分的にアップロードします。





表面上の好奇心ローバー。 また、多くの異種データソースがあります。 therahnuma.comからの画像。



このプロセスでは、2つのサブタスクが区別されます。プロパティに応じてソーステーブルからデータを収集する戦略を定義し、データウェアハウスの「レシーバ」であるテーブルを準備します。 これを行うには、さまざまなGUIとリバースエンジニアリングツールを使用します。 さらに、情報を収集するとき、システムアナリストはDBMS情報テーブル（主にOracle）への補助クエリのプールの取得を開始します。 この記事では、私たちのチームが使用するそのようなスクリプトの「紳士用セット」を共有します。



はじめに、リストされているすべてのスクリプトの簡単な説明：

• listaggは連結に起因する長すぎる文字列を処理できないため、多くのスクリプトはxmlaggを使用して文字列を集約します。
  
  
• 「プロシージャ、関数、およびパッケージ」を除くすべてのスクリプトでは、ターゲットテーブルは「with」ブロックのフィルターテーブルを介して指定されます。 スキームの名前とテーブルの名前が入力されます。
  
  
• 各スクリプトには、1つ以上のユースケース、仕様の説明（結果セット）、および使用されたシステムテーブルのリスト（特定のデータベースでの使用の可能性を評価するため）が付属しています。
  
  

スクリプト「テーブルに関する情報」

仕様 ：



使用されるシステムテーブル： 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。



このスクリプトは、テーブル、他のテーブルとの関係、および完全な属性構成に関する詳細情報が必要な場合に、データウェアハウスで「レシーバ」テーブルを準備するのに役立ちます。 filter2テーブルは、リンクが検索される（fromおよびto）テーブルをフィルタリングするために使用されます。 デフォルトでは、テーブルはシステムスキームを除くすべてのスキームから取得されます。



リクエスト本文：

 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 ;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

おわりに

上記のスクリプトは、システムアナリストがデータベースに関する情報を収集する多くの日常的なタスクを取り除き、読み込み戦略やレシーバーテーブルの構造など、より創造的なことに集中するのに役立ちます。 スクリプトがあなたにとっても役に立つことを願っています。 これらのタスクや同様のタスクを自動化する方法を知ることは興味深いでしょう。