Note: This is now available as part of the Deployment Suite for Oracle that was released in Dec 2019.  Learn more about Redgate Change Control.  
Please contact us if you're trying to automate your Oracle database release process.

Expected time-frame for release: H2 2019

There are two approaches to database version control and deployments: state and migrations.

• State-based approach 
  The desired state of the database is stored in version control and compared to the target database at deployment time to generate the deployment script. 
  
  
• Migrations-based approach 
  Migration scripts (think of each migration script as a micro-deployment script for a changeset) are saved in version control. Crucially, these migration scripts can be customized at development time, which gives developers and/or DBAs full control over the exact script that will be used for deployments. The deployment script is constructed from these migration scripts. A log table in the target databases keeps track of which scripts have already been applied, so they only run once and don't have to be written idempotently.
  
  

Before this Deployment Suite for Oracle release, Redgate's Oracle solution only provided the state-based approach.  This model was chosen to provide the lowest overhead for development teams. It is conceptually simple, but doesn't allow custom migration logic to be specified by the developer or DBA. Where this logic is needed on a regular basis, the migrations model can be preferable. 

The state-based model works very effectively for the vast majority of changes, but there are a small number of changes that can't be inferred by a comparison engine, such as:

• Adding NOT NULL columns without a default
• Column/table splits/renames
• Transactional data updates
• Other "complex" refactorings

If these types of changes occur, the deployment script generated by the state-based approach needs manual customization before running. If these types of changes are infrequent, this may be acceptable and using Pre/Post SQL scripts in your deployment pipeline might be fine. If these types of changes are common, it becomes a chore. This is where the migrations-based deployment methodology is arguably better suited.

One downside to the migrations-based approach is the number of migrations scripts that can be generated. To address this, the EAP features "Programmable Objects" to treat procedures, functions, views and any "code" objects that don't impact data differently to structural changes. Instead of creating a new migration script when a programmable object is modified, the tool will simply update the "state model" and use this for the deployment. This approach vastly reduces the number of migration scripts created as part of development and makes it much easier to manage the project.

Please contact us if you're trying to automate your Oracle database release process.