ORA-12899: value too large for column LONG_TYPE (actual: 30, maximum: 20)
导入的时候发现大量报错ORA-12899: value too large for column LONG_TYPE (actual: 30, maximum: 20)
原因是源端和目标端的字符集不一致。
[oracle@oem data]$ impdp admin/admin@192.168.52.129:1521/jyc dumpfile=adminEXPDP1.DMP DIRECTORY=dmp logfile=adminexpdp1-imp.log full=y TABLE_EXISTS_ACTION=REPLACE cluster=N REMAP_TABLESPACE=LIM:USERS
Import: Release 19.0.0.0.0 - Production on Tue Jun 3 12:15:21 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "admin"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions
Starting "admin"."SYS_IMPORT_FULL_01": admin/********@192.168.52.129:1521/jyc dumpfile=adminEXPDP1.DMP DIRECTORY=dmp logfile=adminexpdp1-imp.log full=y TABLE_EXISTS_ACTION=REPLACE cluster=N REMAP_TABLESPACE=LIM:USERS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
ORA-02374: conversion error loading table "admin"."DCM_LINE"
ORA-12899: value too large for column LONG_TYPE (actual: 30, maximum: 20)
ORA-02372: data for row: LONG_TYPE : 0X'B9FAC4DAB3A4CDBEA3A8B1B1BEA9B7A2C6F0A3A9'
ORA-02374: conversion error loading table "admin"."DCM_LINE"
ORA-12899: value too large for column LONG_TYPE (actual: 30, maximum: 20)
ORA-02372: data for row: LONG_TYPE : 0X'B9FAC4DAB3A4CDBEA3A8B1B1BEA9B7A2C6F0A3A9'
ORA-02374: conversion error loading table "admin"."DCM_LINE"
ORA-12899: value too large for column LONG_TYPE (actual: 30, maximum: 20)
ORA-02372: data for row: LONG_TYPE : 0X'B9FAC4DAB3A4CDBEA3A8B1B1BEA9B7A2C6F0A3A9'
ORA-02374: conversion error loading table "admin"."DCM_LINE"
ORA-12899: value too large for column LONG_TYPE (actual: 30, maximum: 20)
ORA-02372: data for row: LONG_TYPE : 0X'B9FAC4DAB3A4CDBEA3A8B1B1BEA9C2E4B5D8A3A9'
ORA-02374: conversion error loading table "admin"."DCM_LINE"
ORA-12899: value too large for column LONG_TYPE (actual: 30, maximum: 20)
ORA-02372: data for row: LONG_TYPE : 0X'B9FAC4DAB3A4CDBEA3A8B1B1BEA9C2E4B5D8A3A9'
ORA-02374: conversion error loading table "admin"."DCM_LINE"
ORA-12899: value too large for column LONG_TYPE (actual: 30, maximum: 20)
ORA-02372: data for row: LONG_TYPE : 0X'B9FAC4DAB3A4CDBEA3A8B1B1BEA9B7A2C6F0A3A9'
处理办法：修改目标端pdb的字符集，重新导入
[oracle@oem data]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 3 13:10:36 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OEM READ WRITE NO
4 JYC READ WRITE NO
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/users01.dbf
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/oem/system01.dbf
/u01/app/oracle/oradata/ORCL/oem/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/oem/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/oem/users01.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/oem/mgmt_deepdive.dbf
/u01/app/oracle/oradata/ORCL/oem/mgmt_ecm_depot1.dbf
/u01/app/oracle/oradata/ORCL/oem/mgmt.dbf
/u01/app/oracle/oradata/ORCL/jyc/system01.dbf
/u01/app/oracle/oradata/ORCL/jyc/sysaux01.dbf
/u01/app/oracle/oradata/ORCL/jyc/undotbs01.dbf
/u01/app/oracle/oradata/ORCL/jyc/users01.dbf
/u01/app/oracle/oradata/ORCL/jyc/ogg.dbf
/u01/app/oracle/oradata/ORCL/jyc/users02.dbf
/u01/app/oracle/oradata/ORCL/jyc/users03.dbf
/u01/app/oracle/oradata/ORCL/jyc/users04.dbf
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/jyc/users05.dbf
/u01/app/oracle/oradata/ORCL/jyc/users06.dbf
/u01/app/oracle/oradata/ORCL/jyc/users-bad.dbf
25 rows selected.
SQL> create pluggable database pdb1 admin user pdb1 identified by pdb1 create_file_dest='/u01/app/oracle/oradata/ORCL/pdb1';
create pluggable database pdb1 admin user pdb1 identified by pdb1 create_file_dest='/u01/app/oracle/oradata/ORCL/pdb1'
*
ERROR at line 1:
ORA-65165: missing or invalid path for file creation
/u01/app/oracle/oradata/ORCL/pdb1
ORA-01262: Stat failed on a file destination directory
Linux-x86_64 Error: 2: No such file or directory
SQL> !mkdir -p /u01/app/oracle/oradata/ORCL/pdb1
SQL> create pluggable database pdb1 admin user pdb1 identified by pdb1 create_file_dest='/u01/app/oracle/oradata/ORCL/pdb1';
Pluggable database created.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 OEM READ WRITE NO
4 JYC READ WRITE NO
5 PDB1 MOUNTED
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 open read write restricted;
Pluggable database altered.
SQL> alter session set container=pdb1;
Session altered.
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
SQL> alter database character set internal_use zhs16gbk;
Database altered.
SQL> alter pluggable database pdb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK