You can use expdp and impdp utilities to connect to a currently running job and displays the real time processes. In interactive command mode you can request job status. You can also see status and information through Data Pump views: DBA_DATAPUMP_JOBS which shows all active DP jobs and master tables associated with active jobs and DATA_DATAPUMP_SESSIONS which IDs user sessions attached to DP jobs, this is helpful in determining why DP job is stopped. Additionally, V$SESSION_LONGOPS shows DP jobs transferring data and maintains an entry to give current progress. V$SESSION _LONGOPS will gives estimated transfer size and size transferred.
The following commands are applicable when using impdp or expdp in Interactive mode:
ADD_FILE – Add additional dump files.
CONTINUE_CLIENT – Exit interactive mode and enter logging mode.
EXIT_CLIENT – Stop the import or export client session, but leave the job running.
KILL_JOB – Detach all currently attached client sessions and terminate the current job.
PARALLEL – Increase or decrease the number of active worker processes for the current job.
START_JOB – Restart a stopped job to which you are attached.
STATUS – Display detailed status for the current job and/ or set status interval.
STOP_JOB – Stop the current job for later restart.
This article will give an example of using expdp utility in interactive mode to attach to a current running job to view progress.
- Create two Logons to your server as the oracle software owner.
[larry@linux2 ~]$ su – oracle
Password:
Last login: Wed Jan 11 08:17:10 EST 2016 on pts/1
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 ~]$
- Logon to your database as the user sysdba and create the directory dmpdir.
[oracle@linux2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 11 14:20:29 2016
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, Real Application Testing
and Unified Auditing options
SQL> create directory dmpdir as ‘/home/oracle’;
Directory created.
SQL>
- Log out of SQLPLUS and change directory to oracle software owners home directory.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
[oracle@linux2 ~]$ cd
[oracle@linux2 ~]$
- Execute the command:
expdp \”/ as sysdba\” dumpfile=orcl.dmp directory=dmpdir full=y job_name=orcl_full
[oracle@linux2 ~]$ expdp \”/ as sysdba\” dumpfile=orcl.dmp directory=dmpdir full=y job_name=orcl_full
Export: Release 12.1.0.2.0 – Production on Wed Jan 11 14:34:49 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
FLASHBACK automatically enabled to preserve database integrity.
Starting “SYS”.”ORCL_FULL”: “/******** AS SYSDBA” dumpfile=orcl.dmp directory=dmpdir full=y job_name=orcl_full
Estimate in progress using BLOCKS method…
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
- Switch to your secondary screen
expdp \”/ as sysdba\” attach=orcl_full status=1
[oracle@linux2 ~]$ expdp \”/ as sysdba\” attach=orcl_full status=1
Export: Release 12.1.0.2.0 – Production on Wed Jan 11 14:35:18 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
Job: ORCL_FULL
Owner: SYS
Operation: EXPORT
Creator Privs: TRUE
GUID: 45D7B1AF11C77B0FE053620F1E0A3C9A
Start Time: Wednesday, 11 January, 2016 14:34:52
Mode: FULL
Instance: orcl
Max Parallelism: 1
Timezone: +00:00
Timezone version: 18
Endianness: LITTLE
NLS character set: WE8MSWIN1252
NLS NCHAR character set: AL16UTF16
EXPORT Job Parameters:
Parameter Name Parameter Value:
CLIENT_COMMAND “/******** AS SYSDBA” dumpfile=orcl.dmp directory=dmpdir full=y job_name=orcl_full
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/orcl.dmp
bytes written: 4,096
Worker 1 Status:
Instance ID: 1
Instance name: orcl
Host name: linux2.localdomain
Process Name: DW00
State: EXECUTING
Object Schema: SYS
Object Type: DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Completed Objects: 3
Worker Parallelism: 1
Export>
- Once in interactive mode you can continue to ask for updates on job. with status execution.
- This completes monitoring of data pump job from EXPDP or IMPDP utility.
Larry Catt
OCP