##sample 中文解释：导出 schema_详解Oracle数据库终止正在进行expdp导出数据的正确操作
https://blog.csdn.net/weixin_39631767/article/details/112714194
概述
今天在做expdp导出时因没预估好数据量，所以需终止正在进行expdp导出数据的任务。那么怎么正确停止expdp导出任务呢？下面介绍一下我的操作过程。
1、不能用ctrl+c来终止导出(演示)
按照以前的习惯，在进行oracle数据库数据导出操作时，大家一般都会使用组合键“CTRL+C”来终止导出操作。但这种方法在expdp导出数据时，却不能使用，因为虽然可以用ctrl+c终止expdp进程，但数据库导出任务仍在继续，磁盘空间仍在变小。
可以观察到导出的数据还是在增加的。
2、查询状态
--ctrl+C终止的任务运行状态仍为EXECUTINGselect job_name,state from dba_datapump_jobs
3、stop_job命令来终止导出
3.1、进入命令行
expdp glogowner/xxx ATTACH=SYS_EXPORT_SCHEMA_04
3.2、停止job
在 “Export> ”提示符下输入命令：stop_job=immediate
回车后，再输入yes确定结束当前任务，即可正确终止正在进行expdp导出数据的任务。
4、验证
select job_name,state from dba_datapump_jobs;
所以以后不要随随便便用ctrl+c来中断任务，数据库的操作一定要三思而后行，后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
5.咨询了原厂
确定没有备份任务在跑的情况下，表是可以清理的
Base on the check of the info.html and referenced How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? ( Doc ID 336014.1 )
We can drop the NBMSDATA.SYS_EXPORT_SCHEMA* tables manually.
----------- 运行如下sql :
conn / as sysdba
set pages 1000
set num 20
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff';
alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss.ff tzh:tzm';
set mark html on
spool info.html
SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;
select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created ,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like 'ET$%';
select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
order by 1,2;
SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;
spool off
exit
————————————————
版权声明：本文为CSDN博主「weixin_39631767」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_39631767/article/details/112714194
## sample 英文解释：
But job name SYS_EXPORT_<mode>_NN does not mean a datapump job ran as SYS. It's a system-generated default name for any user, when no explicit job name is given.
But job name SYS_EXPORT_<mode>_NN does not mean a datapump job ran as SYS. It's a system-generated default name for any user, when no explicit job name is given.
SQL> select JOB_NAME,OWNER_NAME,STATE from DBA_DATAPUMP_JOBS;
JOB_NAME OWNER_NAME STATE
------------------------------ ------------------------------ ------------------------------
SYS_EXPORT_SCHEMA_19 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_16 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_14 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_06 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_18 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_03 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_12 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_15 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_11 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_02 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_20 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_09 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_17 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_05 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_21 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_13 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_01 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_22 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_07 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_08 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_10 SYSTEM NOT RUNNING
SYS_EXPORT_SCHEMA_04 SYSTEM NOT RUNNING
22 rows selected.
Regards
Jewel
Share on Twitter
Share on Facebook
Mohamed ELAzab
Mohamed ELAzab Member Posts: 816
Mar 16, 2011 5:44AM
Hello,
If you run the job using a username you will find it as:
"username"."SYS_IMPORT_TABLE_01"
As already said the correct command is KILL_JOB:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1007241
It's not normal to have these tables (at least so many),
they are deleted automatically after a successful datapump completion.
Obviously someone issued STOP_JOB commands or there were execution failures.
Hello,
just do the following :
expdp system/password attache=SYS_EXPORT_SCHEMA_19
and
expdp system/password attache=SYS_EXPORT_SCHEMA_16
.
.
.
.
etc
then it will prompt you for
export>
type
export>help
a number of choices will apear:
KILL_JOB
type
export>KILL_JOB
Kind regards
Mohamed ElAzab