某客户在导出某用户下的表数据发现磁盘空间不够,只能ctrl+C 结束导出进程,不过在执行该操作后发现并没有结束导出,dmp文件还在不断增大。

操作系统版本:Redhat6.5

数据库版本:ORACLE 11.2.0.4

导出语句:nohup expdp rhip/sywsj123456 directory=backup CONTENT=DATA_ONLY dumpfile=rhip2.dmp logfile=export3.log COMPRESSION &

判断可能位导出job没有被结束

思路:查出导出job,利用attach参数连接到job,然后停止job

 

查询:

select * from dba_datapump_jobs

OWNER_NAME                                                   JOB_NAME

———————————————————— ————————————————————

OPERATION

——————————————————————————————————————————————————————————————————–

JOB_MODE

——————————————————————————————————————————————————————————————————–

STATE                                                            DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

———————————————————— ———- —————– —————–

RHIP                                                         SYS_EXPORT_SCHEMA_01

EXPORT

SCHEMA

STOPPING                                                              1                 0                 2

 

利用attach参数连接至job

[oracle@his1 backup]$ expdp rhip/sywsj123456 attach=SYS_EXPORT_SCHEMA_01

 

Export: Release 11.2.0.3.0 – Production on Wed Dec 28 15:46:32 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

Job: SYS_EXPORT_SCHEMA_01

  Owner: RHIP                          

  Operation: EXPORT                        

  Creator Privs: TRUE                          

  GUID: 44B40C4E10031664E053B00C4DAC7697

  Start Time: Wednesday, 28 December, 2016 15:38:01

  Mode: SCHEMA                         

  Instance: hisdb1

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        rhip/******** directory=backup CONTENT=DATA_ONLY dumpfile=rhip2.dmp logfile=export2.log

     INCLUDE_METADATA      0

  State: EXECUTING                     

  Bytes Processed: 20,611,525,808

  Percent Done: 31

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /oracle/app/oracle/backup/rhip2.dmp

    bytes written: 20,611,534,848

 

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: RHIP

  Object Name: REGISTATION

  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 4

  Total Objects: 540

  Completed Rows: 9,475,723

  Worker Parallelism: 1

 

停止job,并输入yes确定

Export> stop_job

Are you sure you wish to stop this job ([yes]/no): yes

 

 

后续发现还没有结束进程,dba_datapump_jobs视图中还是有记录,尝试连接jobkill_job

 

[oracle@his1 backup]$ expdp rhip/sywsj123456 attach=SYS_EXPORT_SCHEMA_01

 

Export: Release 11.2.0.3.0 – Production on Wed Dec 28 15:49:56 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

Job: SYS_EXPORT_SCHEMA_01

  Owner: RHIP                          

  Operation: EXPORT                        

  Creator Privs: TRUE                          

  GUID: 44B40C4E10031664E053B00C4DAC7697

  Start Time: Wednesday, 28 December, 2016 15:38:01

  Mode: SCHEMA                        

  Instance: hisdb1

  Max Parallelism: 1

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        rhip/******** directory=backup CONTENT=DATA_ONLY dumpfile=rhip2.dmp logfile=export2.log

     INCLUDE_METADATA      0

  State: STOP PENDING                  

  Bytes Processed: 31,162,869,080

  Percent Done: 53

  Current Parallelism: 1

  Job Error Count: 0

  Dump File: /oracle/app/oracle/backup/rhip2.dmp

    bytes written: 31,162,884,096

 

Worker 1 Status:

  Process Name: DW00

  State: EXECUTING                     

  Object Schema: RHIP

  Object Name: BALANCE

  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA

  Completed Objects: 7

  Total Objects: 540

  Completed Rows: 11,200,549

  Worker Parallelism: 1

 

输入kill_job 结束job

Export> kill_job

Are you sure you wish to stop this job ([yes]/no): yes

 

查看数据库导出日志,导出被用户终止

[oracle@his1 backup]$ more export2.log

Export: Release 11.2.0.3.0 – Production on Wed Dec 28 15:37:59 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

;;;

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

Starting "RHIP"."SYS_EXPORT_SCHEMA_01":  rhip/******** directory=backup CONTENT=DATA_ONLY dumpfile=rhip2.dmp logfile=export2.log

Estimate in progress using BLOCKS method…

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 63.27 GB

. . exported "RHIP"."PRESCRIPTIONDETAILS"                7.565 GB 37643349 rows

. . exported "RHIP"."CLINICFEE"                          5.568 GB 10540584 rows

. . exported "RHIP"."REGISTATION"                        4.911 GB 9861277 rows

. . exported "RHIP"."JKBACK"                             434.2 MB   90146 rows

. . exported "RHIP"."OUTINVOICEINF"                      4.491 GB 20454907 rows

 

Job "RHIP"."SYS_EXPORT_SCHEMA_01" stopped by user request at 16:16:27




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

no comment untill now

Add your comment now

无觅相关文章插件