Oracle creates dblink and scheduled tasks, and oracle creates dblink

Oracle creates dblink and scheduled tasks, and oracle creates dblink

Recently, we are working on a task, specifically, to regularly insert data to the middle table of another user's database.

The first thing we need to do is create an oracle dblink to the other database to access their database so that we can insert data into their intermediate tables. Therefore, we need the IP address, user, password, and table name of Their Intermediate table database, for more information, see \ product \ 10.2.0 \ client_1 \ network \ admin \ tnsnames in the database installation path. 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, enter:

Create database link name

Connect to the other database user name identified by the other Database User Password
Using 'peer database ip Address: Port/instance name ';

For example:

Create database link DLTEST
Connect to PMS identified by PMS
Using '1970. 2.39.25: 176/testdb ';

Select * from dual @ DLTEST; can be used to test whether the connection is successful.

Use select * from table name @ DLTEST; to access the database table

In this way, our oracle dblink is successfully created. Next we need to create a scheduled task to insert our table data to the target table every day.

Query all the databases in the dblink: select table_name from user_tables @ DLTEST;

Delete dblink: drop public database linkdltest;

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

At this time, oracle's scheduled task execution JOB is undoubtedly my best choice. I put the synchronization script in a stored procedure, and then it is OK to execute the stored procedure at a fixed time.

For example:

Create or replace procedure dingshitest_proc IS
BEGIN
Insert into table name @ DLTEST select * from your table;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('exception happened, data was rollback ');
ROLLBACK;
END;

Next, create a scheduled task,

Suppose we want to insert a record to the table name @ DLTEST every two minutes. Here we only need to execute the Stored Procedure dingshitest_proc every two minutes.

Enter the following statement in command window of PL/SQL:

SQL> var job_num number;
SQL> begin
2 dbms_job.submit (: job_num, 'dingshitest _ proc; ', SYSDATE, 'sysdate + 1/24/60 ');
3 end;
4/------------ end/

Remember to click Submit after execution,

View scheduled tasks: select * from dba_jobs a where a. WHAT like '% dingshitest_proc % ';

Delete scheduled task: Execute SQL> exec dbms_job.remove (961) in the Command window );

The following describes how to calculate the time interval of a scheduled task.

The date algorithm required by the First Scheduling task is relatively simple, that is, 'sysdate + N'. Here n is a time interval in days.

Description	Interval parameter value
Run once a day	'Sysdate + 1'
Run once every hour	'Sysdate + 123'
Run Once 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 type of scheduling task requires a more complex time interval (interval) expression than the first type.

Description	INTERVAL parameter value
Every day at midnight	'Trunc (SYSDATE + 1 )'
08:30 every morning	'Trunc (SYSDATE + 1) + (8*60 + 30)/(24*60 )'
Every Tuesday	'Next _ DAY (TRUNC (SYSDATE), ''tuesday'') + 100'
Midnight on the first day of each month	'Trunc (LAST_DAY (SYSDATE) + 1 )'
PM on the last day of each quarter	'Trunc (ADD_MONTHS (SYSDATE + 2/24, 3), 'q')-100'
Every Saturday and 06:10 AM	'Trunc (LEAST (NEXT_DAY (SYSDATE, ''saturday "), NEXT_DAY (SYSDATE," SUNDAY ") + (6 × 60 + 10)/(24 × 60 )'