A customer needs to have two business tables with a large data volume and rows. Now we need to record part of these two tables, separate data based on a certain where condition and create an archive table to migrate the table records. The final result is: the records that do not meet the filtering conditions are left in the original table, and the tables that meet the filtering conditions must be inserted to
A customer needs to have two business tables with a large data volume and rows. Now we need to record part of these two tables, separate data based on a certain where condition and create an archive table to migrate the table records. The final result is: the records that do not meet the filtering conditions are left in the original table, and the tables that meet the filtering conditions must be inserted to
A customer needs to have two business tables with a large data volume and rows. Now we need to record part of these two tables, separate data based on a certain where condition and create an archive table to migrate the table records. The final result is that records that do not meet the filtering conditions are left in the original table, and tables that meet the filtering conditions must be inserted into the archive table, in addition, all the records inserted into the archive table must be deleted from the original table. The conditions are as follows: New table records + archive records = number of original table records
Below I will simulate this process: Because I didn't get the specific table creation statement, here the table content is the most simplified, leaving only two columns as the most basic demonstration.
-- Connect to the test user and create a test tableSQL> conn zlm/zlm Connected. SQL> select * from cat;
No rows selected SQL> create table tabhdr (tabhdrid number (10), status number (10 ));
Table created.
SQL> create table tabdet (tabhdrid number (10 ));
Table created.
SQL> create table arch_tabdet as select * from tabdet;
Table created.
SQL> create table arch_tabhdr as select * from tabhdr;
Table created.
-- Create an operation log tableSQL> create table arch_log (2 archbegintime char (19), 3 archmiddletime char (19), 4 archendtime char (19), 5 archinscount1 number, 6 archdelcount1 number, 7 archinscount2 number, 8 archdelcount2 number, 9 archstatus varchar2 (20), 10 archerrorcode varchar2 (20), 11 archerrormsg varchar2 (1000 ));
Table created.
Operation Log table field description:Archbegintime-> archimiddletime // the time of the first migration operation (insert + delete) archimiddletime-> archendtime // The second migration operation (insert + delete) time of archcount1 // The data volume of the first migration operation (insert + delete) archcount2 // The data volume of the second migration operation (insert + delete) archstatus // operation status (success/failure) archerrorcode // error code archerrormsg // error message
-- Insert test data (10 million records are inserted in each table, and large data is not required only for the test function)SQL> begin 2 for I in 1 .. 100000 3 loop 4 insert into tabhdr values (I, 9); 5 insert into tabdet values (I); 6 end loop; 7 commit; 8 end; 9/
PL/SQL procedure successfully completed.
-- Create the Stored Procedure detach_proSQL> create or replace procedure detach_pro 2 is 3 maxrows number default 10000; 4 rowid_table dbms_ SQL .Urowid_Table; 5 I number; 6 cursor cur_1 is SELECT. rowid FROM tabdet a WHERE tabhdrid IN (SELECT tabhdrid FROM tabhdr where status = 9) order by. rowid; 7 cursor cur_2 is SELECT. rowid FROM tabhdr a WHERE status = 9 order by. rowid; 8 9 v_begintime char (19): = to_char (sysdate, 'yyyy-mm-dd hh: mi: ss'); 10 v_middletime char (19); 11 v_inscount1 number: = 0; 12 v_delcount1 number: = 0; 13 v_inscount2 number: = 0; 14 v_delcount2 number: = 0; 15 v_errcode varchar2 (100); 16 v_errerrm varchar2 (1000 ); 17 18 begin 19 open cur_1; 20 loop 21 exit when cur_1 % notfound; 22 fetch cur_1 bulk collect into rowid_table limit maxrows; 23 24 forall I in 1 .. rowid_table.count 25 insert into arch_tabdet select * from tabdet where rowid = rowid_table (I); 26 commit; 27 v_inscount1: = v_inscount1 + rowid_table.count; 28 forall I in 1 .. limit 29 delete from tabdet where rowid = rowid_table (I); 30 commit; 31 v_delcount1: = v_delcount1 + rowid_table.count; 32 end loop; 33 close cur_1; 34 v_middletime: = to_char (sysdate, 'yyyy-mm-dd hh: mi: ss'); 35 open cur_2; 36 loop 37 exit when cur_2 % notfound; 38 fetch cur_2 bulk collect into rowid_table limit maxrows; 39 40 forall I in 1 .. rowid_table.count 41 insert into arch_tabhdr select * from tabhdr where rowid = rowid_table (I); 42 commit; 43 v_inscount2: = v_inscount2 + rowid_table.count; 44 forall I in 1 .. rowid_table.count 45 delete from tabhdr where rowid = rowid_table (I); 46 commit; 47 v_delcount2: = v_delcount2 + rowid_table.count; 48 end loop; 49 close cur_2; 50 insert into arch_log values (v_begintime, v_middletime, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss'), v_inscount1, v_delcount1, v_inscount2, v_delcount2, 'success ', null, null); 51 commit; 52 exception 53 when others then 54 v_errcode: = sqlcode; 55 v_errerrm: = sqlerrm; insert into arch_log values (v_begintime, v_middletime, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss'), v_inscount1, v_delcount1, v_inscount2, v_delcount2, 'failed', v_errcode, v_errerrm); 57 commit; 58 end; 59/
Procedure created.
-- Start 1st testsSQL> select count (*) from tabdet;
COUNT (*) ---------- 100000
SQL> select count (*) from tabhdr;
COUNT (*) ---------- 100000
SQL> select count (*) from arch_tabdet;
COUNT (*) ---------- 0
SQL> select count (*) from arch_tabdet;
COUNT (*) ---------- 0
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> select count (*) from tabdet;
COUNT (*) ---------- 0
SQL> select count (*) from tabhdr;
COUNT (*) ---------- 0
SQL> select count (*) from arch_tabdet;
COUNT (*) ---------- 100000
SQL> select count (*) from arch_tabhdr;
COUNT (*) ---------- 100000
SQL> select * from arch_log;
Archbegintime archmiddletime archendtime limit ARCHDELCOUNT1 limit ARCHDELCOUNT2 limit ------------- specify ARCHERRORCODE limit ARCHERRORMSG Limit --- 100000 10:30:45 100000 10:30:53 100000 10:31:02 100000 success
We can see that after the Stored Procedure detach_pro is executed, no data exists in the original two tables, and all data is separated into arch_tabdet and arch_tabhdr. Of course, this is an extreme situation, all the previously inserted data comply with the filtering conditions for the inserted data to the archive table, that is, the field "status = 9 ". The operation log table records the insert and delete operations and execution time of each table.
If a new record is inserted into the original table but does not meet the filtering conditions in the inserted archive table, for example, status = 8, let's take a look at the test results:
-- 2nd tests (insert 100 [this article from the Internet (http://www.68idc.cn)] 00 status = 8 Records)SQL> begin
2 for I in 1 .. 10000 3 loop 4 insert into tabhdr values (I, 8); 5 insert into tabdet values (I); 6 end loop; 7 commit; 8 end; 9/
PL/SQL procedure successfully completed.
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> select count (*) from tabdet;
COUNT (*) ---------- 10000
SQL> select count (*) from tabhdr;
COUNT (*) ---------- 10000
SQL> select count (*) from arch_tabdet;
COUNT (*) ---------- 100000
SQL> select count (*) from arch_tabhdr;
COUNT (*) ---------- 100000
SQL> select * from arch_log;
Archbegintime archmiddletime archendtime limit ARCHDELCOUNT1 limit ARCHDELCOUNT2 limit ------------- specify ARCHERRORCODE limit ARCHERRORMSG Limit --- 100000 10:30:45 100000 10:30:53 100000 10:31:02 100000 success
10:43:38 10:43:38 10:43:38 0 0 0 success
Archbegintime archmiddletime archendtime ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 quota --------------- ----------------- ------------- archstatus archerrorcode limit ARCHERRORMSG Limit ----------------------------------------------------------------------------------------------------------------------------------
10:44:28 10:44:28 10:44:28 0 0 0 success
SQL>
It is found that the execution of detach_pro is fast, and the records of the original and archive tables have not changed. Because status = 8 does not meet the filtering conditions, the execution of the stored procedure will not perform the migration operation, even if no operation is successful. Because the stored procedure is executed twice, two rows of operation results will be generated in the record table.
-- 3rd tests (insert 1000 records that meet the filtering conditions again, status = 9)SQL> begin
2 for I in 1 .. 1000 3 loop 4 insert into tabhdr values (I, 9); 5 insert into tabdet values (I); 6 end loop; 7 commit; 8 end; 9/
PL/SQL procedure successfully completed.
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> select count (*) from tabdet;
COUNT (*) ---------- 9000
SQL> select count (*) from tabhdr;
COUNT (*) ---------- 10000
SQL> select count (*) from arch_tabdet;
COUNT (*) ---------- 102000
SQL> select count (*) from arch_tabhdr;
COUNT (*) ---------- 101000
SQL> select * from arch_log;
Archbegintime archmiddletime archendtime limit ARCHDELCOUNT1 limit ARCHDELCOUNT2 limit ------------- specify ARCHERRORCODE limit ARCHERRORMSG Limit --- 100000 10:30:45 100000 10:30:53 100000 10:31:02 100000 success
10:43:38 10:43:38 10:43:38 0 0 0 success
Archbegintime archmiddletime archendtime ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 quota --------------- ----------------- ------------- archstatus archerrorcode limit ARCHERRORMSG Limit ----------------------------------------------------------------------------------------------------------------------------------
10:44:28 10:44:28 10:44:28 0 0 0 success
10:53:15 10:53:15 10:53:15 2000 2000 1000 success
Archbegintime archmiddletime archendtime ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 quota --------------- ----------------- ------------- archstatus archerrorcode limit ARCHERRORMSG Limit ----------------------------------------------------------------------------------------------------------------------------------
First migration operation:The 2000 rows of records in the tabdet table are inserted into the arch_tabdet table, and the corresponding records in the 2000 rows in the tabdet table are deleted. Therefore, the result is that the tabdet table contains 9000 records, and the arch_tabdet table is increased to 12000 records.
The second migration operation:
The 1000 rows of records in the tabhdr table are inserted into the arch_tabhdr table, and then the 1000 rows of records in the tabhdr table are deleted. Therefore, the tabhdr table contains 10000 records, and the arch_tabhdr table is increased to 11000 records.
In actual production, tables that meet the migration conditions are determined based on the specific where conditions in the stored procedure. The test here is not necessarily accurate.
Finally, you can create a job to automatically run the stored procedure on a regular basis, for example, declare.
V_jobnum number; begin
Dbms_job.submit (v_jobnum, 'detach _ Pro', sysdate, 'sysdate + 100 ');
End; commit; or:
Declare v_jobnum number;
Begin
Dbms_job.submit
(Job => v_jobnum
, What => 'detach _ Pro'
, Next_date => sysdate
, Interval => 'sysdate + 123'
, No_parse => TRUE
);
End;
/
Commit;
-- Prevents stored procedures from being put into the job and runs automatically every hour
SQL> declare v_jobnum number;
2 begin
3 dbms_job.submit
4 (job => v_jobnum
5, what => 'detach _ Pro'
6, next_date => sysdate
7, interval => 'sysdate + 123'
8, no_parse => TRUE
9 );
10 end;
11/
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> desc user_jobs;
Name Null? Type
-------------------------------------------------------------------------------------------------------------------------------
JOB NOT NULL NUMBER
LOG_USER not null VARCHAR2 (30)
PRIV_USER not null VARCHAR2 (30)
SCHEMA_USER not null VARCHAR2 (30)
LAST_DATE DATE
LAST_SEC VARCHAR2 (8)
THIS_DATE DATE
THIS_SEC VARCHAR2 (8)
NEXT_DATE NOT NULL DATE
NEXT_SEC VARCHAR2 (8)
TOTAL_TIME NUMBER
BROKEN VARCHAR2 (1)
Interval not null VARCHAR2 (200)
FAILURES NUMBER
WHAT VARCHAR2 (4000)
NLS_ENV VARCHAR2 (4000)
MISC_ENV RAW (32)
INSTANCE NUMBER
SQL> col interval for a15
SQL> col what for a15
SQL> select job, next_date, interval, what from user_jobs;
JOB NEXT_DATE INTERVAL WHAT
-------------------------------------------------
5 30-DEC-14 SYSDATE + 1/24 detach_pro
4 01-JAN-00 SYSDATE + 1/24/60 xxx_pro;
SQL>
-- Delete other irrelevant jobsSQL> exec dbms_job.remove (4 );
PL/SQL procedure successfully completed.
SQL> select job, next_date, interval, what from user_jobs;
JOB NEXT_DATE interval what ---------- --------- --------------- ----------------- 5 30-DEC-14 SYSDATE + 1/24 detach_pro
