最近经常用数据泵在生产库与测试库间传递数据,生厂上expdp出来的dmp文件应用在测试库前,需要先将测试库上的已有用户删除或删除用户下的对象。
我一般都是先将用户删除,然后再导入。删除时,经常报出ora-01940:无法删除当前已连接用户。此时,需要查出哪些session以此用户连接,然后kill掉即可。
对于中间件的连接(weblogic、tomcat、jboss),方法是一样的,只不过中间件的session数量比较大,一个一个kill太过漫长,此时,可以通过pl/sql代码块将session杀掉即可。
整个过程如下:
(1):查看版本,导入前删除用户,报出ora-01940
SQL> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
SQL> drop user sipo132 cascade;
drop user sipo132 cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
(2):查看当前连接到sipo132的session,<span style="font-family: SimSun; font-size: 18px; ">发现有100个session在连接,且通过machine和program确定为中间件在启动时通过连接池建立的100个长连接。</span>
SQL> col osuser for a10
SQL> col machine for a20
SQL> col program for a20
SQL> select sid,serial# sn,osuser,machine,program
2 from v$session
3 where username='SIPO132';
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
300 3353 weblogic nw-9000-06 JDBC Thin Client
301 5472 weblogic nw-9000-06 JDBC Thin Client
302 19132 weblogic nw-9000-06 JDBC Thin Client
303 43181 weblogic nw-9000-06 JDBC Thin Client
306 30606 weblogic nw-9000-06 JDBC Thin Client
311 62464 weblogic nw-9000-06 JDBC Thin Client
312 64812 weblogic nw-9000-06 JDBC Thin Client
315 35657 weblogic nw-9000-06 JDBC Thin Client
317 6786 weblogic nw-9000-06 JDBC Thin Client
318 20600 weblogic nw-9000-06 JDBC Thin Client
319 48894 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
320 42020 weblogic nw-9000-06 JDBC Thin Client
321 15552 weblogic nw-9000-06 JDBC Thin Client
323 12383 weblogic nw-9000-06 JDBC Thin Client
324 57 weblogic nw-9000-06 JDBC Thin Client
325 42 weblogic nw-9000-06 JDBC Thin Client
332 57 weblogic nw-9000-06 JDBC Thin Client
333 43 weblogic nw-9000-06 JDBC Thin Client
334 48 weblogic nw-9000-06 JDBC Thin Client
335 48 weblogic nw-9000-06 JDBC Thin Client
337 67 weblogic nw-9000-06 JDBC Thin Client
339 51 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
340 121 weblogic nw-9000-06 JDBC Thin Client
341 63 weblogic nw-9000-06 JDBC Thin Client
342 57 weblogic nw-9000-06 JDBC Thin Client
343 41 weblogic nw-9000-06 JDBC Thin Client
344 50 weblogic nw-9000-06 JDBC Thin Client
346 51 weblogic nw-9000-06 JDBC Thin Client
347 51 weblogic nw-9000-06 JDBC Thin Client
348 58 weblogic nw-9000-06 JDBC Thin Client
356 43 weblogic nw-9000-06 JDBC Thin Client
358 50 weblogic nw-9000-06 JDBC Thin Client
359 43 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
360 54 weblogic nw-9000-06 JDBC Thin Client
361 46 weblogic nw-9000-06 JDBC Thin Client
362 52 weblogic nw-9000-06 JDBC Thin Client
363 56 weblogic nw-9000-06 JDBC Thin Client
364 41 weblogic nw-9000-06 JDBC Thin Client
365 59 weblogic nw-9000-06 JDBC Thin Client
366 36809 weblogic nw-9000-06 JDBC Thin Client
367 4337 weblogic nw-9000-06 JDBC Thin Client
368 681 weblogic nw-9000-06 JDBC Thin Client
369 58808 weblogic nw-9000-06 JDBC Thin Client
370 97 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
371 49579 weblogic nw-9000-06 JDBC Thin Client
372 47717 weblogic nw-9000-06 JDBC Thin Client
376 33487 weblogic nw-9000-06 JDBC Thin Client
377 55 weblogic nw-9000-06 JDBC Thin Client
378 45528 weblogic nw-9000-06 JDBC Thin Client
380 58 weblogic nw-9000-06 JDBC Thin Client
382 42176 weblogic nw-9000-06 JDBC Thin Client
383 7448 weblogic nw-9000-06 JDBC Thin Client
384 44990 weblogic nw-9000-06 JDBC Thin Client
386 40172 weblogic nw-9000-06 JDBC Thin Client
387 35807 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
388 3737 weblogic nw-9000-06 JDBC Thin Client
389 63918 weblogic nw-9000-06 JDBC Thin Client
391 7052 weblogic nw-9000-06 JDBC Thin Client
399 909 weblogic nw-9000-06 JDBC Thin Client
400 91 weblogic nw-9000-06 JDBC Thin Client
401 61 weblogic nw-9000-06 JDBC Thin Client
402 63 weblogic nw-9000-06 JDBC Thin Client
403 2467 weblogic nw-9000-06 JDBC Thin Client
404 53 weblogic nw-9000-06 JDBC Thin Client
405 62 weblogic nw-9000-06 JDBC Thin Client
406 127 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
407 67 weblogic nw-9000-06 JDBC Thin Client
408 12333 weblogic nw-9000-06 JDBC Thin Client
410 20060 weblogic nw-9000-06 JDBC Thin Client
411 65 weblogic nw-9000-06 JDBC Thin Client
412 63 weblogic nw-9000-06 JDBC Thin Client
413 54 weblogic nw-9000-06 JDBC Thin Client
414 58 weblogic nw-9000-06 JDBC Thin Client
415 69 weblogic nw-9000-06 JDBC Thin Client
416 56 weblogic nw-9000-06 JDBC Thin Client
420 51 weblogic nw-9000-06 JDBC Thin Client
421 55 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
422 55 weblogic nw-9000-06 JDBC Thin Client
424 61 weblogic nw-9000-06 JDBC Thin Client
426 39342 weblogic nw-9000-06 JDBC Thin Client
428 46 weblogic nw-9000-06 JDBC Thin Client
468 20839 weblogic nw-9000-06 JDBC Thin Client
471 48166 weblogic nw-9000-06 JDBC Thin Client
475 54276 weblogic nw-9000-06 JDBC Thin Client
478 24234 weblogic nw-9000-06 JDBC Thin Client
479 8998 weblogic nw-9000-06 JDBC Thin Client
482 15969 weblogic nw-9000-06 JDBC Thin Client
492 21510 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
493 2729 weblogic nw-9000-06 JDBC Thin Client
495 17377 weblogic nw-9000-06 JDBC Thin Client
496 19009 weblogic nw-9000-06 JDBC Thin Client
497 31287 weblogic nw-9000-06 JDBC Thin Client
499 39652 weblogic nw-9000-06 JDBC Thin Client
502 44255 weblogic nw-9000-06 JDBC Thin Client
503 47851 weblogic nw-9000-06 JDBC Thin Client
514 44299 weblogic nw-9000-06 JDBC Thin Client
519 23409 weblogic nw-9000-06 JDBC Thin Client
521 36626 weblogic nw-9000-06 JDBC Thin Client
526 39049 weblogic nw-9000-06 JDBC Thin Client
SID SN OSUSER MACHINE PROGRAM
---------- ---------- ---------- -------------------- --------------------
527 19197 weblogic nw-9000-06 JDBC Thin Client
100 rows selected.
SQL> declare
2 begin
3 for i in (select sid,serial# sn from v$session where username='SIPO132')
4 loop
5 execute immediate 'alter system kill session '||chr(39)||i.sid||','||i.sn||chr(39);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select sid,serial# sn,osuser,machine,program
2 from v$session
3 where username='SIPO132'
4 and status!='KILLED';
no rows selected
(4):此时,删除后重建即可
SQL> drop user sipo132 cascade;
User dropped.
SQL> create user sipo132 identified by sipo default tablespace users temporary tablespace temp;
User created.
SQL> grant dba to sipo132;
Grant succeeded.
SQL> exit
(5):在oracle用户中,impdp导入。此时会报出一个ora-31684:object type user:"sipo132" already exists的错误,这个可以忽略,因为我们在导入前已经建立了这个用户,且授予了权限。这么做为了方便起见。
oracle@cpic-Dtest-01:~> impdp sipo132/sipo directory=data_pump_dir dumpfile=SIPO_20140606.DMP logfile=impdp-20140611.log remap_schema=SIPO:SIPO132 remap_tablespace=SIPO_T01:USERS