Tablespace Migration Method, Apparatus, Electronic Device, and Computer-Readable Storage Medium

Abstract

An embodiment of the present application provides a method, device, electronic device, and computer-readable storage medium for migrating a tablespace, relating to the field of databases. The method includes: creating a target table with the same table structure as the source table in the target tablespace, then generating a DDL row log of the source table through the data definition language (DDL) row log function of the source table, where the DDL row log is used to record data manipulation language (DML) operations on the source table, and further storing first table data in the source table into the target table based on a snapshot of the source table, and storing second table data in the source table into the target table based on the DDL row log, and determining the table name of the target table based on the source table. The embodiment of the present application realizes online tablespace migration in a database, which not only ensures the consistency and integrity of the migrated table data, but also does not affect the normal use of the application program.

Description

Technical Field

[0001] The present application relates to the field of database technologies. Specifically, the present application relates to a method, device, electronic device, and computer-readable storage medium for migrating a tablespace. Background Art

[0002] With the advent of the big data era, databases play an increasingly important role in the field of computers. During the use of databases, it is often necessary to migrate data tables in one tablespace to another tablespace for various reasons, which is called tablespace migration.

[0003] Existing tablespace migrations are generally implemented in two ways. One way is to lock the relevant data tables before migrating the table data, and then directly copy the table data to the target table. The other way is to create a trigger in the data table to be migrated, and synchronize the new data through the trigger after copying the original data to achieve a complete tablespace migration.

[0004] In practical applications, if the first method is adopted to lock the data table, it will affect the normal operation of the application. If the database does not support the data table migration command, it is necessary to offline the database first, and then complete the tablespace migration by creating a new table. This is an offline tablespace migration method, which will have a great impact on the online database and is too inefficient. If the second method is adopted, there are two main limiting conditions, that is, there must be a primary key in the data table to be migrated and at least three new triggers must be created. This method has too many restrictions on the data table and cannot achieve tablespace migration in a large range. Summary of the Invention

[0005] The present application provides a method, device, electronic device, and computer-readable storage medium for identifying the migration of a tablespace, which is used to solve the technical problem that existing tablespace migrations must lock tables or use triggers, resulting in too many restrictions.

[0006] In a first aspect, a method for migrating a tablespace is provided. The method includes:

[0007] Create a target table with the same table structure as the source table in the target tablespace;

[0008] Generate DDL row logs of the source table through the data definition language (DDL) row log function of the source table. The DDL row logs are used to record data manipulation language (DML) operations on the source table;

[0009] Store the first table data in the source table into the target table based on the snapshot of the source table;

[0010] Store the second table data in the source table into the target table based on the DDL row logs;

[0011] Determine the table name of the target table based on the source table.

[0012] Preferably, before storing the first table data in the source table into the target table based on the snapshot of the source table, it further includes:

[0013] When promoting the isolation level of the source table to repeatable read, obtain a snapshot of the source table.

[0014] Preferably, after storing the first table data in the source table into the target table based on the snapshot of the source table, it further includes:

[0015] Create an index for the target table based on the index of the source table.

[0016] Preferably, the method further includes:

[0017] Delete the source table, the index of the source table, and the DDL row log of the source table.

[0018] Preferably, storing the second table data in the source table into the target table based on the DDL row log includes:

[0019] Step A: Scan the current DDL row log to obtain the second table data in the source table;

[0020] Step B: Store the second table data into the target table and delete the second table data in the DDL row log;

[0021] Step C: When it is detected that there is an update in the DDL row log, obtain the updated DDL row log;

[0022] Step D: Use the updated DDL row log as the current DDL row log, and repeat Steps A to D until there is no update in the current DDL row log.

[0023] In a second aspect, a migration device for a tablespace is provided, and the device includes:

[0024] A creation module, configured to create a target table with the same table structure as the source table in the target tablespace;

[0025] A first start module, configured to generate a DDL row log of the source table through the data definition language DDL row log function of the source table, and the DDL row log is used to record data manipulation language DML operations on the source table;

[0026] A first table data module, configured to store the first table data in the source table into the target table based on the snapshot of the source table;

[0027] A second table data module, configured to store the second table data in the source table into the target table based on the DDL row log;

[0028] A determination module, configured to determine the table name of a target table based on a source table.

[0029] Preferably, before the first table data module, it further includes: a second startup module;

[0030] The second startup module is configured to obtain a snapshot of the source table when promoting the isolation level of the source table to repeatable read.

[0031] Preferably, after the first table data module, it further includes: an index module;

[0032] The index module is configured to create an index of the target table based on the index of the source table.

