External tables and Service Account 1

[clair]

Hello everybody,
I'm not a DBA, I'm a System Admin. Our DBA has a problem: when Oracle is accessing the external table on another computer, it gives an error. Oracle runs under local admin account on the server. The directory on another machine, which hosts external tables, has the READ WRITE permissions for everybody.

My questions are:
1. Is it possible to have an external table on a different computer (not Oracle server)?
2. If yes, what modifications to permissions for the directory (which hosts external tables) should I set up?
3. How can you change the account under which Oracle runs? Do I need to make changes only in the Services (in Windows 2000) or somewhere in Oracle also?

Thank you very much for your help. Any input is appreciated.

Clair

 

[AOLEURO]

Hi

Based on too little information, it is hard to see, but have your DBA remembered to set UTL_FILE_DIR in parameter (pfile, spfile, memory)?

Oracle gives a warning about using files on remote servers - but it is possible.
Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[AOLEURO]

Hi again

Hmm must still be sleeping - forgot to write that Oracle gives a warning IN DOCUMENTATION about using files on remote servers - but it is possible.

You can change account on the services - and you do not need to change beside this place except if you grant lower account rights - then you have to check files and directory permissions on server files.

Windows sometimes hate LocalSystem to have access rights to remote servers ex. in schedule with AT command. I think I recall correctly that I changed to Administrator account. Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[jee]

I have a system that uses UTL_FILE to write to a shared folder on a "remote" computer and it works quite well. A couple of hints/points:

1) The database service needs to logon under user account that can access the network and has the necessary permissions to access the shared folder. Generally local accounts on the computer are NOT able to do this. Yes, you change the account for the database service under Services (Control Panel). Select the service, right-click, select Properties, go to the LogOn tab. The service will be probably be named something like OracleServicexxx, where xxx is your database name.

2) I have found that it is also necessary for the TNSListener service to log in under this same user account - and you do this the same way - under Services.

3) Make sure the services are stopped and restarted after making these changes so they logon to the new user account.

4) The folder on the remote computer that you wish to access must be set up as a shared folder, with appropriate permissions granted ON THE SHARE (not on the folder) so the user account mentioned above can access.

4) You (or your DBA) have probably already done this, but the UTL_FILE_DIR parameter in your Oracle parameters file can use UNC notation to reference the share:
UTL_FILE_DIR = \\computer-name\share-name

 

[AOLEURO]

Hi Jee

Just my words - give you a star for the fine description. Regards
Allan
Icq: 346225948
! If you can't prove it - don't bother try to teach me !

 

[clair]

Thank you so much guys!

Clair