今天在准备做一个job，用于分析undo增长过快的原因，检查v$transaction中有什么session运行，session对应的sql_text是什么。
写完存储过程后，放到job中，发现job没有运行。于是进行一下检查分析：
1、检查alertlog中没有发现异常
2、[oracle@siteview-db oracle]$ ps -ef |grep ora_
oracle 2158 1 0 Jan18 ? 00:08:14 ora_pmon_ora9i
oracle 2160 1 0 Jan18 ? 00:05:34 ora_dbw0_ora9i
oracle 2162 1 0 Jan18 ? 00:50:05 ora_lgwr_ora9i
oracle 2164 1 0 Jan18 ? 00:07:13 ora_ckpt_ora9i
oracle 2166 1 0 Jan18 ? 00:03:02 ora_smon_ora9i
oracle 2168 1 0 Jan18 ? 00:00:21 ora_reco_ora9i
没有发现job的进程，ora_cjq0_ora9i不存在。
3、SQL> SELECT * FROM v$bgprocess
2 ORDER BY 1 DESC,2;
PADDR NAME DEscrīptION ERROR
——– —– —————————————————————- —–
6528F338 RECO distributed recovery 00
6528EF88 SMON System Monitor Process 00
6528EBD8 CKPT checkpoint 00
6528E828 LGWR Redo etc. 00
6528E478 DBW0 db writer process 0 00
6528E0C8 PMON process cleanup 00
00 ARC0 Archival Process 0 00
00 ARC1 Archival Process 1 00
00 ARC2 Archival Process 2 00
00 ARC3 Archival Process 3 00
PADDR NAME DEscrīptION ERROR
——– —– —————————————————————- —–
00 ARC4 Archival Process 4 00
00 ARC5 Archival Process 5 00
00 ARC6 Archival Process 6 00
00 ARC7 Archival Process 7 00
00 ARC8 Archival Process 8 00
00 ARC9 Archival Process 9 00
00 CJQ0 Job Queue Coordinator 00
00 DBW1 db writer process 1 00
00 DBW2 db writer process 2 00
00 DBW3 db writer process 3 00
00 DBW4 db writer process 4 00
00 DBW5 db writer process 5 00
PADDR NAME DEscrīptION ERROR
——– —– —————————————————————- —–
00 DBW6 db writer process 6 00
00 DBW7 db writer process 7 00
00 DBW8 db writer process 8 00
00 DBW9 db writer process 9 00
00 DBWa db writer process 10 (a) 00
00 DBWb db writer process 11 (b) 00
00 DBWc db writer process 12 (c) 00
00 DBWd db writer process 13 (d) 00
00 DBWe db writer process 14 (e) 00
00 DBWf db writer process 15 (f) 00
00 DBWg db writer process 16 (g) 00
PADDR NAME DEscrīptION ERROR
——– —– —————————————————————- —–
00 DBWh db writer process 17 (h) 00
00 DBWi db writer process 18 (i) 00
00 DBWj db writer process 19 (j) 00
00 DIAG diagnosibility process 00
00 DMON DG Broker Monitor Process 00
00 EMN0 Event Monitor Process 0 00
00 FMON File Mapping Monitor Process 00
00 LCK0 Lock Process 0 00
00 LMD0 global enqueue service daemon 0 00
00 LMON global enqueue service monitor 00
00 LMS0 global cache service process 0 00
PADDR NAME DEscrīptION ERROR
——– —– —————————————————————- —–
00 LMS1 global cache service process 1 00
00 LMS2 global cache service process 2 00
00 LMS3 global cache service process 3 00
00 LMS4 global cache service process 4 00
00 LMS5 global cache service process 5 00
00 LMS6 global cache service process 6 00
00 LMS7 global cache service process 7 00
00 LMS8 global cache service process 8 00
00 LMS9 global cache service process 9 00
00 LNSV Network Server 00
00 LSP0 Logical Standby 00
PADDR NAME DEscrīptION ERROR
——– —– —————————————————————- —–
00 LSP1 Dictionary build process for Logical Standby 00
00 MRP0 Managed Standby Recovery 00
00 NSV0 DR Server NetSlave Process 0 00
00 QMN0 AQ Time Manager Process 0 00
00 QMN1 AQ Time Manager Process 1 00
00 QMN2 AQ Time Manager Process 2 00
00 QMN3 AQ Time Manager Process 3 00
00 QMN4 AQ Time Manager Process 4 00
00 QMN5 AQ Time Manager Process 5 00
00 QMN6 AQ Time Manager Process 6 00
00 QMN7 AQ Time Manager Process 7 00
PADDR NAME DEscrīptION ERROR
——– —– —————————————————————- —–
00 QMN8 AQ Time Manager Process 8 00
00 QMN9 AQ Time Manager Process 9 00
00 RSM0 DR Resource Manager Process 00
69 rows selected.
SQL>
发现job的后台进程确实不正常，在os以及数据库中都看不到。
4、SQL>
SQL> show parameter job
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 0
SQL>
SQL>
发现初始化参数中设置job运行个数为0！
进行修改：
SQL>
SQL> alter system set job_queue_processes=10;
System altered.
SQL> show parameter job
NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 10
SQL>
SQL>
5、进行检查：
SQL> !ps -ef |grep ora_
oracle 2158 1 0 Jan18 ? 00:08:14 ora_pmon_ora9i
oracle 2160 1 0 Jan18 ? 00:05:34 ora_dbw0_ora9i
oracle 2162 1 0 Jan18 ? 00:50:06 ora_lgwr_ora9i
oracle 2164 1 0 Jan18 ? 00:07:13 ora_ckpt_ora9i
oracle 2166 1 0 Jan18 ? 00:03:02 ora_smon_ora9i
oracle 2168 1 0 Jan18 ? 00:00:21 ora_reco_ora9i
oracle 924 1 0 14:47 ? 00:00:00 ora_cjq0_ora9i
oracle 1020 851 0 14:59 pts/0 00:00:00 /bin/bash -c ps -ef |grep ora_
oracle 1022 1020 0 14:59 pts/0 00:00:00 grep ora_
在数据库中也发现job运行正常！