[0033] Preferably, the apparatus further includes: a deletion module;

[0034] The deletion module is configured to delete the source table, the index of the source table, and the DDL row log of the source table.

[0035] Preferably, the second table data module includes:

[0036] A first processing sub-module, configured to scan the current DDL row log to obtain second table data in the source table;

[0037] A second processing sub-module, configured to store the second table data into the target table and delete the second table data in the DDL row log;

[0038] A third processing sub-module, configured to obtain the updated DDL row log when detecting that there is an update in the DDL row log;

[0039] Use the updated DDL row log as the current DDL row log, and repeatedly call the first processing sub-module, the second processing sub-module, and the third processing sub-module until there is no update in the current DDL row log.

[0040] In a third aspect, an electronic device is provided, and the electronic device includes:

[0041] One or more processors;

[0042] A memory;

[0043] One or more applications, where the one or more applications are stored in the memory and are configured to be executed by the one or more processors, and the one or more programs are configured to: execute the table space migration method shown in the first aspect of this application.

[0044] In a fourth aspect, a computer-readable storage medium is provided, and a computer program is stored on the computer-readable storage medium, and when the program is executed by a processor, it implements the table space migration method shown in the first aspect of this application.

[0045] Applying the method for migrating a tablespace provided by an embodiment of the present application, a target table with the same table structure as the source table is created in the target tablespace, and then a DDL row log of the source table is generated through the data definition language (DDL) row log function of the source table. The DDL row log is used to record data manipulation language (DML) operations on the source table. Furthermore, based on the snapshot of the source table, the first table data in the source table is stored in the target table, and based on the DDL row log, the second table data in the source table is stored in the target table, and the table name of the target table is determined based on the source table.

[0046] The method of automatically recording the changed data of the source table during the tablespace migration through the DDL row log and combining the snapshot of the source table to migrate the original data and the changed data of the source table online to the target table overcomes the technical problems that the existing tablespace migration requires table locking or relies on triggers, resulting in excessive restrictions and low efficiency in tablespace migration. Thus, the technical effect of automatically and online migrating the tablespace inside the database while ensuring the accuracy and integrity of the migrated table data is achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] In order to more clearly illustrate the technical solutions in the embodiments of the present application, the following will briefly introduce the drawings required for description in the embodiments of the present application.

[0048] Figure 1 It is a schematic flowchart of a method for migrating a tablespace provided by an embodiment of the present application;

[0049] Figure 2 It is a schematic flowchart of a method for migrating a tablespace provided by another embodiment of the present application;

[0050] Figure 3 It is a schematic flowchart of a method for migrating a tablespace provided by yet another embodiment of the present application;

[0051] Figure 4 It is a schematic structural diagram of a device for migrating a tablespace provided by an embodiment of the present application;

[0052] Figure 5 It is a schematic structural diagram of an electronic device for migrating a tablespace provided by an embodiment of the present application. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0053] The following details the embodiments of the present application. The examples of the embodiments are shown in the drawings, where the same or similar reference numerals represent the same or similar elements or elements with the same or similar functions from beginning to end. The embodiments described below by referring to the drawings are exemplary and are only used to explain the present application, and should not be construed as a limitation to the present invention.

[0054] Those skilled in the art can understand that, unless specifically stated otherwise, the singular forms "a", "an", "the" and "said" used herein may also include the plural forms. It should be further understood that the term "comprising" used in the specification of this application means the presence of the stated features, integers, steps, operations, elements and / or components, but does not exclude the presence or addition of one or more other features, integers, steps, operations, elements, components and / or their groups. It should be understood that when we say that an element is "connected" or "coupled" to another element, it can be directly connected or coupled to other elements, or there may also be intermediate elements. In addition, the "connection" or "coupling" used herein may include wireless connection or wireless coupling. The term "and / or" used herein includes all or any unit and all combinations of one or more associated listed items.

[0055] To make the objectives, technical solutions and advantages of this application clearer, the following will further describe the embodiments of this application in detail with reference to the accompanying drawings.

[0056] First, several terms related to this application will be introduced and explained:

[0057] Data Definition Language (DDL) is a component of database language, mainly including functions such as creating a database, creating a database table, modifying a database table, deleting a database table, and deleting the content of a data table. The DDL row log is a system temporary table, which adds five fields on the basis of the table structure of the source table, respectively used to record the sequence number, operation type, data before and after the operation, the fields changed in the form of a bitmap, and the transaction number corresponding to the operation.

[0058] Data Manipulation Language (DML) is a component of database language, mainly including operations such as adding, deleting, modifying, and querying a database table.

