将 Oracle® 用户迁移到 Cloud SQL for PostgreSQL：数据类型、用户和表

本文档是系列文章中的一篇，该系列提供了关键的信息和指导来帮助规划和执行 Oracle® 11g/12c 数据库到 Cloud SQL for PostgreSQL 12 版的迁移。除了设置简介部分之外，本系列文章还包括以下部分：

数据类型

Cloud SQL for PostgreSQL 提供多种数据类型，这些数据类型完全等效于或类似于 Oracle 提供的数据类型。下表列出了最常见的 PostgreSQL 数据类型，接着比较了 Oracle 原初数据类型和对应的 Cloud SQL for PostgreSQL 数据类型；如果数据类型不受支持，则列出替代数据类型。

Cloud SQL for PostgreSQL 12 原初数据类型

Cloud SQL for PostgreSQL 数据类型系列 Cloud SQL for PostgreSQL 数据类型名称 数据类型规范
字符串/字符 CHAR(n) 恰好存储 n 个字符。 CHARACTER(n) 的别名。
CHARACTER(n) 恰好存储 n 个字符。
VARCHAR(n) 存储的字符数不定，最多可存储 n 个字符。CHARACTER VARYING(n) 的别名。
CHARACTER VARYING(n) 存储的字符数不定，最多可存储 n 个字符。
TEXT VARCHAR 的特定变体，无需您指定字符数上限。 可存储的最长字符串为 1 GB（适用于所有字符串字符数据类型）。
数字 SMALLINT 最小值为 -32768 | 最大值为 32767。
INTEGER 最小值为 -2147483648 | 最大值为 2147483647。
BIGINT 最小值为 -2^63 | 最大值为 2^63-1。
REAL 单精度浮点数（4 个字节）。
DOUBLE PRECISION 双精度浮点数（8 个字节）。
DECIMAL (p,s) 可存储任何有 p 位数和 s 位小数的值。
NUMERIC(p,s) 可存储任何有 p 位数和 s 位小数的值。
SMALLSERIAL 自动递增的整数。最小值为 1 | 最大值为 32767。
SERIAL 自动递增的整数。最小值为 1 | 最大值为 2147483647。
BIGSERIAL 自动递增的整数。最小值为 1 | 最大值为 2^63-1。
货币 MONEY 具有固定小数精度的货币金额。最小值为 -92233720368547758.08 | 最大值为 +92233720368547758.07。
日期和时间 DATE - 包含日期部分但没有时间部分的值。
- 支持的范围是 4713 BC 至 5874897 AD，精确到天。
TIMESTAMP (p) - 同时包含日期和时间部分的值。
- 支持的范围是 4713 BC 至 294276 AD。
- 单位为秒，精确到 p 位小数位（如果未指定，则为 1 微秒）。
TIMESTAMP (p) with time zone - 包含日期、时间和时区的值。
- 支持的范围是 4713 BC 至 294276 AD。
- 单位为秒，精确到 p 位小数位（如果未指定，则为 1 微秒）。
TIME (p) - 有时间部分但没有日期部分的值。
- 支持的范围是 00:00:00 至 24:00:00。
- 单位为秒，精确到 p 位小数位（如果未指定，则为 1 微秒）。
TIME (p) with time zone - 有时间和时区但没有日期部分的值。
- 支持的范围是 00:00:00 至 24:00:00。
- 单位为秒，精确到 p 位小数位（如果未指定，则为 1 微秒）。
INTERVAL (p) - 时间间隔
- 支持的范围是 -178000000 到 178000000 年。
- 单位为秒，精确到 p 位小数位（如果未指定，则为 1 微秒）。
JSON JSON 文本 JSON 数据。
JSONB 二进制 JSON 数据。
XML XML XML 数据。

几何图形		 GEOMETRY 要使用以下数据模型时指定的列类型。
POINT (x,y) 值。
LINE (A, B, C) 元组，其中 Ax + By + C = 0 并且 A 和 B 不同时为 0。

或

以下格式的点 1 和点 2：

[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
LSEG 线段 ((x1,y1),(x2,y2))。
BOX 矩形框 ((x1,y1),(x2,y2))。
PATH 一系列点 ((x1,y1),...)。
POLYGON 一系列点，这些点实际上形成一个闭合路径。
CIRCLE (x,y),r)（中心点和半径）
逻辑 BOOLEAN - 包含 true、false 或 null 值。
- yes、true、t、on、1 等值可以用来表示 true。
位/二进制数据 BIT (n) 固定长度位字符串。
BYTEA 二进制数据/字节数组。
网络地址类型 CIDER IPv4 和 IPv6 网络。
INET IPv4 和 IPv6 主机和网络。
MACADDR MAC 地址。
MACADDR8 MAC 地址（EUI-64 格式）。
其他 PG_LSN Cloud SQL for PostgreSQL 日志序列号。
TSQUERY 文本搜索查询。
TSVECTOR 文本搜索文档。
TXID_SNAPSHOT 用户级事务 ID 快照。
UUID 通用唯一标识符。

Oracle 到 Cloud SQL for PostgreSQL 数据类型转换

Oracle 数据类型系列 Oracle 数据类型名称 Oracle 数据类型规范 Cloud SQL for PostgreSQL 是否与 Oracle 等效 Cloud SQL for PostgreSQL 的对应/替代数据类型
字符串/字符 CHAR(n) 大小上限为 2000 个字节。 是 CHAR(n)
CHARACTER(n) 大小上限为 2000 个字节。 是 CHARACTER(n)
NCHAR(n) 大小上限为 2000 个字节。 否 CHAR(n)
VARCHAR(n) 大小上限为 2000 个字节。 是 VARCHAR(n)
NCHAR VARYING(n) 长度不定的 UTF-8 字符串
大小上限为 4000 个字节。		 否 CHARACTER VARYING(n)
VARCHAR2(n) 11g 大小上限为 4000 个字节，在 PL/SQL 中大小上限为 32 KB。 是* VARCHAR(n)
VARCHAR2(n)
12g		 大小上限为 32767 个字节 MAX_STRING_SIZE= EXTENDED。 是* VARCHAR(n)
NVARCHAR2(n) 大小上限为 4000 个字节。 否 VARCHAR(n)
LONG 大小上限为 2 GB。 是* TEXT
RAW(n) 大小上限为 2000 个字节。 是* BYTEA
LONG RAW 大小上限为 2 GB。 是* BYTEA
数字 NUMBER 浮点数。 是* NUMERIC(p,s)
NUMBER(*) 浮点数。 是* DOUBLE PRECISION
NUMERIC(p,s) 精确度介于 1 至 38 之间。 是 NUMERIC(p,s)
FLOAT(p,s) 浮点数。 是* DOUBLE PRECISION
DEC(p,s) 定点数。 是 DEC(p,s)
DECIMAL(p,s) 定点数。 是 DECIMAL(p,s)
INT 38 位整数。 是 INT
INTEGER 38 位整数。 是 INTEGER
SMALLINT 38 位整数。 是 SMALLINT
REAL 浮点数。 是* DOUBLE PRECISION
DOUBLE PRECISION 浮点数。 是 DOUBLE PRECISION
日期和时间 DATE 存储日期和时间数据（年、月、日、小时、分钟和秒）。 是* TIMESTAMP(0)
TIMESTAMP(p) 含小数的日期和时间。 是 TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE 含小数和时区的日期和时间。 是 TIMESTAMP (p) WITH TIME ZONE
INTERVAL YEAR(p) TO MONTH 时间间隔。 是* INTERVAL YEAR TO MONTH
INTERVAL DAY(p) TO SECOND(s) 日期和时间间隔。 是* INTERVAL DAY TO SECOND(s)
逻辑 BOOLEAN 值 TRUE、FALSE 和 NULL。无法分配给数据库表列。 是 BOOLEAN
XML XMLTYPE XML 数据。 否 XML
LOB BFILE 指向二进制文件的指针，大小上限为 4 GB。 否 VARCHAR(255)
CLOB 字符大型对象，文件大小上限为 4 GB。 否 TEXT
BLOB 二进制大型对象，大小上限为 4 GB。 否 BYTEA
NCLOB 长度不定的 Unicode 字符串，文件大小上限为 4 GB。 否 TEXT
ROWID ROWID 实际行地址。 否 CTID
UROWID(n) 逻辑行地址的通用行 ID。 否 CTID
空间 SDO_ GEOMETRY 空间对象的几何图形说明。 是 Postgres 有多种几何图形类型，包括 point、line、path、polygon、circle 等
SDO_TOPO_ GEOMETRY 描述拓扑几何图形。 是 PostGIS，一个广泛使用的 Postgres 扩展程序，可提供多个拓扑类型。
SDO_GEORASTER 光栅网格或图片对象存储在一行中。 是 PostGIS，一个广泛使用的 Postgres 扩展程序，可提供将光栅文件加载到数据库中的工具。
媒体类型 ORDDicom 支持医学数字成像和通信 (DICOM) 文件的存储和管理。 是，借助解决方法 Postgres 提供二进制类型 bytea 用于存储大型对象。
ORDDoc 支持任何类型的媒体数据的存储和管理。 是，借助解决方法 Postgres 提供二进制类型 bytea 用于存储大型对象。
ORDImage 支持图片数据的存储和管理。 是，借助解决方法 Postgres 提供二进制类型 bytea 用于存储大型对象。
ORDVideo 支持视频数据的存储和管理。 解决方法 Postgres 提供二进制类型 bytea 用于存储大型对象。

* Cloud SQL for PostgreSQL 中的对应数据类型与 Oracle 基本兼容，但具有不同的名称。

用户定义的类型

Oracle 将用户定义的类型 (UDT) 称为 OBJECT TYPES，通过 PL/SQL 进行管理。通过用户定义的类型，用户可以创建应用专用的复杂数据类型，这些类型基于内置的 Oracle 数据类型列表并通过该列表进行扩展。

