Method and system for deleting CLOB data in database based on recovery protection mechanism

Abstract

According to the method and the system for deleting the CLOB data in the database based on the recovery protection mechanism, a recovery interval is set in a data deletion process, and a dual-stage protection strategy and an asynchronous recovery mechanism are introduced to carry out real-time protection on deletion operation, so that the data security is effectively ensured. Especially in the aspects of recovery interval setting and a rollback mechanism, the system can transfer the data blocks into a read-only state and store the data blocks in an isolation area before deletion, it is ensured that even if abnormal or misoperation occurs, data can be recovered through the rollback mechanism, and therefore data loss is effectively avoided. And the recovery operation does not block other read-write operations of the database, so that the stability and the high efficiency of the system are ensured.

Description

Technical Field

[0001] The present invention relates to the field of data processing, and in particular to a method and system for deleting CLOB data in a database based on a recycling protection mechanism. Background Art

[0002] In the process of digital transformation of enterprises, databases have become the core of information storage and management. In particular, Oracle databases are widely used in various industries due to their powerful functions and high availability, and are responsible for processing large amounts of structured and unstructured data. Among these data types, CLOB (Character Large Object) type fields are used to store large amounts of text information, such as documents, articles, logs, configuration files, long text data, etc. As business develops, the scale of CLOB data continues to grow, which poses unprecedented challenges to database management and maintenance, especially in terms of data deletion and resource recovery.

[0003] Deleting CLOB data is usually a complex task in database management, especially when the data volume is very large. Most traditional CLOB deletion methods rely on directly executing SQL DELETE statements. Although this method is simple, it has many shortcomings when deleting large amounts of data, which puts significant pressure on database performance and system stability. First, the deletion of CLOB data by the DELETE statement is a synchronous operation, which may cause long-term locking of other operations in the database and affect the concurrency of the system. When deleting CLOB data, if the data volume is large, it may cause table locking, thereby blocking other database operations and affecting the response speed and processing capacity of the database.

[0004] Secondly, CLOB data usually contains a lot of text content, and deleting this data will generate high-frequency disk I / O operations. When the database executes a DELETE statement, the disk needs to read and write data frequently, which not only consumes a lot of system resources, but also causes a surge in database I / O pressure, greatly affecting other ongoing data operations and queries. This excessive I / O consumption not only reduces database performance, but also increases the hardware burden, which may cause the database server's response time to increase and the system load to soar.

[0005] Third, the traditional CLOB deletion method generates a large amount of transaction logs. The DELETE operation records all deletion operation information in the transaction log to ensure the atomicity and recoverability of the transaction. However, when deleting a large amount of CLOB data, the amount of log records will increase dramatically. A large number of transaction logs will not only consume more storage space, but also increase the log management burden of the database. Over time, the accumulation of log files may cause the database space to be quickly exhausted and increase the complexity of backup and recovery, which in turn affects the availability and stability of the entire database system.

[0006] In addition to performance challenges, the traditional method of deleting CLOB data also has some risks that cannot be ignored. For example, misoperation or system failure may cause data loss or deletion failure. In these cases, it often takes a long time to recover the accidentally deleted data, and the recovery process may cause additional pressure on the database. In order to reduce the risk of data loss, database management systems often need to implement more complex fault tolerance and rollback mechanisms, which further increases the complexity of the system.

[0007] In order to solve these problems, some optimization solutions have been proposed in the prior art, but most of the methods still have limitations. The traditional deletion method generally processes the entire CLOB field uniformly, and cannot be flexibly adjusted and optimized according to the characteristics of the data. Therefore, how to design an innovative method that can improve the efficiency of CLOB data deletion, reduce system burden and improve fault tolerance has become a difficult problem that needs to be solved in the field of database technology. Summary of the invention

[0008] In order to solve the above problems, the present invention proposes a method and system for deleting CLOB data in a database based on a recycling protection mechanism, which not only solves the performance bottleneck problem encountered by traditional data deletion methods in a big data environment, but also improves the controllability and reliability of the CLOB data deletion process. These innovations not only improve the performance of the Oracle database technically, but also provide a new solution for deleting CLOB data in similar database systems.

[0009] The method for deleting CLOB data in a database based on a recycling protection mechanism proposed by the present invention comprises the following steps:

[0010] (1) Split the CLOB data into data blocks;

[0011] (2) deleting the segmented data blocks in chunks;

[0012] (3) Regularly manage tablespace;

[0013] (4) Monitor and handle abnormalities that occur during operation.

[0014] Wherein, the step (1) specifically comprises:

[0015] (1.1) Identify the table containing the CLOB field and the corresponding data volume;

[0016] (1.2) Design a partitioning strategy based on the data size and divide the CLOB data into multiple data blocks;

[0017] (1.3) Generate a characteristic field index for the CLOB field and identify the target data file to be deleted.

[0018] Wherein, the step (2) specifically includes:

[0019] (2.1) Call the cyclic scheduling task to delete the target data file in blocks;

[0020] (2.2) When performing a deletion operation on the data block, a recycling interval is set to ensure data security.

[0021] Wherein, the step (3) specifically includes:

[0022] (3.1) Regularly monitor the usage of tablespaces containing CLOB data;

[0023] (3.2) Adjust the frequency parameters of the cyclic scheduling task according to the table space usage to ensure the data

[0024] Database performance;

[0025] (3.3) Reclaim table space.

[0026] Wherein, the step (4) specifically comprises:

[0027] (4.1) Monitor the partition information of CLOB data blocks in real time through the management page;

