Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29283: invalid file operation (10g Release 2, SunOS)
icon7.gif  ORA-29283: invalid file operation [message #407633] Wed, 10 June 2009 21:58 Go to next message
brusko_zaragoza
Messages: 2
Registered: May 2009
Location: Philippines
Junior Member

Hi Experts,

Need your HELP and assistance to resolved error ORA-29283: invalid file operation. Done a lot of solutions in the net but still got an error. Thanks in advance for your advice.


Please find below details of the error.

select dump_csv('SELECT Start_Date,
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at "OPS$ORADBA.DUMP_CSV", line 17
ORA-06512: at line 1


SQL>


Below are details and test done:

1) In UNIX: /tmp folder does exist.

aesdbb1raqx1 2% cd /tmp
aesdbb1raqx1 3% ls -ld
drwxrwxrwt 14 root sys 11461 Jun 8 18:39 .
aesdbb1raqx1 4% pwd
/tmp
aesdbb1raqx1 5%

2) SQL> create directory EXT_TABLES as '/tmp';
Directory created.


3) SQL> select * from ALL_DIRECTORIES where DIRECTORY_NAME='EXT_TABLES';

OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS EXT_TABLES
/tmp

4) Tried to execute the ff PL/SQL block and works fine:

SQL> declare
2 FILE_HANDLE utl_file.file_type;
3 begin
4 FILE_HANDLE := utl_file.fopen('EXT_TABLES','file1.txt','w');
5 utl_file.put_line(FILE_HANDLE, 'This is sample text');
6 utl_file.fclose(FILE_HANDLE);
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
aesdbb1raqx1 2% cd /tmp
aesdbb1raqx1 3% ls file*
file1.txt
aesdbb1raqx1 4% cat file1.txt
This is sample text
aesdbb1raqx1 5%



Below are the codes and errors:


select dump_csv('SELECT Start_Date,
Num_Logs,
to_char(Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2),''999999999'') AS Mbytes
FROM (SELECT To_Char(Vlh.First_Time,''MM-W-YYYY'') AS Start_Date,
COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time,''MM-W-YYYY'')) log_hist,
( select distinct bytes from V$log ) Vl
ORDER BY Log_Hist.Start_Date',',','EXT_TABLES','redo_histor y.log')
from dual;


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

SQL> select dump_csv('SELECT Start_Date,
2 Num_Logs,
3 to_char(Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2),''999999999'')
4 FROM (SELECT To_Char(Vlh.First_Time,''MM-W-YYYY'') AS Start_Date,
5 COUNT(Vlh.Thread#) Num_Logs
6 FROM V$log_History Vlh
7 GROUP BY To_Char(Vlh.First_Time,''MM-W-YYYY'')) log_hist,
8 ( select distinct bytes from V$log ) Vl
9 ORDER BY Log_Hist.Start_Date',',','EXT_TABLES','redo_histor y.log')
10 from dual;
select dump_csv('SELECT Start_Date,
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at "OPS$ORADBA.DUMP_CSV", line 17
ORA-06512: at line 1


SQL>
Re: ORA-29283: invalid file operation [message #407634 is a reply to message #407633] Wed, 10 June 2009 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 22728
Registered: January 2009
Senior Member
http://www.dbforums.com/oracle/1643745-ora-29283-invalid-file-operation.html
Re: ORA-29283: invalid file operation [message #407642 is a reply to message #407633] Wed, 10 June 2009 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 58959
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: ORA-29283: invalid file operation [message #407708 is a reply to message #407633] Thu, 11 June 2009 03:51 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I see the signal to noise ratio is heading south again.....

One thing to check - have you granted READ and WRITE on the directory EXT_TABLES to the OPS$ORADBA user.

can you post the code of the DUMP_CSV function - if that's where the error is happening then it'd be useful to see what it's doing
Previous Topic: Using SYS tables as a normal user
Next Topic: Order of Data Dispaly / printing has changed!
Goto Forum:
  


Current Time: Wed Sep 03 01:24:37 CDT 2014

Total time taken to generate the page: 0.06335 seconds