 ORA-29283: invalid file operation [message #407633] |
Wed, 10 June 2009 21:58  |
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 #407708 is a reply to message #407633] |
Thu, 11 June 2009 03:51  |
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
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
