Dynamic data dictionary acquisition method and system based on Oracle incremental data

Abstract

The invention relates to a dynamic data dictionary acquisition method based on Oracle incremental data. The method comprises the steps of querying a mapping relation between a table name and a table ID and a mapping relation between a table field number and a field name through a metadata table of a database, and constructing an initial data dictionary structure; carrying out Oracle incremental data acquisition through an SQL (Structured Query Language) interface of the logminer component, and extracting an incremental DDL (Double Data Language) redo statement; analyzing and processing the increment DDL redo statement by using an SQL grammar analyzer to obtain table structure change information; and issuing a structure change event according to the table structure change information, receiving the structure change event by the dictionary maintainer, and merging the original data dictionary with newly received change data to obtain a dynamically updated new data dictionary. According to the method, the defects that an online directory and an exported data dictionary file require that a data dictionary cannot be changed and a redo log file is low in performance are overcome, the limitation that the data dictionary cannot be changed can be avoided, and the performance of the data dictionary can be prevented from being greatly reduced.

Description

Technical Field

[0001] The present invention belongs to the technical field of data dictionaries, and in particular relates to a dynamic data dictionary acquisition method based on Oracle incremental data, an acquisition system and a computer-readable storage medium. Background Art

[0002] Oracle incremental data mining usually uses a database built-in tool called logminer. Using the logminer component requires specifying the corresponding data dictionary source to translate the object ID and field number in the redosql mined by logminer into the corresponding object name. The available data dictionary source options are usually one of the following three: online directory, redo log file, and exported data dictionary file.

[0003] However, the above three data dictionary source options each have their own advantages and disadvantages: Oracle officially recommends the online catalog option because of its relatively good performance, but the online catalog requires that the data dictionary cannot be changed, which limits its application scenarios; the exported data dictionary file option is similar to the online catalog option and also requires that the data dictionary cannot be changed; although the redo log file option can avoid the above limitations, its performance is much worse than the other two options and it loses its practicality in many scenarios. Summary of the invention

[0004] In order to overcome the above limitations of existing data dictionary source options, the present invention proposes a new dynamic data dictionary collection method based on Oracle incremental data. By using the dynamic data dictionary collection method of the present invention, the limitation that the data dictionary cannot be changed can be avoided, and a significant decline in data dictionary performance can be avoided.

[0005] We know that the data dictionary contains metadata information of objects in the database system, mainly metadata of tables and fields, that is, metadata information of table structure. The table structure in the database will evolve dynamically over time, so the static data dictionary often cannot meet the needs of actual applications. In view of this, the present invention has developed a method for collecting and constructing a dynamic data dictionary based on Oracle incremental change history data, which mainly includes the following four aspects: First, build the initial data dictionary structure, including: The mapping relationship between table name and table ID, and the mapping relationship between table field number and field name are queried through the metadata table view of the database.

[0006] The second aspect is to collect Oracle incremental data and extract incremental DDL redo statements, including: Use the SQL interface of the logminer component to collect incremental data and extract incremental DDL redo statements. First, you need to add redo logs, add the online data dictionary option to enable mining, and then query and extract incremental DDL redo statements from the mining result view.

[0007] Third, parse the table structure change information based on the incremental DDL redo statement, including: The incremental DDL redo statement obtained in the previous step is parsed using a SQL syntax parser to obtain the table structure change information expressed by the DDL.

[0008] Fourth, dynamically update the data dictionary based on table structure change information, including: According to the table structure change information obtained in the previous step, the structure change event is published. The dictionary maintainer registers the corresponding change event handler in advance, follows up the received change events, triggers the corresponding change processing logic, and merges the original data dictionary with the newly received change data to obtain a dynamically updated new data dictionary.

