Guten Tag! Wir sind ein Team von Systemanalysten in einer der Datenverwaltungsabteilungen von Rostelecom. Unser Unternehmen verfügt über mehr als 300 heterogene Datenquellen - eine solche Vielfalt ist erforderlich, um die Arbeit von Rostelecom in allen zahlreichen Bereichen zu unterstützen. Wir untersuchen Datenquellen und laden sie gegebenenfalls teilweise in die Speicherschleife hoch.
Neugierde Rover an der Oberfläche. Es hat auch viele heterogene Datenquellen. Bild von therahnuma.com genommen.In diesem Prozess werden zwei Unteraufgaben unterschieden: Definieren einer Strategie zum Sammeln von Daten aus Quelltabellen in Abhängigkeit von ihren Eigenschaften und Vorbereiten von Tabellen, die „Empfänger“ des Data Warehouse sind. Dazu verwenden wir verschiedene GUIs und Reverse Engineering-Tools. Darüber hinaus beginnt ein Systemanalyst beim Sammeln von Informationen, einen Pool von Zusatzabfragen zu den Informationstabellen des DBMS (hauptsächlich Oracle) abzurufen. In diesem Artikel werde ich das "Gentleman-Set" solcher Skripte teilen, die von unserem Team verwendet werden.
Zunächst eine kleine Erklärung aller aufgeführten Skripte:
- Viele Skripte verwenden xmlagg, um Zeichenfolgen zu aggregieren, da listagg nicht zu lange Zeichenfolgen verarbeiten kann, die aus der Verkettung resultieren.
- In allen Skripten mit Ausnahme von "Prozeduren, Funktionen und Paketen" werden Zieltabellen über die Filtertabelle im Block "mit" angegeben. Der Name des Schemas und der Name der Tabelle werden ausgefüllt.
- Jedes Skript wird von einem oder mehreren Anwendungsfällen, einer Beschreibung der Spezifikation (Ergebnismenge) und einer Liste der verwendeten Systemtabellen begleitet (um die Möglichkeit der Verwendung in einer bestimmten Datenbank zu bewerten).
Skript "Informationen zu Tabellen"
Spezifikation :
Verwendete Systemtabellen
: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
Die Abfrage ist nützlich, um eine Strategie zum Hochladen von Daten aus einem Quellsystem zu bestimmen. Wenn der Primärschlüssel auf der betrachteten Tabelle aufgebaut ist, ist es möglich, das Entladen mit der anschließenden Zuweisung des "Inkrements" darauf zu organisieren. Wenn es einen Zeitstempel gibt - beispielsweise in technischen Bereichen mit Informationen zum Einfügen oder Aktualisieren von Daten - können Sie das Hochladen nur geänderter / hinzugefügter Datensätze für einen bestimmten Zeitraum organisieren. Informationen zur Struktur von Partitionen können nützlich sein, wenn Sie eine ähnliche Tabelle erstellen, den "Empfänger".
Text anfordern: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 ;
Skript "Partitionen und Unterpartitionen"
Spezifikation:
Verwendete Systemtabellen
: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.
Das Skript ist nützlich, um Merkmale (Name, Größe) von Partitionen abzurufen, wenn diese direkt als Datenquellen verwendet werden.
Text anfordern: 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 ;
Skript "Attributzusammensetzung von Tabellen"
Spezifikation:
Verwendete Systemtabellen
: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v $ nls_parameters.
Dieses Skript ist nützlich, um die "Empfänger" -Tabellen im Data Warehouse vorzubereiten, wenn detaillierte Informationen über die Tabelle, ihre Beziehungen zu anderen Tabellen sowie die vollständige Attributzusammensetzung erforderlich sind. Die filter2-Tabelle wird verwendet, um Tabellen zu filtern, nach denen Links gesucht werden (von und nach). Standardmäßig werden Tabellen aus allen Schemata mit Ausnahme der Systemschemata übernommen.
Text anfordern: 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
Skript "Prozeduren, Funktionen und Pakete"
Spezifikation:Verwendete Systemtabellen
: all_source
Bei der Analyse der Quelle kann sich die Aufgabe ergeben, die im System zirkulierenden Datenflüsse zu untersuchen. Es ist fast unmöglich, auf eine Codebasis von Paketen, Funktionen und Prozeduren zu verzichten, insbesondere bei unvollständiger oder fehlender Dokumentation. Der Einfachheit halber können die über ein Skript aufgelisteten Objekte in einer Tabelle dargestellt werden. Das Ergebnis der Abfrage mit dem Konsolendienstprogramm kann in einen Stream ausgegeben und von einem einfachen Handler (Bash-Skript) in Dateien umgeleitet werden, um von Ihrem bevorzugten Editor weiter untersucht zu werden. Darüber hinaus können verschiedene Handler an den Ausgabestream „gehängt“ werden - Verschönern, Auspacken usw.
Text anfordern: 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 ;
Fazit
Die oben beschriebenen Skripte helfen unseren Systemanalysten dabei, viele der Routineaufgaben beim Sammeln von Informationen über die Datenbank zu erledigen und sich auf kreativere Dinge wie die Ladestrategie und die Struktur der "Empfänger" -Tabellen zu konzentrieren. Ich hoffe, die Skripte sind auch für Sie nützlich. Es wäre interessant zu wissen, wie Sie diese und ähnliche Aufgaben automatisieren.