| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: moving from unix to NT
> On NT oracle.exe runs as "OracleService<SID>" service.
> Hence UTL_FILE will be executed under whatever OS account
> "OracleService<SID>" service runs.
Well, I just tested it, and I can open a file on a local drive, but not on
a network
drive.
This is because the VOS is running as a service, somewhat as you stated. The Oracle instance itself does not run as a service.
The VOS ( virtual operating system ) does, and this is the code responsible for writing to disk.
Here's the workaround from Oracle. Maybe there's a step you were missing
in trying
to get the Oracle Service to run as a user other than SYSTEM?
I followed the instructions, and then successfully created a file on the
same network
drive where the attempt failed previously. Here's the code I used:
DECLARE
relink_log UTL_FILE.FILE_TYPE;
log_dir varchar2(80) := 'F:\tmp';
log_fname varchar2(80) := 'utl.txt';
BEGIN
relink_log := UTL_FILE.FOPEN(log_dir, log_fname, 'w');
if utl_file.is_open( relink_log ) then
utl_file.put_line(relink_log, 'The utl file is open');
UTL_FILE.FFLUSH(relink_log);
UTL_FILE.FCLOSE(relink_log);
else
raise_application_error(-20100,'Open failed on ' || log_dir || '/'
|| log_fname );
end if;
END;
/
HTH Jared
Doc ID:
Note:1034188.6
Subject:
INVALID_OPERATION Exception from UTL_FILE when
Writing to/from Network Drive
Type:
PROBLEM
Status:
PUBLISHED
Content Type:
TEXT/PLAIN
Creation Date:
30-JUL-1997
Last Revision
Date:
On the Windows NT platform, the UTL_FILE package raises the INVALID_OPERATION exception when attempting to write to, or read from, a network or compressed drive.
If the exception is not handled, then you receive the following error:
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "SCOTT.MYFILE", line 9
ORA-06512: at line 1
Problem Explanation
The process that is attempting to perform the read or write operation is the Oracle RDBMS Service. The OracleService<sid> service runs as the SYSTEM account by default. The SYSTEM account, however, has no access to shares that are set up by the user who is currently logged in.
Therefore, the Oracle RDBMS service can only read and write files on local hard drives.
Solution Description
Start the Oracle service as a user who has the same permissions as
SYSTEM,
and also who has access to the shared directory.
Solution Explanation
When the Oracle service is started, it is done so as the user SYSTEM. SYSTEM is unable to see any shared directories therefore UTL_FILE is restricted to accessing local drives only. Hence, Oracle must be started as a user who can see the relevant shares.
This user must have full permissions in order for Oracle to run
successfully. In addition, we recommend that you alter the Oracle
SQL*Net
Listener Service to log on as the same user.
For a full description refer to the following article:
[NOTE:45172.1] Running UTL_FILE on Windows NT
Another approach is to alter the SYSTEM user so that it can see shared directories, however, Microsoft does not recommend this because it is a security risk. Refer to the following Microsoft article:
' Q124184:Service Running as System Account Fails Accessing Network ' ( http://support.microsoft.com/support/kb/articles/q124/1/84.asp )
Reference
[BUG:493693] UTL_FILE CANNOT WRITE ON NETWORK DRIVE .
"Igor Neyman" <ineyman_at_perceptron.com>
Sent by: root_at_fatcity.com
02/28/02 11:49 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: Re: moving from unix to NT
Jared,
On NT oracle.exe runs as "OracleService<SID>" service.
Hence UTL_FILE will be executed under whatever OS account
"OracleService<SID>" service runs.
And I was not able to make it running properly under any other than SYSTEM
account.
Remember, on NT Oracle is one big (though multithreaded) process, unlike
it
is on UNIX.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
> I don't have any experience with UTL_FILE on NT. I've used it a little
> on unix. Don't know why it wouldn't work on NT. As stated by others,
> it might be a problem if writing to network drives, as the SYSTEM user
> does not have access to those when run as a service.
>
>
>
>
>
> > > > > >
> >
> To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: moving from unix to NT
> >
>
>
> > >
> >> >
> > -----Original Message-----
> > From: Jared.Still_at_radisys.com [SMTP:Jared.Still_at_radisys.com]
> > Sent: 27 February 2002 18:35
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: moving from unix to NT
> >
> > Perl is *much* more flexible than UTL_FILE for flat file operations.
> >
> > There is simply no basis for comparison.
> >
> > The question in your case is this: Can you easily replace the PL/SQL
> > procedures that are using UTL_FILE with a process that runs outside
> > of the database?
> >
> > If so, myself and others on this list can point you in the right
> > direction, as
> > basics in Perl/Oracle/DBI are really not too hard.
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Feb 28 2002 - 15:03:37 CST
 |
 |