Principle:
(1) Use database link to establish a connection between the two databases.
(2) create a stored procedure: delete all data tables, and insert the data table content of the production database to the test database. (Commit)
(3) Use Jobs to regularly execute stored procedures.
1. Create a dblink in the test database:
Createdatabaselink to_shengchan (dblink name)
Connectto unique identifiedby visu
Using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.138.10.196) (PORT = 1521 ))
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.138.10.198) (PORT = 1521) -- ip address of the production database cluster
)
(CONNECT_DATA =
(SERVICE_NAME = yyjcrac) -- Instance name
)
)'
2. Create a stored procedure:
Createorreplaceprocedure proc_shengchan_bakis
Begin
------------ The following non-real-time table data -----------------
Deletefromz_visu_data_chk;
Commit;
Insertinto z_visu_data_chk
Select * from z_visu_data_chk @ to_shengchan t;
Commit;
Deletefrom z_visu_data_dish;
Commit;
Insertinto z_pai_data_dish
Select * from z_visu_data_dish @ to_shengchan t;
Commit;
Deletefromz_pai_data_fin;
Commit;
Insertinto z_pai_data_fin
Select * from z_visu_data_fin @ to_shengchan t;
Commit;
Deletefromz_visu_data_hr;
Commit;
Insertinto z_pai_data_hr
Select * from z_visu_data_hr @ to_shengchan t;
Commit;
End;
When creating a stored procedure, you must execute F8 To Make It compiled successfully.
3. Execute the Stored Procedure
3.1 manual execution
Call proc_shengchan_bak ()
Or exec proc_shengchan_bak
3.2 run Jobs (create job)
DECLARE
X number;
BEGIN
SYS. DBMS_JOB.SUBMIT
(Job => X
, What => 'testproc ;'
, Next_date => to_date ('02-07-2008 01:00:00 ', 'dd/mm/yyyyhh24: mi: ss ')
, Interval => 'trunc (sysdate) + 1 + 9/(24) '-- run at every morning
, No_parse => FALSE
);
SYS. DBMS_OUTPUT.PUT_LINE ('job Number is: '| to_char (x ));
COMMIT;
END;