[0028] (4.2) Used to capture and handle exceptions that occur during the deletion process.

[0029] The present invention also proposes a CLOB data deletion system in a database based on a recycling protection mechanism, comprising:

[0030] Partition module, used to split CLOB data to form data blocks;

[0031] A deletion module is used to delete the segmented data blocks in blocks;

[0032] Management module, used for regular management of tablespace;

[0033] The processing module is used to monitor and process the anomalies that occur during the operation.

[0034] The partition module is used to partition the CLOB data to form data blocks, and specifically includes:

[0035] An identification module is used to identify the table containing the CLOB field and the corresponding data volume;

[0036] The partitioning module is used to design a partitioning strategy based on the data size and to partition the CLOB data into multiple data blocks;

[0037] The index module is used to generate a characteristic field index for the CLOB field and identify the target data file to be deleted.

[0038] The deleting module is used to delete the divided data blocks in blocks, specifically including:

[0039] The block deletion module is used to call the cyclic scheduling task to delete the target data file in blocks;

[0040] The recycling interval module is used to set the recycling interval to ensure data security when performing a deletion operation on the data block.

[0041] The management module is used to regularly manage the table space, specifically including:

[0042] Table space monitoring module, used to regularly monitor the usage of table space containing CLOB data;

[0043] A frequency adjustment module, used to adjust the frequency parameters of the cyclic scheduling task according to the table space usage to ensure database performance;

[0044] Recycling module, used to recycle table space.

[0045] The processing module is used to monitor and process abnormalities that occur during the operation, and specifically includes:

[0046] The partition monitoring module is used to monitor the partition information of CLOB data blocks in real time through the management page; the exception handling module is used to capture and handle exceptions that occur during the deletion process.

[0047] In summary, the CLOB data deletion method and system of the present invention can significantly improve the efficiency and security of large-scale data deletion operations in Oracle databases by partitioning CLOB data and dividing it into multiple data blocks. The beneficial effects of the present invention are as follows:

[0048] First, traditional CLOB data deletion operations often require processing a large amount of data at one time, resulting in long-term table locking and excessive I / O consumption. The present invention manages CLOB data in blocks, so that each data block can be deleted independently, thereby greatly reducing the resource usage of each deletion operation and reducing the system's table lock time. This block deletion strategy allows the system to allocate resources among multiple concurrent tasks, greatly improving the concurrency and execution efficiency of operations, especially when facing massive data, it can significantly reduce the time required for deletion operations.

[0049] Secondly, CLOB data will generate a large number of disk I / O operations during the deletion process. Traditional methods will greatly increase I / O pressure when processing large amounts of data, affecting system performance. However, by dividing CLOB data into multiple small data blocks and dynamically managing deletion priorities, the present invention can accurately schedule deletion tasks based on factors such as the size of the data block and access frequency. In this way, the system can avoid generating excessive I / O peaks during the deletion process, effectively disperse the I / O load, and reduce the pressure on the disk and storage during a single operation, thereby maintaining the stability of the database under high load.

[0050] Thirdly, the block deletion strategy of the present invention can avoid long-term database locking and resource occupation by introducing a flexible scheduling mechanism, thereby ensuring high availability of the system. Since each data block can be deleted independently, the system no longer needs to lock the entire data table or table space for a long time when performing the deletion operation, which allows other database operations to be executed in parallel, and the normal operation of other services will not be affected by the deletion of CLOB data.

[0051] In addition, the present invention specially designs a recycling protection mechanism. Traditional deletion methods are often prone to data loss due to user misoperation or system failure. The present invention effectively ensures data security by setting a recycling interval during the deletion process, introducing a two-stage protection strategy, and performing real-time protection on the deletion operation through dynamic monitoring and anomaly detection mechanisms. In particular, in terms of recycling interval setting and rollback mechanism, the system can convert the data block to a read-only state before deletion and store it in an isolation area, ensuring that even if anomalies or misoperations occur, the data can be restored through the rollback mechanism, thereby effectively avoiding data loss.

[0052] Finally, the present invention specifically proposes an asynchronous recovery mechanism for high-frequency CLOB data deletion. Unlike the traditional synchronous recovery mechanism, the asynchronous recovery mechanism allows the system to asynchronously perform space recovery tasks after deleting data, avoiding the slowdown of system performance by synchronous recovery operations. The advantage of this mechanism is that the recovery operation will not block other read and write operations of the database, thereby ensuring the stability and efficiency of the system.

[0053] In summary, the present invention partitions CLOB data and divides it into multiple data blocks, and combines dynamic scheduling, asynchronous recovery, exception monitoring and other technical means to not only greatly improve the efficiency and concurrency of CLOB data deletion operations, but also effectively reduce the I / O load of the system, ensure the high availability and stability of the database system, and improve the security of data operations. These beneficial effects make the present invention have significant advantages and broad application prospects in large-scale data deletion scenarios. BRIEF DESCRIPTION OF THE DRAWINGS

[0054] The drawings described herein are used to provide a further understanding of the present invention and constitute a part of the present application, but do not constitute an improper limitation of the present invention. In the drawings:

[0055] Figure 1 It is a flow chart of the method of the present invention.

[0056] Figure 2 It is a system framework diagram of the present invention. DETAILED DESCRIPTION

[0057] The present invention will be described in detail below in conjunction with the accompanying drawings and specific embodiments, wherein the illustrative embodiments and descriptions are only used to explain the present invention but are not intended to limit the present invention.