[0059] A tablespace is a logical division of a database. Several operating system files can form a tablespace. The tablespace uniformly manages the data files in the space. A tablespace can only belong to one database. A database space consists of several tablespaces. All database objects are stored in the specified tablespace.

[0060] A snapshot is a fast read technology for memory based on hardware programming techniques, often used in online data backup and recovery. When an application failure occurs in the storage device or a file is damaged, fast data recovery can be performed to restore the data to the state at a certain available time point. A database snapshot is a view of the database at a certain point in time. When using a snapshot for database recovery, only the pages that have changed are restored to the source database, and this speed will undoubtedly be much higher than the backup recovery method.

[0061] Repeatable Read (RR) is one of the four isolation levels defined by the database standard, that is, queries within the same transaction are consistent with the start time of the transaction.

[0062] Snapshot Isolation (SI) is an isolation level of the database. Under snapshot isolation, three read anomalies, namely dirty reads, non-repeatable reads, and phantom reads, will not occur, and read operations will not be blocked.

[0063] In an existing application scenario, for tablespace migration, the source table is locked, and the source table data is migrated to the target table using the command to migrate the database table. Or after locking the table, the database is taken offline, and the data migration is completed through import and export operations after going offline, and the table name is changed. In this scenario, the database tablespace migration cannot be completed online, and the application program needs to be aborted during the migration process, with relatively low efficiency and affecting the user experience.

[0064] In another existing application scenario, tablespace migration is achieved through triggers. After creating a new table in the target tablespace, at least three triggers are created in the source table. The data is copied from the source table to the target table, and during the copying process, the new DML operations are updated to the target table through the created triggers, and then the table name is changed and the source table data is cleaned up. In this scenario, a primary key must exist in the source table, and multiple triggers need to be created. There are too many limiting conditions for tablespace migration, and this method cannot be widely used for tablespace migration operations.

[0065] The tablespace migration method, device, electronic device, and computer-readable storage medium provided by this application aim to solve the above technical problems in the prior art.

[0066] The technical solutions of this application and how the technical solutions of this application solve the above technical problems will be described in detail below with specific embodiments. These specific embodiments can be combined with each other, and the same or similar concepts or processes may not be repeated in some embodiments. The embodiments of this application will be described below with reference to the accompanying drawings.

[0067] A tablespace migration method is provided in the embodiments of this application, as Figure 1As shown, the method includes:

[0068] Step S101, create a target table in the target tablespace with the same table structure as the source table.

[0069] Create a data table in the target tablespace with exactly the same table structure as the source table that needs to perform data migration. This data table is the target table. Specifically, the table structure of the target table is the same as that of the source table. The table name of the target table can be a special table name. For example, name the table name of the target table with a special prefix or suffix to facilitate matching with the source table in the database and distinguishing it from other data tables in the database. At this time, no index is established for the target table.

[0070] Step S102, generate the DDL row log of the source table through the data definition language DDL row log function of the source table, and the DDL row log is used to record the data manipulation language DML operations for the source table.

[0071] Enable the data definition language DDL row log function of the source table, generate the DDL row log of the source table and start recording the data manipulation language DML operations of the source table from the moment of enabling. The data definition language DDL row log is a system temporary table, which adds five fields such as SEQNO$$, DMLTYPE$$, OLD_FLAG$$, CHANGE_MASK$$, XID$$ on the basis of the table structure of the source table, which are used to record the sequence number, DML operation type, data before and after the operation, fields changed in the form of a bitmap, and the transaction number corresponding to the operation respectively. The DDL row log is implemented internally in storage. After the normal business row operation is completed, if it is judged that the data table has enabled the DDL row log function, then call the corresponding DDL row log operation interface to record the relevant data in the corresponding row log. Specifically, the DDL row log operation interface can include three general entry interfaces: ddl_rlog_insert, ddl_rlog_update, and ddl_rlog_delete. Record the relevant data of the insertion, update, and deletion of the data table into the row log by calling the interface. It should be noted that only examples are given here for the names of the above fields and interfaces, and no strict restrictions are imposed.

[0072] Step S103, store the first table data in the source table into the target table based on the snapshot of the source table.

[0073] Obtain the snapshot data of the source table, and copy the first table data obtained based on the obtained snapshot data to the target table. During this process, the DML operations on the source table are not affected and proceed as normal. The relevant DML operation records are in the DDL row log of the source table, and the copy process does not record physical logs. The first table data refers to the original table data of the source table, that is, the table data not recorded in the DDL row log. By means of snapshot, the first table data in the source table can be indirectly obtained without affecting the source table.

[0074] Step S104, store the second table data in the source table into the target table based on the DDL row log.

