Data Pump is a common method for moving data from one schema to another.  Oftentimes, the easiest way to do this is to do a full schema export, followed by a full schema import using the appropriate ‘remap’ options.  Usually during a job like this, the user will already exist and it was this situation that uncovered an unexpected behavior in Data Pump.  In our situation, the weekly data copy job ran for 37 weeks and all of a sudden the job started to noticeably take longer and longer, until the point to where it would run for days and then never finish.

Upon investigation, we found that the data pump job was initially hanging on the import Data Pump during this step:

Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY

Because we didn’t really know what was going on and we needed the job to finish, we excluded “PASSWORD_HISTORY” from the import DataPump.  Then just a few short weeks later, the job then was exhibiting the same behavior on the export.  Clearly something more was going on.  After taking some time to analyze ASH reports, it was clear that the process was getting hung during processing of the USER_HISTORY$ table.

What is the USER_HISTORY$ table?

As it turns out, this table stores the actual password history for each user in the database.

SQL> desc user_history$
Name Null? Type
----------------------- -------- ----------------
USER# NOT NULL NUMBER
PASSWORD VARCHAR2(30)
PASSWORD_DATE DATE

This table is never purged so each time the user receives a password change, a row is written to this table.  In our case a single password change for this user resulted in millions of rows for this user over the 44 weeks that the job had been occurring and Data Pump was exacerbating the issue.  With each export / import Data Pump, and because we were alternating schemas, this one row became two, two became three, three became five and so on, until week 37 where the table had almost 15 million rows.

Demonstration:

To demonstrate the issue, we will use very simple parameter files and export / import the same schema:
expdp-HR2.par:

USERID='/ as sysdba'
DIRECTORY=EXP_DIR
DUMPFILE=EXP_DIR:hr_exp.dat
LOGFILE=EXP_DIR:hr_exp.log
SCHEMAS=HR2
CONTENT=ALL
COMPRESSION=ALL
REUSE_DUMPFILES=Y
JOB_NAME=EXPHR_JOB

impdp-HR2.par:

USERID='/ as sysdba'
DIRECTORY=EXP_DIR
DUMPFILE=hr_exp.dat
LOGFILE=hr_imp.log
TABLE_EXISTS_ACTION=REPLACE

We should initially check the user_history$ table to determine the baseline for a particular user:

SQL> select name, count(*)
2 from user_history$, user$
3 where user_history$.user# = user$.user#
4 and name like 'HR%'
5 group by name order by name;

NAME  COUNT(*)
----- ---------------
HR    2
HR2   6

We will then run an export and import using the parameter files above and then re-run the query:

SQL> select name,count(*)
 2 from user_history$ a, user$ b
 3 where a.user#=b.user#
 4 and name like 'HR%'
 5 group by name order by 1;

NAME  COUNT(*)
----- ---------------
HR    2
HR2   12

And sure enough, you see the rows for password history have doubled just from running the import 1 time!

Solution:

Luckily we now have two solutions available to us with minimal impacts.  Oracle has made a patch available (16811897) for all versions 11.2.0.3 and above, you can upgrade to 12.1.0.2 or you can exclude ‘PASSWORD_HISTORY’ from the export / import process.

Of course if you are running into this issue, make sure you test thoroughly as results may vary!