[0009] Specifically, the present invention provides a dynamic data dictionary acquisition method based on Oracle incremental data, such as Figure 5 As shown, the method comprises the following steps: S1. Constructing the initial data dictionary structure: querying the mapping relationship between table name and table ID, and the mapping relationship between table field number and field name through the metadata table of the database, and constructing the initial data dictionary structure; S2. Collect Oracle incremental data and extract incremental DDL redo statements: Collect Oracle incremental data through the SQL interface of the logminer component and extract incremental DDL redo statements; S3. Parsing table structure change information according to the incremental DDL redo statement: The incremental DDL redo statement obtained in the previous step is parsed using a SQL syntax parser to obtain table structure change information expressed by the incremental DDL redo statement; S4. Dynamically update the data dictionary according to the table structure change information: According to the table structure change information obtained in the previous step, a structure change event is published. The dictionary maintainer receives the structure change event, merges the original data dictionary with the newly received change data, and obtains a dynamically updated new data dictionary.

[0010] Furthermore, in step S1 of the method for collecting dynamic data dictionary based on Oracle incremental data of the present invention, constructing the initial data dictionary structure refers to constructing a map data structure for storing dictionary data, wherein the key is the table ID and the value is a structure of table metadata.

[0011] Furthermore, the structure of the table metadata in the dynamic data dictionary collection method based on Oracle incremental data of the present invention includes a mapping map structure of the schema name, table name, field number and field name.

[0012] Furthermore, the method for collecting dynamic data dictionary based on Oracle incremental data of the present invention comprises in step S2: S21. Add redo log; S22. Add online data dictionary option and perform Oracle incremental data mining; S23. Query and extract incremental DDL redo statements from the mining result view.

[0013] Furthermore, the table structure change information in step S3 of the method for collecting dynamic data dictionary based on Oracle incremental data of the present invention includes the addition, deletion and modification information of fields.

[0014] Furthermore, the change mode of the corresponding field number after the addition and deletion of the field in the dynamic data dictionary collection method based on Oracle incremental data of the present invention is as follows: (1) The number of the newly added field is the maximum number of the current table field plus 1; (2) Deleting a field will cause the number of the fields following the deleted field to be reduced by 1.

[0015] Furthermore, the method for collecting dynamic data dictionary based on Oracle incremental data of the present invention comprises in step S3: S31. Use the SQL parser to parse the incremental DDL redo statement obtained in the previous step; S32. Get the table structure change information expressed by the incremental DDL redo statement; S33 determines whether the table structure change information is a field change, if so, construct the field change information and return, if not, proceed to the next step; S34. Determine whether the table structure change information is a table change. If so, construct the table change information and return it. If not, return it directly.

[0016] Furthermore, the method for collecting dynamic data dictionary based on Oracle incremental data of the present invention comprises in step S4: S41. Convert the table structure change information obtained in the previous step into a structure change event; S42. Publish the structure change event to the dictionary maintainer; S43 dictionary maintainer pre-register the corresponding change event handler, receiving the structure change event triggers the corresponding change process; S44. Find the dictionary data corresponding to the event table ID from the current dictionary data and merge it with the change event data; S45. The merged dictionary data structure is the new data dictionary, and the dictionary maintainer loops and waits for new change events to arrive.

[0017] On the other hand, the present invention also provides a dynamic data dictionary acquisition system based on Oracle incremental data. When the acquisition system is running, the steps of the above-mentioned dynamic data dictionary acquisition method based on Oracle incremental data are implemented.

[0018] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored, and when the program is executed by a processor, the steps of the above-mentioned dynamic data dictionary acquisition method based on Oracle incremental data are implemented.

[0019] In summary, the dynamic data dictionary collection method based on Oracle incremental data of the present invention overcomes the defects of online directory, exported data dictionary file requiring that the data dictionary cannot be changed and the low performance and practicality of redo log files. By using this method, the restriction that the data dictionary cannot be changed can be avoided, and a significant decline in data dictionary performance can be avoided. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] In order to more clearly illustrate the technical solution of the present invention, the following briefly introduces the drawings required for use in the description of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without paying creative work.

[0021] Figure 1 The flowchart of creating an initial data dictionary in an embodiment of the present invention.

[0022] Figure 2 The flowchart of mining incremental DDL redo statements in an embodiment of the present invention.

[0023] Figure 3 The present invention is a flowchart of parsing incremental DDL redo statements to obtain structural change information in an embodiment of the present invention.

[0024] Figure 4 This is a flow chart of dynamically updating data fields according to structure change information in an embodiment of the present invention.

