I will show, how to create and manage jobs..
When you logon to the EM click on the "Jobs" tab.
Now, choose what kind of job would you like to create and click on the "Go" button.
I have chosen the "SQL Script"
On the next screen, choose the Name and Description of the job:
Next click on the "Add" button, to choose the db in which you would like to run the job:
Choose one or more database. Click on the "Select" button and you should see the database in the "General" tab:
Now, you can add your SQL commands, or the name of the SQL script, you would like to start:
In the "Credentials" tab, insert the OS and database credentials:
In the "Schedule" tab, define, when you want to start the script. I have chosen to start the script every 5 minutes:
In the "Access" tab, you can modify, who will have rights to see or to administer the job:
After you clik on the "Submit" buton, your job will be created:
The job I have created, should insert every 5 minutes in the table test_jobs id of the run and current sysdate.For this, I have created the test_jobs table, index on the pocet column and the test_job_seq sequence:
SQL> create table test_jobs (pocet number(2), datum date) tablespace users;
Table created.
SQL> create unique index pocet_pk on test_jobs(pocet) tablespace users;
Index created.
SQL> create sequence test_job_seq start with 1 increment by 1 nocache;
Sequence created.
Now, I can test, if the job is working:
SQL> select * from test_jobs order by 1;
POCET DATUM
---------- -------------------
1 17.10.2011 14:15:12
2 17.10.2011 14:20:09
After few minutes:
SQL> select * from test_jobs order by 1;
POCET DATUM
---------- -------------------
1 17.10.2011 14:15:12
2 17.10.2011 14:20:09
3 17.10.2011 14:25:10
4 17.10.2011 14:30:06
5 17.10.2011 14:35:04
6 17.10.2011 14:40:06
7 17.10.2011 14:45:11
7 rows selected.
To see the job, which was created via the EM, you can query the MGMT$JOBS view:
SQL> col job_name for a50
SQL> col job_description for a50
SQL> select job_name,job_description from mgmt$jobs where job_name='TEST_JOB_1';
JOB_NAME JOB_DESCRIPTION
-------------------------------------------------- --------------------------------------------------
TEST_JOB_1 insert into table
So the job is running. Now, I will suspend it:
Click on the "Suspend" button int the "Jobs" tab, with the job selected:
SQL> create table test_jobs (pocet number(2), datum date) tablespace users;
Table created.
SQL> create unique index pocet_pk on test_jobs(pocet) tablespace users;
Index created.
SQL> create sequence test_job_seq start with 1 increment by 1 nocache;
Sequence created.
Now, I can test, if the job is working:
SQL> select * from test_jobs order by 1;
POCET DATUM
---------- -------------------
1 17.10.2011 14:15:12
2 17.10.2011 14:20:09
After few minutes:
SQL> select * from test_jobs order by 1;
POCET DATUM
---------- -------------------
1 17.10.2011 14:15:12
2 17.10.2011 14:20:09
3 17.10.2011 14:25:10
4 17.10.2011 14:30:06
5 17.10.2011 14:35:04
6 17.10.2011 14:40:06
7 17.10.2011 14:45:11
7 rows selected.
To see the job, which was created via the EM, you can query the MGMT$JOBS view:
SQL> col job_name for a50
SQL> col job_description for a50
SQL> select job_name,job_description from mgmt$jobs where job_name='TEST_JOB_1';
JOB_NAME JOB_DESCRIPTION
-------------------------------------------------- --------------------------------------------------
TEST_JOB_1 insert into table
So the job is running. Now, I will suspend it:
Click on the "Suspend" button int the "Jobs" tab, with the job selected:
On the next screen, click the "Yes" button:
Now, you can see, that the job was suspended:
To delete the job, the job has to be stopped at first. So click on the "Stop" button and then click on the "Yes" button on the next screen. Now I can see, that there are no active jobs:
When I change the "Status" filter to "All" I can see all runs of the job:
Here I can delete all runs of this job. Select the run you want to delete and click on the "Delete" button. On the next screen, check, if you want to delete just this run of the job or all runs and click on the "OK" button:
After the confirmation, the job was deleted.
I can query the test_jobs table to see, if there are any new rows:
SQL> select * from test_jobs order by 1;
POCET DATUM
---------- -------------------
1 17.10.2011 14:15:12
2 17.10.2011 14:20:09
3 17.10.2011 14:25:10
4 17.10.2011 14:30:06
5 17.10.2011 14:35:04
6 17.10.2011 14:40:06
7 17.10.2011 14:45:11
8 17.10.2011 14:50:08
8 rows selected.
There are no new rows after the 14:50, so the job was successfully deleted.
I can query the test_jobs table to see, if there are any new rows:
SQL> select * from test_jobs order by 1;
POCET DATUM
---------- -------------------
1 17.10.2011 14:15:12
2 17.10.2011 14:20:09
3 17.10.2011 14:25:10
4 17.10.2011 14:30:06
5 17.10.2011 14:35:04
6 17.10.2011 14:40:06
7 17.10.2011 14:45:11
8 17.10.2011 14:50:08
8 rows selected.
There are no new rows after the 14:50, so the job was successfully deleted.