[0058] like Figure 1 As shown, the present invention proposes a method for deleting CLOB data in a database based on a recycling protection mechanism. First, (1) the CLOB data is segmented to form data blocks. In an Oracle database, CLOB fields are usually used to store a large amount of text data. As the amount of CLOB data in the database increases, traditional deletion methods will lead to long-term table locking and system performance degradation. Therefore, the present invention improves the efficiency of data deletion by segmenting the CLOB data to form data blocks. Then, (2) the segmented data blocks are deleted in blocks, and (3) the table space is managed regularly, and (4) the anomalies that occur during the operation are monitored and processed. Each step of the present invention has a clear operation process to ensure the efficiency, security and system performance of data deletion.

[0059] In step (1), the CLOB data to be deleted is first obtained by identifying the table containing the CLOB field and its corresponding data volume. The specific operations of this step are preferably: (1.1) identifying the table containing the CLOB field and counting the data volume in the table; then, (1.2) designing a reasonable partitioning strategy according to the scale and nature of the data in the table to divide the CLOB data to be deleted into multiple data blocks; finally, (1.3) generating a characteristic field index according to the characteristics of the CLOB field, so as to more accurately identify the target data file to be deleted, and ensure the efficiency and accuracy of subsequent operations.

[0060] First, in step (1.1), identifying and counting the table containing the CLOB field and its data volume is the core of the method. In order to achieve efficient data identification and processing, this embodiment introduces a distributed data acquisition method in the database architecture, configures an independent data scanning engine on each node, and performs real-time synchronous identification of the table containing the CLOB field through a distributed collaborative algorithm. The scanning engine of each node can obtain the local CLOB data of the node in real time, and ensure the comprehensive synchronization of the data of each node through distributed collaboration. This effectively avoids the problem of excessive load on a single node and improves the real-time and accuracy of data identification. Specifically, the following preferred steps may be included:

[0061] In this embodiment, step (1.1.1) first identifies all tables containing CLOB fields in the database. Each database node is configured with an independent data scanning engine, which is responsible for scanning and processing the data of this node. Each node uses a distributed collaborative algorithm to share the status and results of data collection with other nodes to ensure that all tables containing CLOB fields in the database are identified and processed.

[0062] Next, in step (1.1.2), each node calculates the distribution characteristics of the CLOB field data volume. For example, suppose there are multiple tables containing CLOB fields in an Oracle database, such as "article table", "user comment table" and "log table". Each node scans the table containing the CLOB field and summarizes the total data volume of its CLOB field. The node calculates the CLOB data volume in each table when scanning using the following formula:

[0063]

[0064] Among them, D i,t represents the amount of CLOB data of node i in time interval t, f(CLOB k ) is the kth CLOB field data size function, N is the number of CLOB fields, w k,i,t is a weight factor related to the CLOB field characteristics. Each node will periodically transmit its scan results to the main control module.

[0065] For example, in a node i, three CLOB fields CLOB1, CLOB2, and CLOB3 are detected, with sizes of 10MB, 5MB, and 8MB respectively. The weight factors of access frequency are set to 1.2, 0.8, and 1.0, then: i,j =(10×1.2)+(5×0.8)+(8×1.0)=23.6MB. By calculation, node i determines the total data volume D of the local CLOB field. i,j It is 23.6MB.

[0066] Through this formula, each node can calculate the specific quantitative value of the managed CLOB data according to the size and characteristics of its own data, and prioritize the data according to the actual storage requirements and data access frequency. This process not only improves the accuracy of data volume statistics, but also provides necessary support for subsequent partitioning strategies.

[0067] Finally, in the distributed architecture, the present invention adopts a dynamic separation strategy to optimize the high-load data query of a single node. When the task queue of a node is piled up or the CPU occupancy rate is too high, the priority scheduling algorithm will transfer part of the query tasks to other low-load nodes. The core rule of priority scheduling is: dynamically adjust the priority and allocation strategy of the query task according to the importance of the task (such as the size of the CLOB data volume) and the resource situation of the node. For example: When the CPU load rate of node 1 exceeds 90%, the main control module automatically allocates its subsequent tasks to nodes 2 and 3 until the load of node 1 returns to normal levels. Through the above distributed collaborative algorithm, efficient distribution characteristic calculation formula and dynamic separation strategy, real-time and efficient CLOB data identification and data volume update are achieved. The problems of low efficiency of centralized query and node resource bottleneck are solved, and the performance and stability of large-scale CLOB data management in Oracle database are greatly improved.

[0068] Next, in step (1.2), a reasonable partitioning strategy is designed according to the size and nature of the data in the table to divide the CLOB data to be deleted into multiple data blocks.

[0069] In the embodiment, in order to optimize the efficiency of the block deletion operation, a dynamic time partitioning strategy is adopted to divide the CLOB data into multiple reasonable sub-intervals according to the time field, and calculate the data block size S of each partition. j,t .

[0070] Calculate the total data volume: First, obtain the total data volume D of all nodes i in the current time interval t through the distributed data acquisition module i,t , represents the amount of CLOB data of node i in time interval t. For example, the total amount of data detected in interval t is 100GB.

[0071] Introducing access frequency R i,t :Record the access frequency R of each node through the monitoring system i,t .

[0072] The target block size is calculated according to the formula

[0073]

[0074] Among them, S j,trepresents the target data block size in the current time interval t of partition j; D i,t represents the amount of CLOB data of node i in the partition within the current time interval t; R i,t It represents the data access frequency of node i in the partition in the current time interval t; β is the adjustment coefficient of access frequency to partition size; n is the number of partitions, and L is the number of nodes.

[0075] After that, step (1.3) generates characteristic field indexes for CLOB fields and identifies the target data files to be deleted based on these indexes. The purpose of this step is to generate characteristic field indexes by analyzing various characteristics of CLOB fields, thereby efficiently identifying and selecting data files to be deleted and optimizing the execution of the deletion operation.

[0076] First, the system analyzes and extracts the features of the CLOB field. CLOB fields usually have multiple significant features, such as field length, data storage location, access frequency, last modification time, etc. These features provide the basis for subsequent index generation. During the analysis process, the system mainly extracts features from the following aspects:

[0077] Field length: Counts the storage size of each CLOB field and determines its priority in the data deletion process based on the size of the field. Larger fields usually take up more storage space and may have a greater impact on database performance. Therefore, the system will prioritize larger fields for deletion.

[0078] Access frequency: Analyze the access frequency of each CLOB field within a specified time period, including the number of queries and the frequency of read and write operations. Fields with higher access frequencies indicate that they are frequently used, while data with lower access frequencies may be redundant or outdated data and should be deleted first.

[0079] Last modified time: Record the last modified time of the CLOB field and identify fields that have not been modified for a long time. These fields usually indicate that their contents may be outdated, so deleting them first will not affect business operations.

[0080] Next, the system builds a feature field index based on the feature values ​​extracted above. The key to generating a feature field index is to group CLOB fields with similar features. Specifically, the system groups CLOB fields according to multiple dimensions such as field access frequency, storage space size, and last modification time. For example, data with high access frequency and large storage space may be divided into one group, while data that has not been modified for a long time will be divided into another group. Each group represents a class of CLOB data with similar characteristics, which usually have similar processing requirements and can therefore be deleted in the same batch. The system generates these feature field indexes through dynamic algorithms, which can update field features in real time to adapt to changes in database data.

[0081] Based on the generated characteristic field index, the system can accurately identify the data files to be deleted. By analyzing the index information of each group, the deletion strategy will give priority to field data that meets the deletion conditions. For example, the system may give priority to the following data for deletion:

[0082] Data that has not been modified for a long time: This data is likely no longer used, and deleting it will have little impact on the operation of the database.

[0083] Data that takes up a lot of storage space: CLOB fields that store a lot of redundant or outdated information can significantly free up table space and optimize database storage management once deleted.

[0084] Data that is frequently accessed but no longer needed: Although these fields are frequently accessed, their data content is outdated or duplicated, so they can still be considered for deletion to optimize system response time and performance.

[0085] In addition, the generation of feature field indexes is not a one-time task. As the amount of CLOB data continues to grow, the system needs to dynamically adjust and optimize the feature field indexes to ensure that it can always accurately identify the data to be deleted. In particular, when the access frequency of certain fields increases suddenly or the storage space increases dramatically, the system can update the index in real time and promptly identify new data to be deleted. In order to further improve the accuracy of the deletion strategy, the system can also combine machine learning algorithms to predict future field access trends based on the analysis of historical data, and dynamically adjust the deletion plan based on these predictions. In this way, the system can schedule deletion tasks more intelligently and reduce the impact on database performance.

[0086] In step (2), the deletion operation is specifically performed through the following sub-steps: (2.1) calling a cyclic scheduling task to delete the target data file in blocks; (2.2) when performing the deletion operation, setting a recycling interval to ensure data security and avoid data loss due to operation interruption or system failure.

[0087] In step (2.1), first, the partitions to be deleted are divided into multiple logical groups using the partition metadata of the database, each of which contains several partitions. The priority of group deletion can be determined based on parameters such as the number of partitions in the group, the storage size of the partition, the access weight of the partition, the average creation time of the group, and the resource usage of the group.

[0088] For example, for three groups: Group 1 has a total data volume of 30GB, an average creation time of 5 days, a resource usage of 10 units, and an access weight of 1.5; Group 2 has a total data volume of 20GB, an average creation time of 10 days, a resource usage of 8 units, and an access weight of 2.0; Group 3 has a total data volume of 40GB, an average creation time of 3 days, a resource usage of 12 units, and an access weight of 1.2. Taking into account the impact of various parameters, it is determined that Group 1 has the highest priority, and the system will process Group 1 first.

[0089] After determining the group priority, for the currently prioritized group, the partitions in the group are deleted in batches. The number of partitions B processed in a single batch is determined by the following formula:

[0090]

[0091] Among them, C u is the currently available system resources, S avg is the average size of the partitions in the group, γ is the safety factor, which limits the resource usage ratio of a single task, M is the total number of partitions in the group, Indicates the rounding down symbol. For example, in group 1, assuming that the current available resources of the system are 500 units, the average partition size is 5GB, the safety factor γ = 0.8, and there are 6 partitions in the group, then:

[0092]

[0093] The system deletes all partitions in group 1 at one time in batches, thereby reducing the task switching overhead.

[0094] After the deletion task is completed, the system generates an operation log to record the specific time, data volume, and system resource usage of the partition deletion. For example, after the deletion task of group 1 is completed, the following records are recorded: total deletion time 300 seconds, deleted data volume 30GB, and CPU usage rate 70%. Log analysis can re-evaluate group priority and batch processing parameters based on this data.

[0095] In the specific implementation of step (2.2), in order to ensure that the recycling interval is set in the CLOB data block deletion operation to achieve data security, and at the same time support users to quickly restore data blocks in the event of erroneous operations, an optimized recycling management mechanism is adopted to achieve the security, controllability and efficiency of the deletion operation.

