Selamat siang Kami adalah tim analis sistem di salah satu divisi manajemen data Rostelecom. Perusahaan kami memiliki lebih dari 300 sumber data yang heterogen - variasi seperti itu diperlukan untuk mendukung pekerjaan Rostelecom di semua bidang. Kami mempelajari sumber data dan, jika perlu, mengunggah sebagian ke loop penyimpanan.
Rasa penasaran menjelajah di permukaan. Ini juga memiliki banyak sumber data yang heterogen. Gambar diambil dari therahnuma.com.Dalam proses ini, dua subtugas dibedakan: menentukan strategi untuk mengumpulkan data dari tabel sumber tergantung pada properti mereka dan menyiapkan tabel yang merupakan "penerima" dari gudang data. Untuk melakukan ini, kami menggunakan berbagai GUI dan alat rekayasa terbalik. Selain itu, ketika mengumpulkan informasi, analis sistem mulai memperoleh kumpulan pertanyaan tambahan ke tabel informasi DBMS (terutama Oracle). Dalam artikel ini saya akan membagikan "set gentleman" dari skrip tersebut yang digunakan oleh tim kami.
Untuk memulai, penjelasan kecil dari semua skrip yang terdaftar:
- Banyak skrip menggunakan xmlagg untuk mengagregasi string, karena listagg tidak dapat menangani string terlalu panjang yang dihasilkan dari penggabungan.
- Dalam semua skrip kecuali "Prosedur, Fungsi dan Paket", tabel target ditentukan melalui tabel filter di blok "with". Nama skema dan nama tabel diisi.
- Setiap skrip disertai dengan satu atau beberapa kasus penggunaan, deskripsi spesifikasi (set hasil), dan daftar tabel sistem yang digunakan (untuk menilai kemungkinan penggunaan pada database tertentu).
Script "Informasi tentang tabel"
Spesifikasi :
Tabel sistem yang digunakan: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
Permintaan berguna untuk menentukan strategi untuk mengunggah data dari sistem sumber. Jika kunci utama dibuat di atas meja yang sedang dipertimbangkan, maka dimungkinkan untuk mengatur pembongkaran dengan alokasi "kenaikan" selanjutnya di atasnya. Jika ada cap waktu - misalnya, dalam bidang teknis dengan informasi tentang memasukkan data atau memperbarui - Anda dapat mengatur unggahan hanya catatan yang diubah / ditambahkan untuk jangka waktu tertentu. Informasi tentang struktur partisi dapat berguna saat membuat tabel yang serupa, "penerima".
Badan permintaan: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 ;
Script "Partisi dan subpartisi"
Spesifikasi:
Tabel sistem yang digunakan: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.
Script akan berguna untuk mendapatkan karakteristik (nama, ukuran) partisi ketika mereka langsung digunakan sebagai sumber data.
Badan permintaan: 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 ;
Script "Atribut komposisi tabel"
Spesifikasi:
Tabel sistem yang digunakan: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v $ nls_parameters.
Skrip ini akan berguna untuk menyiapkan tabel "receiver" di gudang data ketika informasi terperinci tentang tabel, hubungannya dengan tabel lain, serta komposisi atribut lengkap diperlukan. Tabel filter2 digunakan untuk memfilter tabel yang tautannya dicari (dari dan ke). Secara default, tabel diambil dari semua skema, kecuali yang sistem.
Badan permintaan: 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
Script "Prosedur, fungsi dan paket"
Spesifikasi:Tabel sistem yang digunakan: all_source
Saat menganalisis sumber, tugas mungkin muncul untuk mempelajari aliran data yang beredar di sistem. Hampir tidak mungkin dilakukan tanpa basis kode paket, fungsi dan prosedur, terutama dengan dokumentasi yang tidak lengkap atau hilang. Untuk kenyamanan, objek yang terdaftar melalui skrip dapat direpresentasikan dalam tabel. Hasil kueri menggunakan utilitas konsol dapat di-output ke stream dan dialihkan oleh handler sederhana (skrip bash) ke file untuk dipelajari lebih lanjut oleh editor favorit Anda. Selain itu, berbagai penangan dapat "digantung" pada arus keluaran - mempercantik, membuka bungkus, dll.
Badan permintaan: 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 ;
Kesimpulan
Skrip yang dijelaskan di atas membantu analis sistem kami menyingkirkan banyak tugas rutin mengumpulkan informasi tentang database dan fokus pada hal-hal yang lebih kreatif, seperti strategi pemuatan dan struktur tabel penerima. Saya harap skrip berguna untuk Anda juga. Akan menarik untuk mengetahui bagaimana Anda mengotomatiskan ini dan tugas-tugas serupa.