[0075] Scan the DDL row log of the source table to obtain the relevant DML operation data recorded in the DDL row log after the DDL row log function of the source table is enabled. Obtain the second table data in the source table according to the scanned DDL row log storing the DML operation data, execute the DDL row log to copy the second table data in the source table to the target table. After the execution is completed, delete the table data that has been copied to the target table in the DDL row log of the source table. The second table data refers to the updated data of the source table, which is distinguished from the original data of the source table obtained by snapshot. Execute the DDL row log of the source table with recorded DML operations to store the second table data of the source table into the target table.

[0076] Detect whether there is an update in the DDL row log. When it is detected that there is an update in the DDL row log, continue to scan the current DDL row log, and repeat the above process to copy the second table data to the target table until there is no update in the DDL row log of the source table and the table data of the source table has been completely migrated to the target table.

[0077] In other words, in this embodiment, the above operation of scanning and executing the DDL row log of the source table is performed at least once until all the DDL row logs of the source table are executed, that is, stop when it is detected that there is no update in the DDL row log.

[0078] Step S105, determine the table name of the target table based on the source table.

[0079] After all the DDL row logs of the source table are processed, immediately lock the source table and the target table. At this time, the table data has been migrated. The lock table operation is for the subsequent operations of table space migration, including determining the table name of the target table based on the table name of the source table. Specifically, first change the table name of the source table to a standard table name other than the table names of the source table and the target table, and then replace the special table name of the target table with the table name of the source table before table space migration.

[0080] Apply the method for migrating a tablespace provided by an embodiment of the present application. Create a target table with the same table structure as the source table in the target tablespace, and then generate a DDL row log of the source table through the data definition language (DDL) row log function of the source table. The DDL row log is used to record data manipulation language (DML) operations on the source table. Furthermore, store the first table data in the source table into the target table based on the snapshot of the source table, and store the second table data in the source table into the target table based on the DDL row log. Determine the table name of the target table based on the source table.

[0081] The method of automatically recording the changed data of the source table during the tablespace migration through the DDL row log and combining the snapshot of the source table to migrate the original data and changed data of the source table to the target table online overcomes the technical problems that the existing tablespace migration requires table locking or relies on triggers, resulting in excessive restrictions and low efficiency in tablespace migration. Thus, it achieves the technical effect of automatically migrating the tablespace online inside the database while ensuring the accuracy and integrity of the migrated table data.

[0082] Another possible implementation manner is provided in an embodiment of the present application, as Figure 2 shown, including:

[0083] Step S201, create a target table with the same table structure as the source table in the target tablespace.

[0084] Create a data table with exactly the same table structure as the source table that needs to migrate data in the target tablespace. This data table is the target table. Specifically, the table structure of the target table is the same as that of the source table. The table name of the target table can be a special table name. For example, name the table name of the target table with a special prefix or suffix to facilitate matching with the source table in the database and differentiating it from other data tables in the database. At this time, no index is established for the target table to reduce the system load and speed up the execution process.

[0085] Step S202, generate a DDL row log of the source table through the data definition language (DDL) row log function of the source table, and the DDL row log is used to record data manipulation language (DML) operations on the source table.

[0086] Enable the data definition language (DDL) row log function for the source table, generate the DDL row log for the source table, and start recording data manipulation language (DML) operations on the source table starting from the moment of enabling. The DDL row log is a system temporary table that adds five fields, such as SEQNO$$, DMLTYPE$$, OLD_FLAG$$, CHANGE_MASK$$, and XID$$, to the table structure of the source table, which are used to record the sequence number, DML operation type, data before and after the operation, fields changed in the form of a bitmap, and the transaction number corresponding to the operation, respectively. The DDL row log is implemented internally in storage. After a normal business row operation is completed, if it is determined that the DDL row log function is enabled for the data table, the corresponding DDL row log operation interface is called to record the relevant data in the corresponding row log. Specifically, the DDL row log operation interface can include three general entry interfaces: ddl_rlog_insert, ddl_rlog_update, and ddl_rlog_delete. The relevant data for inserting, updating, and deleting the data table is recorded in the row log by calling the interface. It should be noted that the names of the above fields and interfaces are only examples here and are not strictly restricted.

[0087] In step S203, when promoting the isolation level of the source table to repeatable read, obtain a snapshot of the source table.

[0088] When the source table enables the repeatable read isolation level, based on the repeatable read isolation level, the original data of the source table and the incremental data after enabling the DDL row log can be distinguished, effectively ensuring the correctness of concurrent data reading. When promoting the isolation level of the source table to the repeatable read isolation level, obtain a snapshot of the source table. The repeatable read isolation level is one of the four isolation levels defined by the database standard, that is, queries within the same transaction are consistent at the moment when the transaction starts. Therefore, obtaining a snapshot of the source table is not affected by the DML operations of the source table.