[0025] Figure 5 The figure is a schematic diagram of the overall implementation process of the method of the present invention. DETAILED DESCRIPTION

[0026] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of them. The present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways based on different viewpoints and applications without departing from the spirit of the present invention.

[0027] At the same time, it should be understood that the protection scope of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the protection scope of the present invention.

[0028] Embodiment: A dynamic data dictionary acquisition method based on Oracle incremental data like Figure 5 As shown, the method comprises the following steps: Step 1: Build the initial data dictionary structure like Figure 1 As shown, the mapping relationship between table name and table ID, and the mapping relationship between table field number and field name are queried through the metadata table view of the database to build the initial data dictionary structure.

[0029] If the data dictionary option is not specified for the incremental data collected by logminer, the collected DML SQL does not contain a specific table name, but only a table ID, and the field part is a sequential number such as COL1, COL2, and COL3.

[0030] When it is started for the first time, the initial digital dictionary data is queried, that is, the mapping (correspondence) relationship between the table name and the table ID, and the mapping (correspondence) relationship between the table field number and the field name.

[0031] 1) Query the mapping relationship between table name and table ID: select owner, object_name, object_id from sys.dba_objects whereobject_type = 'TABLE' and owner ='{schema_name}'; 2) Query the mapping relationship between table field number and field name: select col#, name from sys.col$ where obj# ='{tableid}', tableid is the table ID obtained from the query in step 1) above; 3) Build the initial data dictionary structure Construct a map data structure to store dictionary data. Its key is the table ID and its value is the structure of table metadata. The structure contains a mapping structure of schema name, table name, field number and field name.

[0032] Assuming that angle brackets represent map structures and curly brackets represent structures, the above dictionary data structure can be represented as: <tableid, {schema_name, table_name,<column_number, column_name>}> Step 2: Collect Oracle incremental data and extract incremental DDL redo statements Incremental data collection is performed through the SQL interface of the logminer component, and incremental DDL redo statements are extracted. Figure 2 As shown, you first need to add a redo log, add the online data dictionary option and enable incremental data mining, and then query and extract incremental DDL redo statements from the mining result view.

[0033] 1) Add redo log sys.dbms_logmnr.add_logfile(LogFileName =>'{log}', Options =>dbms_logmnr.ADDFILE); 2) Add online data dictionary option for incremental data mining sys.dbms_logmnr.start_logmnr(); 3) Query and extract the mined incremental DDL redo statements select sql_redo, seg_name, tabel_name from v$logmnr_contents whereoperation = 'DDL'.

[0034] Step 3: Analyze table structure change information based on incremental DDL redo statements The incremental DDL redo statement obtained in the previous step is parsed using a SQL syntax parser to obtain the table structure change information expressed by the DDL.

[0035] like Figure 3 As shown, the incremental DDL redo statement obtained in the previous step is parsed using a SQL syntax parser to obtain the table structure change information expressed by the DDL, including different types of changes such as adding, deleting, and modifying fields.

[0036] The addition and deletion of fields correspond to changes in field numbers.

[0037] 1) The number of the newly added field is the maximum number of the current table field + 1.

[0038] 2) Deleting a field will cause the number of the fields following the deleted field to be reduced by 1. For example, suppose there is a table with 3 fields numbered 1, 2, and 3. After deleting field numbered 2, the field numbered 3 becomes 2.

[0039] When adding a new table, the order of field definitions in the DDL is the same as the order of numbers. In addition to the DDL, the change message of the newly created table also contains object_id. If the statement is created using another table as a template: create tb_1 as tb_2, the field metadata of tb_2 can be used as the metadata of tb_1.

[0040] Step 4: Dynamically update the data dictionary based on table structure change information like Figure 4 As shown, according to the table structure change information obtained in the previous step, the structure change event is published, the dictionary maintainer registers the corresponding change event handler in advance, follows up the received change event, triggers the corresponding change processing logic, and merges the original data dictionary with the newly received change data to obtain a dynamically updated new data dictionary.

[0041] 1) Convert the table structure change information obtained after the previous analysis into a structure change event. Assume that the event is for adding a new field COL_1 to table A. 2) Publish structure change events to the dictionary maintainer; 3) The dictionary maintainer registers a table field new event handler in advance, and triggers the corresponding processing flow due to receiving the table new field event; 4) Find the dictionary data corresponding to the table ID of the event from the current dictionary data, and merge it with the data of the change event. Assuming that the event of adding field COL_1 to table A in step 1) above, find the field information set of table A and add the metadata of field COL_1 to it; 5) The merged dictionary data structure is the new data dictionary, and the dictionary maintainer loops and waits for new change events to arrive.

[0042] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. A dynamic data dictionary acquisition method based on Oracle incremental data, characterized in that: The method comprises: S1. Constructing the initial data dictionary structure: querying the mapping relationship between table name and table ID, and the mapping relationship between table field number and field name through the metadata table of the database, and constructing the initial data dictionary structure; S2. Collect Oracle incremental data and extract incremental DDL redo statements: Collect Oracle incremental data through the SQL interface of the logminer component and extract incremental DDL redo statements; S3. Parsing table structure change information according to the incremental DDL redo statement: The incremental DDL redo statement obtained in the previous step is parsed using a SQL syntax parser to obtain table structure change information expressed by the incremental DDL redo statement; S4. Dynamically update the data dictionary according to the table structure change information: According to the table structure change information obtained in the previous step, a structure change event is published. The dictionary maintainer receives the structure change event, merges the original data dictionary with the newly received change data, and obtains a dynamically updated new data dictionary.

2. The method for collecting dynamic data dictionary based on Oracle incremental data according to claim 1, characterized in that: The construction of the initial data dictionary structure in step S1 refers to constructing a map data structure for storing dictionary data, wherein the key is the table ID and the value is the structure of the table metadata.

3. The method for collecting dynamic data dictionary based on Oracle incremental data according to claim 2, characterized in that: The structure of the table metadata includes a mapping structure of a schema name, a table name, a field number, and a field name.

4. The method for collecting dynamic data dictionary based on Oracle incremental data according to claim 1, characterized in that: Step S2 includes: S21. Add redo log; S22. Add online data dictionary option and perform Oracle incremental data mining; S23. Query and extract incremental DDL redo statements from the mining result view.

5. The method for collecting dynamic data dictionary based on Oracle incremental data according to claim 1, characterized in that: The table structure change information in step S3 includes field addition, deletion, and modification information.

6. The method for collecting dynamic data dictionary based on Oracle incremental data according to claim 5, characterized in that: The change of the corresponding field number after adding or deleting the field is as follows: (1) The number of the newly added field is the maximum number of the current table field plus 1; (2) Deleting a field will cause the number of the fields following the deleted field to be reduced by 1.

7. The method for collecting dynamic data dictionary based on Oracle incremental data according to claim 1, characterized in that: Step S3 includes: S31. Use the SQL parser to parse the incremental DDL redo statement obtained in the previous step; S32. Get the table structure change information expressed by the incremental DDL redo statement; S33 determines whether the table structure change information is a field change, if so, construct the field change information and return, if not, proceed to the next step; S34. Determine whether the table structure change information is a table change. If so, construct the table change information and return it. If not, return it directly.

8. The method for collecting dynamic data dictionary based on Oracle incremental data according to claim 1, characterized in that: Step S4 includes: S41. Convert the table structure change information obtained in the previous step into a structure change event; S42. Publish the structure change event to the dictionary maintainer; S43 dictionary maintainer pre-register the corresponding change event handler, receiving the structure change event triggers the corresponding change process; S44. Find the dictionary data corresponding to the event table ID from the current dictionary data and merge it with the change event data; S45. The merged dictionary data structure is the new data dictionary, and the dictionary maintainer loops and waits for new change events to arrive.

9. A dynamic data dictionary collection system based on Oracle incremental data, characterized in that: When the collection system is running, the steps of the dynamic data dictionary collection method based on Oracle incremental data described in any one of claims 1 to 8 are implemented.

10. A computer-readable storage medium having a computer program stored thereon, wherein the program, when executed by a processor, implements the steps of the method for collecting a dynamic data dictionary based on Oracle incremental data according to any one of claims 1 to 8.

Images