Oracle establishes Dblink and establishes timed tasks

I've been doing a thing recently, specifically, we're going to plug the data into the middle of someone's database.

The first thing to do is to create an Oracle Dblink to the other database so that we can access their databases so that we could insert data into their intermediate tables. So we need them. The table name of the database of the intermediate table, the user, the password, the intermediate table, can see the \product\10.2.0\client_1\network\admin\ under the database installation path. Tnsnames.ora file

For example:

TestDB =
(DESCRIPTION =
(Address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 172.19.27.21) (PORT = 1521))
)
(Connect_data =
(service_name = TESTDB) TESTDB--------Instance Name
)
)

In the PL/SQL tool, type:

Create DATABASE Link Link name

Connect to offset database user name identified by offset database user password
Using ' Offset database IP: Port/instance name ';

For example:

Create DATABASE link Dltest
Connect to PMS identified by PMS
Using ' 176.2.39.25:1521/testdb ';

Available SELECT * from [email protected]; To test if the connection is successful

Through the SELECT * from table name @dltest; To access the database table

In this way, our Oracle Dblink is created successfully, and next we need to create a timed task to insert our table data into each other's middle table every day.

Query all tables under this Dblink database: SELECT table_name from [email protected];

Delete Dblink: DROP public DATABASE LINKdltest;

--------------------------------------------------------------------------

At this point, Oracle's scheduled job assignment is definitely my best choice. I put the synchronized script in a stored procedure, and then execute the stored procedure at a fixed time to be OK.

For example:

CREATE OR REPLACE PROCEDURE Dingshitest_proc is
BEGIN
INSERT into table name @dltest SELECT * from its own table ;
COMMIT;
EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (' Exception happened,data was rollback ');
ROLLBACK;
END;

Next, customize the Scheduled Tasks,

Suppose we want to insert a record into the table name @dltest every two minutes, here we only need to execute the stored procedure dingshitest_proc every 2 minutes.

In PL/SQL, Command window, enter the following statement:

sql> var job_num number;
Sql> begin
2 Dbms_job.submit (: Job_num, ' Dingshitest_proc; ', sysdate, ' sysdate+1/24/60 ');
3 END;
4/------------End with/AS

Be sure to remember to click the Submit button when you're done.

View timed tasks: SELECT * from Dba_jobs a where a.what like '%dingshitest_proc% ';

Delete timed tasks: Execute sql> exec dbms_job.remove (961) under the Command window;

Here's how the time interval for timed tasks counts.

The first scheduling task requires a date algorithm that is simpler, i.e. ' sysdate+n ', where n is a time interval in days.

Describe	Interval parameter values
Run Once a day	' Sysdate + 1 '
Run once per hour	' Sysdate + 1/24 '
Run every 10 minutes	' Sysdate + 10/(60*24) '
Run once every 30 seconds	' Sysdate + 30/(60*24*60) '
Run every other week	' Sysdate + 7 '
No longer run the task and delete it	Null

The second scheduling task requires a more complex interval (interval) expression relative to the first

Describe	Interval parameter values
12 O'Clock midnight every day.	' TRUNC (sysdate + 1) '
Daily 8:30 A.M.	' TRUNC (sysdate + 1) + (8*60+30)/(24*60) '
Every Tuesday noon 12 o'clock	' Next_day (TRUNC (sysdate), ' Tuesday ') + 12/24 '
12 O'Clock midnight on the first day of every month	' TRUNC (Last_day (sysdate) + 1) '
The last day of every quarter 11 o'clock	' TRUNC (add_months (sysdate + 2/24, 3), ' Q ') -1/24 '
Every Saturday and Sunday 6:10 A.M.	' TRUNC (LEAST (Next_day (sysdate, "SATURDAY"), Next_day (Sysdate, "SUNDAY")) + (6X60+10)/(24x60) '

Oracle establishes Dblink and establishes timed tasks