plsql - automating rollback script oracle

Question

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.

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?

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

Answer 1

DBMS_METADATA_DIFF and a few metadata queries can automate this process.

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;

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" 

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 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.

Some other notes:

• The package can be very slow sometimes.
• If network links on the server are a problem you will need to run it through a local instance with links to both servers.
• There may be false positives. Sometimes it returns a row with just a space in it.

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%.