[0096] First, when deleting a data block, the system determines the recycling interval T based on the importance level, access frequency, and change history of the data. r , to ensure that the data block is in a recoverable state within the safety period. The calculation formula is:

[0097]

[0098] Among them, L d Indicates the importance level of the data. The more important the block, the longer the recycling interval. a is the data access frequency. The higher the block frequency, the longer the recovery interval. v It is the change history of the data. The more frequently the block changes, the shorter the recycling interval. By adjusting the coefficients α, β, and γ, the influence of the above three factors can be balanced. For example, for a data block with high importance, medium access frequency, and frequent changes, the system calculates that the recycling interval is 60 minutes, and the deletion operation will trigger the final cleanup after this time.

[0099] Then, when the deletion task is executed, the system first sets the target data block to read-only status and moves it to an isolated storage area to prevent the data block from being accidentally overwritten or operated. In the isolated storage area, the data block still retains its original metadata information, allowing users to directly restore it to its original location through a recovery command within the recycling interval. When the recycling interval expires, the system enters the second stage of the cleanup process and uses asynchronous processing to safely destroy the data block. At the same time, the metadata of related operations (such as cleanup time, target data block ID, and operating user) is recorded during the cleanup process to ensure that all deletion tasks can be audited and traced back.

[0100] At the same time, during the recycling period, the protected data blocks are monitored in real time, including capturing user operation requests for isolated data blocks, detecting system anomalies or hardware failures, etc. When monitoring events that may affect the security of data blocks (such as user mistakenly operating deletion commands or system IO sudden abnormalities), the system will automatically suspend the current recycling task, generate event logs and notify the administrator to manually confirm before continuing the operation. For example, when a user tries to access a data block outside the isolation area, the system generates a warning and records the relevant operations to prevent accidental deletion or illegal modification.

[0101] Finally, when the recycling operation is triggered, the system performs a step-by-step cleanup, including the first step of soft-deleting the data block, removing it from the logical table but retaining its physical storage location; the second step of performing the final destruction operation, completely deleting the data block, and updating the operation log for subsequent auditing. The retention of the soft-deletion mark allows the status of the target data block to be tracked during the cleanup, ensuring that important information is not lost even if the cleanup is interrupted.

[0102] Step (3) mainly involves the management of tablespaces, how to optimize the management of tablespaces containing CLOB data in Oracle databases through dynamic monitoring and adjustment mechanisms, ensure stable database performance, and reduce the impact of irregular operations on database systems and other services. Specifically, it includes: (3.1) Regularly monitor the usage of tablespaces containing CLOB data to promptly identify potential storage bottlenecks; (3.2) Dynamically adjust the frequency parameters of cyclic scheduling tasks based on the usage of tablespaces to optimize database performance and ensure reasonable allocation of resources; finally, (3.3) Reclaim the tablespace to release the space occupied by deleted data blocks and improve storage utilization.

[0103] First, regularly monitor the usage of tablespaces containing CLOB data. In actual operation, the method can adopt a dynamic monitoring strategy based on a time window to accurately grasp the usage of tablespaces. The monitoring process uses database performance monitoring tools to collect comprehensive data by real-time monitoring of the storage capacity, usage rate, growth rate, and I / O load of the database tablespace. The system collects tablespace usage data every predetermined time period (such as every minute or every hour), which includes the remaining capacity of the tablespace, the current load, and the access frequency. The system generates a usage trend chart based on the collected monitoring data, and compares and analyzes it with historical data to predict the growth trend of tablespace usage. Through real-time monitoring and trend prediction, the system can identify potential performance bottlenecks or data backlogs and make optimization decisions in a timely manner.

[0104] Next, the frequency parameters of the cyclic scheduling task are dynamically adjusted according to the table space usage. In order to avoid unnecessary impact on database performance, the system introduces an adaptive adjustment mechanism. Specifically, the system dynamically adjusts the execution frequency of the cyclic scheduling task based on the table space usage monitored in real time. If the table space usage rate is high, the system will accelerate the frequency of data recovery and cleanup tasks; conversely, if the table space usage is relatively idle, the system will reduce the execution frequency of the recovery task. Therefore, the invention can intelligently adjust the task execution frequency, thereby avoiding excessive performance pressure due to too high a frequency, or insufficient recovery due to too low a frequency.

[0105] Finally, reclaim the tablespace to further ensure that the database performance is not burdened. When the tablespace reaches a high load, the system will start the asynchronous garbage collection mechanism and recycle data through the background thread without affecting the real-time query or write operations of the database. The asynchronous recycling mechanism breaks down the recycling task into multiple subtasks and distributes them to different threads or server nodes using a load balancing strategy, which can minimize I / O pressure and system jams. Specifically, the system processes data in blocks through multi-threading technology, and each thread processes a subset of the data recycling task, ensuring that the discarded data in the tablespace can be recycled in time without affecting the overall performance. The asynchronous recycling mechanism also includes an error recovery function, that is, if a recycling task is abnormal, the system will automatically roll back and restore the state before recycling to avoid the loss of important data due to errors in the recycling process.

[0106] To further optimize system performance, the system incorporates the database's intelligent error detection and repair mechanism. When a user performs an operation that may affect the table space, the system promptly detects irregular operations through log analysis and operation auditing functions, and notifies the administrator through the alarm system. If an erroneous operation or a query request that does not meet the specifications is detected, the system will automatically roll back to restore the normal state of the data table, and notify the user to make necessary operational corrections.

[0107] In step (4), abnormal monitoring and handling during the operation is very important for security assurance. Specifically, (4.1) the partition information of the CLOB data block is monitored in real time through the management page to ensure that any abnormal data or operation problems can be discovered in time; (4.2) when abnormalities occur during the deletion process, the system will automatically capture and handle these abnormalities to ensure the stability of database operations and the integrity of data.

[0108] In the specific implementation, through comprehensive management page monitoring, exception capture and processing, and regular monitoring mechanisms, the efficiency, reliability, and robustness of CLOB data deletion operations throughout the database life cycle are ensured. The following is a detailed description of the implementation steps:

[0109] First, the partition information of CLOB data blocks is monitored in real time through the management page. The monitoring page provides a real-time display of all CLOB data block information in the database. Users can intuitively view the status, partition status, deletion progress, and current storage capacity of each data block on this page. The page connects to the database API to obtain the latest block status data and presents it in a graphical manner. Users can set query conditions on this management page, such as viewing the deletion progress of a specific partition, or viewing the overall status of all ongoing deletion operations. In order to improve monitoring efficiency, the management page also supports filtering and sorting functions, allowing administrators to quickly locate blocks or data partitions that need attention. This function is not only for the convenience of manual monitoring, but also provides basic data support for subsequent automated scheduling and problem diagnosis.

[0110] Next, the system captures and handles exceptions that may occur during the automatic deletion process. During the data deletion process, the system uses a built-in exception capture mechanism to monitor the execution status of the deletion task in real time and capture system exceptions or operation errors in a timely manner. For example, when I / O blocking, connection timeout, or data access conflict occurs during the deletion operation, the system automatically detects these exceptions and notifies the administrator through logging and alarm mechanisms. The system further implements different processing strategies based on the type of exception. For minor exceptions, the system will retry and restart the deletion task; for serious exceptions, the system will immediately stop the deletion task and restore the data to the state before deletion to prevent data loss. The exception handling mechanism includes multiple rollback levels to ensure that the integrity and consistency of the data are not affected even under high load or improper operation. The mechanism can be further adjusted through dynamic configuration to adopt corresponding recovery strategies for different types of exceptions.

[0111] In addition, regular monitoring is implemented through the background management function to ensure the robustness of the deletion operation and promptly resolve the impact of the deletion operation on the database system performance. In order to ensure that the deletion operation will not have a negative impact on the normal operation of the database during execution, the system regularly evaluates the database performance through the background management function. This work is mainly achieved by regularly monitoring the database's key performance indicators such as CPU usage, memory consumption, and disk I / O. The background management system automatically detects the occupation of these resources by the deletion operation and dynamically adjusts the execution speed of the deletion task according to the current load. For example, when the system detects that the CPU load of the database is too high, the execution speed of the deletion task will be automatically reduced to reduce the pressure on the system; when the I / O resources are relatively idle, the deletion task can be accelerated to free up space more effectively. This monitoring system is not limited to the deletion operation itself, but also considers the workload of the entire database to ensure that in a multi-tasking environment, the execution of the deletion operation will not interfere with the normal operation of other services. In order to cope with complex database loads and external interference, the system also provides data recovery and optimization adjustment functions to help administrators promptly discover potential performance bottlenecks and adjust operations.

[0112] Through the above method, this embodiment realizes comprehensive monitoring and management of CLOB data deletion operations, which not only ensures the smooth progress of deletion operations, but also improves the reliability and performance of database operations through intelligent scheduling and abnormal recovery mechanisms. The implementation of each function is closely linked and supports each other, ensuring the robustness and efficiency of the entire system.

[0113] like Figure 2 As shown, the present invention also proposes a CLOB data deletion system in a database based on a recycling protection mechanism. Through the collaborative work of a series of modules, an efficient and reliable CLOB data deletion method is provided, aiming to optimize database performance, improve data management efficiency, and ensure the stability and security of the system during data deletion. The system mainly includes a partitioning module, a deletion module, a management module, and a processing module. The modules work closely together to ensure the smooth execution of the CLOB data deletion operation.

[0114] The main function of the partitioning module is to partition and split the CLOB data so that the CLOB data can be split into multiple data blocks to facilitate subsequent deletion operations. The specific implementation of this module includes the identification module, the segmentation module and the index module. The identification module is responsible for identifying all tables containing CLOB fields in the database and counting the amount of CLOB data in these tables. By scanning the database architecture and table structure, the identification module can determine which tables contain CLOB fields and the size of CLOB data in each table, thereby providing the necessary data basis for the subsequent partitioning strategy design. With the support of the segmentation module, the identified table information and data volume become the basis for designing the partitioning strategy.

[0115] The segmentation module designs an appropriate partitioning strategy based on the identified data volume and table structure to segment the CLOB data into multiple data blocks. This step ensures good performance of the deletion operation of each data block by setting a reasonable block size and avoids excessive load during the operation. The specific segmentation method can be dynamically adjusted according to the actual database load, table space usage, and storage characteristics of the target data to achieve performance optimization. The segmentation module also considers factors such as data access frequency and table space storage usage to ensure that deletion tasks can be evenly distributed and avoid excessive concentration of single deletion operations.

[0116] The index module generates a characteristic field index based on the characteristic information of the CLOB field. By analyzing the field's storage size, access frequency, last modification time and other characteristics, the index module can generate a multi-dimensional characteristic index for each CLOB field and classify the data fields. For example, data that has not been modified for a long time can be distinguished from outdated data with a high access frequency. The generated index not only improves the accuracy and efficiency of the data deletion process, but also allows the deletion strategy to be flexibly adjusted to adapt to the changes and growth of data in the database.

