You Asked
Hi Tom,
Could you please give me an example of using SEQUENCE(1,1) parameter in the create table definition of an external table ?
It seems that it is supported (Oracle9i Database Utilities, Chapter 11
section "Rejected Rows") talks about it but I haven't been able to use it.
I need this in the definition of the external table as I finally create a table using ctas from external table and the final table needs to have a unique sequence number for each record. I use CTAS with PARALLEL option and I'm hitting a bug where if I use the sequence.nextval in the embedded select, it gives me ORA-03113: end-of-file on communication channel error.
I had found a bug # "Bug 3321767 Dump possible (in xtyqbcb) from CTAS with a SEQUENCE" which is marked fixed in 9.2.0.6 but it still does not work with PARALLEL clause (I have reproduced it against a table with 9 million records with 9.2.0.6). Because I'm hitting this bug, I want to assign this unique id to the records in the external table definition so that I do not have to use a sequence in my ctas parallel statement. I can't share the test case as my current data is confidential (I can share the sql script and modify the data if you are interested ...).
Please note that I have already tried ctas without the parallel clause but with a parallel hint for the embedded select and the performance is 3 times "slower" than if I were to use a parallel clause with ctas
(I had tested this by removing the sequence from the embedded select).
Here's my test case for external table (using SEQUENCE) :
Please note that the data file has values for only l_name,p_name,n_name,c1,y1,x2 and y2 (not for d_id).
create table test_ext
( l_name varchar2(100),
p_name varchar2(100),
n_name varchar2(100),
x1 number,
y1 number,
x2 number,
y2 number ,
d_id number
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( records delimited by newline
LOGFILE log_file_dir:'rect_log.txt'
BADFILE log_file_dir:'rect_bad.txt'
fields terminated by ',' missing field values are null
(l_name,
p_name,
n_name,
x1,
y1,
x2,
y2,
d_id SEQUENCE(1,1))
)
location ('test_ext.data')
)PARALLEL 4
REJECT LIMIT UNLIMITED;
Table gets created.
select count(*) from test_ext
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "comma, char,
date, defaultif, decimal, double, float, integer, (, nullif, oracle_date,
oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw,
varcharc, zoned"
KUP-01008: the bad identifier was: SEQUENCE
KUP-01007: at line 12 column 11
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
Another realted question is :
If my external table definition has "parallel 8" and I do a ctas with parallel 8 clause against it, I still see only 1 select
if I run your showsql.sql script even though I see 16 processes IN USE in select * from v$px_process;
Not sure if the select against the external table is parallelized.
Same is the case with insert append select with a parallel 8 hint. Only 1 select against process (P000) is shown by showsql.sql.
My data file for external table has 43.5 million records and the file is on a solaris 9 machine.
Thanks,
Gunjeet
and Tom said...
whenever I'm stumped trying to figure out how to convert a sqlldr control file'ism to an external table, I use
sqlldr / test.ctl external_table=generate_only
and see what it says.... Here, it says "use a sequence object". consider:
LOAD DATA
INFILE *
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(empno, ename, job, mgr,
hiredate DATE(20) "DD-Month-YYYY",
sal, comm,
deptno CHAR TERMINATED BY ':',
projno,
loadseq SEQUENCE(MAX,1) )
that results in:
sequences created to simulate SEQ in control file:
------------------------------------------------------------------------
CREATE SEQUENCE SYS_SQLLDR_X_SEQ_000 MINVALUE 0 START WITH 0 INCREMENT BY 1
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_EMP"
(
EMPNO NUMBER(4),
...
PROJNO NUMBER
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test.bad'
LOGFILE 'test.log_xt'
READSIZE 1048576
SKIP 30
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
EMPNO CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
....
PROJNO CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'test.ctl'
)
)REJECT LIMIT UNLIMITED
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO EMP
(
EMPNO,
...
LOADSEQ
)
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO, PROJNO,
SYS_SQLLDR_X_SEQ_000.nextval
FROM "SYS_SQLLDR_X_EXT_EMP"
so, use an Oracle sequence to do this.
if you see 16 processes in use, then you are going parallel.
the select against the external tables are parallelized. do a select count(*) and see what you see.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment