How To: Abandon a Single Changeset from a Script

Instead of abandoning all changesets from a single script, there is desire to abandon just one changeset.

For example: A script got packaged which generated 5 different changesets. Now, we only want to abandon one of those 5 changesets.

Another documentation talks about how to abandon all changesets resulting from a script (How To: Abandon changes). But this would abandon all 5 changesets, per our example.

Considerations

Several considerations must be taken into account.

Only DDL scripts (scripts committed into the "ddl" directory) will generate multiple changesets.

This means that a unique changeset will be generated for each object that the DDL script operates on.
Here is an example: a script created two changesets because it contains SQL code for creating two different tables:

customer_billing_SEPT.sql

--------------------------------------------------------
--  DDL for Tables: 
--					customer_billing_address
--					customer_billing_details
--------------------------------------------------------

CREATE TABLE "PPADM"."customer_billing_address" 
   (	"ID" NUMBER(*,0), 
	"FIRST_NAME" VARCHAR2(50 BYTE), 
	"MIDDLE_NAME" VARCHAR2(50 BYTE), 
	"LAST_NAME" VARCHAR2(50 BYTE)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "PPDEFAULT" ;
REM INSERTING into PPADM.customer_billing_address
SET DEFINE OFF;


CREATE TABLE "PPADM"."customer_billing_details" 
   (	"ID" NUMBER(*,0), 
	"FIRST_NAME" VARCHAR2(50 BYTE), 
	"MIDDLE_NAME" VARCHAR2(50 BYTE), 
	"LAST_NAME" VARCHAR2(50 BYTE)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "PPDEFAULT" ;
REM INSERTING into PPADM.customer_billing_details
SET DEFINE OFF;

Scripts committed into any other directory (data_dml, function, package, packagebody, procedure, sql_direct) will result in only one changeset for that script.
- Here is how a changeset would look like which generated from a script committed into "data_dml" directory:

Considerations

Step-by-step guide

Related articles