Heterogeneous databases support different data types. DTS maps a data type in the source database to a data type supported by the destination database during schema migration. This topic lists the data type mappings for you to evaluate the impact of data migration on your business.
For more information about data migration scenarios, see Overview.
Migrate data from a user-created Oracle database to a user-created MySQL database, an ApsaraDB RDS for MySQL database, or an ApsaraDB for POLARDB database
| Data type in the Oracle database | Data type in the MySQL database | Supported by DTS |
|---|---|---|
| varchar2(n [char/byte]) | varchar(n) | Yes |
| nvarchar2[(n)] | national varchar[(n)] | Yes |
| char[(n [byte/char])] | char[(n)] | Yes |
| nchar[(n)] | national char[(n)] | Yes |
| number[(p[,s])] | decimal[(p[,s])] | Yes |
| float(p)] | double | Yes |
| long | longtext | Yes |
| date | datetime | Yes |
| binary_float | decimal(65,8) | Yes |
| binary_double | double | Yes |
| timestamp[(fractional_seconds_precision)] | datetime[(fractional_seconds_precision)] | Yes |
| timestamp[(fractional_seconds_precision)]with localtimezone | datetime[(fractional_seconds_precision)] | Yes |
| timestamp[(fractional_seconds_precision)]with localtimezone | datetime[(fractional_seconds_precision)] | Yes |
| clob | longtext | Yes |
| nclob | longtext | Yes |
| blob | longblob | Yes |
| raw | varbinary(2000) | Yes |
| long raw | longblob | Yes |
| bfile | N/A | No |
| interval year(year_precision) to month | N/A | No |
| interval day(day_precision)to second[(fractional_seconds_precision)] | N/A | No |
Note
- A char column with a length greater than 255 bytes is converted to the varchar(n) type.
- Data types such as bfile, interval year to month, and interval day to second in Oracle
databases are not supported in MySQL databases. They cannot be converted to data types
supported by the destination database during schema migration.
The schema migration fails if the table to be migrated contains these three data types. You must make sure that columns with these three data types are excluded from the objects to be migrated.
- The timestamp data type of MySQL databases does not contain the time zone information. However, the timestamp with time zone and timestamp with local time zone data types in Oracle databases provide time zone information. Therefore, DTS converts the values of these data types based on the time zone to UTC time for storage in the destination instance.
Migrate data from a user-created Oracle database to a database under a DRDS instance
| Data type in the Oracle database | Data type in the DRDS database | Supported by DTS |
|---|---|---|
| varchar2(n [char/byte]) | varchar(n) | Yes |
| nvarchar2[(n)] | national varchar[(n)] | Yes |
| char[(n [byte/char])] | char[(n)] | Yes |
| nchar[(n)] | national char[(n)] | Yes |
| number[(p[,s])] | decimal[(p[,s])] | Yes |
| float(p)] | double | Yes |
| long | longtext | Yes |
| date | datetime | Yes |
| binary_float | decimal(65,8) | Yes |
| binary_double | double | Yes |
| timestamp[(fractional_seconds_precision)] | datetime[(fractional_seconds_precision)] | Yes |
| timestamp[(fractional_seconds_precision)]with localtimezone | datetime[(fractional_seconds_precision)] | Yes |
| timestamp[(fractional_seconds_precision)]with localtimezone | datetime[(fractional_seconds_precision)] | Yes |
| clob | longtext | Yes |
| nclob | longtext | Yes |
| blob | longblob | Yes |
| raw | varbinary(2000) | Yes |
| long raw | longblob | Yes |
| bfile | N/A | No |
| interval year(year_precision) to month | N/A | No |
| interval day(day_precision)to second[(fractional_seconds_precision)] | N/A | No |
Note
- A char column with a length greater than 255 bytes is converted to the varchar(n) type in the DRDS database.
- The timestamp data type of DRDS databases does not contain the time zone information. However, the timestamp with time zone and timestamp with local time zone data types in Oracle databases provide time zone information. Therefore, DTS converts the values of these data types based on the time zone to UTC time for storage in the destination instance.
Migrate data from a user-created Oracle database to an ApsaraDB RDS for PPAS database
| Data type in the Oracle database | Data type in the ApsaraDB RDS for PPAS database | Supported by DTS |
|---|---|---|
| varchar2(n [char/byte]) | varchar2[(n)] | Yes |
| nvarchar2[(n)] | nvarchar2[(n)] | Yes |
| char[(n [byte/char])] | char[(n)] | Yes |
| nchar[(n)] | nchar[(n)] | Yes |
| number[(p[,s])] | number[(p[,s])] | Yes |
| float(p)] | double precision | Yes |
| long | long | Yes |
| date | date | Yes |
| binary_float | real | Yes |
| binary_double | double precision | Yes |
| timestamp[(fractional_seconds_precision)] | timestamp[(fractional_seconds_precision)] | Yes |
| timestamp[(fractional_seconds_precision)]with time zone | timestamp[(fractional_seconds_precision)]with time zone | Yes |
| timestamp[(fractional_seconds_precision)]with local time zone | timestamp[(fractional_seconds_precision)]with time zone | Yes |
| clob | clob | Yes |
| nclob | nclob | Yes |
| blob | blob | Yes |
| raw | raw(size) | Yes |
| long raw | long raw | Yes |
| bfile | N/A | No |
| interval year(year_precision) to month | interval year to month | No |
| interval day(day_precision) to second[(fractional_seconds_precision)] | interval day to second[(fractional_seconds_precision)] | No |
Note The timestamp[(fractional_seconds_precision)]with local time zone data type in Oracle
databases is not supported in ApsaraDB RDS for PPAS databases. DTS converts the values
of these data types based on the time zone to UTC time before storing them in the
timestamp[(fractional_seconds_precision)]with time zone data type in the destination
instance.