今天在测试用数据泵同时迁移多个SCHEMA时，遇到PRODUCT_USER_PROFILE警告。数据库版本为10.2.0.1.0 for OEL5.7 32bit。

迁移命令如下：

expdp system/oracle directory=xxx dumpfile=xxx.dmp schemas=dbdream,stream

impdp system/oracle directory=xxx dumpfile=xxx.dmp schemas=dbdream,stream

迁移完成后，在数据库中查看用户都自动创建了，而且数据也都加载进去了，可是切换用户的时候，遇到上述的警告。

SQL> select username from dba_users order by created;

USERNAME

------------------------------

SYS

SYSTEM

OUTLN

DIP

TSMSYS

DBSNMP

DBDREAM

STREAM

8 rows selected.

SQL> conn dbdream/dbdream

Error accessing PRODUCT_USER_PROFILE

Warning:  Product user profile information not loaded!

You may need to run PUPBLD.SQL as SYSTEM

这只是个警告，并不影响用户登录和正常使用，是用户通过sqlplus登录的时候验证PRODUCT_USER_PROFILE表失败导致的，到这这个问题的原因应该是使用system用户导入普通用户的时候，没有更新PRODUCT_USER_PROFILE表。解决方法很简单，照着提示做就可以了，以system用户运行pupbld.sql脚本即可。这个脚本在$ORACLE_HOME/sqlplus/admin目录下。

SQL> conn system/oracle

SQL> @?/sqlplus/admin/pupbld.sql

DROP SYNONYM PRODUCT_USER_PROFILE
*

ERROR at line 1:

ORA-01434: private synonym to be dropped does not exist

DATE_VALUE FROM PRODUCT_USER_PROFILE
*

ERROR at line 3:

ORA-00942: table or view does not exist

DROP TABLE PRODUCT_USER_PROFILE
*

ERROR at line 1:

ORA-00942: table or view does not exist

ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*

ERROR at line 1:

ORA-00942: table or view does not exist
Table created.

DROP TABLE PRODUCT_PROFILE
*

ERROR at line 1:

ORA-00942: table or view does not exist

DROP VIEW PRODUCT_PRIVS
*

ERROR at line 1:

ORA-00942: table or view does not exist

View created.

Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
*

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
*

ERROR at line 1:

ORA-01434: private synonym to be dropped does not exist

Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*

ERROR at line 1:

ORA-01432: public synonym to be dropped does not exist

Synonym created.

从运行脚本的日志可以看出，PRODUCT_USER_PROFILE表在跑脚本之前是不存在的，所以会有上述的警告。跑完脚本后，再次切换用户，就不会有这个警告了。

SQL> conn dbdream/dbdream

SQL>

本文固定链接: https://www.dbdream.com.cn/2014/07/1501/ | 信春哥，系统稳，闭眼上线不回滚！