oradebug暂停进程
oradebug是sqlplus的工具，以sysdba身份登陆数据库则可使用此工具。本文演示一下oradebug暂停进程的测试。
1：session 1：查看session 1的pid和spid
|
1 2 3 4 5 6 7 8 |
SYS@PROD>select pid,spid from v$process where addr in (select paddr from v$session where sid= userenv('sid') ); PID SPID ---------- ------------------------ 25 25678 Elapsed: 00:00:00.01 SYS@PROD> |
2：session 1：执行一个匿名块，打印系统时间，每次循环，sleep 5秒，匿名块执行完毕需要25秒
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SYS@PROD>begin 2 for i in 1 .. 5 loop 3 dbms_output.put_line(sysdate); 4 dbms_lock.sleep(5); 5 end loop; 6 end; 7 / 2014-03-01 05:26:46 2014-03-01 05:26:51 2014-03-01 05:26:56 2014-03-01 05:27:01 2014-03-01 05:27:06 PL/SQL procedure successfully completed. Elapsed: 00:00:25.02 SYS@PROD> |
3：session 1：再次执行匿名块，执行后打开session 2（当然，可以提前先打开）
|
1 2 3 4 5 6 7 |
SYS@PROD>begin 2 for i in 1 .. 5 loop 3 dbms_output.put_line(sysdate); 4 dbms_lock.sleep(5); 5 end loop; 6 end; 7 / |
4：session 2：利用spid执行暂停，等待一会儿，此时看session 1，你会发现，匿名块一直还处于未完成状态
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
[oracle@prod ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 1 04:54:27 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SYS@PROD>oradebug setospid 25678 Oracle pid: 25, Unix process pid: 25678, image: oracle@prod (TNS V1-V3) SYS@PROD> SYS@PROD>oradebug suspend Statement processed. SYS@PROD> |
5：session 2：执行resume
|
1 2 3 |
SYS@PROD>oradebug resume Statement processed. SYS@PROD> |
6：session 1：查看会话1，此时匿名块执行完成，我们查看输出的时间，第二行时间和第三行时间间隔1分多钟，说明，此时暂停是生效的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SYS@PROD>begin 2 for i in 1 .. 5 loop 3 dbms_output.put_line(sysdate); 4 dbms_lock.sleep(5); 5 end loop; 6 end; 7 / 2014-03-01 05:28:49 2014-03-01 05:28:54 2014-03-01 05:30:20 2014-03-01 05:30:25 2014-03-01 05:30:30 PL/SQL procedure successfully completed. Elapsed: 00:01:46.24 SYS@PROD> |
当然，利用pid也可以完成上述动作的。实验步骤如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
--session 1：正常执行一次 SYS@PROD>begin 2 for i in 1 .. 5 loop 3 dbms_output.put_line(sysdate); 4 dbms_lock.sleep(1); 5 end loop; 6 end; 7 / 2014-03-01 05:35:09 2014-03-01 05:35:10 2014-03-01 05:35:11 2014-03-01 05:35:12 2014-03-01 05:35:13 PL/SQL procedure successfully completed. Elapsed: 00:00:05.02 SYS@PROD> --session 1：再次执行匿名块 SYS@PROD>begin 2 for i in 1 .. 5 loop 3 dbms_output.put_line(sysdate); 4 dbms_lock.sleep(1); 5 end loop; 6 end; 7 / --session 2：利用pid暂停进程 SYS@PROD>oradebug setorapid 25 Oracle pid: 25, Unix process pid: 25678, image: oracle@prod (TNS V1-V3) SYS@PROD>oradebug suspend Statement processed. SYS@PROD> --session 2：隔几秒钟，执行resume SYS@PROD>oradebug resume Statement processed. SYS@PROD> session 1：匿名块执行完成，我们可以看到，在循环第三次之前暂停了进程 2014-03-01 05:35:41 2014-03-01 05:35:42 2014-03-01 05:36:14 2014-03-01 05:36:15 2014-03-01 05:36:16 PL/SQL procedure successfully completed. Elapsed: 00:00:35.40 SYS@PROD> |