Oracle different character set import export (GBK import UTF8)

Due to database migration, the original Oracle backup file needs to be imported into the new Oracle database, EXP export, imp error, found that the string length is too long error. Find out, the original database is the GBK character set (Oracle in the VARCHAR2 type of a Chinese character occupies two bits), the new database is the UTF8 character set (a man occupies three bits).

As a workaround, change the length of all fields containing VARCHAR2 in the original database in bulk.

The modification is divided into two steps, VARCHAR2 longest support 4000 length, if CARCHAR2 length exists more than 2667 (4000*2/3), can be modified to CLOB type, or verify the longest use length in the field, if less than 2667, adjust to 4000.

The bulk modification methods are as follows:

Create or replace procedure p_test as

CURSOR TEMP is
SELECT table_name, COLUMN_NAME, Ceil (DATA_LENGTH*3/2) data_length
From User_tab_columns
WHERE data_type = ' VARCHAR2 '
and Data_length < 2000;
STR VARCHAR2 (100): = ";
BEGIN
For S in TEMP LOOP
STR: = ' ALTER TABLE ' | | S.table_name | | ' MODIFY (' | | S.column_name | |
' VARCHAR2 (' | | Data_length | | ‘))‘;
Dbms_output. Put_Line (STR);
EXECUTE IMMEDIATE STR;

END LOOP;
END;

Note: If there is a function index of the field, the batch modification will cause an error to be deleted before the function index can be executed.

Finally, you export DMP, and then imp imports it.

Oracle different character set import export (GBK import UTF8)