[0117] The deletion module is the core component of the system, which is mainly responsible for performing block deletion operations on the segmented data blocks. The deletion module includes a block deletion module and a recycling interval module. The block deletion module deletes the target data file in blocks according to a predetermined plan by calling a cyclic scheduling task. By periodically executing these deletion tasks, the deletion module can ensure that the data is deleted and the storage space is released. During this process, the block deletion module will dynamically evaluate the system load and database performance, and adjust the execution priority and timing of the deletion operation to ensure that the normal business operation of the database will not be affected during high-load periods.

[0118] The recycling interval module sets the recycling interval during the deletion operation to ensure data security. Data deletion operations are usually accompanied by resource recycling. In order to avoid data loss or system abnormalities caused by deletion operations, the recycling interval module will set a reasonable recycling interval based on the risk level of the operation and the recycling strategy to ensure that data security is guaranteed after each deletion operation without affecting the stability and reliability of the database.

[0119] The management module is responsible for regular management of the tablespace to ensure that the database can maintain good performance in long-term operation. The management module includes a tablespace monitoring module, a frequency adjustment module, and a recycling module. The tablespace monitoring module regularly monitors the usage of the tablespace containing CLOB data, and grasps the storage status and growth trend of the tablespace in real time. According to the usage of the tablespace, the frequency adjustment module can adjust the execution frequency of the cyclic scheduling task to ensure that the database can reasonably allocate resources during high-load periods and avoid the negative impact of deletion tasks on database performance. The recycling module is responsible for recycling the tablespace released by the deleted data, ensuring that the data space that is no longer used in the database is recycled in a timely manner, thereby ensuring the efficient use of the tablespace.

[0120] The processing module is used to monitor and handle exceptions that occur during the operation, ensuring that potential problems are discovered and resolved in a timely manner during the data deletion process. This module includes a partition monitoring module and an exception handling module. The partition monitoring module monitors the partition information of the CLOB data blocks in real time through the management page to ensure that the partition operation can proceed smoothly at all stages. The exception handling module is responsible for capturing and handling various exceptions that may occur during the deletion process, such as data deletion failure, insufficient system resources, or data block corruption. Through real-time monitoring and exception handling, the processing module can effectively ensure the smooth progress of the deletion process and prevent system crashes or data loss caused by abnormal situations.

[0121] The CLOB data deletion system in the Oracle database of the present invention can efficiently and flexibly perform the CLOB data deletion task by virtue of the collaborative work of the four modules of partitioning, deletion, management and processing. Through the application of the system, users can effectively manage a large amount of CLOB data, reduce unnecessary storage occupation, and improve the performance and response speed of the database. In addition, the system has a high degree of flexibility and scalability, can adapt to database environments of different scales and complexities, and provides a reliable and efficient CLOB data deletion solution for Oracle database users.

[0122] In summary, the CLOB data deletion method and system of the present invention can significantly improve the efficiency and security of large-scale data deletion operations in Oracle databases by partitioning CLOB data and dividing it into multiple data blocks. The beneficial effects of the present invention are as follows:

[0123] First, traditional CLOB data deletion operations often require processing a large amount of data at one time, resulting in long-term table locking and excessive I / O consumption. The present invention manages CLOB data in blocks, so that each data block can be deleted independently, thereby greatly reducing the resource usage of each deletion operation and reducing the system's table lock time. This block deletion strategy allows the system to allocate resources among multiple concurrent tasks, greatly improving the concurrency and execution efficiency of operations, especially when facing massive data, it can significantly reduce the time required for deletion operations.

[0124] Secondly, CLOB data will generate a large number of disk I / O operations during the deletion process. Traditional methods will greatly increase I / O pressure when processing large amounts of data, affecting system performance. However, by dividing CLOB data into multiple small data blocks and dynamically managing deletion priorities, the present invention can accurately schedule deletion tasks based on factors such as the size of the data block and access frequency. In this way, the system can avoid generating excessive I / O peaks during the deletion process, effectively disperse the I / O load, and reduce the pressure on the disk and storage during a single operation, thereby maintaining the stability of the database under high load.

[0125] Thirdly, the block deletion strategy of the present invention can avoid long-term database locking and resource occupation by introducing a flexible scheduling mechanism, thereby ensuring high availability of the system. Since each data block can be deleted independently, the system no longer needs to lock the entire data table or table space for a long time when performing the deletion operation, which allows other database operations to be executed in parallel, and the normal operation of other services will not be affected by the deletion of CLOB data.

[0126] In addition, the present invention specially designs a recycling protection mechanism. Traditional deletion methods are often prone to data loss due to user misoperation or system failure. The present invention effectively ensures data security by setting a recycling interval during the deletion process, introducing a two-stage protection strategy, and performing real-time protection on the deletion operation through dynamic monitoring and anomaly detection mechanisms. In particular, in terms of recycling interval setting and rollback mechanism, the system can convert the data block to a read-only state before deletion and store it in an isolation area, ensuring that even if anomalies or misoperations occur, the data can be restored through the rollback mechanism, thereby effectively avoiding data loss.

[0127] Finally, the present invention specifically proposes an asynchronous recovery mechanism for high-frequency CLOB data deletion. Unlike the traditional synchronous recovery mechanism, the asynchronous recovery mechanism allows the system to asynchronously perform space recovery tasks after deleting data, avoiding the slowdown of system performance by synchronous recovery operations. The advantage of this mechanism is that the recovery operation will not block other read and write operations of the database, thereby ensuring the stability and efficiency of the system.