[0089] In step S204, store the first table data in the source table into the target table based on the snapshot of the source table.

[0090] Obtain the snapshot data of the source table, and copy the first table data obtained based on the obtained snapshot data to the target table. During this process, the DML operations on the source table are not affected and proceed as normal. The relevant DML operations are recorded in the DDL row log of the source table, and the copy process does not record physical logs. The first table data refers to the original table data of the source table, that is, the table data not recorded in the DDL row log. By means of the snapshot, the first table data in the source table can be indirectly obtained without affecting the source table.

[0091] In step S205, create an index for the target table based on the index of the source table.

[0092] Create the indexes of the target table according to the index definitions of the source table. Database indexes are internal implementation technologies of relational database management systems and fall within the scope of the internal schema. They can quickly locate the content to be queried. One or more indexes can be created on the base table to provide multiple access paths and speed up the search. In this embodiment, after the snapshot data copy of the source table is completed, create the indexes of the target table and the table constraint definitions according to the index definitions of the source table. This process does not record physical logs, reducing the system load.

[0093] Step S206, store the second table data in the source table into the target table based on the DDL row log.

[0094] Scan the DDL row log of the source table to obtain the relevant DML operation data recorded in the DDL row log after the DDL row log function of the source table is enabled. Obtain the second table data in the source table according to the scanned DDL row log storing the DML operation data. Execute this DDL row log to copy the second table data in the source table to the target table. After the execution is completed, delete the table data that has been copied to the target table in the DDL row log of the source table. The second table data refers to the updated data of the source table, which is distinguished from the original data of the source table obtained through the snapshot. Execute the DDL row log of the source table recording the DML operation to store the second table data of the source table into the target table.

[0095] Detect whether there is an update in the DDL row log. When it is detected that there is an update in the DDL row log, continue to scan the current DDL row log and repeat the above process to copy the second table data to the target table until there is no update in the DDL row log of the source table and all the table data of the source table has been completely migrated to the target table.

[0096] In other words, in this embodiment, the above operation of scanning and executing the DDL row log of the source table is performed at least once until all the DDL row logs of the source table are executed, that is, stop when it is detected that there is no update in the DDL row log.

[0097] Step S207, determine the table name of the target table based on the source table.

[0098] After all the DDL row logs of the source table are processed, immediately lock the source table and the target table. At this time, the table data has been migrated. The lock table operation is for the subsequent operations of table space migration, including determining the table name of the target table based on the table name of the source table. Specifically, first change the table name of the source table to a standard table name other than the table names of the source table and the target table, and then replace the special table name of the target table with the table name of the source table before table space migration.

[0099] Step S208, delete the source table, the indexes of the source table, and the DDL row log of the source table.

[0100] After the tablespace migration is completed, delete the source table, the indexes of the source table, the DDL row logs of the source table, and the data information related to the source table in the source tablespace, and release the tablespace occupied by the source table.

[0101] Apply the method for migrating a tablespace provided by the embodiment of the present application. Create a target table with the same table structure as the source table in the target tablespace, and then generate the DDL row logs of the source table through the data definition language (DDL) row log function of the source table. The DDL row logs are used to record the data manipulation language (DML) operations on the source table. When the isolation level of the source table is upgraded to repeatable read, obtain the snapshot of the source table, and then store the first table data in the source table into the target table based on the snapshot of the source table, create the indexes of the target table based on the indexes of the source table, store the second table data in the source table into the target table based on the DDL row logs, determine the table name of the target table based on the source table, and delete the source table, the indexes of the source table, and the DDL row logs of the source table.

[0102] The method of automatically recording the changed data of the source table during the tablespace migration through the DDL row logs and combining the snapshot of the source table to migrate the original data and the changed data of the source table to the target table online overcomes the technical problems that the existing tablespace migration requires table locking or depends on triggers, resulting in excessive restrictions and low efficiency in tablespace migration. Thus, it achieves the technical effect of automatically migrating the tablespace online inside the database while ensuring the accuracy and integrity of the migrated table data.

[0103] In the embodiment of the present application, a possible implementation manner of storing the second table data in the source table into the target table based on the DDL row logs is provided, such as Figure 3 shown, including:

[0104] Step A: Scan the current DDL row logs to obtain the second table data in the source table.

[0105] Step B: Store the second table data into the target table, and delete the second table data in the DDL row logs.

