Creating and Managing Scheduled Tasks

Context

When a customer executes some time-consuming tasks during the day time, (for example, statistics summary task or other database synchronization tasks), the service performance will be influenced. So customers execute tasks on database during night time, increasing the workload. The scheduled task function of the database is compatible with the Oracle database scheduled task function that customers can create scheduled tasks. When the scheduled task time arrives, the task will be triggered. Therefore, the workload of OM has been reduced.

Database complies with the Oracle scheduled task function using the DBMS.JOB interface, which can be used to create scheduled tasks, execute tasks automatically, delete a task, and modify task attributes(including task ID, enable/disable a task, the task triggering time/interval and task contents).

The hybrid data warehouse (standalone) does not support scheduled tasks.

Periodic Task Management

1. Creates a test table.

   1	CREATE TABLE test(id int, time date);

   If the following information is displayed, the table has been created.

   1	CREATE TABLE

2. Create the customized storage procedure.

   1 2 3 4 5 6 7 8 9

   CREATE OR REPLACE PROCEDURE PRC_JOB_1() AS N_NUM integer :=1; BEGIN FOR I IN 1..1000 LOOP INSERT INTO test VALUES(I,SYSDATE); END LOOP; END; /

   If the following information is displayed, the procedure has been created.

   1	CREATE PROCEDURE

3. Create a task.
   • Create a task with unspecified job_id and execute the PRC_JOB_1 storage procedure every two minutes.

     1 2 3 4 5

     call dbms_job.submit('call public.prc_job_1(); ', sysdate, 'interval ''1 minute''', :a); job ----- 1 (1 row)

   • Create task with specified job_id.

     1 2 3 4 5

     call dbms_job.isubmit(2,'call public.prc_job_1(); ', sysdate, 'interval ''1 minute'''); isubmit --------- (1 row)

4. View the created task information about the current user.

   1 2 3 4 5

   select job,dbname,start_date,last_date,this_date,next_date,broken,status,interval,failures,what from user_jobs; job | dbname | start_date | last_date | this_date | next_date | broken | status | interval | failures | what -----+--------+---------------------+----------------------------+----------------------------+---------------------+--------+--------+---------------------+----------+--------------------------- 1 | | 2017-07-18 11:38:03 | 2017-07-18 13:53:03.607838 | 2017-07-18 13:53:03.607838 | 2017-07-18 13:54:03 | n | s | interval '1 minute' | 0 | call public.prc_job_1(); (1 row)

5. Stop a task.

   1 2 3 4 5

   call dbms_job.broken(1,true); broken -------- (1 row)

6. Start a task.

   1 2 3 4 5

   call dbms_job.broken(1,false); broken -------- (1 row)

7. Modify attributes of a task.
   • Modify the Next_date parameter information about a task.
     -- Specify the task of modifying Next_date of Job1 will be executed in one hour.

     1 2 3 4 5

     call dbms_job.next_date(1, sysdate+1.0/24); next_date ----------- (1 row)

   • Modify the Interval parameter information of a task.
     -- Set Interval of Job1 to 1.

     1 2 3 4 5

     call dbms_job.interval(1,'sysdate + 1.0/24'); interval ---------- (1 row)

   • Modify the What parameter information of a JOB.
     -- Change What to the SQL statement insert into public.test values(333, sysdate+5); for Job1.

     1 2 3 4 5

     call dbms_job.what(1,'insert into public.test values(333, sysdate+5);'); what ------ (1 row)

   • Modify Next_date, Interval, and What parameter information of JOB.

     1 2 3 4 5

     call dbms_job.change(1, 'call public.prc_job_1();', sysdate, 'interval ''1 minute'''); change -------- (1 row)

8. Delete a JOB.

   1 2 3 4 5

   call dbms_job.remove(1); remove -------- (1 row)

9. Set JOB permissions.
   • During the creation of a job, the job is bound to the user and database that created the job. Accordingly, the user and database are added to dbname and log_user columns in the pg_job system view, respectively.
   • If the current user is a DBA user, system administrator, or the user who created the job (log_user in pg_job), the user has the permissions to delete or modify parameter settings of the job using the remove, change, next_data, what, or interval interface. Otherwise, the system displays a message indicating that the current user has no permission to perform operations on the JOB.
   • If the current database is the one that created a job, (that is, dbname in pg_job), you can delete or modify parameter settings of the job using the remove, change, next_data, what, or interval interface.
   • When deleting the database that created a job, (that is, dbname in pg_job), the system associatively deletes the job records of the database.
   • When deleting the user who created a job, (that is, log_user in pg_job), the system associatively deletes the job records of the user.