Oracle ORA-29283: 无效的文件操作

嗨，汤姆，

感谢您与我们在一起，并感谢您多年来的支持。

从最近几天开始，我在从应用程序中写入文件时遇到错误，即
异常信息: ORA-29283: 无效的文件操作。
但是，如果我从SQL developer运行相同的过程，它可以正常工作。

并且此错误与特定过程无关。任何程序都可能出现这种情况。

假设我的应用程序调用50个过程将数据一个一个地写入文件，不能确定当这个错误会来的时候，它可能会在调用50个过程时出现。

以前，所有东西都运行良好，没有授予和操作系统级别权限的问题，
我已经交叉检查，并且Sql developer的过程运行良好。


我还尝试在调用过程之间将过程延迟5秒钟，但仍然会出现错误。

请提出一些建议。

你得到这个是因为数据库由于某种原因无法访问文件。通常是因为文件权限。

但是，如果这是间歇性发生的，则表明您的进程正在破坏彼此的文件访问。例如，一个会话在另一个会话尝试访问它之前删除该文件。

为了提供更有意义的帮助，我们需要查看您的代码!与你如何调用这个得到一个错误的描述。

为了进一步阅读，MOS note 746882.1讨论了ORA-29283的各种原因和解决方案:

Goal

This article will explain you the general scenario when ORA-29283 (INVALID FILE OPERATION) can be thrown from UTL_FILE package while accessing operating system file and how to debug the exception
Solution

The error can be raised because of the following causes.
General causes and solutions of ORA-29283 error:


1. Check whether the schema where the PLSQL code is run has READ,WRITE permission on the database directory.

If SCOTT is the schema , then login as SYS grant required permission.
GRANT READ,WRITE ON DIRECTORY TO SCOTT



2. Check whether the path used in database directory physically exists on the OS.

Eg :
CREATE OR REPLACE DIRECTORY DIRNAME as '/home/test';


/home/test should exist on the OS. When the database directory is created , oracle doesnt check the validity of the directory path used. The validation of the path is done at the runtime. Hence it is necessary to check the file path on OS before the program is executed.

3. Check whether the owner of oracle process has enough permission on the OS directory where the file is accessed.
]$ ls -l /u01/app/oracle/product/10.2/db_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 96725778 Jul 7 2008 /u01/app/oracle/product/10.2/db_1/bin/oracle

"oracle" is the owner of the oracle executable. oracle - user should have enough permission on the directory.

]$ su oracle
Password:
]$ whoami
oracle
]$touch /home/test/abc.txt


You should be able to create a file using touch command login as the owner of the oracle process.

4. Make sure you have restarted the listener once you have done any OS level changes to the directories accessed by RDBMS.
Once you have done any changes in OS level permission, you should always restart the listener so that it inherits the recently changed permission.

Else oracle will sometime raise a ora-29283 error.

5. Even though oracle-user has enough permissions on the directory, it may raise ora-29283 if the owner of the file is not oracle.

If the file is created with the rw-r-- permission and owned by another user, RDBMS wont be able to read the file. In such case you will have to change the permissions of the file to rw-rw--
chmod 750



6. Using remote directories :

UTL_FILE package can access only server side files where the database instance is running. You cannot access client side files. If you are using UNIX system, then create a NFS mount of the client folder on the server . If on Windows platform then go through

Note 45172.1 : Running UTL_FILE on Windows NT

Note 1034188.6: INVALID_OPERATION Exception from UTL_FILE when Writing to/from Network Drive

Solution:

Start the Oracle service as a user who has the same permissions as SYSTEM, and also who has access to the shared directory.

7. Check ORA_NLS on application server :

If the ORA_NLSXX where XX is 32, 33 or 10 is set, it must be set before starting the database and on the client side too.

Note 77442.1 : ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained