Thanks for the question, Pardeep.
Asked: September 02, 2015 - 1:02 pm UTC
Last updated: September 15, 2015 - 2:17 am UTC
Version: Oracle 11g R2
Viewed 1000+ times
You Asked
Hi Tom,
I have written a framework for processing text files using external table, using shell scripts and procedures. Below is the flow:
1. User places the file in a particular location on server.
2. File watcher is used to monitor the location and in event of a new file arrival it will call a shell script.
3. This shell script is responsible to check if the file is with proper naming convention and validated values of trailer against information present in the file.
4. After successful validation, it removes the trailer and splits the file into smaller files based on configuration and finally for each split file it calls a procedure.
5. Procedure will create external table dynamically and update count of records in file status monitoring table and later submits a job to call another procedure2.
6. Procedure-2 has to first load data by joining other production tables, into a GTT.
7. Then we perform some validations and all the failure records are written to new reject file, and external table is dropped. Also all the valid records are added to final set of tables.
I didn't face any problems in my development environment, below configuration:
OS: Solaris 10
Oracle 11g release 2 patch 4.
But on pre-production system(Oracle Database Appliances), with following configuration:
OS: Oracle unbreakable linux
Oracle 11g release 2 latest patch.
We are facing problems, when we are having 5 files getting processed in parallel few of them works correctly and few throws below error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file ACTIVITY_IMINT_90000128_28072015_00001.txt_1.log
After debugging found that it is failing when we try to read data from external table in step 5 to take the count form external table. Fixed that by removing parallel option from external table create command.
Now when we are loading data into GTT by joining External table with regular tables, some of which are partitioned and parallel is enabled at table level, I am getting the same error.
Also tried disabling logfile creation for external table by using NOLOGFILE option, and it started giving same error for the data file.
Now it works 80% of time but failing otherwise. I can make it work if I dump all the data from external table to a new temp table, and then move it to GTT.
I want to know if there are any other options I can try.
Thanks,
Pardeep
and Chris said...
Joining a table with parallel enabled to one without can still result in the query executing in parallel. To prevent this either disable parallel on the other tables or supply the no_parallel hint.
create table t1 (x integer);
create table t2 (y integer) parallel 2;
explain plan for
select * from t1, t2 where y = x;
select * from table(dbms_xplan.display(null, null, 'BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 711838933
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PX COORDINATOR | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 |
| 3 | HASH JOIN BUFFERED | |
| 4 | BUFFER SORT | |
| 5 | PX RECEIVE | |
| 6 | PX SEND HYBRID HASH | :TQ10000 |
| 7 | STATISTICS COLLECTOR | |
| 8 | TABLE ACCESS FULL | T1 |
| 9 | PX RECEIVE | |
| 10 | PX SEND HYBRID HASH | :TQ10001 |
| 11 | PX BLOCK ITERATOR | |
| 12 | TABLE ACCESS FULL | T2 |
------------------------------------------------
explain plan for
select /*+ no_parallel */* from t1, t2 where y = x;
select * from table(dbms_xplan.display(null, null, 'BASIC'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1838229974
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH JOIN | |
| 2 | TABLE ACCESS FULL| T1 |
| 3 | TABLE ACCESS FULL| T2 |
-----------------------------------