[0128] In summary, the present invention partitions CLOB data and divides it into multiple data blocks, and combines dynamic scheduling, asynchronous recovery, exception monitoring and other technical means to not only greatly improve the efficiency and concurrency of CLOB data deletion operations, but also effectively reduce the I / O load of the system, ensure the high availability and stability of the database system, and improve the security of data operations. These beneficial effects make the present invention have significant advantages and broad application prospects in large-scale data deletion scenarios.

[0129] The above description is only a preferred embodiment of the present invention, so all equivalent changes or modifications made according to the structure, characteristics and principles described in the scope of the patent application of the present invention are included in the scope of the patent application of the present invention.

Claims

1. A method for deleting CLOB data in a database based on a recycling protection mechanism, characterized in that: The following steps are involved: (1) Split the CLOB data into data blocks; (2) deleting the segmented data blocks in chunks; (3) Regularly manage tablespace; (4) Monitor and handle abnormalities that occur during operation; Wherein, the step (1) specifically comprises: (1.1) Identify the table containing the CLOB field and the corresponding data volume; (1.2) Design a partitioning strategy based on the data size and divide the CLOB data into multiple data blocks; (1.3) Generate a characteristic field index for the CLOB field and identify the target data file to be deleted; Wherein, the step (2) specifically includes: (2.1) Call the cyclic scheduling task to delete the target data file in blocks; (2.2) When performing a deletion operation on the data block, a recycling interval is set to ensure data security.

2. The method according to claim 1, wherein step (2.2) further comprises: Step (2.2.1) constructs a recycling interval calculation model based on data importance, access frequency, and change history, and uses the following formula to calculate the recycling interval: Among them, T r Indicates the recycling interval; L d Indicates the level of data importance; F a Indicates the access frequency; H v represents the change history; α, β and γ are weight factors; Step (2.2.2) In data recycling, a two-stage protection mechanism is constructed. In the first stage, the data blocks to be deleted are set to read-only state and stored in an isolated storage area. In the second stage, after the recycling interval expires, the data is cleaned up and the metadata information of the recycling operation is recorded; Step (2.2.3) monitors the data blocks being recycled in real time, captures user misoperation or system anomalies, and automatically suspends the recycling operation until the anomaly is confirmed to be resolved.

3. According to the method of claim 2, the second stage of step (2.2.2) is to clean up the data and destroy the data blocks in an asynchronous processing manner; The metadata information of the recycling operation includes at least: Cleanup time, target data block ID and / or operation user.

4. The method according to claim 1, wherein: The step (3) specifically comprises: (3.1) Regularly monitor the usage of tablespaces containing CLOB data; (3.2) adjusting the frequency parameters of the cyclic scheduling task according to the table space usage to ensure database performance; (3.3) Reclaim table space.

5. The method according to claim 1, wherein: The step (4) specifically comprises: (4.1) Monitor the partition information of CLOB data blocks in real time through the management page; (4.2) Used to capture and handle exceptions that occur during the deletion process.

6. A CLOB data deletion system in a database based on a recycling protection mechanism, used to execute the method described in claims 1-5, characterized in that: include: Partition module, used to split CLOB data to form data blocks; A deletion module is used to delete the segmented data blocks in blocks; Management module, used for regular management of tablespace; A processing module is used to monitor and process abnormalities that occur during the operation; The partition module is used to partition the CLOB data to form data blocks, and specifically includes: An identification module is used to identify the table containing the CLOB field and the corresponding data volume; The partitioning module is used to design a partitioning strategy based on the data size and to partition the CLOB data into multiple data blocks; An index module is used to generate a characteristic field index for a CLOB field and identify target data files to be deleted; The deleting module is used to delete the divided data blocks in blocks, specifically including: The block deletion module is used to call the cyclic scheduling task to delete the target data file in blocks; The recycling interval module is used to set the recycling interval to ensure data security when performing a deletion operation on the data block.

7. The system according to claim 6, wherein the recovery spacer mold specifically comprises: The model building module is used to build a recycling interval calculation model based on data importance, access frequency, and change history, and calculate the recycling interval using the following formula: Among them, T r Indicates the recycling interval; L d Indicates the level of data importance; F a Indicates the access frequency; H v represents the change history; α, β and γ are weight factors; The recycling protection module is used to build a two-stage protection mechanism in data recycling. In the first stage, the data blocks to be deleted are set to read-only state and stored in an isolated storage area. In the second stage, after the recycling interval expires, the data is cleaned up through an asynchronous deletion process and metadata information of the recycling operation is recorded; The monitoring module is used to monitor the data blocks being recycled in real time, capture user errors or system anomalies, and automatically suspend the recycling operation until the anomaly is confirmed to be resolved.

8. The system according to claim 7, wherein the recycling protection module cleans up data in the second stage and destroys data blocks in an asynchronous processing manner; The metadata information of the recycling operation includes at least: Cleanup time, target data block ID and / or operation user.

9. The system according to claim 6, wherein: The management module is used to regularly manage the table space, specifically including: Table space monitoring module, used to regularly monitor the usage of table space containing CLOB data; A frequency adjustment module, used to adjust the frequency parameters of the cyclic scheduling task according to the table space usage to ensure database performance; Recycling module, used to recycle table space.

10. The system according to claim 6, wherein: The processing module is used to monitor and process the abnormalities that occur during the operation, and specifically includes: Partition monitoring module, used to monitor the partition information of CLOB data blocks in real time through the management page; The exception handling module is used to capture and handle exceptions that occur during the deletion process.

Images