Home » SQL & PL/SQL » SQL & PL/SQL » Network location for external table's file (Oracle 10g database)
Network location for external table's file [message #317685] Fri, 02 May 2008 08:40 Go to next message
ashetye.bi@googlemail.com
Messages: 2
Registered: May 2008
Location: Glasgow
Junior Member
Hi,

I am trying to import data from a csv file in oracle 10g database.

I created a directory object pointing to a folder on a system(Other than Oracle 10g server) in the network. This folder is shared and can be accessed from the Oracle 10g server. When I create an external table with the default directory as the shared folder it couldn't read the csv file from that folder ( Shared folder on another system).

But when I redefined the directory object on a local folder which was on Oracle 10g server, it could read from csv file in the local folder using external table.

My understanding is using external tables, one can only read files that are on the local machine i.e. Oracle 10g server and not on a different system.

Please correct me if i am wrong. Any further suggestions would be highly appreciated.

Regads,

Aniket
Re: Network location for external table's file [message #317689 is a reply to message #317685] Fri, 02 May 2008 09:41 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
From what i know, directory object can not access file over network. It can only access local folder on the server
Re: Network location for external table's file [message #317693 is a reply to message #317685] Fri, 02 May 2008 10:00 Go to previous message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Just found something while browsing on Metalink... it say that if you want to access network file, Oracle services on the server must be started as an Administrator user instead of Local System. Because LocalSystem don`t have access to network drive.

I also found something that you can tweak to access network drive with LocalSystem privileges. Found it too on Metalink, but don`t know if it really work

Here is a cut & paste of what i found

Quote:

HI,

Here's the story:

1) UNC names: Check out
http://support.microsoft.com/support/kb/articles/q122/7/02.asp
This describes how you get a service running under the LocalSystem account to access shares on another machine. Basically, you need to either add the share name to, \\hkey_local_machine\system\currentcontrolset\services\
lanmanserver\parameters\NullSessionShares

or you need to set

\\hkey_local_machine\system\currentcontrolset\services\lanmanserver\parameters\RestrictNullAccess to FALSE on the machine where the share lives.

2) Non-UNC names [drive letters]: By default, the OracleServiceSID runs under the local system account, and things running under the local system account can only access local hard drives when using drive letters. This is because when you map a drive after you log in, only the user who has logged in can see that drive. System services like Oracle can't see drive letters mapped by a user at the keyboard.
To get this to work, go to
Start->Settings->ControlPanel->Services->OracleServiceSID,
and hit the Startup button. Then click LogOnAs: ThisAccount, and enter a user's account name. This user should
have enough priviledge to run as a service and access the database files. Then, whenever the OracleServiceSID runs, it runs under that user's account and can see any drive letters that are automatically mapped when that user logs into NT.

Anyhow, generally it is recommnded not to use DBMS_LOB to access file on shared drives.

Regards,
Ashok CG


Previous Topic: update blob field
Next Topic: Is it Possible?
Goto Forum:
  


Current Time: Fri Dec 09 07:41:57 CST 2016

Total time taken to generate the page: 0.17016 seconds