执行EXPDP操作时出现ORA-29283: invalid file operation错误

最新推荐文章于 2025-03-03 11:22:02 发布

原创最新推荐文章于 2025-03-03 11:22:02 发布 · 1.8w 阅读

3 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#file #processing #object #table #64bit

在执行Oracle EXPDP导出操作时遇到ORA-29283错误，原因是尝试打开的目录不存在。通过创建指定的目录/home/oracle/testdump并重新运行，问题得到解决。此错误与64位系统、对象或表统计无关，主要是文件操作相关。

执行EXPDP操作时出现ORA-29283: invalid file operation错误

SQL> create or replace directory DUMPDIR 2 as '/home/oracle/testdump/';

Directory created.

$ cat test.sh date expdp test/test@tstdb dumpfile=test_onetable.dmp directory=dumpdir tables=table1 logfile=n job_name=exptab parallel=4 date $

$ . /home/oracle/test.sh Fri Nov 10 11:20:35 EAT 2006

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 10 November, 2006 11:20:35

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation

Fri Nov 10 11:20:43 EAT 2006

在网上搜了半天，还在itpub上发了贴，半天都没有找到解决办法，只好决定自己再次详细检查一下，结果发现是如下原因所致：

SQL> create or replace directory DUMPDIR 2 as '/home/oracle/testdump/';

Directory created.

SQL> host ls /home/oracle/testdump /home/oracle/testdump not found

创建文件夹/home/oracle/testdump 后，如上test.sh可以正确运行：

$ mkdir testdump $ . /home/oracle/test.sh Fri Nov 10 15:06:47 EAT 2006

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 10 November, 2006 15:06:47

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, OLAP and Data Mining options Starting "test"."EXPTAB": test/********@tstdb dumpfile=test_onetable.dmp directory=dumpdir tables=product_attr_i_p_l logfile=test.log job_name=exptab parallel=4 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 80 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "test"."PRODUCT_ATTR_I_P_L" 66.67 MB 1937788 rows Master table "test"."EXPTAB" successfully loaded/unloaded ****************************************************************************** Dump file set for test.EXPTAB is: /home/oracle/testdump/test_onetable.dmp Job "test"."EXPTAB" successfully completed at 15:07:19

Fri Nov 10 15:07:22 EAT 2006 $

删除文件夹/home/oracle/testdump 后，运行test.sh错误重现： $ rmdir testdump

$ ls testdump testdump not found

$ . /home/oracle/test.sh Fri Nov 10 14:59:00 EAT 2006

Export: Release 10.2.0.1.0 - 64bit Production on Friday, 10 November, 2006 14:59:00

可见，在sqlplus中创建DIRECTORY时，oracle并不检查引用的操作系统文件夹是否已经存在，应该仔细检查。