12c pdb expdp use DATA_PUMP_DIR meet ORA-39145
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIRis invalid
原因如下:
If none of the previous three conditions yields a directory object and you are a privileged user (i.e. uses who have the EXP_FULL_DATABASE role and IMP_FULL_DATABASE role), then Data Pump attempts to use the value of the default server-based directory object, DATA_PUMP_DIR.
It is important to understand that Data Pump does not create the DATA_PUMP_DIR directory object; it merely attempts to use its value when a privileged user has not provided a directory object using any of the mechanisms previously described. This default directory object must first be created by a DBA. Do not confuse this with the client-based environment variable of the same name.
Example to create all files in the directory DATA_PUMP_DI
DATA_PUMP_DIR 目录需要有EXP_FULL_DATABASE role and IMP_FULL_DATABASE role ,而12c pdb 下的system 用户没有该roll:
在cdb 下 system: PRIVILEGE 列为空,
SET lines 80
COL grantee FORMAT a20
COL privilege FORMAT a10
SELECT directory_name, grantee, privilege,DIRECTORY_PATH
FROM user_tab_privs t, all_directories d
WHERE t.table_name(+)=d.directory_name
ORDER BY 1,2,3;
DIRECTORY_NAME
--------------------------------------------------------------------------------
GRANTEE PRIVILEGE
-------------------- ----------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATAPUMP
/centrproddblog/dump
DATA_PUMP_DIR
/opt/oracle12c/admin/centrprod/dpdump/
OPATCH_INST_DIR
/opt/oracle12c/product/12.1/OPatch
OPATCH_LOG_DIR
/opt/oracle12c/product/12.1/QOpatch
OPATCH_SCRIPT_DIR
/opt/oracle12c/product/12.1/QOpatch
XSDDIR
/opt/oracle12c/product/12.1/rdbms/xml/schema
8 rows selected.
SQL> SQL>
在pdb 下 system: 即便给了读写权限给system, 仍然没有IMP_FULL_DATABASE rool;
SQL> /
DIRECTORY_NAME
--------------------------------------------------------------------------------
GRANTEE PRIVILEGE
-------------------- ----------
DIRECTORY_PATH
--------------------------------------------------------------------------------
DATA_PUMP_DIR
SYSTEM READ
/opt/oracle12c/admin/centrprod/dpdump/
DATA_PUMP_DIR
SYSTEM WRITE
/opt/oracle12c/admin/centrprod/dpdump/
OPATCH_INST_DIR
/opt/oracle12c/product/12.1/OPatch
OPATCH_LOG_DIR
/opt/oracle12c/product/12.1/QOpatch
OPATCH_SCRIPT_DIR
/opt/oracle12c/product/12.1/QOpatch
解决办法:
1.pdb 下手工创建directory datapump :
create or replace directory datapump as '/centruatdblog/dump';
grant read,write on directory datapump to public;
2 使用 datapump 替代 DATA_PUMP_DIR
Solution for user SCOTT: as described in 5.3. user SCOTT can set the environment variable DATA_PUMP_DIR to MY_DIR:
C:\> expdp scott/tiger@my_db_alias DUMPFILE=expdp_s.dmp LOGFILE=expdp_s.log SCHEMAS=scott
refer:
Export/Import DataPump Parameter DIRECTORY - How to Specify a Directory (文档 ID 266875.1)
相关文章
- What is Big Data?
- Elasticsearch-->Get Started--> Exploring Your Data
- error CS0234: 命名空间“System.Web”中不存在类型或命名空间名称“Optimization”(是否缺少程序集引用?)“,“data“:null}
- 「基于Django的全民健康智慧中医数字服务平台」后端应用General_Data设计思路
- Content type ‘multipart/form-data;boundary=--------------------------9107
- function(data)
- 《Core Data应用开发实践指南》一2.5 Integer 16、Integer 32与Integer 64
- 《Core Data应用开发实践指南》一2.10 代码片段:demo方法
- [Oracle]跨越 DBLINK 访问表时,数据缓存在何处的Data Buffer 中?
- CYQ.Data V5 MDataTable 专属篇介绍
- Python requests.post方法中data与json参数区别
- 删除Management Data Warehouse (MDW) job失败
- Data Visualization Tools 数据可视化工具