[20211209]pdb数据库kill job遇到的奇怪情况.txt

来源：这里教程网时间：2026-03-03 17:16:19 作者：

[20211209]pdb数据库kill job遇到的奇怪情况.txt --//在18c上测试遇到的问题，做一个记录。job进程无法清除在pdb层面上，并且在cdb层面也出现一点点情况，不过进程倒是可以kill。 1.环境： orcl> @ pr ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 18.0.0.0.0 BANNER : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production BANNER_FULL : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0 BANNER_LEGACY : Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.测试： orcl> @ spid SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50 ---------- ---------- ------------------------------------------------ ------------------ ------ ------- ---------- -------------------------------------------------- 14 63048 62441 DEDICATED 2890 64 115089 alter system kill session '14,63048' immediate; --//打开另外会话。 orcl> alter system kill session '14,63048' immediate; System altered. --//可以发现进程倒是可以kill掉。也就是在pdb层面上这样杀会话进程没有问题。 3.测试2： create or replace procedure test_load_as_select is type num_tab is table of number; l_tab num_tab; begin for i in 1..1000000 loop with sq1 as (select /*+ materialize */ level x from dual connect by level <= 1000000) ,sq2 as (select /*+ materialize */ x from sq1 where x <= 999999) ,sq3 as (select /*+ materialize */ x from sq1 where x <= 888888) select x bulk collect into l_tab from sq3 where x <= 3; end loop; end; / --//注意：测试使用提示materialize，使用临时表空间，产生少量日志，主要是懒得再做例子。 create or replace procedure test_load_as_select_job(p_job_cnt number) as l_job_id pls_integer; begin for i in 1..p_job_cnt loop dbms_job.submit(l_job_id, 'begin while true loop test_load_as_select; end loop; end;'); end loop; commit; end; / --//注:代码是死循环。 create or replace procedure clean_jobs as begin for c in (select job from dba_jobs) loop begin dbms_job.remove (c.job); -- exception when others then null; end; commit; end loop; for c in (select d.job, d.sid, (select serial# from v$session where sid = d.sid) ser from dba_jobs_running d) loop begin execute immediate 'alter system kill session '''|| c.sid|| ',' || c.ser|| ''' immediate'; dbms_job.remove (c.job); -- exception when others then null; end; commit; end loop; -- select * from dba_jobs; -- select * from dba_jobs_running; end; / --//注：我注解了exception部分。 orcl> exec test_load_as_select_job(1); PL/SQL procedure successfully completed. orcl> select * from DBA_JOBS 2 @ pr ============================== JOB : 28 LOG_USER : TTT PRIV_USER : TTT SCHEMA_USER : TTT LAST_DATE : LAST_SEC : THIS_DATE : 2021-12-09 09:39:37 THIS_SEC : 09:39:37 NEXT_DATE : 2021-12-09 09:39:14 NEXT_SEC : 09:39:14 TOTAL_TIME : 26 BROKEN : N INTERVAL : null FAILURES : WHAT : begin while true loop test_load_as_select; end loop; end; NLS_ENV : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' MISC_ENV : 0102000000000000 INSTANCE : 0 PL/SQL procedure successfully completed. orcl> select * from DBA_JOBS_RUNNING ; SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE ---------- ---------- ---------- ------------------- ---------------- ------------------- ---------------- ---------- 27 28 2021-12-09 09:39:37 09:39:37 0 orcl> column status format a10 orcl> @ trans SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID ---------- ---------- ------------------------- ---------------- ---------------- ---------- ---------- ------------------ ---------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ---------------- 27 46598 TTT 000000008C2F6BF8 0000000093F7E380 1 1 4001623 ACTIVE 2021-12-09 09:40:44 7 21 108059 070015001BA60100 0000000000000000 0000000000000000 --//产生少量日志。 orcl> @ sid 27 sid = 27 SPID PID SID SERIAL# CLIENT_INFO PNAME TRACEFILE PROGRAM TERMINAL SQL_ID STATUS C50 ------ ------- ---------- ---------- -------------------- ------ ---------------------------------------------------------------------------- ---------------------------------------- ------------ -------------------------- ---------------- -------------------------------------------------- 5212 64 27 46598 J000 /u01/app/oracle/diag/rdbms/orclcdb/orclcdb/trace/orclcdb_j000_5212.trc oracle@xxxx2 (J000) UNKNOWN a3376g221j75v ACTIVE alter system kill session '27,46598' immediate; orcl> exec clean_jobs BEGIN clean_jobs; END; * ERROR at line 1: ORA-00026: missing or invalid session ID ORA-06512: at "TTT.CLEAN_JOBS", line 14 ORA-06512: at "TTT.CLEAN_JOBS", line 14 ORA-06512: at line 1 --//可以发现clean_jobs脚本报错，出现ORA-00026: missing or invalid session ID。也就是无法清除job进程。 orcl> select * from DBA_JOBS_RUNNING ; SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE ---------- ---------- ---------- ------------------- ---------------- ------------------- ---------------- ---------- 27 28 --//清除其它信息，但是job还在运行。 orcl> @ trans SID SERIAL# USERNAME TADDR SES_ADDR USED_UBLK USED_UREC 0xFLAG STATUS START_DATE XIDUSN XIDSLOT XIDSQN XID PRV_XID PTX_XID ---------- ---------- ------------------------- ---------------- ---------------- ---------- ---------- ------------------ ---------- ------------------- ---------- ---------- ---------- ---------------- ---------------- ---------------- 27 46598 TTT 000000008C2F6BF8 0000000093F7E380 1 1 4001623 ACTIVE 2021-12-09 09:43:27 6 28 90712 06001C0058620100 0000000000000000 0000000000000000 --//可以发现job进程还在运行。 orcl> @ killi sid=27 COMMANDS_TO_VERIFY_AND_RUN ---------------------------------------------------------------------------------- alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000)); orcl> alter system kill session '27,46598' immediate ; alter system kill session '27,46598' immediate * ERROR at line 1: ORA-00026: missing or invalid session ID --//报ORA-00026，你可以发现job一直在运行。如果不使用clean_jobs脚本，手工删除session报错一样。使用sys用户操作也是一样。 # ps -elf | egrep "j0[0]|PI[D]" F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD 0 R oracle 5212 1 62 80 0 - 1350671 - 09:39 ? 00:04:15 ora_j000_orclcdb --//在cdb层面操作。 SYS@192.168.2.7:1521/orclcdb> alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000)); alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000)) * ERROR at line 1: ORA-00031: session marked for kill --//注意提示是ORA-00031: session marked for kill。你可能注意前面没有这个提示。 SYS@192.168.2.7:1521/orclcdb> alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000)); alter system kill session '27,46598' immediate -- TTT@xxxx2 (oracle@hosp2 (J000)) * ERROR at line 1: ORA-00030: User session ID does not exist. --//实际上这时进程kill，已经不存在了。也许遇到这样情况直接kill进程还是最方便，不知道为什么出现这样的情况。 4.收尾检查： orcl> select * from DBA_JOBS_RUNNING ; no rows selected orcl> select * from DBA_JOBS ; no rows selected orcl> @ trans no rows selected

编辑推荐：

返回列表

[20211209]pdb数据库kill job遇到的奇怪情况.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推