Data Pump Import/Export is Running Slow or Hangs

When Data pump is running slowly or when it is hanging, determining which query is currently running will help in understanding the problem. The following script will show the SQL statement executed with the current wait event every 10 minutes.

First, create a script named dp_current_sql.sql that contains the following code.

dp_current_sql.sql

Connect with sqlplus as SYSDBA. Determine the SPID of the Data Pump Worker (DW00) process with the following query.

Run the dp_current_sql.sql script you created above from sqlplus. When asked for the spid value, enter the SPID value detected in the above step.

When you run the script, it will produce output similar to the following. You can also find the output in the current_sql.out file in the current directory.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *