今天查看数据库的负载，发现cpu消耗异常的高。里面有不少dw的进程.但是查看impdp的进程却不存在。select * from dba_datapump_jobs where owner_name='MIG_TEST';SYS_IMPORT_TABLE_01 SYS_IMPORT_TABLE_02 SYS_IMPORT_TABLE_03 SYS_IMPORT_TABLE_04 SYS_IMPORT_TABLE_05 SYS_IMPORT_TABLE_06 SYS_IMPORT_TABLE_07 SYS_IMPORT_TABLE_08 SYS_IMPORT_TABLE_09 SYS_IMPORT_TABLE_10 SYS_IMPORT_TABLE_12 SYS_IMPORT_TABLE_13 SYS_IMPORT_TABLE_14 SYS_IMPORT_TABLE_15 SYS_IMPORT_TABLE_16 SYS_IMPORT_TABLE_17 SYS_IMPORT_TABLE_18 SYS_IMPORT_TABLE_20 SYS_IMPORT_TABLE_21 SYS_IMPORT_TABLE_23 SYS_IMPORT_TABLE_24 SYS_IMPORT_TABLE_25 SYS_IMPORT_TABLE_26 SYS_IMPORT_TABLE_28 。。。。

查看数据库的session情况，发现有一个临时测试用户下有120多个session.这个环境平时没有人用，不会有那么多的session.没有impdp的任务，查看那个临时用户，发现下面有很多SYS_IMPORT_TABLE_xx的表，ETxxx, ERRxxx的表。这些表都是datapump在数据导入的时候创建的临时表。ERR的表类似下面的形式。ERR$DP13FC20810001ERR$DP16F10AF10001ERR$DP1AA200C30001ERR$DP1C125B790001ERR$DP1C1B86AF0001

ET的表类似下面的形式。

查看里面的内容也是被禁止的。SQL> select *from ET$1B5C6DCF0001;ERROR:ORA-29913: error in executing ODCIEXTTABLEOPEN calloutKUP-11024: This external table can only be accessed from within a Data Pumpjob.既然没有对应的impdp的任务来跑，那些session也就是在空跑了。和同事确认了下，这个测试中的数据任务执行时间太长，就被删除了进程。没想到杀掉impdp的进程，对应的parallel process还是存在，把一张分区表分成了100份，每一份包含一些分区的数据，这样并行4个，下来就有120个左右的session了，他们使用的脚本类似下面的形式。nohup impdp xxxx/xxxx directory=memo_dir dumpfile=par1_xxxxxxxx.dmp parallel=2 include=table_data logfile=par1_xxxxxxxx_impdp.log parfile=xxxxxxxx_partition_parallel_1.par_impdp TABLE_EXISTS_ACTION=append REMAP_SCHEMA=prdappo:MIG_TEST DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS &nohup impdp xxxx/xxxx directory=memo_dir dumpfile=par2_xxxxxxxx.dmp parallel=2 include=table_data logfile=par2_xxxxxxxx_impdp.log parfile=xxxxxxxx_partition_parallel_2.par_impdp TABLE_EXISTS_ACTION=append REMAP_SCHEMA=prdappo:MIG_TEST DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS &nohup impdp xxxx/xxxx directory=memo_dir dumpfile=par3_xxxxxxxx.dmp parallel=2 include=table_data logfile=par3_xxxxxxxx_impdp.log parfile=xxxxxxxx_partition_parallel_3.par_impdp TABLE_EXISTS_ACTION=append REMAP_SCHEMA=prdappo:MIG_TEST DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS &nohup impdp xxxx/xxxx directory=memo_dir dumpfile=par4_xxxxxxxx.dmp parallel=2 include=table_data logfile=par4_xxxxxxxx_impdp.log parfile=xxxxxxxx_partition_parallel_4.par_impdp TABLE_EXISTS_ACTION=append REMAP_SCHEMA=prdappo:MIG_TEST DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS &nohup impdp xxxx/xxxx directory=memo_dir dumpfile=par5_xxxxxxxx.dmp parallel=2 include=table_data logfile=par5_xxxxxxxx_impdp.log parfile=xxxxxxxx_partition_parallel_5.par_impdp TABLE_EXISTS_ACTION=append REMAP_SCHEMA=prdappo:MIG_TEST DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS &确认了下，准备删除session。可以使用如下的sql来查找对应的session，确认后删除。select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39) ||';' from v$session where username='MIG_TEST';尝试使用attach来手工停掉job.但是对应的process还是没有停掉。> impdp mig_test/mig_test attach=SYS_IMPORT_TABLE_02Import: Release 11.2.0.2.0 - Production on Wed Jun 18 19:08:05 2014Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsJob: SYS_IMPORT_TABLE_02 Owner: MIG_TEST Operation: IMPORT Creator Privs: FALSE GUID: FC1B7F5BBC97829EE040007F01004205 Start Time: Wednesday, 18 June, 2014 19:08:20 Mode: TABLE Instance: PETCUS1 Max Parallelism: 4 EXPORT Job Parameters: CLIENT_COMMAND xxxxx/******** directory=memo_dir dumpfile=par10_mo1_memo.dmp logfile=par10_mo1_memo_expdp.log parfile=mo1_memo_partition_parallel_10.par_imp exclude=statistics,constraint,ref_constraint,index,comment,grant IMPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND mig_test/******** directory=memo_dir dumpfile=par10_mo1_memo.dmp parallel=4 logfile=par10_mo1_memo_impdp.log parfile=mo1_memo_partition_parallel_10.par_impdp TABLE_EXISTS_ACTION=append REMAP_SCHEMA=prdappo:MIG_TEST DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS DATA_OPTIONS 1 TABLE_EXISTS_ACTION APPEND State: IDLING Bytes Processed: 0 Current Parallelism: 4 Job Error Count: 0 Dump File: /dbccbsPT1/oracle/PETCUS1/archive/memo_test/par10_mo1_memo.dmp Worker 1 Status: Process Name: DW1H State: UNDEFINED Object Schema: MIG_TEST Object Name: MO1_MEMO Object Type: TABLE_EXPORT/TABLE/TABLE_DATA Completed Objects: 1 Total Objects: 10 Completed Bytes: 305,547,744 Worker Parallelism: 1 Worker 2 Status: Process Name: DW1J State: UNDEFINED Worker 3 Status: Process Name: DW1I State: UNDEFINED Worker 4 Status: Process Name: DW1C State: UNDEFINED Import> stopAre you sure you wish to stop this job ([yes]/no): yes尝试使用kiill选项，直接hang住了。但是查看主机的cpu消耗，还是异常的高。对应的并行session对应的进程还是在，而且消耗了大量的资源。最后尝试删除对应的et,err,export临时表。删除后，对应的dw process就消失了。删除drop table "ET$0ECF04B90001"; drop table "ET$0F8D29250001"; drop table "ET$104D1D5D0001"; drop table "ET$133B1CDD0001"; drop table "ET$13FC20810001"; drop table "ET$16F10AF10001"; drop table "ET$1AA200C30001"; drop table "ET$1C125B790001"; drop table SYS_IMPORT_TABLE_21 ;drop table SYS_IMPORT_TABLE_23 ;drop table SYS_IMPORT_TABLE_24 ;drop table SYS_IMPORT_TABLE_25 ;drop table SYS_IMPORT_TABLE_26 ;drop table SYS_IMPORT_TABLE_28 ;

。。。。