Database – Migrating from Oracle to Netezza and trying to see what datatype differences there are

You should have two choices to make :

1. Choose your database character set (used by VARCHAR2, CHAR, CLOB datatypes).
2. Choose your national character set (used by NVARCHAR2, NCHAR, NCLOB datatypes).

As seen here :

Oracle recommends using Unicode for all new system deployments.

National character sets can only be Unicode : UTF-8 or UTF-16. So choosing the same character set for both would be redundant...

My advice (you say your application is in English only) :

• Ask for your database character set to be UTF-8.
• Ask for your national character set to be UTF-16.

And here is my general advice for your schema definition. Table by table, column by column (I take the VARCHAR2/NVARCHAR2 sample here) :

• if your column could contain any character in the world (as in user input), make it NVARCHAR2.
• if you have control about what is going to be stored (English then), make it VARCHAR2.

You'd be better off using EXP/IMP or EXPDP/IMPDP if transferring from one Oracle database to another. They are covered in the Utilities manual

If you MUST use a CSV file, the unloader tool on the AskTom site. But be careful to check dates/timestamps and any exotic datatypes.