Thanks for the question, Partheeban.
Asked: June 24, 2016 - 8:28 pm UTC
Last updated: March 09, 2019 - 2:32 am UTC
Version: Oracle 10g
Viewed 10K+ times! This question is
You Asked
Hi
I am explaining from beginning,
My Server is : UNIX BOX (HPSA)
I have created the below folder in this UNIX server and folder permissions are
mm13pb:/ $ ls -ltr
drwxr-xr-x 7 root root 4096 Dec 15 2013 data
mm13pb:/data $ ls -ltr
drwxrwxrwx 13 root sys 4096 Oct 29 2015 download
mm13pb:/data/download $ ls -ltr
drwxrwxr-x 31 uid_load users 8192 Jun 24 16:29 uid_load
mm13pb:/data/download/uid_load $ ls -ltr
drwxrwxr-x 2 uid_load users 4096 Jun 24 20:18 ETL_DATA_DIRECTORY
mm13pb:/data/download/uid_load/ETL_DATA_DIRECTORY $ ls -ltr
-rwxrwxr-x 1 uid_load users 145473300 Jun 24 15:29 RXD_ACCOUNT_MAPPING_AUTO.CSV
--Create UNIX Folder directory
--data\download\uid_load\ETL_DATA_DIRECTORY
--Create Oracle Directory
CREATE OR REPLACE DIRECTORY ETL_DATA_DIRECTORY AS '/data/download/uid_load/ETL_DATA_DIRECTORY';
--Grant READ and WRITE permission to this ETL_DATA_DIRECTORY in Oracle.
GRANT READ, WRITE ON DIRECTORY ETL_DATA_DIRECTORY TO UID_LOAD;
--CREATE EXTERNAL TABLE for RXD_ACCOUNT_MAPPING_AUTO.CSV
CREATE TABLE UID_OWNER_ETL.RXDACCTMAP_EXT_TAB
(
RBSG_SORT_CODE NUMBER,
RBSG_ACCOUNT_NUM NUMBER,
BRAND VARCHAR2(10),
RBSG_TARGET_SORT_CODE NUMBER,
WG_TARGET_SORT_CODE NUMBER,
WG_TARGET_ACCOUNT_NUM NUMBER,
SWITCH_RAINBOW_INDICATOR NUMBER,
SEPARATION_STATUS NUMBER,
ACCOUNT_PLATFORM VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ETL_DATA_DIRECTORY
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
MISSING FIELD VALUES ARE NULL
(
RBSG_SORT_CODE,RBSG_ACCOUNT_NUM,BRAND,RBSG_TARGET_SORT_CODE,WG_TARGET_SORT_CODE,WG_TARGET_ACCOUNT_NUM,SWITCH_RAINBOW_INDICATOR,SEPARATION_STATUS,ACCOUNT_PLATFORM
)
)
LOCATION('RXD_ACCOUNT_MAPPING_AUTO.CSV')
)
parallel 5
reject limit unlimited;
GRANT SELECT ON UID_OWNER_ETL.RXDACCTMAP_EXT_TAB TO UID_LOAD;
---
---
mm13pb:/data/download/uid_load/ETL_DATA_DIRECTORY $ sqlplus /
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jun 24 20:54:52 2016
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options
SQL> SELECT COUNT(*) FROM UID_OWNER_ETL.RXDACCTMAP_EXT_TAB;
SELECT COUNT(*) FROM UID_OWNER_ETL.RXDACCTMAP_EXT_TAB
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P002
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file RXDACCTMAP_EXT_TAB_6533.log
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER", line 19
This issue is from production server I am unable to get the count of oracle external table, can you please help me
thanks
Partheeban.J
and Connor said...
Being able to *read* the file is insufficient in this case, because as you query an external table, we also want to *write* a log file. Since you are using the 'DEFAULT DIRECTORY' clause, we will write the log file to the same location as your file.
Either that location needs to be read/write, or you can specify an explicit (different) directory for the log file.
Hope this helps.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment