自动化回滚脚本 Oracle

Question

自动化回滚脚本 Oracle

4

当将数据库代码发布到非开发数据库时，我使用以下方法：创建一个发布的sqlplus脚本，按顺序运行多个创建表/视图/序列/包等语句。我还应该创建回滚脚本，在部署或进一步使用过程中如果需要执行drop和其他语句。但手动创建回滚脚本非常麻烦。例如，当我放置...时。

alter table table_a add column some_column number(5);

将其转换为发布脚本。我必须放置。

alter table table_a drop column some_column;

将其转化为回滚脚本，反之亦然。

有没有方法可以优化（或者半自动优化）它？也许有一些Java/Python等库可以将DDL语句解析成逻辑部分？

也许有一些更好的方法用于发布/回滚PL/SQL代码？

- sev3ryn

你看过Oracle闪回吗？我认为你可以撤销DDL，但不是100％确定。也许有人可以对此发表评论。 - OldProgrammer

闪回，据我理解，是针对一些不同的解决方案。它也会回滚数据。例如，我已经发布了一个版本（向表中添加了列），然后用户向该表中插入了一些数据，然后用户决定基于此列的功能现在不应该发布，而是在以后的版本中发布。如果我使用闪回，则用户插入的数据将与我添加的列一起被删除。但我只想删除该列，保留数据。 - sev3ryn

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jon Heller · Accepted Answer

DBMS_METADATA_DIFF和一些元数据查询可以自动化此过程。

此示例演示了六种更改类型：1）添加列 2）增加序列 3）删除表 4）创建表 5）更改视图 6）分配范围。

create table user1.add_column(id number);
create table user2.add_column(id number);
alter table user2.add_column add some_column number(5);

create sequence user1.increment_sequence nocache;
select user1.increment_sequence.nextval from dual;
select user1.increment_sequence.nextval from dual;
create sequence user2.increment_sequence nocache;
select user2.increment_sequence.nextval from dual;

create table user1.drop_table(id number);

create table user2.create_table(id number);

create view user1.change_view as select 1 a from dual;
create view user2.change_view as select 2 a from dual;

create table user1.allocate_extent(id number);
create table user2.allocate_extent(id number);
insert into user2.allocate_extent values(1);
rollback;

您说得对，DBMS_METADATA_DIFF不适用于CREATE或DROP操作。尝试比较只在一个模式中存在的对象，会生成以下类似的错误消息：

ORA-31603: object "EXTRA_TABLE" of type TABLE not found in schema "USER1"
ORA-06512: at "SYS.DBMS_METADATA", line 7944
ORA-06512: at "SYS.DBMS_METADATA_DIFF", line 712

然而，通过以下脚本添加和删除对象可能很容易：

--Dropped objects
select 'DROP '||object_type||' USER1.'||object_name v_sql
from
(
    select object_name, object_type from dba_objects where owner = 'USER1'
    minus
    select object_name, object_type from dba_objects where owner = 'USER2'
);

V_SQL
-----
DROP TABLE USER1.DROPPED_TABLE

--Added objects
select dbms_metadata.get_ddl(object_type, object_name, 'USER2') v_sql
from
(
    select object_name, object_type from dba_objects where owner = 'USER2'
    minus
    select object_name, object_type from dba_objects where owner = 'USER1'
);

V_SQL
-----
  CREATE TABLE "USER2"."CREATED_TABLE" 
   (    "ID" NUMBER
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

可以使用以下SQL语句处理这些更改:

select object_name, object_type, dbms_metadata_diff.compare_alter(
    object_type => object_type,
    name1 => object_name,
    name2 => object_name,
    schema1 => 'USER2',
    schema2 => 'USER1',
    network_link1 => 'MYSELF',
    network_link2 => 'MYSELF') difference
from
(
    select object_name, object_type from dba_objects where owner = 'USER1'
    intersect
    select object_name, object_type from dba_objects where owner = 'USER2'
) objects;


OBJECT_NAME         OBJECT_TYPE    DIFFERENCE
-----------         -----------    ----------
ADD_COLUMN          TABLE          ALTER TABLE "USER2"."ADD_COLUMN" DROP ("SOME_COLUMN")
ALLOCATE_EXTENT     TABLE          -- ORA-39278: Cannot alter table with segments to segment creation deferred.
CHANGE_VIEW         VIEW           -- ORA-39308: Cannot alter attribute of view: SUBQUERY
INCREMENT_SEQUENCE  SEQUENCE       ALTER SEQUENCE "USER2"."INCREMENT_SEQUENCE" RESTART START WITH 3

以下是这些结果的一些注释：

ADD_COLUMN正常工作。
ALLOCATE_EXTENT可能是误报，我认为您不需要关心延迟段创建。它对您的系统影响极小。
CHANGE_VIEW根本不起作用。但与之前的元数据查询一样，应该有一种相对容易的方法可以使用DBA_VIEWS构建此脚本。
INCREMENT_SEQUENCE效果太好了。大多数情况下，应用程序不关心序列值。但有时当事情失调时，您需要更改它们。这个“RESTART START WITH”语法非常有帮助。您不需要删除或重新创建索引，也不需要多次操作“increment by”。这个语法并没有在12c手册中。事实上，我无法在Google上找到它的任何信息。看起来这个包使用了未记录的功能。

其他注意事项：

这个包有时会非常缓慢。
如果服务器上的网络链接有问题，则需要通过连接到两个服务器的本地实例运行它。
可能会有误报。有时它会返回一个只含空格的行。

完全自动化这个过程是可能的。但基于上述问题以及我对所有这类自动化工具的经验，您不应该100%信任它。