[0106] Step C: When it is detected that there is an update in the DDL row logs, obtain the updated DDL row logs.

[0107] Step D: Use the updated DDL row logs as the current DDL row logs, and repeat Steps A to D until there is no update in the current DDL row logs.

[0108] Scan the DDL row log of the source table to obtain the relevant DML operation data recorded in the DDL row log after the DDL row log function of the source table is enabled. Obtain the second table data in the source table according to the scanned DDL row log storing the DML operation data. Execute the DDL row log to copy the second table data in the source table to the target table. After the execution is completed, delete the table data that has been copied to the target table in the DDL row log of the source table. The second table data refers to the updated data of the source table, which is distinguished from the original data of the source table obtained through the snapshot. Execute the DDL row log of the source table with recorded DML operations to store the second table data of the source table in the target table.

[0109] Detect whether there is an update in the DDL row log. When it is detected that there is an update in the DDL row log, continue to scan the current DDL row log, repeat the above process, and copy the second table data to the target table until there is no update in the DDL row log of the source table and the table data of the source table has been completely migrated to the target table.

[0110] In other words, in this embodiment, the above operation of scanning and executing the DDL row log of the source table is performed at least once until all the DDL row logs of the source table are executed, that is, stop when it is detected that there is no update in the DDL row log.

[0111] Apply a method for migrating a tablespace provided by an embodiment of the present application. Create a target table with the same table structure as the source table in the target tablespace, and then generate the DDL row log of the source table through the data definition language DDL row log function of the source table. The DDL row log is used to record the data manipulation language DML operations for the source table. When the isolation level of the source table is upgraded to repeatable read, obtain the snapshot of the source table, and then store the first table data in the source table in the target table based on the snapshot of the source table, create the index of the target table based on the index of the source table, store the second table data in the source table in the target table based on the DDL row log, determine the table name of the target table based on the source table, and delete the source table, the index of the source table, and the DDL row log of the source table.

[0112] The method of automatically recording the changed data of the source table during the tablespace migration process through the DDL row log and combining the snapshot of the source table to migrate the original data and changed data of the source table to the target table online overcomes the technical problems of the existing tablespace migration that requires table locking or relies on triggers, resulting in too many restrictions and low efficiency in tablespace migration. Thus, it achieves the technical effect of automatically migrating the tablespace online inside the database while ensuring the accuracy and integrity of the migrated table data.

[0113] An embodiment of the present application provides a tablespace migration device, as Figure 4 shown. The tablespace migration device includes:

[0114] Creation module 401, which is used to create a target table with the same table structure as the source table in the target tablespace.

[0115] First startup module 402, which is used to generate the DDL row log of the source table through the data definition language (DDL) row log function of the source table, and the DDL row log is used to record the data manipulation language (DML) operations on the source table.

[0116] First table data module 403, which is used to store the first table data in the source table into the target table based on the snapshot of the source table.

[0117] Second table data module 404, which is used to store the second table data in the source table into the target table based on the DDL row log.

[0118] Determination module 405, which is used to determine the table name of the target table based on the source table.

[0119] In a preferred embodiment of the present application, before the first table data module, it further includes:

[0120] Second startup module, which is used to obtain the snapshot of the source table when promoting the isolation level of the source table to repeatable read.

[0121] In a preferred embodiment of the present application, after the first table data module, it further includes:

[0122] Index module, which is used to create the index of the target table based on the index definition of the source table.

[0123] In a preferred embodiment of the present application, the device further includes:

[0124] Deletion module, which is used to delete the source table, the index of the source table, and the DDL row log of the source table.

[0125] In a preferred embodiment of the present application, the second table data module includes:

[0126] First processing sub-module, which is used to scan the current DDL row log to obtain the second table data in the source table;

[0127] Second processing sub-module, which is used to store the second table data into the target table and delete the second table data in the DDL row log;

[0128] Third processing sub-module, which is used to obtain the updated DDL row log when it is detected that the DDL row log has been updated;

[0129] Use the updated DDL row log as the current DDL row log, and repeatedly call the first processing sub-module, the second processing sub-module, and the third processing sub-module until there is no update to the current DDL row log.

[0130] Apply a table space migration device provided by an embodiment of the present application. Create a target table with the same table structure as the source table in the target table space, and then generate a DDL row log of the source table through the data definition language (DDL) row log function of the source table. The DDL row log is used to record data manipulation language (DML) operations on the source table. When the isolation level of the source table is upgraded to repeatable read, obtain a snapshot of the source table, and then store the first table data in the source table into the target table based on the snapshot of the source table, create an index of the target table based on the index of the source table, store the second table data in the source table into the target table based on the DDL row log, determine the table name of the target table based on the source table, and delete the source table, the index of the source table, and the DDL row log of the source table.

[0131] A method for automatically recording the changed data of the source table during the table space migration through the DDL row log and combining it with the snapshot of the source table to migrate the original data and changed data of the source table to the target table online overcomes the technical problems of the existing table space migration that requires locking the table or relying on triggers, resulting in excessive restrictions and low efficiency in table space migration. Thus, it achieves the technical effect of automatically migrating the table space online inside the database while ensuring the accuracy and integrity of the migrated table data.

[0132] An embodiment of the present application provides an electronic device, which includes: a memory and a processor; at least one program stored in the memory and used to be executed by the processor. Compared with the prior art, it can achieve: a method for automatically recording the changed data of the source table during the table space migration through the DDL row log and combining it with the snapshot of the source table to migrate the original data and changed data of the source table to the target table online overcomes the technical problems of the existing table space migration that requires locking the table or relying on triggers, resulting in excessive restrictions and low efficiency in table space migration. Thus, it achieves the technical effect of automatically migrating the table space online inside the database while ensuring the accuracy and integrity of the migrated table data.

[0133] In an optional embodiment, an electronic device is provided, as Figure 5 shown Figure 5The electronic device 5000 shown includes: a processor 5001 and a memory 5003. Among them, the processor 5001 and the memory 5003 are connected, such as being connected through a bus 5002. Optionally, the electronic device 5000 may further include a transceiver 5004, and the transceiver 5004 can be used for data interaction between this electronic device and other electronic devices, such as data transmission and / or data reception, etc. It should be noted that in practical applications, the transceiver 5004 is not limited to one, and the structure of the electronic device 5000 does not constitute a limitation on the embodiments of the present application.

[0134] The processor 5001 can be a CPU (Central Processing Unit, central processing unit), a general-purpose processor, a DSP (Digital Signal Processor, data signal processor), an ASIC (Application Specific Integrated Circuit, application-specific integrated circuit), an FPGA (Field Programmable Gate Array, field programmable gate array), or other programmable logic devices, transistor logic devices, hardware components, or any combination thereof. It can implement or execute various exemplary logic blocks, modules, and circuits described in connection with the disclosure of the present application. The processor 5001 can also be a combination that implements computing functions, such as a combination including one or more microprocessors, a combination of a DSP and a microprocessor, etc.

[0135] The bus 5002 may include a path for transmitting information between the above components. The bus 5002 can be a PCI (Peripheral Component Interconnect, peripheral component interconnect standard) bus or an EISA (Extended Industry Standard Architecture, extended industry standard architecture) bus, etc. The bus 5002 can be divided into an address bus, a data bus, a control bus, etc. For the sake of representation, Figure 5 only a thick line is used to represent it in the figure, but it does not mean that there is only one bus or one type of bus.

[0136] The memory 5003 may be a ROM (Read Only Memory), or other types of static storage devices that can store static information and instructions, a RAM (Random Access Memory), or other types of dynamic storage devices that can store information and instructions. It may also be an EEPROM (Electrically Erasable Programmable Read Only Memory), a CD-ROM (Compact Disc Read Only Memory), or other optical disc storage, optical disc storage (including compact discs, laser discs, optical discs, digital versatile discs, Blu-ray discs, etc.), magnetic disk storage media, or other magnetic storage devices, or any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed by a computer, but is not limited thereto.

[0137] The memory 5003 is used to store the application program code for executing the solution of this application, and is controlled by the processor 5001 for execution. The processor 5001 is used to execute the application program code stored in the memory 5003 to implement the content shown in the foregoing method embodiments.

[0138] The embodiments of this application provide a computer-readable storage medium, on which a computer program is stored. When it runs on a computer, it enables the computer to execute the corresponding content in the foregoing method embodiments. Compared with the prior art, the method of automatically recording the changed data of the source table during the table space migration process through the DDL row log and combining it with the snapshot of the source table, and migrating the original data and changed data of the source table to the target table online overcomes the technical problems that the existing table space migration requires locking the table or relying on triggers, resulting in too many restrictions and low efficiency in table space migration. Thus, the technical effect of ensuring the accuracy and integrity of the migrated table data while automatically migrating the table space online inside the database is achieved.

[0139] It should be understood that although the steps in the flowchart of the accompanying drawings are shown sequentially according to the indication of the arrows, these steps are not necessarily executed sequentially according to the order indicated by the arrows. Unless there is a clear indication in this article, the execution of these steps has no strict order limitation, and they can be executed in other orders. Moreover, at least a part of the steps in the flowchart of the accompanying drawings may include multiple sub-steps or multiple stages. These sub-steps or stages are not necessarily executed at the same moment, but can be executed at different moments, and their execution order is not necessarily sequential, but can be executed alternately or in turn with at least a part of other steps or sub-steps or stages of other steps.

