oracle impdp/expdp 的权限问题

直接运行 expdp scott/tiger
系统报错：
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null

网上说要把在 data_dump_dir 上的 read,write 的权限赋予scott。按照此说法做后，发现问题依然。
grant dba to scott 权限后，问题解决。

其实问题的核心不在于dba权限，而在于 EXP_FULL_DATABASE / IMP_FULL_DATABASE 角色。

oracle的官方文档中早就有了此问题和原因（Oracle® Database Utilities）：
The following is an example of using the default DATA_PUMP_DIR directory object available to privileged users. This example assumes that the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles have been granted to the user hr. After the DATA_PUMP_DIR directory object has been created, a privileged user need not use the DIRECTORY parameter. Dump files, log files, and SQL files will be written to the path associated with DATA_PUMP_DIR.

SQL> CREATE DIRECTORY data_pump_dir AS '/usr/dba/dpumpfiles':

> expdp hr/hr DUMPFILE=emp.dmp LOGFILE=emp.log TABLES=hr.employees

The emp.dmp and emp.log files will be written to /usr/dba/dpumpfiles.

If the DATA_PUMP_DIR directory object had not first been created by a DBA, then the following error messages would have been displayed:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

Remember that the default DATA_PUMP_DIR directory object is not available to nonprivileged users. In the following example, user sh is a nonprivileged user. Therefore, because no directory object is specified, error messages are generated and the export is not performed.

> expdp sh/sh DUMPFILE=sales.dmp LOGFILE=sales.log TABLES=sh.sales
 
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39145: directory object parameter must be specified and non-null


Thomas Kyte建议的要从头到尾看 Oracle Concept Guide，绝对是见很正确的事。

原文地址：http://www.douban.com/note/37656300/