ORA-29285: file write error

I got ” ORA-29285: file write error ”  error in Oracle database.

 

ORA-29285: file write error

 

Details of error are as follows.

ORA-29285 - file write error

Cause - Failed to write to, flush, or close a file.

Action - Verify that the file exists, that it is accessible, and that it is open in write or append mode.

 

 

 

file write error

This ORA-29285 error is related with the Failed to write to, flush, or close a file.

Verify that the file exists, that it is accessible, and that it is open in write or append mode.

 

Calling UTL_FILE.PUT_LINE repeatedly in a loop when writing cumulatively more than 1024  characters may fail with the following error:

ORA-29285: file write error

In some cases, ORA-29283:  invalid file operation may also be thrown.

The environment is running Oracle 10.2  RDBMS or later and connecting from SQL*Plus client via a database listener. However this can apply to other environments

The same SQL script works successfully when run locally (BEQUEATH) from the 10.2 Database or later.

For Example:

The following sample code which writes 100 characters at a time – loop 11 times to produce 1100 characters.

 

-- create a DIRECTORY Object 'MYDIR'
-- e.g. create or replace directory MYDIR as '/tmp/mydir';
--
DECLARE
  file_name VARCHAR2(256) := 'mydoc.lst';
  file_text VARCHAR2(100) := '1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890';
  file_id UTL_FILE.file_type;
BEGIN
  file_id := UTL_FILE.fopen('MYDIR', file_name, 'W');
  FOR x IN 1..11 LOOP -- write 11 records
     UTL_FILE.put_line(file_id, file_text);
  END LOOP;

  UTL_FILE.fclose(file_id);

END;
/

The solution from this note did not work as expected:

Note 255888.1 UTL_FILE.PUT_LINE Results In UTL_FILE.WRITE_ERROR Although Max Line Size Is Less Than 1023 Bytes

The Client NLS_LANG and ORA_NLS10 was set accordingly to match the Database server settings.

 

ORA_NLS10 must be set on both the Database AND listener process.

Bug 8412698 UTL_FILE.FFLUSH DOES NOT FLUSH THE BUFFER

 

 

Ensure the database has been started with ORA_NLS10 set. Also ensure the database listener has been restarted since the ORA_NLS10 was set.

To verify that ORA_NLS10 is set for your database + listener you can follow the details in Bug 8412698

In Summary:

On the Database server  (use the equivalent for your Operating system)

Find the PMON process

For Linux:

$ strings /proc/<pmon process ID>/environ  | grep NLS

For Solaris:

$ pargs -e <process ID> | grep NLS

If this does not return ORA_NLS10 and NLS_LANG then these have not been set when starting the database and/or listener.

Set ORA_NLS10 and restart both the database and listener.

 

Sometime the related directory may be full, then you need to Free up the space.

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 415 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply