insert / select not getting all the rows

[RanD]

I'm hoping someone more familiar with ORACLE than I am (noob) can tell me what might be going wrong here.

The setup:
I'm trying to bring data from a TERADATA system to an ORACLE system via SQL Server's DTS data pump scripts.

There are 8 tables that I'm loading on the ORACLE system.
There are 8 DTS scripts that load each ORACLE table, 1 table per script.

When I execute the package, each load completes successfully. The script shows the number of rows loaded.
Teradata SELECTS always confirm that the number of rows displayed in the script as loaded matches the number of rows in the TERADATA table.

When I perform a SELECT COUNT(*) against my ORACLE tables everyone matches save one. It always contains less rows than the script indicates were loaded.

There are no duplicate rows in the load.

What follows is the Table DDL ... Note that the SYS_IL indexes are created by ORACLE when the table is created. I don't know why this happens - it doesn't happen on all the tables I created only some. I don't know what these do and I cannot drop them. I'm wondering if they might be playing a role in excluding some rows during the load.

CREATE TABLE TVFIELDSSTG (
SYS_ID VARCHAR2 (12) NOT NULL,
LOADDATE DATE NOT NULL,
TABLEID CHAR (12) NOT NULL,
FIELDNAME CHAR (30) NOT NULL,
FIELDID NUMBER (5) NOT NULL,
NULLABLE CHAR (1),
FIELDTYPE CHAR (2),
MAXLENGTH NUMBER (10),
DEFAULTVALUE CLOB,
DEFAULTVALUEI CLOB,
TOTALDIGITS NUMBER (5),
IMPLIEDPOINT NUMBER (5),
FIELDFORMAT CHAR (30),
FIELDTITLE CLOB,
COMMENTSTRING CLOB,
COLLATIONFLAG CHAR (1),
UPPERCASEFLAG CHAR (1),
DATABASEID CHAR (8) NOT NULL,
COMPRESSIBLE CHAR (1),
COMPRESSVALUE CLOB,
COLUMNCHECK CLOB,
CHECKCOUNT NUMBER (5) NOT NULL,
CREATEUID CHAR (8) NOT NULL,
CREATORNAME VARCHAR2 (30),
CREATETIMESTAMP DATE NOT NULL,
LASTALTERUID CHAR (8),
LASTALTERNAME VARCHAR2 (30),
LASTALTERTIMESTAMP DATE,
LASTACCESSTIMESTAMP DATE,
ACCESSCOUNT NUMBER (10),
SPPARAMETERTYPE CHAR (1) NOT NULL,
CHARTYPE NUMBER (5),
CLOSEDDATE DATE,
PRIMARY KEY ( TABLEID, FIELDID, DATABASEID, CREATEUID, CREATETIMESTAMP ) ) ;


CREATE UNIQUE INDEX SYS_IL0000033516C00009$$ ON
TVFIELDSSTG()
;

CREATE UNIQUE INDEX SYS_IL0000033516C00010$$ ON
TVFIELDSSTG()
;

CREATE UNIQUE INDEX SYS_IL0000033516C00014$$ ON
TVFIELDSSTG()
;

CREATE UNIQUE INDEX SYS_IL0000033516C00015$$ ON
TVFIELDSSTG()
;

CREATE UNIQUE INDEX SYS_IL0000033516C00020$$ ON
TVFIELDSSTG()
;

CREATE UNIQUE INDEX SYS_IL0000033516C00021$$ ON
TVFIELDSSTG()
;

CREATE INDEX TABLEIDS_IDX ON
TVFIELDSSTG(TABLEID)
;

CREATE INDEX TABLE_FIELDS_IDX ON
TVFIELDSSTG(TABLEID, FIELDID)
;


The DTS package's SELECT statement follows -
(The insert into part of the statement is implied)

SELECT SYS_ID, LOADDATE, TABLEID, FIELDNAME, FIELDID, NULLABLE, FIELDTYPE, MAXLENGTH, DEFAULTVALUE, DEFAULTVALUEI, TOTALDIGITS, IMPLIEDPOINT, FIELDFORMAT, FIELDTITLE, COMMENTSTRING, COLLATIONFLAG, UPPERCASEFLAG, DATABASEID, COMPRESSIBLE, COMPRESSVALUE, COLUMNCHECK, CHECKCOUNT, CREATEUID, CREATORNAME, CREATETIMESTAMP, LASTALTERUID, LASTALTERNAME, LASTALTERTIMESTAMP, LASTACCESSTIMESTAMP, ACCESSCOUNT, SPPARAMETERTYPE, CHARTYPE, CLOSEDDATE
FROM SANDBOX.TVFIELDSSTG;

Thanks in advance for any help you can provide ;-)

RanD

 

[51m0n]

"It always contains less rows than the script indicates were loaded."

are you shure the rows are missing? maybe the loader shows the wrong number of rows.

 

[carp]

Is it possible some rows wound up in your bad/reject files? SQLLoader may have tried to load a row, but it might have hit an exception of some sort and wound up in the reject pile instead.

 

[OraCool]

Well,

Do this on teradata:
select count (distinct TABLEID, FIELDID, DATABASEID, CREATEUID, CREATETIMESTAMP ) from TVFIELDSSTG;

Then do the following (On Teradata):
select count (*) from TVFIELDSSTG;

If the second result is more than the forst then you have a duplicate somewhere.