| External Tables [message #635236] |
Wed, 25 March 2015 07:20  |
 |
ORA2015
Messages: 49
Registered: March 2015
|
Member |
|
|
Hello,
I am trying to create an external table. The table shows as being created but i am getting the following error when i try to access the data in the table using Toad:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file ADMINS111_3228.log
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER", line 19
A directory was created on the linux5.5 machine where the 10gdatabase resides. I was granted access to the share where the file is placed.
CREATE TABLE ADMINS111
(
EMP_ID NUMBER,
ENAME VARCHAR2(20),
DNAME VARCHAR(20)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY NLEASE
ACCESS PARAMETERS
( records delimited by newline
fields terminated by ','
)
LOCATION (NLEASE:'names.txt')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
The contents of my names.txt text file is below:
7499,ALLEN,SALESMAN
7521,WARD,SALESMAN
7654,MARTIN,SALESMAN
7844,TURNER,SALESMAN
The directory was created in linux by owner :sys with name NLEASE and path /home/pear/nlease/
I am accessing it through share in windows: \\fruits\
[Updated on: Wed, 25 March 2015 07:23] Report message to a moderator |
|
|
|
| Re: External Tables [message #635238 is a reply to message #635236] |
Wed, 25 March 2015 07:26  |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
External table reads file as OS user oracle (as OS user oracle was installed under, to be precise). Therefore OS user oracle must have read permission on file itself and all directories on directory path to that file.
SY.
|
|
|
|
| Re: External Tables [message #635239 is a reply to message #635238] |
Wed, 25 March 2015 07:45 |
|
ORA2015
Messages: 49
Registered: March 2015
|
Member |
|
|
SY, can you give me more details as to what permissions exactly i must have. I don;t understand what your're saying.
The file was grated read, write access to the group everyone as well as the user as to how i connect to on toad say with name 'pear'.
When you say OS user 'oracle' what exactly do you mean?
[Updated on: Wed, 25 March 2015 07:46] Report message to a moderator |
|
|
|
| Re: External Tables [message #635240 is a reply to message #635239] |
Wed, 25 March 2015 07:55 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
External table is database server side tool, not client side tool. So first of all file must reside in directory accessible from database server, not your client machine. Secondly, as I already mentioned, Oracle will try reading this file as database server OS user oracle, so OS user oracle must have read permission on file itself and all directories on the path to the file. In your case it appears you created Oracle directory object NLEASE pointing to database server OS directory /home/pear/nlease and file name is names.txt. Therefore database server OS user oracle must have read permission on /home directory, /home/pear directory, /home/pear/nlease directory and file /home/pear/nlease/names.txt itself.
SY.
|
|
|
|
| Re: External Tables [message #635241 is a reply to message #635236] |
Wed, 25 March 2015 08:06 |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
This is really confusing. If I understand you correctly, your database is running on a Windows machine, and you are trying to use a file in a directory exported from a Linux SMB server. Is that correct? If so, your Oracle process is probably running under local system account, and there is no reason why the SMB server would permit that to have any access.
|
|
|
|
|
|
|
|
| Re: External Tables [message #635247 is a reply to message #635243] |
Wed, 25 March 2015 08:58 |
 |
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
|
Senior Member |
|
|
ORA2015 wrote on Wed, 25 March 2015 07:12JW,
Sorry for any confusion. My 10g database is running on a Linux 5.5 machine.
A share was created through Samba for me to use in Windows XP, to access the directory and for me to place and edit my text file.
Just to be clear, the \\fruits\ share is on your Windows XP workstation and the directory the share points to is on the actual database server (Linux 5.5 machine) - is this correct?
As others have mentioned, the Linux Oracle user account must have Read and Write permissions on the "/home/pear/nlease/names.txt" directory and file. Additionally, the Linux Oracle user account must have read/write permissions on the location you have specified where the "log" file will be written - since this is the error you are receiving...
Quote:KUP-04063: unable to open log file ADMINS111_3228.log
OS error Permission denied
Since you didn't specify the log location (using: LOGFILE <file name>) in your External Table create SQL, it is using the default location which your Oracle user clearly doesn't have permissions on.
Also, what about rows that are rejected? You're not logging those so how will you know if you have all of the expected rows from the file?
Craig...
[Updated on: Wed, 25 March 2015 09:02] Report message to a moderator |
|
|
|
| Re: External Tables [message #635249 is a reply to message #635247] |
Wed, 25 March 2015 09:38 |
|
ORA2015
Messages: 49
Registered: March 2015
|
Member |
|
|
Craig,
i will arrange getting the oracle permission with the DBA.
But is it a requirement to include the log and bad file in the create statement.
Also,
if i include it in the statement how exactly do i create the files in the directory and how do i give it its naming. i am confused about that.
[Updated on: Wed, 25 March 2015 09:40] Report message to a moderator |
|
|
|
|
|
| Re: External Tables [message #635266 is a reply to message #635265] |
Wed, 25 March 2015 14:00 |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You have exactly the problem SY said. The oracle owner cannot get to the nlease directory because only user pear can get to the parent directory.
|
|
|
|
| Re: External Tables [message #635268 is a reply to message #635265] |
Wed, 25 March 2015 14:07 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Again, in order for OS user oracle to locate file /home/pear/nlease/names.txt, OS user oracle needs to read /home directory to locate pear, then read /home/pear directory to locate nlease, then read /home/pear/nlease directory to locate names.txt and ONLY then open file names.txt for read or write. All you showed is OS user oracle is able to open file names.txt for read or write. That is not sufficient. If OS user oracle can't traverse reading directory tree to get to names.txt ability to open it for read/write is useless.
SY.
|
|
|
|
|
|
| Re: External Tables [message #635274 is a reply to message #635249] |
Wed, 25 March 2015 17:14 |
 |
EdStevens
Messages: 1376
Registered: September 2013
|
Senior Member |
|
|
ORA2015 wrote on Wed, 25 March 2015 09:38
But is it a requirement to include the log and bad file in the create statement.
What does the documentation say?
BADFILE | NOBADFILE
The BADFILE clause names the file to which records are written when they cannot be loaded because of errors. For example, a record was written to the bad file because a field in the data file could not be converted to the datatype of a column in the external table. Records that fail the LOAD WHEN clause are not written to the bad file but are written to the discard file instead. Also, any errors in using a record from an external table (such as a constraint violation when using INSERT INTO...AS SELECT... from an external table) will not cause the record to be written to the bad file.
The purpose of the bad file is to have one file where all rejected data can be examined and fixed so that it can be loaded. If you do not intend to fix the data, then you can use the NOBADFILE option to prevent creation of a bad file, even if there are bad records.
If you specify BADFILE, then you must specify a file name or you will receive an error.
If neither BADFILE nor NOBADFILE is specified, then the default is to create a bad file if at least one record is rejected. The name of the file will be the table name followed by _%p, and it will have an extension of .bad.
http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#SUTIL1394
Quote:
Also,
if i include it in the statement how exactly do i create the files in the directory and how do i give it its naming. i am confused about that.
YOU don't crate the file. The external table processor creates it. You simply provide the name. Again, see the above that I quoted straight from the docs.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: External Tables [message #636169 is a reply to message #636144] |
Sat, 18 April 2015 14:19 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
ORA2015 wrote on Fri, 17 April 2015 14:32Hello,
Are there any risks to the server by users having read access to it and read and write access to the file name?
Same as users having read access to tables. You don't grant read on directory if files residing in directory have sensitive data. And if by users you mean OS users, granting rear/write on directory object to oracle user gives to rights to OS users. Oracle reads files as OS user oracle (user Oracle was installed under, to be precise).
SY.
|
|
|
|
|
|
| Re: External Tables [message #636221 is a reply to message #636220] |
Mon, 20 April 2015 06:30 |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
But does your user have only read access? All too often, he has read and write. That means that he can write a shell script with utl_file and execute it with dbms_scheduler. Or (much more subtle) nominate the shell script as a pre-processor for the external table.
|
|
|
|
|
|
| Re: External Tables [message #636279 is a reply to message #636221] |
Mon, 20 April 2015 13:07 |
|
ORA2015
Messages: 49
Registered: March 2015
|
Member |
|
|
Quote:But does your user have only read access? All too often, he has read and write. That means that he can write a shell script with utl_file and execute it with dbms_scheduler. Or (much more subtle) nominate the shell script as a pre-processor for the external table.
Read and write access is just on the text file that the external table is pointing to within the directory.
Read access to the directory.
That is what needs to be done for the external tables to be working.
Is that a good practice?or acceptable in terms of security on the server. I did not see any documentation where it poses a threat to the server but just want to make sure
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