[0140] The above are only some embodiments of the present invention. It should be noted that for those of ordinary skill in the art, without departing from the principle of the present invention, several improvements and refinements can be made, and these improvements and refinements should also be regarded as the protection scope of the present invention.

Claims

1. A method for migrating a tablespace, characterized in that, including: create a target table in the target tablespace with the same table structure as the source table; generate the DDL row log of the source table through the data definition language (DDL) row log function of the source table, where the DDL row log is used to record data manipulation language (DML) operations on the source table; the DDL row log is a system temporary table, and the system temporary table adds five fields for recording sequence numbers, DML operation types, data before and after operations, changes recorded in bitmap form, and transaction numbers corresponding to operations on the basis of the table structure of the source table; store the first table data in the source table into the target table based on the snapshot of the source table; determine the second table data in the source table based on the DML operation data recorded in the DDL row log, and copy the second table data to the target table; determine the table name of the target table based on the source table.

2. The method for migrating a tablespace according to claim 1, characterized in that, Before storing the first table data in the source table into the target table based on the snapshot of the source table, it further includes: when promoting the isolation level of the source table to repeatable read, obtain the snapshot of the source table.

3. The method for migrating a tablespace according to claim 1, characterized in that, After storing the first table data in the source table into the target table based on the snapshot of the source table, it further includes: create an index for the target table based on the index of the source table.

4. The method for migrating a tablespace according to claim 1, characterized in that, The method further includes: delete the source table, the index of the source table, and the DDL row log of the source table.

5. The method for migrating a tablespace according to claim 1, characterized in that, Determining the second table data in the source table based on the DML operation data recorded in the DDL row log and copying the second table data to the target table includes: Step A, scan the current DDL row log, and obtain the second table data in the source table according to the DML operation data recorded in the DDL row log; Step B, copy the second table data to the target table, and delete the second table data in the DDL row log; Step C, when it is detected that the DDL row log has an update, obtain the updated DDL row log; Step D, use the updated DDL row log as the current DDL row log, and repeat steps A to D until there is no update in the current DDL row log.

6. A tablespace migration device, characterized in that, including: a creation module for creating a target table in the target tablespace with the same table structure as the source table; a first startup module for generating the DDL row log of the source table through the data definition language (DDL) row log function of the source table, where the DDL row log is used to record data manipulation language (DML) operations on the source table; the DDL row log is a system temporary table, and the system temporary table adds five fields for recording sequence numbers, DML operation types, data before and after operations, changes recorded in bitmap form, and transaction numbers corresponding to operations on the basis of the table structure of the source table; a first table data module for storing the first table data in the source table into the target table based on the snapshot of the source table; a second table data module for determining the second table data in the source table based on the DML operation data recorded in the DDL row log and copying the second table data to the target table; A determination module, configured to determine the table name of the target table based on the source table.

7. The tablespace migration device according to claim 6, characterized in that, Before the first table data module, it further includes: a second startup module; The second startup module is configured to obtain a snapshot of the source table when promoting the isolation level of the source table to repeatable read.

8. The tablespace migration device according to claim 6, characterized in that, After the first table data module, it further includes: an index module; The index module is configured to create an index for the target table based on the index definition of the source table.

9. The tablespace migration device according to claim 6, characterized in that, The device further includes: a deletion module; The deletion module is configured to delete the source table, the index of the source table, and the DDL row log of the source table.

10. The migration device for a tablespace according to claim 6, wherein, The second table data module includes: A first processing sub-module, configured to scan the current DDL row log, and obtain second table data in the source table according to the DML operation data recorded in the DDL row log; A second processing sub-module, configured to copy the second table data to the target table, and delete the second table data in the DDL row log; A third processing sub-module, configured to obtain an updated DDL row log when detecting that the DDL row log has an update; Taking the updated DDL row log as the current DDL row log, and repeatedly calling the first processing sub-module, the second processing sub-module, and the third processing sub-module until there is no update in the current DDL row log.

11. An electronic device, wherein, The electronic device includes: One or more processors; A memory; One or more applications, where the one or more applications are stored in the memory and configured to be executed by the one or more processors, and the one or more programs are configured to: execute the table space migration method according to any one of claims 1 to 5.

12. A computer-readable storage medium having a computer program stored thereon, wherein, When the computer program is executed by a processor, it implements the table space migration method according to any one of claims 1 to 5.

Images