こんにちは 私たちは、ロステレコムのデータ管理部門のシステムアナリストチームです。 当社には、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 , decode ( a.data_type , 'NUMBER', nvl(a.data_scale, 0) , '' ) as scale , decode ( a.data_type , 'NUMBER', nvl(a.data_precision, 38) , '' ) as precision , 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
スクリプト「手順、関数、およびパッケージ」
仕様：使用されるシステムテーブル： 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 ;
おわりに
上記のスクリプトは、システムアナリストがデータベースに関する情報を収集する多くの日常的なタスクを取り除き、読み込み戦略やレシーバーテーブルの構造など、より創造的なことに集中するのに役立ちます。 スクリプトがあなたにとっても役に立つことを願っています。 これらのタスクや同様のタスクを自動化する方法を知ることは興味深いでしょう。