Archives of the TeradataForum

Message Posted: Fri, 31 Oct 2003 @ 22:06:29 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I agree on getting the data back into Oracle. There are no ways to take an Oracle dump file and get it into Teradata.

The datatypes are also going to be a bit different. Number vs Decimal, VARCHAR2 vs VARCHAR...

If by chance you have Oracle, Teradata and SQL Server 2000, you could actually cut some corners to get the data imported into Teradata very quickly.

Here is what I have done in the past to get Oracle tables converted into Teradata.

First: Get the dump into Oracle... You have no choice... (can you use an Oracle dump on any other database???)

Second: Use the SQL Server DTS feature to import data into Teradata from the Oracle instance. This is great because the DTS feature in SQL Server will give you the option to create the tables. It will interpret the table from Oracle and do the transformation for you.

You do have to be careful that you have the correct datatypes set for the Teradata side, but you can always adjust once the data is over on the Teradata side.

From there you can run the DTS package or you can choose to not run the package at all. By the time you have completed building the package the tables will have already been built in preparation for the data exchange. (This is if you have a large amount of data to import.) If you do not have a large amount of data (aka 500,000 rows or less) then you can run the DTS.

Otherwise I would suggest that you extract the data table by table into a CSV or Tab delimited file and fastload the tables.

This is a bit outside the box but it works...

Another option is to use ERWIN to reverse engineer the Oracle database and then forward engineer into Teradata. Then do the extract and FastLoad. Mind how the Primary Indexes are applied. ERWIN tries to build the scripts as if it were for Teradata.

In a long winded way of answering your question... No you can't use a straight Oracle dump file to load into Teradata.

Chris Coffing
Senior Vice President
Coffing Data Warehousing
www.coffingdw.com

	<Prev	Next>		<<First	<Prev	Next>	Last>>