将 Oracle ROWID 功能迁移到 Postgre SQL AWS

由 Rakesh Raghav (AWS) 和 Ramesh Pathuri () 创作 AWS

摘要

此模式描述了将 Oracle 数据库中的ROWID伪列功能迁移到适用于 Postgre 的亚马逊关系数据库服务（亚马逊）、亚马逊 A SQL urora Postg SQL re SQL 兼容版或亚马逊弹性计算云（亚马逊RDS）中的 Postgre 数据库或亚马逊弹性计算云（亚马逊）的 Postgre 数据库的选项。EC2

在 Oracle 数据库中，ROWID 伪列是表中某一行的物理地址。即使表中不存在主键，该伪列也用于唯一标识行。Postgre SQL 有一个类似的伪列ctid，名为，但它不能用作。ROWID正如 Postgre SQL 文档中所述，如果更新或在每个VACUUM过程之后，ctid可能会发生变化。

您可以通过三种方式在 Postgre 中创建ROWID伪列功能：SQL

使用主键列代替 ROWID 来标识表中的一行。
在表中使用逻辑主键/唯一键（可能是复合键）。
添加一个带有自动生成值的列，并使其成为模拟 ROWID 的主键/唯一键。

此模式将引导您完成所有三种实现，并描述每个选项的优缺点。

先决条件和限制

先决条件

