Oracle过程或作业可根据时间线将数据从一种模式自动复制到另一种模式 [英] Oracle procedure or job to copy automatically data from one schema to another based on timeline

问题描述

我在数据库的架构(Schema1)中有一个表，该表中有8.5个mio记录.大多数记录都超过360天.

i have a table in my schema (Schema1) in my DB which has 8.5 mio records in a table. Most of the records older than 360 days.

我有另一个具有相同表结构的架构(Schema2)，并希望将所有早于 360天的Schema1.table中的记录复制到此Schema2.table中.

I have a second schema (Schema2) with the same table structure and want to copy all records from Schema1.table older than 360 days into this Schema2.table.

同样，每周都应该收集行中的下一条记录以进行复制.

我的下一个似乎无法正常工作.

My below one doesnt seem to work as I expected it to work.

CREATE OR REPLACE PROCEDURE ARCHIVE_DATA
AS
   CURSOR Schema1_extract
   IS
      SELECT column1, column2, column3 FROM Schema1.table;
BEGIN
   FOR Schema2_insert IN Schema1_extract
   LOOP
      BEGIN
         INSERT INTO Schema2.table (column1, column2, column3)
              VALUES (column1, column2, column3);

         COMMIT;
      END;
   END LOOP;
END;

编辑

另外，在上面的第一个之后还有另一个动作要执行，因为我想使用以下语句从src表中清除已合并的数据:

Additional there is another action to be executed after the first above, because i want to purge the already merged data from the src table with below statement:

DELETE FROM src
      WHERE     table_date <= SYSDATE - 360
            AND table_id IN (SELECT table_id FROM trg.column);

推荐答案

尝试使用MERGE，您可以根据逻辑替换where条件以获取早于360天的记录. ON条件应该是两个表的匹配联接列.

Try using MERGE, You can replace the where condition to get the records which are older than 360 days based on your logic. ON condition should be the matching join columns of the two tables.

如果代码很好，也许您可​​以安排每周运行.

If the code is fine, perhaps you can schedule to run every week.

我还建议使用BULK COLLECT插入记录，看看

I also suggest using BULK COLLECT to insert records, have a look at this

 CREATE OR REPLACE PROCEDURE ARCHIVE_DATA
AS
BEGIN
    MERGE
    INTO Schema2.table trg
    USING (
            SELECT
                column1
              , column2
              , column3
            FROM
                Schema1.table
            WHERE
                col_date >= 360
        )
        src
    ON
        (
            trg.column1 = src.column1
        )
    WHEN NOT MATCHED THEN
    INSERT
        (
            column1
          , column2
          , column3
        )
        VALUES
        (
            src.column1
          , src.column2
          , src.column3
        )
    ;

    COMMIT;
END;
/

这篇关于Oracle过程或作业可根据时间线将数据从一种模式自动复制到另一种模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！