When you start import in Oracle database with impdp command, you may want to kill ( impdp ) or stop these jobs. I will explain the How to Kill or Stop Oracle Datapump Import ( KILL IMPDP ) Job in this post.
If you don’t know export and import in Oracle database, you should read following link.
Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4
Kill or Stop Oracle Datapump Import ( KILL IMPDP ) Job
Let’s say You want to IMPORT full database with impdp and you used following script.
Don’t forget that Nohup will be run following impdp command in background of operating system.
[[email protected] ~]$ nohup impdp \"/ as sysdba\" directory=PUMP dumpfile=FULL_EXPORT.dmp logfile=FULL_IMPORT.log & [1] 9310 [[email protected] ~]$ nohup: ignoring input and appending output to `nohup.out' [[email protected] ~]$
Check Import ( impdp ) job
Import has started with nohup and we check import process from database.
[[email protected] ~]$ [[email protected] ~]$ [[email protected] ]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 21 10:01:05 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> select JOB_NAME,state from dba_datapump_jobs; JOB_NAME STATE ------------------------------ ------------------------------ SYS_IMPORT_FULL_02 EXECUTING SYS_IMPORT_SCHEMA_02 NOT RUNNING SYS_IMPORT_SCHEMA_01 NOT RUNNING SYS_IMPORT_FULL_01 NOT RUNNING SYS_IMPORT_SCHEMA_03 NOT RUNNING SQL> SQL> exit Disconnected from Oracle Database 12c Release 12.1.0.2.0 - 64bit Production [[email protected] ~]$
Attach impdp job
You can see that IMPORT process is run with SYS_IMPORT_FULL_02 EXECUTING name like above.
We can attach or connect to this process like following
[[email protected] ~]$ [[email protected] ~]$ impdp \"/ as sysdba\" attach=SYS_IMPORT_FULL_02 IMPORT: Release 12.1.0.2.0 - Production on Fri Mar 22 12:17:08 2019 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Release 12.1.0.2.0 - 64bit Production Job: SYS_IMPORT_FULL_02 Owner: SYS Operation: IMPORT Creator Privs: TRUE GUID: 84AC4FBE3E0B2469E0530A20A8C0E1E2 Start Time: Friday, 22 March, 2019 12:16:37 Mode: FULL Instance: DEVECI12C Max Parallelism: 1 IMPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND "/******** AS SYSDBA" directory=PUMP dumpfile=FULL_EXPORT.dmp logfile=FULL_EXPORT.log State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /backup/IMPORT/FULL_EXPORT.dmp bytes written: 4,096 Worker 1 Status: Process Name: DW00 State: EXECUTING Object Name: +* Object Type: DATABASE_IMPORT/TRUSTED_DB_LINK Completed Objects: 1 Total Objects: 1 Worker Parallelism: 1 IMPORT>
You can check status of IMPORT process like following. We can see the status of IMPORT job in this way especially
IMPORT> status
Job: SYS_IMPORT_FULL_02
Operation: IMPORT
Mode: FULL
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /backup/IMPORT/FULL_EXPORT.dmp
bytes written: 4,096
Worker 1 Status:
Process Name: DW00
State: EXECUTING
Object Schema: SYSMAN
Object Type: DATABASE_IMPORT/SCHEMA/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed Objects: 3
Total Objects: 3
Worker Parallelism: 1
IMPORT>
KILL IMPORT ( IMPDP ) JOB
If you want to kill this IMPORT job, you can kill it with kill_job command like following.
IMPORT> kill_job
Are you sure you wish to stop this job ([yes]/no): yes
You can see that this IMPORT job is killed.
[[email protected] ~]$ [1]+ Done nohup impdp \"/ as sysdba\" directory=PUMP dumpfile=FULL_EXPORT.dmp logfile=FULL_EXPORT.log [[email protected] ~]$
Do you want to learn How to Kill or Stop Oracle Datapump Export ( EXPDP ) Job, then read the following post.
Do you want to learn Oracle Database for Beginners, then Click and read the following articles.
Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
1,748 views last month, 5 views today
Can you expand on this?
I will continue…