自动回滚脚本 oracle

2021-12-24 00:00:00 oracle plsql release-management

将数据库代码发布到非开发数据库时，我使用这种方法 - 我创建发布 sqlplus 脚本，该脚本按顺序运行多个 create table/view/sequence/package/etc 语句.如果在部署或进一步使用期间需要，我还应该创建执行 drop 和其他语句的回滚脚本.但是总是手动创建回滚脚本很烦人.IE.- 当我把

When releasing database code to non-development databases , I use such approach - I create release sqlplus script that runs multiple create table/view/sequence/package/etc statements in a sequence. I also should create rollback script which performs drop and other statements if would be needed during deployment or further use. But it is quite annoying always to create rollback scripts manually. I.E. - when I put 

alter table table_a add column some_column number(5);

进入发布脚本.我必须放

into release script. I have to put

alter table table_a drop column some_column;

进入回滚脚本.反之亦然.

into the rollback script. And vice-versa.

有没有办法优化(或半优化)它?也许有一些 Java/Python/etc 库允许将 ddl 语句解析为逻辑部分?

Is there way to optimize(or semi-optimize) it? Maybe some there are some Java/Python/etc libraries that allow to parse ddl statements into logical parts?

也许有一些更好的方法来发布/回滚 pl/sql 代码?

Maybe there are some better approaches for release/rollback pl/sql code?

推荐答案

DBMS_METADATA_DIFF 和一些元数据查询可以自动执行此过程.

DBMS_METADATA_DIFF and a few metadata queries can automate this process.

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

This example demonstrates 6 types of changes: 1) adding a column 2) incrementing a sequence 3) dropping a table 4) creating a table 5) changing a view 6) allocating an extent.

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.尝试区分仅存在于一个模式中的对象将生成错误消息像这样:

You are correct that DBMS_METADATA_DIFF does not work for CREATE or DROP. Trying to diff an object that only exists in one schema will generate an error message like this:

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

但是，删除和添加对象可能很容易使用以下脚本编写:

However, dropping and adding objects may be easy to script with the following:

--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 语句处理改变:

The alters can be handled with a SQL statement like this:

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

关于这些结果的一些说明:

Some notes about these results:

  • ADD_COLUMN 按预期工作.
  • ALLOCATE_EXTENT 可能是误报，我怀疑您是否关心延迟段的创建.它不太可能影响您的系统.
  • CHANGE_VIEW 根本不起作用.但与之前的元数据查询一样，使用 DBA_VIEWS 构建此脚本应该有一种相对简单的方法.
  • INCREMENT_SEQUENCE 效果太好了.大多数情况下，应用程序并不关心序列值.但有时当事情不同步时，你需要改变它们.这个 RESTART START WITH 语法非常有用.您不需要删除或重新创建索引，也不需要多次使用 increment by .此语法不在 12c 手册中.事实上，我无法在 Google 上的任何地方找到它.看起来这个包使用了未记录的功能.
  • ADD_COLUMN works as expected.
  • ALLOCATE_EXTENT is probably a false positive, I doubt you care about deferred segment creation. It is very unlikely to affect your system.
  • CHANGE_VIEW does not work at all. But as with the previous metadata queries, there should be a relatively easy way to build this script using DBA_VIEWS.
  • INCREMENT_SEQUENCE works too well. Most of the time an application does not care about the sequence values. But sometimes when things get out of sync you need to change them. This RESTART START WITH syntax can be very helpful. You don't need to drop or re-create the indexes, or mess with the increment by multiple times. This syntax is not in the 12c manual. In fact, I cannot find it anywhere on Google. Looks like this package is using undocumented features.

其他一些注意事项:

  • 有时打包速度会很慢.
  • 如果服务器上的网络链接有问题，您将需要通过一个本地实例运行它，并链接到两台服务器.
  • 可能存在误报.有时它返回一行，其中只有一个空格.

可以完全自动化此过程.但根据上述问题，以及我使用所有此类自动化工具的经验，您不应该 100% 信任它.

It is possible to fully automate this process. But based on the issues above, and my experience with all such automated tools, you should not trust it 100%.

相关文章