Oracle 用户定义的类型 Oracle 概览或实现 Cloud SQL for PostgreSQL 支持 Cloud SQL for PostgreSQL 的对应或替代解决方案
抽象数据类型 (ADT) CREATE TYPE ADT_DEMO AS OBJECT (
ID NUMBER(6),
NAME VARCHAR2(20)
);		 部分支持，但语法不同 Cloud SQL for PostgreSQL 复合类型由属性名称和数据类型列表指定：

CREATE TYPE ADT_DEMO AS
(
ID NUMERIC(6),
NAME VARCHAR(20)
);
VARRAY 类型 CREATE TYPE VARRAY_DEMO AS VARRAY (5) OF VARCHAR2(25); 否 解决方法是 Cloud SQL for PostgreSQL 支持将列定义为数组：

CREATE TABLE VARRAY_TABLE (
NAME VARCHAR(25)[]
);
嵌套表类型 CREATE TYPE NTT_DEMO AS OBJECT (
ID NUMBER(6),
NAME VARCHAR2(20)
) ;
CREATE TYPE NTT_TABLE AS TABLE OF NTT_DEMO;		 否 解决方法是在 Cloud SQL for PostgreSQL 中使用复合类型和数组列定义的组合，以实现与 Oracle 的嵌套表类型类似的功能。

CREATE TYPE NTT_DEMO AS
(
ID NUMERIC(6),
NAME VARCHAR(20)
);
CREATE TABLE NTT_TABLE (
RECORD NTT_DEMO[]
);
不完整类型 不完整类型是没有属性和方法的类型。可被其他类型引用。但是，您必须先提供类型定义，然后才能使用。

CREATE TYPE INCOMPLETE_DEMO;

 否 N/A

用户

本部分介绍如何创建用户并分配权限，还介绍了将 Oracle 表转换为 Cloud SQL for PostgreSQL 表的必要性。

创建用户并授予权限

Oracle 数据库用户账号（Oracle“用户”和“架构”相同）可用于验证和连接数据库会话，而特定数据库对象/权限的授权访问权限在用户个人级别设置。

一般来说，有两种类型的数据库用户：

  • 管理员：管理数据库实例、用户和资源。
  • 用户账号：提供应用等逻辑操作。

管理员向用户和应用用户账号授予访问数据库对象的权限。针对特定操作（例如创建会话/连接）或特定数据库对象（例如针对特定表的 SELECT 或针对特定存储过程的 EXECUTE）向用户授予 Oracle 数据库权限。

Oracle 中区分用户和角色的概念。用户用于向数据库进行身份验证，而角色可提供一组可整体授予的权限。

在 Cloud SQL for PostgreSQL 中，用户和角色是同义词。Cloud SQL for PostgreSQL 用户是具有 connect 权限的角色。您可以使用 CREATE USER 或 CREATE ROLE 语句创建数据库用户。与 Oracle 不同，在 Cloud SQL for PostgreSQL 中，架构和用户是分别创建的。架构是对象（例如表、数据类型、函数等）的集合，并由用户拥有。

Oracle 创建用户
CREATE USER user_name IDENTIFIED BY password;
Cloud SQL for PostgreSQL 创建用户
CREATE USER user WITH PASSWORD 'password';

或

CREATE ROLE user WITH LOGIN PASSWORD 'password';

转换注意事项

  • 用户在根级别定义，对于同一 Cloud SQL for PostgreSQL 集群中的所有数据库均有效。这类似于 Oracle 12c 中的“共同用户”。
  • Cloud SQL for PostgreSQL CREATE USER 语法与 Oracle 不同，因此无法按原样迁移。Cloud SQL for PostgreSQL 中的 CREATE USER 是 CREATE ROLE 的别名，唯一的区别是 LOGIN 选项默认处于启用状态。

表

Oracle 表由列数据类型、表约束、索引、分区、Oracle 表专有功能等诸多元素构建而成。要成功迁移到 Cloud SQL for PostgreSQL 数据库表，必须将所有 Oracle 表元素转换为 Cloud SQL for PostgreSQL 表。某些元素无需修改或只需经过细微修改，而其他一些元素必须完全修改。

从迁移的角度来看，将 PL/SQL 转换为 Cloud SQL for PostgreSQL 可能需要更多的工作量，因为将 Oracle 表转换为 Cloud SQL for PostgreSQL 表是一个关键阶段，具有至关重要的意义，并且会进一步影响性能和数据大小。

以下是 Oracle 和 Cloud SQL for PostgreSQL 表和相关功能的主要差异。本系列的其余部分将讨论这些差异。

  • 创建表的语法
  • 表和索引元数据
  • 约束支持
  • 数据类型支持和限制
  • 索引
  • 分区和分区管理
  • 表和索引的维护
  • 临时表
  • 视图
  • 可见列和不可见列 (Oracle 12c)
  • 表和列的字符集

后续步骤