一个活跃的AWS账户
程序Language/PostgreSQL (PL/pgSQL）编码专业知识
源 Oracle 数据库
SQL兼容 Amazon RDS for Postgre SQL 或 Aurora Postgre 的集群，或者用于托管 Postgre EC2 数据库的实例 SQL

限制

此模式为 ROWID 功能提供了解决方法。Postgre SQL 没有提供与 Oracle 数据库ROWID中的等效项。

产品版本

Postgre SQL 11.9 或更高版本

架构

源技术堆栈

Oracle Database

目标技术堆栈

Aurora Postgre SQL 兼容、适用RDS于 Postgre 的亚马逊SQL，或者带有 Postgre EC2 数据库的实例 SQL

实施选项

有三个选项可以解决 Postgre 中缺乏ROWID支持的问题SQL，具体取决于您的表是主键还是唯一索引、逻辑主键还是身份属性。您的选择取决于您的项目时间表、当前的迁移阶段以及对应用程序和数据库代码的依赖关系。

选项	描述	优点	劣势
主键或唯一索引	如果您的 Oracle 表具有主键，您可以使用该键的属性来唯一标识一行。	不依赖于专有数据库功能。对性能的影响最小，因为主键字段已编制索引。	需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到主键字段。
逻辑主键/唯一键	如果您的 Oracle 表具有逻辑主键，您可以使用该键的属性来唯一标识一行。逻辑主键由可以唯一标识行的一个属性或一组属性组成，但不会通过约束在数据库上强制执行。	不依赖于专有数据库功能。	需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到主键字段。如果未对逻辑主键的属性编制索引，则会对性能产生重大影响。不过，您可以添加唯一索引来防止出现性能问题。
标识属性	如果您的 Oracle 表没有主键，您可以创建一个附加字段作为 `GENERATED ALWAYS AS IDENTITY`。每当向表中插入数据时，此属性都会生成一个唯一的值，因此它可用于唯一标识数据操作语言 (DML) 操作的行。	不依赖于专有数据库功能。 Postgre SQL 数据库填充该属性并保持其唯一性。	需要更改依赖于 `ROWID` 的应用程序和数据库代码才能切换到标识属性。如果未对附加字段编制索引，则会对性能产生重大影响。不过，您可以添加一个索引来防止出现性能问题。

工具

适用于 Postgre 的亚马逊关系数据库服务（亚马逊RDS）SQL可帮助您在云中设置、操作和扩展 Postgre SQL 关系数据库。AWS
Amazon Aurora Postgre SQL 兼容版是一款完全托管、ACID兼容的关系数据库引擎，可帮助您设置、操作和扩展 Postgre 部署。SQL
AWSCommand Line Interface (AWSCLI) 是一个开源工具，可帮助您通过命令行外壳中的命令与AWS服务进行交互。在这种模式中，你可以使用AWSCLI来运行SQL命令pgAdmin。
pgAdmin是 Postgre SQL 的开源管理工具。它提供了一个图形界面，可帮助您创建、维护和使用数据库对象。
AWSSchema Conversion Tool (AWSSCT) 通过自动将源数据库架构和大部分自定义代码转换为与目标数据库兼容的格式来支持异构数据库迁移。

操作说明

任务描述所需技能

任务	描述	所需技能
确定使用该`ROWID`属性的 Oracle 表。	使用 AWS Schema Conversion Tool (AWSSCT) 来识别具有`ROWID`功能的 Oracle 表。有关更多信息，请参阅AWSSCT文档。 —或者— 在 Oracle 中，使用 `DBA_TAB_COLUMNS` 视图来标识具有 `ROWID` 属性的表。这些字段可用于存储 10 字节的字母数字字符。确定用法，并将其转换为 `VARCHAR` 字段（如果适用）。	DBA或开发人员
标识引用这些表的代码。	AWSSCT用于生成迁移评估报告，以确定受影响的程序`ROWID`。有关更多信息，请参阅AWSSCT文档。 —或者— 在源 Oracle 数据库中，使用 `dba_source` 表的文本字段来标识使用 `ROWID` 功能的对象。	DBA或开发人员

确定使用该ROWID属性的 Oracle 表。

使用 AWS Schema Conversion Tool (AWSSCT) 来识别具有ROWID功能的 Oracle 表。有关更多信息，请参阅AWSSCT文档。

—或者—

在 Oracle 中，使用 DBA_TAB_COLUMNS 视图来标识具有 ROWID 属性的表。这些字段可用于存储 10 字节的字母数字字符。确定用法，并将其转换为 VARCHAR 字段（如果适用）。

DBA或开发人员

标识引用这些表的代码。

AWSSCT用于生成迁移评估报告，以确定受影响的程序ROWID。有关更多信息，请参阅AWSSCT文档。

—或者—

在源 Oracle 数据库中，使用 dba_source 表的文本字段来标识使用 ROWID 功能的对象。

DBA或开发人员

任务描述所需技能

任务	描述	所需技能
标识没有主键的表。	在源 Oracle 数据库中，使用 `DBA_CONSTRAINTS` 标识没有主键的表。这些信息将帮助您确定每个表的策略。例如： `select dt.* from dba_tables dt where not exists (select 1 from all_constraints ct where ct.owner = Dt.owner and ct.table_name = Dt.table_name and ct.constraint_type = 'P' ) and dt.owner = '{schema}'`	DBA或开发人员

标识没有主键的表。

在源 Oracle 数据库中，使用 DBA_CONSTRAINTS 标识没有主键的表。这些信息将帮助您确定每个表的策略。例如：


select dt.*
from dba_tables dt
where not exists (select 1
                  from all_constraints ct
                  where ct.owner = Dt.owner
                    and ct.table_name = Dt.table_name
                    and ct.constraint_type = 'P'
                  )
and dt.owner = '{schema}'

DBA或开发人员

任务	描述	所需技能
对具有已定义主键或逻辑主键的表应用更改。	对其他信息部分中显示的应用程序和数据库代码进行更改，以使用唯一主键或逻辑主键来标识表中的行。	DBA或开发人员
向没有定义主键或逻辑主键的表添加附加字段。	添加 `GENERATED ALWAYS AS IDENTITY` 类型的属性。对其他信息部分中显示的应用程序和数据库代码进行更改。	DBA或开发人员
如有必要，请添加索引。	向附加字段或逻辑主键添加索引以提高SQL性能。	DBA或开发人员

其他信息

以下各节提供了 Oracle 和 Postgr SQL e 代码示例，以说明这三种方法。

场景 1：使用主唯一键

在以下示例中，您将创建表 testrowid_s1，并使用 emp_id 作为主键。

Oracle 代码：


create table testrowid_s1 (emp_id integer, name varchar2(10), CONSTRAINT testrowid_pk PRIMARY KEY (emp_id));
INSERT INTO testrowid_s1(emp_id,name) values (1,'empname1');
INSERT INTO testrowid_s1(emp_id,name) values (2,'empname2');
INSERT INTO testrowid_s1(emp_id,name) values (3,'empname3');
INSERT INTO testrowid_s1(emp_id,name) values (4,'empname4');
commit;

SELECT rowid,emp_id,name FROM testrowid_s1;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3pAAAAAAAMOAAA          1 empname1
AAAF3pAAAAAAAMOAAB          2 empname2
AAAF3pAAAAAAAMOAAC          3 empname3
AAAF3pAAAAAAAMOAAD          4 empname4

UPDATE testrowid_s1 SET name = 'Ramesh' WHERE rowid = 'AAAF3pAAAAAAAMOAAB' ;
commit;

SELECT rowid,emp_id,name FROM testrowid_s1;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3pAAAAAAAMOAAA          1 empname1
AAAF3pAAAAAAAMOAAB          2 Ramesh
AAAF3pAAAAAAAMOAAC          3 empname3
AAAF3pAAAAAAAMOAAD          4 empname4

邮政编SQL码：


CREATE TABLE public.testrowid_s1
(
    emp_id integer,
    name character varying,
    primary key (emp_id)
);

insert into public.testrowid_s1 (emp_id,name) values 
(1,'empname1'),(2,'empname2'),(3,'empname3'),(4,'empname4');

select emp_id,name from testrowid_s1;
 emp_id |   name   
--------+----------
      1 | empname1
      2 | empname2
      3 | empname3
      4 | empname4

update testrowid_s1 set name = 'Ramesh' where emp_id = 2 ;

select emp_id,name from testrowid_s1;
 emp_id |   name   
--------+----------
      1 | empname1
      3 | empname3
      4 | empname4
      2 | Ramesh

场景 2：使用逻辑主键

在以下示例中，您将创建表 testrowid_s2，并使用 emp_id 作为逻辑主键。

Oracle 代码：


create table testrowid_s2 (emp_id integer, name varchar2(10) );
INSERT INTO testrowid_s2(emp_id,name) values (1,'empname1');
INSERT INTO testrowid_s2(emp_id,name) values (2,'empname2');
INSERT INTO testrowid_s2(emp_id,name) values (3,'empname3');
INSERT INTO testrowid_s2(emp_id,name) values (4,'empname4');
commit;

SELECT rowid,emp_id,name FROM testrowid_s2;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3rAAAAAAAMeAAA          1 empname1
AAAF3rAAAAAAAMeAAB          2 empname2
AAAF3rAAAAAAAMeAAC          3 empname3
AAAF3rAAAAAAAMeAAD          4 empname4

UPDATE testrowid_s2 SET name = 'Ramesh' WHERE rowid = 'AAAF3rAAAAAAAMeAAB' ;
commit;

SELECT rowid,emp_id,name FROM testrowid_s2;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3rAAAAAAAMeAAA          1 empname1
AAAF3rAAAAAAAMeAAB          2 Ramesh
AAAF3rAAAAAAAMeAAC          3 empname3
AAAF3rAAAAAAAMeAAD          4 empname4

邮政编SQL码：


CREATE TABLE public.testrowid_s2
(
    emp_id integer,
    name character varying
);

insert into public.testrowid_s2 (emp_id,name) values 
(1,'empname1'),(2,'empname2'),(3,'empname3'),(4,'empname4');

select emp_id,name from testrowid_s2;
 emp_id |   name   
--------+----------
      1 | empname1
      2 | empname2
      3 | empname3
      4 | empname4

update testrowid_s2 set name = 'Ramesh' where emp_id = 2 ;

select emp_id,name from testrowid_s2;
 emp_id |   name   
--------+----------
      1 | empname1
      3 | empname3
      4 | empname4
      2 | Ramesh

场景 3：使用标识属性

在以下示例中，您将使用标识属性创建不带主键的表 testrowid_s3。

Oracle 代码：


create table testrowid_s3 (name varchar2(10));
INSERT INTO testrowid_s3(name) values ('empname1');
INSERT INTO testrowid_s3(name) values ('empname2');
INSERT INTO testrowid_s3(name) values ('empname3');
INSERT INTO testrowid_s3(name) values ('empname4');
commit;

SELECT rowid,name FROM testrowid_s3;
ROWID              NAME
------------------ ----------
AAAF3sAAAAAAAMmAAA empname1
AAAF3sAAAAAAAMmAAB empname2
AAAF3sAAAAAAAMmAAC empname3
AAAF3sAAAAAAAMmAAD empname4

UPDATE testrowid_s3 SET name = 'Ramesh' WHERE rowid = 'AAAF3sAAAAAAAMmAAB' ;
commit;

SELECT rowid,name FROM testrowid_s3;
ROWID              NAME
------------------ ----------
AAAF3sAAAAAAAMmAAA empname1
AAAF3sAAAAAAAMmAAB Ramesh
AAAF3sAAAAAAAMmAAC empname3
AAAF3sAAAAAAAMmAAD empname4

邮政编SQL码：


CREATE TABLE public.testrowid_s3
(
    rowid_seq bigint generated always as identity,
    name character varying
);

insert into public.testrowid_s3 (name) values 
('empname1'),('empname2'),('empname3'),('empname4');

select rowid_seq,name from testrowid_s3;
 rowid_seq |   name   
-----------+----------
         1 | empname1
         2 | empname2
         3 | empname3
         4 | empname4

update testrowid_s3 set name = 'Ramesh' where rowid_seq = 2 ;

select rowid_seq,name from testrowid_s3;
 rowid_seq |   name   
-----------+----------
         1 | empname1
         3 | empname3
         4 | empname4
         2 | Ramesh

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

将 Oracle 迁移 PeopleSoft 到亚马逊RDS定制版

将 Oracle 错误代码迁移到兼容亚马逊 Aurora Postgre SQL 的数据库

将 Oracle ROWID 功能迁移到 Postgre SQL AWS

摘要

先决条件和限制

架构

工具

操作说明

相关资源

其他信息